Index

Postgres DDL

administration

show Databases
SELECT datname FROM pg_database WHERE datistemplate = false;
show tables
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
show users
SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
Useful / Used queries
create user
CREATE USER xxx PASSWORD 'yyy';
GRANT ALL ON SCHEMA public TO xxx;
create database
CREATE DATABASE [dbname];
GRANT ALL PRIVILEGES ON [dbname] TO [user];
super user privileges
alter role [user] with superuser;
change password
ALTER ROLE [role] WITH PASSWORD '[passw]';
drop users / databases
drop owned by [role];
drop database [dbname];
drop schema public CASCADE;
drop user [user];
show constraints
select * from information_schema.table_constraints

editing tables

describe table
select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = '[table]';
create sequence
CREATE SEQUENCE [name]
CREATE SEQUENCE [name]
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
create sequence if not exists
CREATE SEQUENCE IF NOT EXISTS [name]
create table
create table [table]([column] [type]);
create table if not exists
CREATE TABLE IF NOT EXISTS [table]();
create table with auto generated PK
CREATE TABLE [table]([column] SERIAL);
create table with auto increment column
CREATE TABLE [table](
	[column] [type] NOT NULL DEFAULT nextval('[sequence]')
);
rename table
ALTER TABLE [old_name] RENAME TO [new_name];
auto increment existing column
ALTER TABLE ONLY [table] ALTER COLUMN [column] SET DEFAULT nextval('[sequence]');
alter column, add default (CURRENT_DATE -> today @ midnight)
ALTER TABLE ONLY [table] ALTER COLUMN [column] SET DEFAULT CURRENT_DATE;
add column
ALTER TABLE [table] ADD COLUMN [column] character varying(255);
add column with constraint, integer must be positive
ALTER TABLE [table] ADD COLUMN [column] integer NOT NULL DEFAULT 5 CHECK ([table].[column] >= 0);
drop single column
ALTER TABLE [table] DROP [column];
drop multiple columns
ALTER TABLE [table] DROP [column1] DROP [column2];
dropping a constraint
alter table users drop constraint [constraint];
add forein key constraint
ALTER TABLE ONLY [table] ADD CONSTRAINT [constraint_name] FOREIGN KEY ([column]) REFERENCES [referenced_table]([referenced_column]);
documentation
https://www.postgresql.org/docs/current/sql-createtable.html
https://www.postgresql.org/docs/current/sql-createsequence.html

data types

small number from sequence (-32768 to +32767)
[column] smallserial
[column] smallint DEFAULT nextval('[sequence]'))
integer from sequence (-2147483648 to +2147483647)
[column] serial
[column] integer DEFAULT nextval('[sequence]'))
large integer from sequence
[column] bigserial
[column] bigint DEFAULT nextval('[sequence]'))
varchar
[column] character varying(255);
date defaulting to today
[column] date not null default CURRENT_DATE
timestamp defaulting to right now
[column] timestamp without time zone not null default now()
documentation
https://www.postgresql.org/docs/current/datatype.html
https://www.postgresql.org/docs/current/datatype-numeric.html
https://www.postgresql.org/docs/current/datatype-character.html
https://www.postgresql.org/docs/current/datatype-binary.html
https://www.postgresql.org/docs/current/datatype-datetime.html
https://www.postgresql.org/docs/current/datatype-boolean.html

constraints

not null
CREATE TABLE [table] (
  [column] [type] NOT NULL
);
assigning a name to a constraint
CREATE TABLE [table] (
    [column] integer CONSTRAINT [name] UNIQUE,
primary key
[column] [type] PRIMARY KEY,
check constraint on column
[column] numeric CHECK ([column] > 0)
check constraint on table
CHECK ([column] > 0)
check constraint with name
[column] numeric CONSTRAINT [name] CHECK (price > 0)
check constraint over multiple columns
CREATE TABLE products (
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);
check not empty
CHECK ([column] <> '')
check regex (string not blank)
CREATE TABLE [table]([column] CHAR(6) PRIMARY KEY CHECK ([column] !~'^\s*$'));
foreign key
[column] integer REFERENCES [table] ([column]),
cascade delete
[column] integer REFERENCES [table] ([column]) ON DELETE CASCADE,
alter table only [table] add constraint [constraint_name] FOREIGN KEY ([column]) REFERENCES [table] ([column]) ON DELETE CASCADE;
unique
CREATE TABLE [table] (
    [column] [type] UNIQUE
);
CREATE TABLE [table] (
    [column] [type],
    UNIQUE (product_no)
);
multi column unique
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

alter table [table] add CONSTRAINT [name] UNIQUE ([column], [column])