Postgres: Difference between revisions

Revision as of 16:03, 26 May 2025

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

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;

List all users

SELECT * FROM pg_user;

Links