Table of contents
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];
ALTER TABLE [table] DROP COLUMN IF EXISTS [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])