PostgreSQL tutorial for beginners

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. The following is based on the Debian operating system. Other operating systems really don't have the energy, but most of it should be universal.

installs

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, the PostgreSQL server will be automatically opened on port 5432 of the machine after installation.

If you also want to install a graphical management interface, you can run the following command, but this article does not cover that.

sudo apt-get install pgadmin3

adds new users and a new database

1. After the initial installation, a database named postgres and a database user named postgres are generated by default. It is important to note that an Linux system user named postgres was also generated.
Next, we use postgres users to generate other users and new databases. There are several ways to do this, and here are two.

2. The first method is to use the PostgreSQL console.

First, create a new Linux user with whatever name you want, dbuser in this case.
sudo adduser dbuser    

Then, switch to the postgres user.
sudo su - postgres
Next, log into the PostgreSQL console using the psql command.

psql

This is equivalent to the system user postgres as the database user of the same name, login to the database, which is not required to enter a password. If all goes well, the prompt will change to "postgres=#", indicating that you have entered the database console. The following commands are done in the console.
The first thing is to set a password for the postgres user using the \password command.
\password postgres

The second thing is to create the database user dbuser (the Linux system user was just created) and set the password.

CREATE USER dbuser WITH PASSWORD 'password';

The third thing is to create the user database, which in this case is exampledb, and specify the owner as dbuser.

CREATE DATABASE exampledb OWNER dbuser;

The fourth thing is to give dbuser all the privileges of the exampledb database. Otherwise, dbuser can only log into the console and has no database operation rights.

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

Finally, exit the console using the \q command (you can also directly press ctrl+D).
\q

3. The second method USES the shell command line.

Adding new users and new databases can be done from the shell command line, in addition to the PostgreSQL console. This is because PostgreSQL provides the command line programs createuser and createdb. Take the example of dbuser and exampledb.
First, create the database user dbuser and specify it as the superuser.

sudo -u postgres createuser --superuser dbuser
Then, log in to the database console, set the password for the dbuser user, and exit the console when completed.

sudo -u postgres psql
\password dbuser
\q

Next, on the shell command line, create the database exampledb and specify the owner as dbuser.
sudo -u postgres createdb -O dbuser exampledb

login database

1. After adding a new user and a new database, log in to the database as the new user, using the psql command.
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
The arguments to the above command have the following meaning: -U specifies the user, -d specifies the database, -h specifies the server, and -p specifies the port.
After entering the above command, the system prompts for the password of the dbuser user. Enter it correctly and you are ready to log on to the console.
The psql command has a shorthand form. If the current Linux system user is also an PostgreSQL user, you can omit the user name (part of the -U parameter). For example, if my Linux system user name is ruanyf and the PostgreSQL database has the same name, then I can log into Linux system as ruanyf and log into the database directly using the following command without requiring a password.

psql exampledb
At this point, even the database name can be omitted if a database with the same name as the current system user still exists inside PostgreSQL. For example, if a database called ruanyf exists, type psql directly to log into the database.
psql
Also, if you want to restore external data, use the following command.
psql exampledb < exampledb.sql

The console command

1. In addition to the previously used \password command (set password) and \q command (exit), the console provides a series of other commands.
\h: view the explanation of the SQL command, e.g. \h select.
\ & # 63; : view the list of psql commands. \l: list all databases.
\c [database_name] : connect to other databases.
\d: lists all the tables for the current database.
\d [table_name] : lists the structure of a table.
\du: list all users.
\e: open a text editor.
\conninfo: lists the current database and connection information.

database operations

The 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 records 
SELECT * FROM user_tbl;
#  Update the data 
UPDATE user_tbl set name = ' Li si ' WHERE name = ' Zhang SAN ';
#  Delete records 
DELETE FROM user_tbl WHERE name = ' Li si ' ;
#  Add a column 
ALTER TABLE user_tbl ADD email VARCHAR(40);
#  Update the structure 
ALTER TABLE usertbl ALTER COLUMN signupdate SET NOT NULL;
#  The name field 
ALTER TABLE usertbl RENAME COLUMN signupdate TO signup;
#  Delete the column 
ALTER TABLE user_tbl DROP COLUMN email;
#  Table name  
ALTER TABLE usertbl RENAME TO backuptbl;
#  Delete the form 
DROP TABLE IF EXISTS backup_tbl;

Precautions

This experience is based on the Debian operating system