Getting started with PostgreSQL

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 the first time users. The following content is based on the Debian operating system. Other operating systems do not have the energy to take into account, but most of the content should be universally applied.

Install

1. First, install the PostgreSQL client.

Sudo apt-get install postgresql-client

Then, install the PostgreSQL server.

Sudo apt-get install postgresql

2. Normally, after the installation is complete, the PostgreSQL server will automatically enable port 5432 on the local machine.

If you want to install the graphic management interface, run the following command, but this article does not cover this aspect.

Sudo apt-get install pgadmin3

Add new users and databases

1. After the initial installation, a database named S and a database user named postgres are generated by default. Note that a Linux user named S is also generated.
Next, we use postgres users to generate other users and new databases. There are several methods to achieve this purpose. Here we will introduce two methods.

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

First, create a new Linux User. You can use the name you want. Here it is dbuser.
Sudo adduser dbuser

Then, switch to the postgres user.
Sudo su-postgres
Next, use the psql command to log on to the PostgreSQL console.

Psql

In this case, the System user ipvs logs on to the database as the database user with the same name. No Password is required. If everything is normal, the system prompt will change to "s = #", indicating that the database console has been started. The following commands are all completed in the console.
The first thing is to use the \ password command to set a password for the postgres user.
\ Password S

The second thing is to create a database user dbuser (just created a Linux User) and set a password.

Create user dbuser with password 'Password ';

The third thing is to create a user database. Here is exampledb and the owner is specified as dbuser.

Create database exampledb OWNER dbuser;

The fourth thing is to grant all permissions of the exampledb database to dbuser. Otherwise, dbuser can only log on to the console without any database operation permissions.

Grant all privileges on database exampledb to dbuser;

Finally, use the \ q command to exit the console (or press ctrl + D ).
\ Q

3. Method 2: Use shell command line.

Add new users and new databases. In addition to the PostgreSQL console, you can also add new users and new databases using the shell command line. This is because PostgreSQL provides the command line program createuser and createdb. Take dbuser and exampledb as examples.
First, create a database user dbuser and specify it as a Super User.

Sudo-u postgres createuser -- superuser dbuser
Then, log on to the Database Console, set the password of the dbuser user, and then exit the console.

Sudo-u postgres psql
\ Password dbuser
\ Q

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

Log on to the database

1. After adding a new user and a new database, You need to log on to the database in the name of a new user. The psql command is used.
Psql-U dbuser-d exampledb-h 127.0.0.1-p 5432
The parameter Meanings of the preceding command are as follows:-U specifies the user,-d specifies the database,-h specifies the server, and-p specifies the port.
After entering the preceding command, the system will prompt you to enter the password of dbuser. Enter the correct information to log on to the console.
Psql commands are abbreviated. If you are a Linux user and a PostgreSQL user, you can omit the user name (part of the-U parameter ). For example, if my Linux user name is ruanyf and the PostgreSQL database has a user with the same name, after logging on to the Linux system as ruanyf, you can directly use the following command to log on to the database, password is not required.

Psql exampledb
In this case, if PostgreSQL still has a database with the same name as the current system user, the database name can be omitted. For example, if a database named ruanyf exists, you can directly type psql to log on to the database.
Psql
In addition, to restore external data, use the following command.
Psql exampledb <exampledb. SQL

Console Commands

1. In addition to the previous \ password Command (SET password) and \ q command (exit), the console also provides a series of other commands.
\ H: view the explanations of SQL commands, such as \ h select.
\? : View the psql command list. \ L: list all databases.
\ C [database_name]: connects to other databases.
\ D: list all tables of the current database.
\ D [table_name]: list the structure of a table.
\ Du: list all users.
\ E: Open the text editor.
\ Conninfo: lists information about the current database and connection.

Database Operations

1. A basic database operation is to use a common SQL language.

# Create table usertbl (name VARCHAR (20), signupdate DATE); # INSERT data INTO usertbl (name, signupdate) VALUES ('zhang san ', '1970-12-22 '); # SELECT * FROM user_tbl; # UPDATE data UPDATE user_tbl set name = 'lily' WHERE name = 'zhangsan '; # DELETE record delete from user_tbl WHERE name = 'lily'; # ADD a column alter table user_tbl ADD email VARCHAR (40); # update the structure alter table usertbl alter column signupdate set not null; # RENAME the column alter table usertbl rename column signupdate TO signup; # Delete the column alter table user_tbl drop column email; # RENAME the table alter table usertbl rename to backuptbl; # delete a table drop table if exists backup_tbl;

Notes

This experience is based on the Debian Operating System