postgres comands

postgres.md

Postgres tips

DML = Data Manipulation language (INSERT, UPDATE, DELETE and SELECT) DDL = Data definition Language (CREATE, ALTER, DROP)

smallserial, serial and bigserial columns are autoincrement

psql commands

Show roles: \du Show tables: \dt Show databases: \l Connect to a database: \c Show columns of a table: \d

or \d+ Quit: \q

Commands

createdb -O user database

dropdb database

# simple plain text backup
pg_dump --no-owner -d database > /tmp/database.sql

# migrate from one serve to another without create a file
# -C add create database to script
# --no-owner remover owner from backup
pg_dump -C --no-owner -d database| psql -h 10.108.0.2 -U user database

# only exports one table data with INSERT TO not COPY
# use PGPASSWORD var to set password
PGPASSWORD=password pg_dump --column-inserts -t table_name -a database

# restore a backup with custom format
pg_restore -d "postgres://user:password@ip_address:5432/database" database.custom

# export table to csv
psql -U postgres -d database -c "\COPY videos TO '/srv/videos.csv' DELIMITER ',' CSV HEADER;"

# import table from csv
psql -U postgres -d database -c "\COPY videos(title,version,aasm_state,system_id,created_by_id,created_at,updated_at,briefing) FROM '/srv/videos.csv' DELIMITER ',' CSV HEADER;"

# re-run query with /watch
SELECT * from products; \watch

CREATE DATABASE database WITH OWNER user;
ALTER DATABASE database RENAME TO new_name;
DROP DATABASE database;

CREATE SCHEMA IF NOT EXISTS schema;
ALTER SCHEMA schema;
DROP SCHEMA IF EXISTS schema;

ALTER DATABASE database OWNER TO user;

# list pg extensions
SELECT * FROM pg_available_extensions;

# install extension
CREATE EXTENSION pgcrypto;
CREATE ROLE users NOCREATEDB NOCREATEROLE NOLOGIN INHERIT;
CREATE ROLE username WITH INHERIT LOGIN ENCRYPTED PASSWORD 'password' IN ROLE users;

# remove all permissions of a role
REVOKE ALL ON ALL TABLES IN SCHEMA schema FROM role;
REVOKE ALL ON SCHEMA schema FROM role
REVOKE ALL ON DATABASE database FROM role;
DROP ROLE role;
	
# create read-only user	
GRANT CONNECT ON DATABASE example_database TO example_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO example_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO example_user;

-- Table: public.capsules

-- DROP TABLE public.capsules;

CREATE TABLE public.capsules
(
    id bigint NOT NULL DEFAULT nextval('capsules_id_seq'::regclass),
    from_name character varying COLLATE pg_catalog."default",
    from_email character varying COLLATE pg_catalog."default",
    guid character varying COLLATE pg_catalog."default" NOT NULL,
    synched boolean,
    event_id bigint,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    remote_id character varying COLLATE pg_catalog."default",
    author_birthdate date,
    CONSTRAINT capsules_pkey PRIMARY KEY (id),
    CONSTRAINT fk_rails_eaaac7dd2f FOREIGN KEY (event_id)
        REFERENCES public.events (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.capsules
    OWNER to postgres;
-- Index: index_capsules_on_event_id

-- DROP INDEX public.index_capsules_on_event_id;

CREATE INDEX index_capsules_on_event_id
    ON public.capsules USING btree
    (event_id ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: index_capsules_on_guid

-- DROP INDEX public.index_capsules_on_guid;

CREATE UNIQUE INDEX index_capsules_on_guid
    ON public.capsules USING btree
    (guid COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

# ON CONFLICT - do nothing if an error happens in the insert method
INSERT INTO CAPSULES (id, guid, created_at, updated_at) VALUES (1, '123', now(), now()) ON CONFLICT(id) DO NOTHING;

# ON CONFLICT with UPDATE. EXCLUDED is the row generate vy the insert.
INSERT INTO CAPSULES (guid, created_at, updated_at) VALUES ('123', now(), now()) ON CONFLICT(guid) DO UPDATE SET guid=EXCLUDED.guid;

# information_schema.columns is a postgres view that shows info about all columns.
SELECT table_name, column_name, data_type FROM information_schema.columns where table_catalog = 'database' and table_schema = 'public';

# Postgres CTE. They are typically used to simplify complex joins and subqueries.
WITH example AS ( SELECT id, from_name FROM capsules)
SELECT * FROM example;

Postgres functions

SQL

CREATE OR REPLACE FUNCTION sum_function(num1 INTEGER, num2 INTEGER)
RETURNS INTEGER
LANGUAGE SQL
AS $$
	SELECT $1 + $2;
$$;

SELECT sum_function(1, 3);

PLPGSQL language

CREATE OR REPLACE FUNCTION user_info(user_id BIGINT)
RETURNS VARCHAR
SECURITY INVOKER
LANGUAGE PLPGSQL
CALLED ON NULL INPUT
AS $$
DECLARE username VARCHAR;
BEGIN 
	SELECT INTO username 'name: ' || name 
	FROM users
	WHERE id = user_id;
	
	RETURN username;
END; $$;

SELECT user_info(2)

See size information about tables

select table_name, pg_relation_size(quote_ident(table_name)), pg_table_size(quote_ident(table_name)), pg_indexes_size(quote_ident(table_name)), pg_total_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 5;