Preface

PostgreSQL is an open source object relational database management system (ORDBMS). This paper aims to introduce some basic operations of PostgreSQL (hereinafter referred to as pg) for beginners'reference and reference. It should be noted that all the following operations were performed in Ubuntu 16.04, using version 9.6 of PG. For different operating systems and versions, there may be some differences in the operation process. See the reference document below for details.

install

Ubuntu integrates pg by default and can be installed directly from the command line

The following commands install the client and server of pg separately

$ sudo apt-get install postgresql-client
$ sudo apt-get install postgresql

After the installation is completed, the pg can be started by following commands

$ sudo service postgresql start

Correspondingly, as long as the start is changed to stop and restart, the service can be stopped and restarted.

Create a database

After the initial installation, pg defaults to create a database user named Postgres and a database named postgres, as well as a Linux system user named postgres. In fact, the Postgres database users created here have super administrator status, can access all the databases we created later, and can also create new database users and modify user passwords and other operations.

In order to operate the database, we usually need to login to the console first.

$ sudo -u postgres psql

This command indicates that Linux user Postgres logged in to the database named Postgres as superuser postgres, because it is a Ubuntu system, so by default it does not need to enter a password and goes directly to the pg console.

postgres=#

Usually we set the password for the postgres user after login.

postgres=# \password postgres

Successful modification can be achieved by entering the same password twice.

Because postgers users have very high privileges, we usually do not log in directly as this identity, but create a new user, such as a database user with the same name as the current system user.

postgres=# CREATE USER dbuser WITH PASSWORD 'password';

Then we can create a database for this new user.

postgres=# CREATE DATABASE exampledb OWNER dbuser;

Next we can use the user login console we just created and connect to the created database for a series of operations.

$ psql -U dbuser -d exampledb

Common console commands

\ password setting password
 \ q Exit
 \ h View the explanation of the SQL command, such ash select
 \ View the psql command list
 \ List a l l databases
 \ c [database_name] Connects to other databases
 \ d Lists all tables in the current database
 \ d [table_name] Lists the structure of a table
 \ du lists all users

Common SQL statements

# Create new table
CREATE TABLE table_name(name VARCHAR(20), birth DATE);

# insert data
INSERT INTO table_name(name, birth) VALUES('Irving', '1994-08-23');

# Query record
SELECT * FROM table_name;

# Update data
UPDATE table_name set name = 'Loew' WHERE name = 'Irving';

# Delete record
DELETE FROM table_name WHERE name = 'Irving' ;

# Add fields
ALTER TABLE table_name ADD email VARCHAR(40);

# Change field type
ALTER TABLE table_name ALTER COLUMN birth SET NOT NULL;

# Set default values for fields (note that single quotes are used for strings)
ALTER TABLE table_name ALTER COLUMN email SET DEFAULT 'example@example.com';

# Remove field defaults
ALTER TABLE table_name ALTER email DROP DEFAULT;

# Rename fields
ALTER TABLE table_name RENAME COLUMN birth TO birthday;

# Delete field
ALTER TABLE table_name DROP COLUMN email;

# Table rename
ALTER TABLE table_name RENAME TO backup_table;

# Delete table
DROP TABLE IF EXISTS backup_table;

# Delete Library
\c postgres;
DROP DATABASE IF EXISTS hello;

Backup and recovery

$ pg_dump --format=t -d db_name -U user_name -W -h 127.0.0.1 > dump.sql
$ pg_restore -d db_name -h 127.0.0.1 -U user_name < dump.sql

Reference material