Search

Thursday, September 1, 2022

Portgress commands

 

Users

Set/Reset postgres user password

\password username

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

For example: Change password for current user “postgres”:

\password postgres
Enter new password: xxxx
Enter it again: xxxx

Show all users

select * from pg_user;

// Display PostgreSQL database users (SQL)

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

// Display PostgreSQL database roles (psql)

Tables

Show table

\d TABLE_NAME

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

Show details

\d+ TABLE_NAME

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

List tables from current schema

\dt

// List tables from current schema (psql)

List tables from all schemas

\dt *.*

// List tables from all schemas (psql)

List tables for a schema

\dt <name-of-schema>.*

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

Copy table data to CSV file

\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

SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND
schemaname='__schema_name__';

// Show table indexes (SQL)

Collects statistics about the contents of tables

ANALYZE [__table__]

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

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

Adding comment on table/column

Comment on table employee is 'Stores employee records';

// Comment on table (SQL)

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

// Comment on column (SQL)

Approximate Table Row count / Table Cardinality

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

service postgresql stop

// Stops postgresql service through root user (Linux)

service postgresql start

// Starts postgresql service through root user (Linux)

service postgresql restart

// Restarts postgresql service through root user (Linux)

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

show all

// List all current runtime configuration parameter (psql)

Display configuration parameters using sql

select * from pg_settings;

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

Show current setting from “max_connections”

SELECT current_setting('max_connections');
 current_setting 
-----------------
 100
(1 row)

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

Show Postgres config file location

show config_file;
               config_file                
------------------------------------------
 /etc/postgresql/9.6/main/postgresql.conf
(1 row)

// Show PostgreSQL configuration file location (psql)

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

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)

– 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

$ 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)

root@localhost:~# su - postgres

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

Enter postgres terminal

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

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

Connect database

\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

$ psql -V
psql (PostgreSQL) 9.6.12

// Check psql client version (psql)

Check Postgres server version

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

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

\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

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

// Insert records into table (SQL)

Conditional select query

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);

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)

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

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)

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

truncate only employee;
TRUNCATE TABLE

// Truncate table employee (SQL)

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.

truncate only tableA, tableB;
TRUNCATE TABLE

// Truncate multiple tables at once (SQL)

Conditional delete query (Rollback)

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

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

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

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)

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:

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
$$
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)
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
select add(5,9);
 add 
-----
  14
(1 row)
// Function call (SQL)
List functions
\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
\df+
// Display all Functions including addition information including owner, source code & description etc. (psql)
Edit function
\ef myfunction
// Edit a function in default editor (psql)


Views
List views
\dv
// List views from current schema (psql)
List views
\dv *.*
// List views from all schemas (psql)

Users
Set/Reset postgres user password
\password username
// To set/reset a password for PostgreSQL database user (psql)
For example: Change password for current user “postgres”:
\password postgres
Enter new password: xxxx
Enter it again: xxxx
Show all users
select * from pg_user;
// Display PostgreSQL database users (SQL)
\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
$ 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
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)
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
\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
\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
drop index idx_employee_emp_name;
// Drop an existing index from a table (SQL)

Constraints
Create a table with primary & unique constraints
$$
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
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)
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
$$
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)
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)
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)
$$
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)
\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 ]
select 'Gordon' || ' ' || 'Moore' As fullName;
   fullname   
--------------
 Gordon Moore
(1 row)
// Concatenates two or more strings using “||”. (SQL)
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 (|/ & ||/)
select |/25 As sqrt;
 sqrt 
------
    5
(1 row)
// Square root operator. (SQL)
select ||/125 As cubert;
 cubert 
------
    5
(1 row)
// Cube root operator. (SQL)
Factorial Operator (!)
select 5! As factorial;
 factorial 
-----------
       120
(1 row)
// Factorial operator. (SQL)
Binary Complement Operator (~)
select ~60 As compl;
 compl 
----------
      -61
(1 row)
// Binary 2’s complement. This operator has flipping effect on bits. (SQL)
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) ]
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) ]
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) ]
select position('pan' in 'japan') As pos;
 pos 
-----
   3
(1 row)
// Location of specified substring (SQL)
Extract substring [ substring(string from [int] for [int] ]
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
postgres=# select 'line 1'||E'\n'||'line 2' As newline;
 newline 
---------
 line 1 +
 line 2
(1 row)
// Insert new line using E’\n’ (SQL)
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
Update "my table" set "a&b" = 0;
// Using double quotes as delimited identifier
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
select $$Maria’s dogs$$ As col;
 col 
--------------------
 Maria’s dogs
(1 row)
// Use dollar quotes string constant instead of double quotes for strings (SQL)
If a string contains many single quotes or backslashes then Postgres has an alternative called “dollar quoting”.    

Maintenance
Garbage Collect (Reclaim Storage)
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)
  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
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)
  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
Monitoring
Session Monitor
SELECT 
    pid
    ,datname
    ,usename
    ,application_name
    ,client_hostname
    ,state
    ,client_port
    ,backend_start
    ,query_start
    ,query  
FROM pg_stat_activity
// Monitors Postgres sessions (SQL)
Few important parameters to know
Pid - Backend Process ID
Datname - Database Name
Usename - User running the query
Application_name - Client Application Name
State - State of Session (e.g. Active, Waiting, Idle ..)
Query - Query executed
Cancel Running Query
SELECT pg_cancel_backend(pid);
// To cancel a running query with pid provided. This is useful in case of killing long running queries (SQL)
Biggest Postgres Table/Indexes by their sizes
SELECT
  nspname || '.' || relname AS "Object Name", relkind As "Object Type",
  pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
// Top 20 Big tables/Indexes (Excluding catalog tables) (SQL)

Backup
Database Backup (With default options)
$ 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)
$ 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)
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
$ 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)
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
$ 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)
$ 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)
$ 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)


PSQL
Magic words:
psql -U postgres
Some interesting flags (to see all, use -h or --help depending on your psql version):
  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)
Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*
  • \?: Show help (list of available commands with an explanation)
  • \q: Quit/Exit
  • \c __database__: Connect to a database
  • \d __table__: Show table definition (columns, etc.) including triggers
  • \d+ __table__: More detailed table definition including description and physical disk size
  • \l: List databases
  • \dy: List events
  • \df: List functions
  • \di: List indexes
  • \dn: List schemas
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \dT+: List all data types
  • \dv: List views
  • \dx: List all extensions installed
  • \df+ __function__ : Show function SQL code.
  • \x: Pretty-format query results instead of the not-so-useful ASCII tables
  • \copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV
  • \des+: List all foreign servers
  • \dE[S+]: List all foreign tables
  • \! __bash_command__: execute __bash_command__ (e.g. \! ls)
User Related:
  • \du: List users
  • \du __username__: List a username if present.
  • create role __test1__: Create a role with an existing username.
  • create role __test2__ noinherit login password __passsword__;: Create a role with username and password.
  • set role __test__;: Change role for current session to __test__.
  • grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__.
  • \deu+: List all user mapping on server
Configuration
  • Service management commands:
sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

  • Changing verbosity & querying Postgres log:
    1) First edit the config file, set a decent verbosity, save and restart postgres:
sudo vim /etc/postgresql/9.3/main/postgresql.conf

# Uncomment/Change inside:
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1

sudo service postgresql restart

  1. Now you will get tons of details of every statement, error, and even background tasks like VACUUMs
tail -f /var/log/postgresql/postgresql-9.3-main.log

  1. How to add user who executed a PG statement to log (editing postgresql.conf):
log_line_prefix = '%t %u %d %a '

  • Check Extensions enabled in postgres: SELECT * FROM pg_extension;
  • Show available extensions: SELECT * FROM pg_available_extension_versions;
Create command
There are many CREATE choices, like CREATE DATABASE __database_name__, CREATE TABLE __table_name__ ... Parameters differ but can be checked at the official documentation.
Handy queries
  • SELECT * FROM pg_proc WHERE proname='__procedurename__': List procedure/function
  • SELECT * FROM pg_views WHERE viewname='__viewname__';: List view (including the definition)
  • SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));: Show DB table space in use
  • SELECT pg_size_pretty(pg_database_size('__database_name__'));: Show DB space in use
  • show statement_timeout;: Show current user's statement timeout
  • SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';: Show table indexes
  • Get all indexes from all tables of a schema:
SELECT
   t.relname AS table_name,
   i.relname AS index_name,
   a.attname AS column_name
FROM
   pg_class t,
   pg_class i,
   pg_index ix,
   pg_attribute a,
    pg_namespace n
WHERE
   t.oid = ix.indrelid
   AND i.oid = ix.indexrelid
   AND a.attrelid = t.oid
   AND a.attnum = ANY(ix.indkey)
   AND t.relnamespace = n.oid
    AND n.nspname = 'kartones'
ORDER BY
   t.relname,
   i.relname
  • Execution data:
    • Queries being executed at a certain DB:
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query 
  FROM pg_stat_activity 
  WHERE datname='__database_name__';
  • Get all queries from all dbs waiting for data (might be hung):
SELECT * FROM pg_stat_activity WHERE waiting='t'
  • Currently running queries with process pid:
SELECT 
  pg_stat_get_backend_pid(s.backendid) AS procpid, 
  pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
  • Get Connections by Database: SELECT datname, numbackends FROM pg_stat_database;
Casting:
  • CAST (column AS type) or column::type
  • '__table_name__'::regclass::oid: Get oid having a table name
Query analysis:
  • EXPLAIN __query__: see the query plan for the given query
  • EXPLAIN ANALYZE __query__: see and execute the query plan for the given query
  • ANALYZE [__table__]: collect statistics
Generating random data (source):
  • INSERT INTO some_table (a_float_value) SELECT random() * 100000 FROM generate_series(1, 1000000) i;
Get sizes of tables, indexes and full DBs:
select current_database() as database,
  pg_size_pretty(total_database_size) as total_database_size,
  schema_name,
  table_name,
  pg_size_pretty(total_table_size) as total_table_size,
  pg_size_pretty(table_size) as table_size,
  pg_size_pretty(index_size) as index_size
  from ( select table_name,
          table_schema as schema_name,
          pg_database_size(current_database()) as total_database_size,
          pg_total_relation_size(table_name) as total_table_size,
          pg_relation_size(table_name) as table_size,
          pg_indexes_size(table_name) as index_size
          from information_schema.tables
          where table_schema=current_schema() and table_name like 'table_%'
          order by total_table_size
      ) as sizes;
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
  • List all grants for a specific user
SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'user_to_check' ORDER BY table_name;
  • List all assigned user roles
SELECT
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit,
    r.rolvaliduntil,
    ARRAY(SELECT b.rolname
      FROM pg_catalog.pg_auth_members m
      JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
      WHERE m.member = r.oid) as memberof, 
    r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;
  • Check permissions in a table:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='name-of-the-table';
  • Kill all Connections:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();
Keyboard shortcuts
  • CTRL + R: reverse-i-search
Tools
$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc
  • Show IP of the DB Instance: SELECT inet_server_addr();
  • File to save PostgreSQL credentials and permissions (format: hostname:port:database:username:password): chmod 600 ~/.pgpass
  • Collect statistics of a database (useful to improve speed after a Database Upgrade as previous query plans are deleted): ANALYZE VERBOSE;
  • To obtain the CREATE TABLE query of a table, any visual GUI like pgAdmin allows to easily, but else you can use pg_dump, e.g.: pg_dump -t '<schema>.<table>' --schema-only <database> (source)
Resources & Documentation
  • Postgres Weekly newsletter: The best way IMHO to keep up to date with PG news
  • 100 psql Tips: Name says all, lots of useful tips!
  • PostgreSQL Exercises: An awesome resource to learn to learn SQL, teaching you with simple examples in a great visual way. Highly recommended.
  • A Performance Cheat Sheet for PostgreSQL: Great explanations of EXPLAIN, EXPLAIN ANALYZE, VACUUM, configuration parameters and more. Quite interesting if you need to tune-up a postgres setup.
  • annotated.conf: Annotations of all 269 postgresql.conf settings for PostgreSQL 10.
  • psql -c "\l+" -H -q postgres > out.html: Generate a html report of your databases (source: Daniel Westermann)





--Show IP of the DB Instance
SELECT inet_server_addr();
--List all grants for an specific user
SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'user_to_check' ORDER BY table_name;

--List all assigned roles for the users
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
    FROM pg_catalog.pg_auth_members m
    JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
    WHERE m.member = r.oid) as memberof
, r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;

--Check permissions in a table
FROM information_schema.role_table_grants
WHERE table_name='name-of-the-table';

--Check Extensions enabled in postgres
SELECT * FROM pg_extension;
--Show available extensions
SELECT * FROM pg_available_extension_versions;
--Connections by Database
SELECT datname, numbackends FROM pg_stat_database;
--Check current queries
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

--Kill all Connections
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();

-- File to save PostgreSQL credentials and permissions (hostname:port:database:username:password)
chmod 600 ~/.pgpass
--Collect statistics of a database (useful to improve speed after a Database Upgrade as previous query plans are deleted)
ANALYZE VERBOSE;




1. Show a list of all databases in PostgreSQL vs MySQL
As part of setup, we have created a database named demo (using create database demo) upfront in both PostgreSQL and MySQL. To show a list of all databases present in PostgreSQL or MySQL, we can use the below commands:
List or show databases in Postgres or psql:
Default databases in most PostgreSQL servers are template0 , template0, postgres.
To list all the databases in Postgres i.e. psql we can use : \l
Example in psql client: For our tutorial, you can see database demo is listed in the below example.
1
2
3
4
5
6
7
8
9
10
11

postgres=# \l
                                    List of databases
   Name    |     Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+---------------+----------+-------------+-------------+-----------------------
 demo      | demouser      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |               |          |             |             | postgres=CTc/postgres
 template1 | postgres      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |               |          |             |             | postgres=CTc/postgres
(4 rows)

List or show databases in MySQL:
Default databases in most MySQL servers are information_schema , performance_schema, mysql, sys
In MySQL to show list of all databases we can use: show databases
Example in mysql client: For our tutorial, you can see database demo is listed in the below example.
1
2
3
4
5
6
7
8
9
10
11

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| demo               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

MySQL show databases is equivalent to PostgreSQL list (\l) databases.
2. Use or Change database in PostgreSQL vs MySQL
If you have multiple databases on your DB server, we often need to switch between multiple databases to perform various DDL or DML operations or to read data. In order to change or use a different database than the currently selected database, we can use the below queries.
Change or Use database in PostgreSQL:
To change the database in PostgreSQL we can use : \c {dbname}
\c {dbname} : Switches the current database to the specified database in PostgreSQL
Note: Text in curly braces can to be replaced with your own data. Don’t forget to remove the braces. See example below.
1
2

postgres-# \c demo
You are now connected to database "demo" as user "demouser".

Use or Change database in MySQL:
To change the database in MySQL we can use : use {dbname}
use {dbname} : Switches the current database to the specified database in MySQL
Example:
1
2
3

mysql> use demo;
Database changed
mysql> 

3. Show schemas in PostgreSQL vs MySQL
Schema is like a namespace of database objects such as tables, views, indexes etc. Depending on the type of DB server, a database can contain multiple schemas, but a schema belongs to only one database. In some DB servers, schema and database are used synonymously.
Show schemas in PostgreSQL:
PostgreSQL supports having multiple schemas in a single database so that you can specify different namespace for different features.
\dn : Shows list of schemas in PostgreSQL
Example:
1
2
3
4
5
6

postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

Show schemas in MySQL:
In MySQL a schema is similar or synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.
show schemas : Shows list of schemas in MySQL
Example:
1
2
3
4
5
6
7
8
9
10
11

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| demo               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

4. Show a list of all tables in PostgreSQL vs MySQL
Once we change to the desired database, one of the most frequently used operations is to list all the tables that are present in the current database.
List or show tables PostgreSQL:
To show the list of tables in PostgreSQL, we can use \dt or \dt+ or \d or \d+
\dt or \d : Gives list of tables in current database
\dt+ or \d+: Gives list of tables in current database with additional information like Size, description etc.
star Pro Tip: In postgres/psql, many commands support + as part of the syntax. Eg: Try \dt+ instead of \dt . If we use + , we can see additional information for that specific command.
Example: For this example two tables demo_table_primarykey (with primary key) and demo_table (without primary key) are already created for demo purposes.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

demo-# \dt
                   List of relations
 Schema |         Name          | Type  |     Owner     
--------+-----------------------+-------+---------------
 public | demo_table            | table | demouser
 public | demo_table_primarykey | table | demouser
(2 rows)

demo-# \dt+
                                              List of relations
 Schema |         Name          | Type  |     Owner     | Persistence | Access method |  Size   | Description 
--------+-----------------------+-------+---------------+-------------+---------------+---------+-------------
 public | demo_table            | table | demouser | permanent   | heap          | 0 bytes | 
 public | demo_table_primarykey | table | demouser | permanent   | heap          | 0 bytes | 
(2 rows)


List or show tables MySQL:
Once we select current database with use demo , to show the list of tables in MySQL, we can use show tables
show tables Gives list of tables in current database for mysql
1
2
3
4
5
6
7
8
9

mysql> show tables;
+-----------------------+
| Tables_in_demo        |
+-----------------------+
| demo_table            |
| demo_table_primarykey |
+-----------------------+
2 rows in set (0.00 sec)

5. Describe table in PostgreSQL vs MySQL
Now that we know how to list all the tables, let’s focus on specific table. If we have to describe the table fields, index etc, we can use the below queries.
Describe table in PostgreSQL:
To describe a table in PostgreSQL, we can use : \dt {table_name} or \dt+ {table_name} Alternatively \d {table_name} or \d+ {table_name} can also be used with each of the queries resulting in slightly different columns.
\dt {table_name} Gives basic information about specific table
\dt+ {table_name} Gives additional information about specific table
\d+ {table_name} Gives index level details too for more specific information. (Preferred way)
Example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

demo=# \d+ demo_table_primarykey;
                                  Table "public.demo_table_primarykey"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           | not null |         | plain   |             |              | 
Indexes:
    "demo_table_primarykey_pkey" PRIMARY KEY, btree (id)
Access method: heap

demo=# \dt demo_table_primarykey;
                   List of relations
 Schema |         Name          | Type  |     Owner     
--------+-----------------------+-------+---------------
 public | demo_table_primarykey | table | demouser
(1 row)

demo=# \dt+ demo_table_primarykey;
                                              List of relations
 Schema |         Name          | Type  |     Owner     | Persistence | Access method |  Size   | Description 
--------+-----------------------+-------+---------------+-------------+---------------+---------+-------------
 public | demo_table_primarykey | table | demouser | permanent   | heap          | 0 bytes | 
(1 row)


Describe table in MySQL:
In MySQL, to describe a table we can use desc {table_name} or describe {table_name} for detailed table information.
describe {table_name} Gives full information about specific table
Example below:
1
2
3
4
5
6
7

mysql> describe table demo_table_primarykey;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | demo_table_primarykey | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

6. Show create table in PostgreSQL vs MySQL
If we want to see how the table is created or detailed indexes etc, MySQL has a convenient way to show the exact table structure. But in PostgreSQL there is no straight forward way to see it. But here are some alternatives.
Show create table in PostgreSQL:
Since there is no inbuilt query to show how table is created, we can use \d+ {table_name} like we discussed in the list table section to view the detailed table info. If we want the exact table structure we can rely on pg_dump utility which is originally an export schema/data utility of postgres. We can leverage the schema dump mechanism from that.
pg_dump -st {table_name} {dbname} Dumps the exact table DDL
Show create table in MySQL:
In MySQL, in order to see how the table is created, we can use
show create table {table_name} Shows the exact DDL that is used to create the table
1
2
3
4
5
6
7
8
9
10
11

mysql> show create table demo_table_primarykey;
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                        |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| demo_table_primarykey | CREATE TABLE `demo_table_primarykey` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


7. Show list of users or roles in PostgreSQL vs MySQL
To view the list of users or roles in PostgreSQL vs MySQL we can use the below syntax.
List users or roles in PostgreSQL:
Detailed of information of users and roles can be obtained from \du
\du Shows the list of roles and their attributes in PostgreSQL
1
2
3
4
5
6

demo=# \du
                                     List of roles
   Role name   |                         Attributes                         | Member of 
---------------+------------------------------------------------------------+-----------
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 demouser | Superuser, Create role, Create DB                          | {}

Show all users or roles in MySQL:
In MySQL mysql.user table gives detailed information about various users, roles and their properties. To get user and their host you can use below query. Optionally try DESCRIBE mysql.user to see full list of supported attributes.
SELECT User, Host from mysql.user; Shows the list of roles and their attributes in MySQL
1
2
3
4
5
6
7
8
9
10
11

mysql> SELECT User, Host from mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| demouser             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)


8. Show process list in PostgreSQL vs MySQL
If you have a slow query or you want to view the list of current running process list, we can use the below queries.
Show process list in PostgreSQL:
To show process list in PostgreSQL, we can rely on pg_stat_activity table. This table has very useful information about currently running activities on the database.
select * from pg_stat_activity Gives the detailed list of processes in PostgreSQL
Example:
1
2
3
4
5
6

postgres=# select * from pg_stat_activity;
 datid | datname  |  pid  | leader_pid | usesysid |    usename    | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type |     wait_event      | state  | backend_xid | backend_xmin | query_id |              query              |         backend_type         
-------+----------+-------+------------+----------+---------------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+---------------------------------+------------------------------
       |          |  1855 |            |          |               |                  |             |                 |             | 2022-06-17 17:30:34.569684-07 |                               |                               |                               | Activity        | AutoVacuumMain      |        |             |              |          |                                 | autovacuum launcher
       |          |  1857 |            |       10 | postgres      |                  |             |                 |             | 2022-06-17 17:30:34.574222-07 |                               |                               |                               | Activity        | LogicalLauncherMain |        |             |              |          |                                 | logical replication launcher
only some datashown for example purposes

Show process list in MySQL:
To show process list in MySQL you can use : show processlist
show processlist Gives the detailed list of processes in MySQL
Example:
1
2
3
4
5
6
7

mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time  | State                  | Info             |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 65151 | Waiting on empty queue | NULL             |
|  8 | demouser            | localhost | demo | Query   |     0 | init                   | show processlist |
+----+-----------------+-

As you can see from the above comparison, each database is different and the way you can get information from these databases change a bit. So bookmark and share this post to ease your day-to-day development.
9. Comments, Quotes, Case sensitivty in PostgreSQL VS MySQL
There are some basic differences between PostgreSQL VS MySQL in terms of comments, quotes, case sensitivity. Lets check them here.
Comments:
  • Single line comments in PostgreSQL can be added using: --
  • Single line comments in MySQL can be added using: #
Handling Quotes:
  • PosgreSQL support only single quote (') character for strings. Example: WHERE site = 'tipseason.com'
  • MySQL supports both single quote (') and double quote (") characters for strings. Example: WHERE site = 'tipseason.com' or WHERE site = "tipseason.com"
Case sensitivity:
  • PosgreSQL is case-sensitive. Example: WHERE site = 'tipseason.com' might give different results than WHERE site = 'TipSeason.com' . We can use case conversion like (lower , upper etc.) and compare the objects.
  • MySQL is case-insensitive. So in above example both queries gives same results.
PostgreSQL vs MySQL Comparison Side by side comparison CheatSheet in Table form
As a handy reference, here is the cheatsheet for PostgreSQL vs MySQL syntax comparison highlighting the differences.
How toPostgreSQLMySQL
Show list of databases\lshow databases
Use database\c {db_name}use {db_name}
Show list of schemas\dnshow schemas
Show list of tables\d or \d+ or \dt or \dt+show tables
Describe table\dt {table_name} or \d+ {table_name}describe {table_name}
Show create tablepg_dump -st {table_name} {dbname}show create table {table_name}
Show list of users or roles\duSELECT User, Host from mysql.user;
Show process listselect * from pg_stat_activityshow processlist
Comment single line-- (double dash)# (hash)
String quotesOnly supports single quote (') eg:`where name = 'TipSeason'`Supports both single(') and double quotes (") eg: `where name = "TipSeason"`
Case sensitivityPostgres is case sensitive eg: `WHERE name = 'TipSeason'` gives different results than `WHERE name = 'tipseason'`MySQL is case insensitive.


Posted by at
Subscribe to: Posts (Atom)