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

• View table information/structure

\d new_table

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

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