James' CS Book

Appearance

Databases ​

List databases ​
bash
\l
\l

// List all databases using \l (or \list) (psql)

List databases ​
bash
\l+
\l+

// List all databases using \l+ with more details (including description, tablespace & DB size) (psql)

Help on CREATE DATABASE command syntax ​
bash
\h CREATE DATABASE
\h CREATE DATABASE

// Help on SQL Command Syntax (For e.g. CREATE DATABASE) (psql)

Create database ​
bash
CREATE DATABASE mytest;
CREATE DATABASE mytest;

// Creates a new database “mytest” (SQL)

INFO

By default, the owner of the database is the current login user.

Create database ​
bash
\c test
You are now connected to database "test" as user "postgres".
\c test
You are now connected to database "test" as user "postgres".

// Connect to a PostgreSQL database “test” as “postgres” user (psql)

Tables ​

Show table ​
bash
\d TABLE_NAME
\d TABLE_NAME

// Show table definition including indexes, constraints & triggers (psql)

Show details ​
bash
\d+ TABLE_NAME
\d+ TABLE_NAME

// More detailed table definition including description and physical disk size (psql)

List tables from current schema ​
bash
\dt
\dt

// List tables from current schema (psql)

List tables from all schemas ​
bash
\dt *.*
\dt *.*

// List tables from all schemas (psql)

List tables for a schema ​
bash
\dt <name-of-schema>.*
\dt <name-of-schema>.*

// List the tables in a specific schema (psql)

Copy table data to CSV file ​
bash
\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV
\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV

// Export a table as CSV (psql)

Check indexes for a table using sql ​
bash
SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND
schemaname='__schema_name__';
SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND
schemaname='__schema_name__';

// Show table indexes (SQL)

Collects statistics about the contents of tables ​
bash
ANALYZE [__table__]
ANALYZE [__table__]

// Analyze a table and store the results in the pg_statistic system catalog (SQL)

INFO

With no parameter, ANALYZE examines every table in the current database

Adding comment on table/column ​
bash
Comment on table employee is 'Stores employee records';
Comment on table employee is 'Stores employee records';

// Comment on table (SQL)

bash
Comment on column employee.ssn is 'Employee Social Security Number';
Comment on column employee.ssn is 'Employee Social Security Number';

// Comment on column (SQL)

Approximate Table Row count / Table Cardinality ​
bash
SELECT reltuples AS card FROM pg_class WHERE relname = '<table_name>';
SELECT reltuples AS card FROM pg_class WHERE relname = '<table_name>';

// Use this to do fast (but not exact) counts from tables. Helpful if table has millions / billions of records and you just want estimated rows quickly. (SQL)

Configuration ​

Stop / Start postgresql service ​
bash
service postgresql stop
service postgresql stop

// Stops postgresql service through root user (Linux)

bash
service postgresql start
service postgresql start

// Starts postgresql service through root user (Linux)

bash
service postgresql restart
service postgresql restart

// Restarts postgresql service through root user (Linux)

INFO

If running from non root user you must prefix your command with “sudo” and non-root user should already be there in sudoer’s list. Also be careful with running these commands because some distributors don’t provide them these days.

Display configuration parameters ​
bash
show all
show all

// List all current runtime configuration parameter (psql)

Display configuration parameters using sql ​
bash
select * from pg_settings;
select * from pg_settings;

// List all current runtime configuration parameter using sql with additional details including description (SQL)

Show current setting from "max_connections" ​
bash
SELECT current_setting('max_connections');
 current_setting 
-----------
 100
(1 row)
SELECT current_setting('max_connections');
 current_setting 
-----------
 100
(1 row)

// Display current value set for “max_connections” parameter (SQL)

Show Postgres config file location ​
bash
show config_file;
               config_file                
------------------------------------
 /etc/postgresql/9.6/main/postgresql.conf
(1 row)
show config_file;
               config_file                
------------------------------------
 /etc/postgresql/9.6/main/postgresql.conf
(1 row)

// Show PostgreSQL configuration file location (psql)

INFO

The PostgreSQL configuration files are stored in directory from above command output. The main configuration file is called "postgresql.conf".

Display contents of postgres config file location ​
bash
postgres@localhost:~$ less /etc/postgresql/9.6/main/postgresql.conf
 
. . . .
data_directory = '/var/lib/postgresql/9.6/main'         ## use data in another directory
                                        ## (change requires restart)
hba_file = '/etc/postgresql/9.6/main/pg_hba.conf'       ## host-based authentication file
                                        ## (change requires restart)
ident_file = '/etc/postgresql/9.6/main/pg_ident.conf'   ## ident configuration file
                                        ## (change requires restart)
listen_addresses = '*'                  ## what IP address(es) to listen on;
                                        ## comma-separated list of addresses;
                                        ## defaults to 'localhost'; use '*' for all
                                        ## (change requires restart)
port = 5432                             ## (change requires restart)
 
. . . .
postgres@localhost:~$ less /etc/postgresql/9.6/main/postgresql.conf
 
. . . .
data_directory = '/var/lib/postgresql/9.6/main'         ## use data in another directory
                                        ## (change requires restart)
hba_file = '/etc/postgresql/9.6/main/pg_hba.conf'       ## host-based authentication file
                                        ## (change requires restart)
ident_file = '/etc/postgresql/9.6/main/pg_ident.conf'   ## ident configuration file
                                        ## (change requires restart)
listen_addresses = '*'                  ## what IP address(es) to listen on;
                                        ## comma-separated list of addresses;
                                        ## defaults to 'localhost'; use '*' for all
                                        ## (change requires restart)
port = 5432                             ## (change requires restart)
 
. . . .

(Linux)

INFO

-- data_directory directive tells where the database files are stored.
-- hba_file directive tells the host based authentication file.
-- port directive tells the TCP port number. The default is 5432.

Connections ​

Login using postgres user ​
bash
$ ssh -l postgres 200.34.22.75
postgres@200.34.22.75's password: 
Linux localhost 4.9.0-9-amd64 ##1 SMP Debian 4.9.168-1 (2019-04-12) x86_64
$ ssh -l postgres 200.34.22.75
postgres@200.34.22.75's password: 
Linux localhost 4.9.0-9-amd64 ##1 SMP Debian 4.9.168-1 (2019-04-12) x86_64

// Login as PostgreSQL superuser “postgres” on remote PostgreSQL server using ssh (Linux)

bash
root@localhost:~## su - postgres
root@localhost:~## su - postgres

// Login as PostgreSQL superuser “postgres” (Linux)

Enter postgres terminal ​
bash
postgres@localhost:~$ psql
psql (9.6.12)
Type "help" for help.
postgres=##
postgres@localhost:~$ psql
psql (9.6.12)
Type "help" for help.
postgres=##

// Enter PostgreSQL Command Line via “psql” client (psql)

Connect database ​
bash
\c test
You are now connected to database "test" as user "postgres".
\c test
You are now connected to database "test" as user "postgres".

// Connect to a PostgreSQL database “test” as “postgres” user (psql)

Check psql Client version ​
bash
$ psql -V
psql (PostgreSQL) 9.6.12
$ psql -V
psql (PostgreSQL) 9.6.12

// Check psql client version (psql)

Check Postgres server version ​
bash
select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.12 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.12 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

// Check postgres server version (SQL)

Queries ​

Create a new table ​
bash
CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,        -- AUTO_INCREMENT integer, as primary key
  emp_name VARCHAR(50) NOT NULL,    
  emp_salary NUMERIC(9,2) NOT NULL
);
CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,        -- AUTO_INCREMENT integer, as primary key
  emp_name VARCHAR(50) NOT NULL,    
  emp_salary NUMERIC(9,2) NOT NULL
);

// Creates a new table (SQL)

Display table ​
bash
\d employee
                                    Table "public.employee"
   Column   |         Type          |                         Modifiers                         
------+-----------------------+-----------------------------------------------------------
 emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
 emp_name   | character varying(50) | not null
 emp_salar  | numeric(9,2)          | not null
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
\d employee
                                    Table "public.employee"
   Column   |         Type          |                         Modifiers                         
------+-----------------------+-----------------------------------------------------------
 emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
 emp_name   | character varying(50) | not null
 emp_salar  | numeric(9,2)          | not null
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)

// Display table (psql)

Insert query ​
bash
INSERT INTO employee (emp_name, emp_salary) VALUES
('John', 5000),
('Jack', 4568.0),
('Robert',7500.50);
INSERT INTO employee (emp_name, emp_salary) VALUES
('John', 5000),
('Jack', 4568.0),
('Robert',7500.50);

// Insert records into table (SQL)

Conditional select query ​
bash
select * from employee where emp_salary >= 5000;
 emp_id | emp_name | emp_salary 
--+----------+------------
    1   | John     |    5000.00
    3   | Robert   |    7500.50
(2 rows)
select * from employee where emp_salary >= 5000;
 emp_id | emp_name | emp_salary 
--+----------+------------
    1   | John     |    5000.00
    3   | Robert   |    7500.50
(2 rows)

// Select data based on filter condition (e.g. emp_salary >= 5000) (SQL)

Conditional update query (Safe Update); ​
bash
BEGIN;
  update employee set emp_salary = 6000 where emp_name = 'John'; 
COMMIT;
BEGIN;
  update employee set emp_salary = 6000 where emp_name = 'John'; 
COMMIT;

// Update record based on a condition (e.g. Update emp_salary for employee ‘John’) (SQL)

INFO

Records are not committed inside database unless you issue a commit. Updates can be undone as well if you issue ROLLBACK command instead of COMMIT.

Alter Table ​
bash
alter table employee add column dept_id integer;
ALTER TABLE
alter table employee add column dept_id integer;
ALTER TABLE

// Alter table to add a new column (e.g. add dept_id in employee table) (SQL)

bash
alter table employee drop column dept_id;
ALTER TABLE
alter table employee drop column dept_id;
ALTER TABLE

// Alter table to drop column (e.g. drop dept_id from employee table) (SQL)

Truncate Table ​
bash
truncate only employee;
TRUNCATE TABLE
truncate only employee;
TRUNCATE TABLE

// Truncate table employee (SQL)

INFO

Truncating a table is a quick way to remove records from a table because it does not need to scan the table. Truncate is a not logged activity inside database. Truncating a table is also a lot easier than dropping the table and recreating it.

This is safe to use “ONLY” keyword so that you don't accidentally truncate dependant/child tables.

bash
truncate only tableA, tableB;
TRUNCATE TABLE
truncate only tableA, tableB;
TRUNCATE TABLE

// Truncate multiple tables at once (SQL)

Conditional delete query (Rollback) ​
bash
BEGIN; 
  delete from employee where emp_id = 2;
  select * from employee where emp_id = 2;
  emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id 
  --+----------+---------+------------+-------------
  (0 rows)
ROLLBACK;

select * from employee where emp_id = 2;
 emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id 
--+----------+---------+------------+-------------
      2 | Rohit    | 1234    |    5000.00 |           1
(1 row)
BEGIN; 
  delete from employee where emp_id = 2;
  select * from employee where emp_id = 2;
  emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id 
  --+----------+---------+------------+-------------
  (0 rows)
ROLLBACK;

select * from employee where emp_id = 2;
 emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id 
--+----------+---------+------------+-------------
      2 | Rohit    | 1234    |    5000.00 |           1
(1 row)

// Delete from employee based on filter condition (emp_id = 1) & then Rollback transaction (SQL)

Parameterized Statements ​
bash
PREPARE myplan (int) AS SELECT * FROM employee where emp_id = $1;
PREPARE myplan (int) AS SELECT * FROM employee where emp_id = $1;

// Creates a parameterized statement and stores query access plan on server (SQL)

bash
EXECUTE myplan(2);
 emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id 
--+----------+---------+------------+-------------
      2 | Rohit    | 1234    |    5000.00 |           1
(1 row)
EXECUTE myplan(2);
 emp_id | emp_name | emp_ssn | emp_salary | emp_dept_id 
--+----------+---------+------------+-------------
      2 | Rohit    | 1234    |    5000.00 |           1
(1 row)

// Executes query as per stored access plan providing value for parameters (e.g. $1=2) (SQL)

INFO

Parameterized statements are an effective way to write queries that use the same access plan irrespective of run time values provided.
Applications like java use the JDBC API to create parameterized statements like this:

bash
Int emp_id = 2;
con = getDBConnection();  // get database connection
PreparedStatement pstmt = con.prepareStatement("select * from employee where emp_id = ?"); // prepare parameterized statement
pstmt.setInt(1, emp_id); // provide runtime value
pstmt.execute();  // execute query
Int emp_id = 2;
con = getDBConnection();  // get database connection
PreparedStatement pstmt = con.prepareStatement("select * from employee where emp_id = ?"); // prepare parameterized statement
pstmt.setInt(1, emp_id); // provide runtime value
pstmt.execute();  // execute query

Functions ​

Create a new function ​
bash
$$
CREATE FUNCTION add(integer, integer) RETURNS integer
 AS 'select $1 + $2;'
 LANGUAGE SQL
 IMMUTABLE
 RETURNS NULL ON NULL INPUT;
$$
$$
CREATE FUNCTION add(integer, integer) RETURNS integer
 AS 'select $1 + $2;'
 LANGUAGE SQL
 IMMUTABLE
 RETURNS NULL ON NULL INPUT;
$$

// Create a function to add 2 integers (SQL)

INFO

This function takes 2 integers as parameters
IMMUTABLE means that the function cannot modify the database and always returns the same result when given the same argument values

Calling function ​
bash
select add(5,9);
 add 
--
  14
(1 row)
select add(5,9);
 add 
--
  14
(1 row)

// Function call (SQL)

List functions ​
bash
\df
                        List of functions
 Schema | Name | Result data type | Argument data types |  Type  
--+------+------------------+---------------------+--------
 public | add  | integer          | integer, integer    | normal
(1 row)
\df
                        List of functions
 Schema | Name | Result data type | Argument data types |  Type  
--+------+------------------+---------------------+--------
 public | add  | integer          | integer, integer    | normal
(1 row)

// Display all Functions (psql)

List functions ​
bash
\df+
\df+

// Display all Functions including addition information including owner, source code & description etc. (psql)

Edit function ​
bash
\ef myfunction
\ef myfunction

// Edit a function in default editor (psql)

Views ​

List views ​
bash
\dv
\dv

// List views from current schema (psql)

List views ​
bash
\dv *.*
\dv *.*

// List views from all schemas (psql)

Users ​

Set/Reset postgres user password ​
bash
\password username
\password username

// To set/reset a password for PostgreSQL database user (psql)

INFO

For example: Change password for current user "postgres":

bash
\password postgres
Enter new password: xxxx
Enter it again: xxxx
\password postgres
Enter new password: xxxx
Enter it again: xxxx
Show all users ​
bash
select * from pg_user;
select * from pg_user;

// Display PostgreSQL database users (SQL)

bash
\du
                                   List of roles
 Role name |                         Attributes                           | Member of 
--------+------------------------------------------------------------+-----------
 testrole    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS   | {}
\du
                                   List of roles
 Role name |                         Attributes                           | Member of 
--------+------------------------------------------------------------+-----------
 testrole    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS   | {}

// Display PostgreSQL database roles (psql)

Login & enter postgres terminal ​
bash
$ psql -U testuser mytest
Password for user testuser: ......
psql (9.6.12)
Type "help" for help.
$ psql -U testuser mytest
Password for user testuser: ......
psql (9.6.12)
Type "help" for help.

// Login to PostgreSQL: psql -U user database (psql)

Indexes ​

Create a new index on a table ​
bash
create index idx_employee_emp_name on employee using btree (emp_name asc);
create index idx_employee_emp_name on employee using btree (emp_name asc);

// Create a new index on the emp_name column of employee table (SQL)

INFO

This index specifies “btree” as the index method and uses “asc” to store the index key column data in ascending order

View indexes of a table ​
bash
\d employee
postgres=## \d employee;
                                    Table "public.employee"
   Column   |         Type          |                         Modifiers                         
---------+-----------------------+-----------------------------------------------------------
 emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
 emp_name   | character varying(50) | not null
 emp_salary | numeric(9,2)          | not null
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "idx_employee_emp_name" btree (emp_name)
\d employee
postgres=## \d employee;
                                    Table "public.employee"
   Column   |         Type          |                         Modifiers                         
---------+-----------------------+-----------------------------------------------------------
 emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
 emp_name   | character varying(50) | not null
 emp_salary | numeric(9,2)          | not null
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "idx_employee_emp_name" btree (emp_name)

// List indexes of a table along with table definition (psql)

List all indexes ​
bash
\di
                      List of relations
 Schema |         Name          | Type  |  Owner   |  Table   
-----+-----------------------+-------+----------+----------
 public | employee_pkey         | index | postgres | employee
 public | idx_employee_emp_name | index | postgres | employee
(2 rows)
\di
                      List of relations
 Schema |         Name          | Type  |  Owner   |  Table   
-----+-----------------------+-------+----------+----------
 public | employee_pkey         | index | postgres | employee
 public | idx_employee_emp_name | index | postgres | employee
(2 rows)

// List all indexes from all tables (psql)

Drop index from a table ​
bash
drop index idx_employee_emp_name;
drop index idx_employee_emp_name;

// Drop an existing index from a table (SQL)

Constraints ​

Create a table with primary & unique constraints ​
bash
$$
CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL `PRIMARY` KEY,        
  emp_name VARCHAR(50) NOT NULL,    
  emp_ssn VARCHAR (30) NOT NULL `UNIQUE`, 
  emp_salary NUMERIC(9,2) NOT NULL
);
$$
$$
CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL `PRIMARY` KEY,        
  emp_name VARCHAR(50) NOT NULL,    
  emp_ssn VARCHAR (30) NOT NULL `UNIQUE`, 
  emp_salary NUMERIC(9,2) NOT NULL
);
$$

// Creates a new table with primary & unique key constraints (SQL)

Avoid Duplicate Records ​
bash
INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Rohit', '1234', 5000.0);
INSERT 0 1

INSERT INTO employee (emp_name, emp_ssn, emp_salary) values (Mason, '1234', 7500.0);
ERROR:  duplicate key value violates unique constraint "employee_emp_ssn_key"
DETAIL:  Key (emp_ssn)=(1234) already exists.
INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Rohit', '1234', 5000.0);
INSERT 0 1

INSERT INTO employee (emp_name, emp_ssn, emp_salary) values (Mason, '1234', 7500.0);
ERROR:  duplicate key value violates unique constraint "employee_emp_ssn_key"
DETAIL:  Key (emp_ssn)=(1234) already exists.

// Insert records in a table with unique key constraints specified (SQL)

INFO

This table uses emp_id as primary key column (Keyword “primary”) and a unique constraint (Keyword “unique”) is specified on employee social security number (emp_ssn) to avoid duplicate ssn being entered.

Create a table with check constraint ​
bash
$$
CREATE TABLE orders(
  ord_no integer,
  ord_date date,
  ord_qty numeric,
  ord_amount numeric `CHECK (ord_amount>0)`
);
$$
$$
CREATE TABLE orders(
  ord_no integer,
  ord_date date,
  ord_qty numeric,
  ord_amount numeric `CHECK (ord_amount>0)`
);
$$

// Creates a new table with check constraint specified (SQL)

bash
insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (1, '2019-08-29', 1, 10);
INSERT 0 1
 
insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (2, '2019-08-29', 1, 0);
ERROR:  new row for relation "orders" violates check constraint "orders_ord_amount_check"
DETAIL:  Failing row contains (2, 2019-08-29, 1, 0).
insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (1, '2019-08-29', 1, 10);
INSERT 0 1
 
insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (2, '2019-08-29', 1, 0);
ERROR:  new row for relation "orders" violates check constraint "orders_ord_amount_check"
DETAIL:  Failing row contains (2, 2019-08-29, 1, 0).

// Insert records in table with check constraints specified (SQL)

INFO

Check constraint (Keyword “check”) is specified on order amount (ord_amount > 0) on table so any records with ord_amount <=0 will fail to insert

Define relation between two tables (foreign key constraint) ​
bash
$$
CREATE TABLE IF NOT EXISTS department (
  dept_id SERIAL PRIMARY KEY,        
  dept_name VARCHAR(50) NOT NULL   
);
$$
CREATE TABLE
 
$$
CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,        
  emp_name VARCHAR(50) NOT NULL,    
  emp_ssn VARCHAR (30) NOT NULL UNIQUE, 
  emp_salary NUMERIC(9,2) NOT NULL,
  emp_dept_id INTEGER `REFERENCES` department (dept_id)    -- Foreign Key
);
$$
CREATE TABLE
$$
CREATE TABLE IF NOT EXISTS department (
  dept_id SERIAL PRIMARY KEY,        
  dept_name VARCHAR(50) NOT NULL   
);
$$
CREATE TABLE
 
$$
CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,        
  emp_name VARCHAR(50) NOT NULL,    
  emp_ssn VARCHAR (30) NOT NULL UNIQUE, 
  emp_salary NUMERIC(9,2) NOT NULL,
  emp_dept_id INTEGER `REFERENCES` department (dept_id)    -- Foreign Key
);
$$
CREATE TABLE

// Creates table department & employee and defines a relation of an employee to department using foreign key (“REFERENCES”) (SQL)

Check constraints on a table (using \d option) ​
bash
\d employee;
                                     Table "public.employee"
   Column    |         Type          |                         Modifiers                         
----------+-----------------------+-----------------------------------------------------------
 emp_id      | integer               | not null default nextval('employee_emp_id_seq'::regclass)
. . .
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "employee_emp_ssn_key" UNIQUE CONSTRAINT, btree (emp_ssn)
Foreign-key constraints:
    "employee_emp_dept_id_fkey" FOREIGN KEY (emp_dept_id) REFERENCES department(dept_id)
\d employee;
                                     Table "public.employee"
   Column    |         Type          |                         Modifiers                         
----------+-----------------------+-----------------------------------------------------------
 emp_id      | integer               | not null default nextval('employee_emp_id_seq'::regclass)
. . .
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "employee_emp_ssn_key" UNIQUE CONSTRAINT, btree (emp_ssn)
Foreign-key constraints:
    "employee_emp_dept_id_fkey" FOREIGN KEY (emp_dept_id) REFERENCES department(dept_id)

// Display constraints on a table (using \d option) (psql)

String functions, Operators & Identifiers ​

String concatenate Operator [ String || String ] ​
bash
select 'Gordon' || ' ' || 'Moore' As fullName;
   fullname   
-----------
 Gordon Moore
(1 row)
select 'Gordon' || ' ' || 'Moore' As fullName;
   fullname   
-----------
 Gordon Moore
(1 row)

// Concatenates two or more strings using “||”. (SQL)

INFO

This operator can be applied on table columns as well. e.g. "select first_name || ' ' || last_name As fullName from person"

Square & Cube Root Operator (|/ & ||/) ​
bash
select |/25 As sqrt;
 sqrt 
---
    5
(1 row)
select |/25 As sqrt;
 sqrt 
---
    5
(1 row)

// Square root operator. (SQL)

bash
select ||/125 As cubert;
 cubert 
---
    5
(1 row)
select ||/125 As cubert;
 cubert 
---
    5
(1 row)

// Cube root operator. (SQL)

Factorial Operator (!) ​
bash
select 5! As factorial;
 factorial 
--------
       120
(1 row)
select 5! As factorial;
 factorial 
--------
       120
(1 row)

// Factorial operator. (SQL)

Binary Complement Operator (~) ​
bash
select ~60 As compl;
 compl 
-------
      -61
(1 row)
select ~60 As compl;
 compl 
-------
      -61
(1 row)

// Binary 2’s complement. This operator has flipping effect on bits. (SQL)

INFO

Assume if A = 60, now in binary format they will be as follows − A = 0011 1100 ~A = 1100 0011 (flipping bits. change 0 to 1 & 1 to 0)

String lower & upper function [ lower(string), upper(string) ] ​
bash
select lower('Rohit Kumawat') As lowerCase, upper('Rohit Kumawat') As upperCase;
   lowercase   |   uppercase   
------------+---------------
 rohit kumawat | ROHIT KUMAWAT
(1 row)
select lower('Rohit Kumawat') As lowerCase, upper('Rohit Kumawat') As upperCase;
   lowercase   |   uppercase   
------------+---------------
 rohit kumawat | ROHIT KUMAWAT
(1 row)

// Postgres lower & upper function (SQL)

Number of characters in string [ char_length(string) ] ​
bash
select char_length('Arizona') as num_chars;
 num_chars 
--------
         7
(1 row)
select char_length('Arizona') as num_chars;
 num_chars 
--------
         7
(1 row)

// Number of characters in string (SQL)

Location of specified substring [ position(substring in string) ] ​
bash
select position('pan' in 'japan') As pos;
 pos 
--
   3
(1 row)
select position('pan' in 'japan') As pos;
 pos 
--
   3
(1 row)

// Location of specified substring (SQL)

Extract substring [ substring(string from [int] for [int] ] ​
bash
select substring('postgres' from 3 for 3) As sub_string;
 sub_string 
---------
 stg
(1 row)
select substring('postgres' from 3 for 3) As sub_string;
 sub_string 
---------
 stg
(1 row)

// Extract substring ‘postgres’ starting from 3rd character upto 3 characters length (SQL)

Insert newline in SQL output ​
bash
postgres=## select 'line 1'||E'\n'||'line 2' As newline;
 newline 
------
 line 1 +
 line 2
(1 row)
postgres=## select 'line 1'||E'\n'||'line 2' As newline;
 newline 
------
 line 1 +
 line 2
(1 row)

// Insert new line using E'\n' (SQL)

INFO

Another option is to use the chr() function. (E is used for the Escape String constant). Here’s a few other escape sequences: \b backspace \f form feed \n newline \r carriage return \t tab

Quote identifier ​
bash
Update "my table" set "a&b" = 0;
Update "my table" set "a&b" = 0;

// Using double quotes as delimited identifier

INFO

This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. Also double quotes are used for escaping reserved keywords in postgres

Dollar-quoted String constant ​
bash
select $$Maria’s dogs$$ As col;
 col 
-----------------
 Maria’s dogs
(1 row)
select $$Maria’s dogs$$ As col;
 col 
-----------------
 Maria’s dogs
(1 row)

// Use dollar quotes string constant instead of double quotes for strings (SQL)

INFO

If a string contains many single quotes or backslashes then Postgres has an alternative called "dollar quoting".

Maintenance ​

Garbage Collect (Reclaim Storage) ​
bash
VACUUM [__Table__]

vacuum(verbose, analyze) employee;
INFO:  vacuuming "public.employee"
INFO:  scanned index "employee_pkey" to remove 1 row versions
. . .
sample, 1 estimated total rows
VACUUM
VACUUM [__Table__]

vacuum(verbose, analyze) employee;
INFO:  vacuuming "public.employee"
INFO:  scanned index "employee_pkey" to remove 1 row versions
. . .
sample, 1 estimated total rows
VACUUM

// Use the vacuum command to reclaim storage from deleted rows in the employee table (SQL)

INFO

  1. Table rows that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM command is executed. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.
  2. Verbose Prints a detailed vacuum activity report for each table.
  3. Analyze update statistics for table
Gather statistics ​
bash
ANALYZE [__table__]

analyze verbose employee;
INFO:  analyzing "public.employee"
INFO:  "employee": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
ANALYZE
ANALYZE [__table__]

analyze verbose employee;
INFO:  analyzing "public.employee"
INFO:  "employee": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
ANALYZE

// Analyze a table and stores the results in the pg_statistic system catalog (SQL)

INFO

  1. ANALYZE gathers statistics for the query planner to create the most efficient query execution plans. Accurate statistics assist planner to choose the most appropriate query plan, and thereby improve the speed of query processing.
  2. Verbose Prints a detailed analyze activity report for each table.
  3. With no table name specified, ANALYZE examines every table in the current database

Backup ​

Database Backup (With default options) ​
bash
$ pg_dump mydb > mydb.bak.sql
$ pg_dump mydb > mydb.bak.sql

// Create a backup for a database “mydb” in plain-text SQL Script file (mydb.bak.sql) (pg_dump)

Database Backup (With Customised options) ​
bash
$ pg_dump -c -C -F p -f mydb.bak.sql mydb
$ pg_dump -c -C -F p -f mydb.bak.sql mydb

// Creates a backup for a database “mydb” in plain text format with drop & create database commands included in output file mydb.bak.sql (pg_dump)

INFO

Backup options:
-- -c: Output commands to clean(drop) database objects prior to writing commands to create them
-- -C: Begin output with "CREATE DATABASE" command itself and reconnect to created database -- -F: Format of the output (value p means plain SQL output and value c means custom archive format suitable for pg_restore) -- -f: Backup output file name

Remote Backup ​
bash
$ pg_dump -h <remote_host> -p <port> -U <user> -f mydb.bak mydb
$ pg_dump -h <remote_host> -p <port> -U <user> -f mydb.bak mydb

// Running pg_dump on client computer to back up data on a remote postgres server (pg_dump)

INFO

Use the -h flag to specify the IP address of your remote Host and -p to identify the port on which PostgreSQL is listening:

All databases backup ​
bash
$ pg_dumpall > alldb.bak.sql
$ pg_dumpall > alldb.bak.sql

// Backup of all databases along with database roles and cluster wide information. (pg_dumpall)

Restore ​

Restore from backup file (.sql) ​
bash
$ psql -U username -f filename.sql
$ psql -U username -f filename.sql

// Restore database plain-text backup(.sql) generated by pg_dump or pg_dumpall with psql utility (psql)

Restore from custom archive backup file (.bak) ​
bash
$ pg_restore -d db_name /path/to/your/file/db_name.bak -c -U db_user
$ pg_restore -d db_name /path/to/your/file/db_name.bak -c -U db_user

// Restore database custom archive backup(.bak) using pg_restore utility (pg_restore)

Released under the MIT License.

Copyright © 2024 CS-Book