Postgres

Comment

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

Data types

Type	min	max	comment
boolean
char(n)
varchar(n)
text
smallint	-32768	32767
int
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
IS NOT NULL
LIKE		WHERE "time
	_ = single char	escaped = \_
	% = 0 or more chars	escaped = \%
NOT LIKE
ILIKE	case-insensitive LIKE
IN		WHERE name IN ('Joe', 'Bob', 'Jane')
		WHERE user_id IN (SELECT user_id FROM orders)
NOT IN
~	regex
!~	not regex

Databases

• List all databases

SELECT datname FROM pg_database
WHERE datistemplate = false;

Tables

• List all tables

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

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

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