psql

PostgreSQL cheat sheet

• A PostgreSQL instance can have
  • multiple databases which can have * multiple schemas which can have * multiple tables.

Schemas also contain other kinds of named objects, including data types, functions, and operators.

Connect to DB

psql -U <userName> -h <dbHost> -p <portNum>

• At this point you will work on the DB <databaseName>
• psql will assume you want to connect to a database whose name is the same as your username

List DB

\l

Switch DB

\c

List schemas

There default schema is: public

\dn - List schemas

List tables within schemas

List tables inside public schema:

\dt

List table in specific schema schema1:

\dt schema1.

Note: The dot (.) after the schema is requried.

List all columns/headers of a table

\d+ tableName

Includes information like:

• Column (column name)
• Type (e.g. character varying(255), boolean, ...)
• Collation
• Nullable (e.g. not null or empty)
• Default
• Storage (e.g. extended, plain)
• Stats
• target
• Description
• Table indexes
• Tablespace
• Access method (e.g. heap)

Executing queries

SELECT * FROM tableName;

• Keywords like SELECT are case insensitive
• Statements must end with a semikolon (;) to be executed
  • If you forgot it entering ; in the next line will execute the previous statement

Pattern matching

Simple

• 'abc' LIKE 'abc -> case insensitive search (complete pattern)
  • 'abc' LIKE 'ab -> false
  • 'abc' LIKE 'bc -> false
• 'abc' LIKE 'ab% -> wildcard (zero or more chars)
• 'abc' LIKE '_b_' -> wildcard (one char)

Regular expressions (RegEx search)

SIMILAR TO

Unusual format then one is used to in other implementations -> check out POSIX regex'es.

dbName=> SELECT 'abc' SIMILAR TO '.*b.*' as blah FROM tableName limit 1;
 blah
------
 f
(1 row)

dbName=> SELECT 'abc' SIMILAR TO '%*b%*' as blah FROM tableName limit 1;
ERROR:  invalid regular expression: quantifier operand invalid
dbName=> SELECT 'abc' SIMILAR TO '%b%' as blah FROM tableName limit 1;
 blah
------
 t
(1 row)

Further reading: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP

POSIX regular expressions

snowlink=> SELECT 'abc' ~ '.*b.*' as blah FROM snow_hosts limit 1;
 blah
------
 t
(1 row)

Further reading: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Example queries

dbName=> select sys_id, name, datacenter_location, fqdn, ip_address, sys_updated_on, install_status FROM tableName WHERE install_status='Installed' AND fqdn IS NULL ORDER BY sys_updated_on DESC;

• ORDER BY
  • allows also expressions like a + b
  • ASC/DESC

snowlink=> SELCT 'abc' LIKE 'abc' as blah FROM snow_hosts limit 4;
 blah
------
 t
 t
 t
 t
(4 rows)

Get number of rows in a table

dbName=> SELECT count(*) AS exact_count FROM tableName;
 exact_count
-------------
       17662
(1 row)

Further reading: https://stackoverflow.com/a/7945274/4773274 (esp. if you have big tables)

Get DB size on disk

dbName=> SELECT pg_size_pretty( pg_database_size('dbName') );
 pg_size_pretty
----------------
 38 MB
(1 row)

dbName=> SELECT pg_size_pretty( pg_total_relation_size('tableName') );
 pg_size_pretty
----------------
 25 MB
(1 row)