-- 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
\l
SELECT datname FROM pg_database
WHERE datistemplate = false;
CREATE DATABASE newdb OWNER user;
GRANT ALL PRIVILEGES ON DATABASE newdb TO user;
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
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