-- copy data from one table to another
INSERT INTO schema.table1 (id,status,title,text,created_at,updated_at)
SELECT id,status,title,text,created_at,updated_at
FROM schema.table2
ORDER BY id DESC LIMIT 200;
-- increment sequence and show resulting value
SELECT nextval('schema.table_id_seq')
-- set sequence to current highest value of
SELECT setval('schema.table_id_seq', (SELECT MAX(id) FROM schema.table));
-- display highest id of table
SELECT MAX(id) FROM schema.table
-- add column, define default
ALTER TABLE table ADD COLUMN type VARCHAR DEFAULT 'default_type';
-- create database
CREATE DATABASE database_name OWNER user_name;
-- grant all on schema to certain user
GRANT ALL ON SCHEMA my_schema_name TO my_user_name;
-- create role
CREATE ROLE testuser WITH LOGIN ENCRYPTED PASSWORD 'secret';
-- change password
ALTER ROLE username WITH PASSWORD 'new_password';
-- remove password:
ALTER ROLE username WITH PASSWORD NULL;
-- create user (from promt):
createuser --interactive --pwprompt
-- enabling login through unix sockets
-- edit:
/etc/postgresql/10/main/pg_hba.conf
-- set: (method is the important one)
# TYPE DATABASE USER ADDRESS METHOD
local all all md5
slash commands
reference: https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546
\?: Show help (list of available commands with an explanation)
\q: Quit/Exit
\c __database__: Connect to a database
\d __table__: Show table definition (columns, etc.) including triggers
\d+ __table__: More detailed table definition including description and physical disk size
\l: List databases
\dy: List events
\df: List functions
\di: List indexes
\dn: List schemas
\dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
\dT+: List all data types
\dv: List views
in Frankfurt