Since MySQL was acquired by Oracle, PostgreSQL has gradually become the first choice for open source relational databases.
This article introduces the installation and basic usage of PostgreSQL for first-time users to get started. The following content is based on the Debian operating system. Other operating systems really don’t have the energy to take into account, but most of the content should be generally applicable.
Install
1. First, install the PostgreSQL client.
sudo apt-get install postgresql-client
Then, install the PostgreSQL server.
sudo apt-get install postgresql
2. Under normal circumstances, after the installation is completed, the PostgreSQL server will automatically be turned on port 5432 of the machine.
If you also want to install the graphical management interface, you can run the following commands, but this article does not cover this aspect.
sudo apt-get install pgadmin3
Add new users and new databases
1. After the first installation, a database named postgres and a database user named postgres are generated by default. It should be noted here that a Linux system user named postgres is also generated.
Below, we use the postgres user to generate other users and new databases. There are several ways to achieve this goal, and two are introduced here.
2. The first method is to use the PostgreSQL console.
First, create a new Linux user and you can choose the name you want, which is dbuser here.
sudo adduser dbuser
Then, switch to the postgres user.
sudo su - postgres
Next, use the psql command to log in to the PostgreSQL console.
psql
This is equivalent to the system user postgres logging into the database as the database user with the same name, without entering a password. If everything is OK, the system prompt will change to "postgres=#", indicating that you have entered the database console at this time. The following commands are completed in the console.
The first thing is to use the \password command to set a password for the postgres user.
\password postgres
The second thing is to create the database user dbuser (the one I just created is a Linux system user) and set the password.
CREATE USER dbuser WITH PASSWORD 'password';
The third thing is to create a user database, here is exampledb, and specify the owner as dbuser.
CREATE DATABASE exampledb OWNER dbuser;
The fourth thing is to assign all permissions of the exampledb database to dbuser, otherwise the dbuser can only log in to the console and does not have any database operation permissions.
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
Finally, use the \q command to exit the console (you can also press ctrl+D directly).
\q
3. The second method is to use the shell command line.
Adding new users and new databases can also be done under the shell command line in addition to within the PostgreSQL console. This is because PostgreSQL provides command line programs createuser and createb. Let’s take the newly created user dbuser and database exampleb as an example.
First, create the database user dbuser and specify it as a superuser.
sudo -u postgres createuser --superuser dbuser
Then, log in to the database console, set the password of the dbuser user, and exit the console after completion.
sudo -u postgres psql
\password dbuser
\q
Next, under the shell command line, create the database exampledb and specify the owner as dbuser.
sudo -u postgres createdb -O dbuser exampledb
Log in to the database
1. After adding a new user and a new database, you must log in to the database in the name of the new user. At this time, the psql command is used.
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
The parameters of the above command are as follows: -U specifies the user, -d specifies the database, -h specifies the server, and -p specifies the port.
After entering the above command, the system will prompt to enter the password of the dbuser user. Enter correctly and you can log in to the console.
The psql command exists in the abbreviation form. If the current Linux system user is also a PostgreSQL user, the user name (the part of the -U parameter) can be omitted. For example, my Linux system user name is ruanyf, and there is a user with the same name on the PostgreSQL database. After I log into the Linux system as ruanyf, I can directly use the following command to log in to the database without a password.
psql exampledb
At this time, if there is a database with the same name as the current system user in PostgreSQL, even the database name can be omitted. For example, if there is a database called ruanyf, you can log in to the database by simply typing psql.
psql
In addition, if you want to restore external data, you can use the following command.
psql exampledb <
Console Commands
1. In addition to the \password command (set password) and \q command (exit) that have been used earlier, the console also provides a series of other commands.
\h: Check the explanation of SQL commands, such as \h select.
\?: Check the psql command list. \l: List all databases.
\c [database_name]: Connect to other databases.
\d: List all tables in the current database.
\d [table_name]: Lists the structure of a certain table.
\du: List all users.
\e: Open the text editor.
\conninfo: Lists the current database and connection information.
Database operations
1The basic database operation is to use the general SQL language.
# Create a new table
CREATE TABLE usertbl(name VARCHAR(20), signupdate DATE);
# Insert data
INSERT INTO usertbl(name, signupdate) VALUES('Zhang San', '2013-12-22');
# Select a recordSELECT * FROM user_tbl;
# Update dataUPDATE user_tbl set name = 'Li Si' WHERE name = 'Zhang San';
# Delete recordsDELETE FROM user_tbl WHERE name = 'Li Si' ;
# Add a columnALTER TABLE user_tbl ADD email VARCHAR(40);
# Update the structureALTER TABLE usertbl ALTER COLUMN signupdate SET NOT NULL;
# Change the name columnALTER TABLE usertbl RENAME COLUMN signupdate TO signup;
# Delete the columnALTER TABLE user_tbl DROP COLUMN email;
# Table name changeALTER TABLE usertbl RENAME TO backuptbl;
# Delete the formDROP TABLE IF EXISTS backup_tbl;
Things to note
This experience is based on Debian operating system