Postgres

Comment

-- single line comment
/* multi line comment */

Data types

Type	min	max	comment
boolean
char(n)
varchar(n)
text
smallint	-32768	32767
int	-2147483648	2147483647
serial			(~auto_increment in mysql)
float(n)
real / float8
numeric(p,s)
date
time
timestamp
timestamptz
interval
json
jsonb
uuid
+ special

Operators

		Example
=	equal	WHERE "time" = '2024-09-28 15:46:09.602492+00'
<>, !=	not equal
<, >	less/greater than
<=, >=	less/greater than or equal
IS NULL
LIKE		WHERE "time
	_ = single char	escaped = \_
	% = 0 or more chars	escaped = \%
ILIKE	case-insensitive LIKE
IN		WHERE name IN ('Joe', 'Bob', 'Jane')
		WHERE user_id IN (SELECT user_id FROM orders)
~	regex
!~	not regex
AND
OR
BETWEEN

Databases

• List all databases

\l

SELECT datname FROM pg_database
WHERE datistemplate = false;

• Create database

CREATE DATABASE newdb OWNER user;
GRANT ALL PRIVILEGES ON DATABASE newdb TO user;

• Connect to database

\c newdb

Tables

• List all tables

\dt
\dt *.*

SELECT table_catalog,table_schema,table_name FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' AND table_schema = 'public' 
ORDER BY table_type, table_name

• Number of rows in table

SELECT COUNT(*) FROM process_value_numeric;

• Biggest tables by size

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;

• View table information/structure

\d new_table

• Columns information

SELECT 
    ordinal_position,
    column_name, 
    data_type, 
    is_nullable, 
    column_default
FROM 
    information_schema.columns
WHERE 
    table_schema = 'public'
    AND table_name = 'your_table_name'
ORDER BY 
    ordinal_position;

• Indexes information

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'your_table_name'
ORDER BY indexname;

• Constraints information

SELECT
    tc.constraint_name,
    tc.constraint_type,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    chk.check_clause
FROM 
    information_schema.table_constraints AS tc
    LEFT JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
     AND tc.table_schema = kcu.table_schema
     AND tc.table_name = kcu.table_name
    LEFT JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
     AND ccu.table_schema = tc.table_schema
    LEFT JOIN information_schema.check_constraints AS chk
      ON tc.constraint_name = chk.constraint_name
WHERE tc.table_schema = 'public'
  AND tc.table_name = 'your_table_name'
ORDER BY tc.constraint_type, tc.constraint_name, kcu.ordinal_position;

List all users

SELECT * FROM pg_user;

Links

• https://www.w3schools.com/postgresql/
• https://www.timescale.com/learn/postgres-cheat-sheet
• https://www.timescale.com/learn/postgres-basics
• https://www.timescale.com/learn/postgres-guides
• https://www.timescale.com/learn/postgres-best-practices