ruk·si
/notes

🐘 PostgreSQL
Command Line

Updated at 2015-11-18 23:06

This note contains basic PostgreSQL command line usage.

Commands

\conninfo           -- tells connected database and user
\l                  -- lists all databases
\c                  -- switch database
\q                  -- exits psql
\h                  -- SQL help
\i ~/sqls/test.sql  --
\set COMP_KEYWORD_CASE upper    -- format tab completion in uppercase
\x auto                         -- readable formatting for big tables
\pset null '[NULL]'             -- show nulls as ¤
\d table_name                   -- show details about the given entity
\ef uuid_generate_v1            -- show details about a function

-- most of the following functions have S variant which also lists system stuff
-- or wildcard filtering e.g. \dt user*
\daS        -- lists all aggregate functions
\dt         -- lists all tables in the current schema and database
\dt *.      -- lists all tables in all schemas of the current database
\da         -- lists user-defined aggregate functions
\dC         -- lists type casting options
\dD         -- lists user-defined domains
\dn         -- lists user-defined schemas
\dm         -- lists user-defined materialized views
\dv         -- lists user-defined views
\di         -- lists user-defined indexes
\df         -- lists user-defined functions
\du         -- lists user-defined roles
\dt         -- lists user-defined data types
\dx         -- lists installed extensions
\dL         -- lists supported procedure languages

Create ~/.psqlrc for better command line usage.

\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
\set PROMPT2 '[more] %R > '
\set COMP_KEYWORD_CASE upper
\x auto
\pset null '[NULL]'

createdb -T app_db app_backup
dropdb app_db
createdb -T app_backup app_db

Backups

pg_dump -Fc woozerdb > woozerdb.bak
pg_restore -Fc woozerdb.bak

Exporting Data

-- Perform client copying, not very efficient but good for small datasets.
-- Neat because it can write to your local machine from a remote database.
-- Use database dumps or SQL COPY for larger datasets.
\copy users TO ~/test.tsv;
\copy (SELECT name FROM users) TO ~/test.tsv;
\copy users TO ~/test.csv WITH (FORMAT csv, HEADER true);

\copy users FROM '~/test.csv' WITH CSV;

You restore data with pg_restore.

pg_restore --verbose --clean --no-acl --no-owner \
    -U app_user -d app_database /path/to/db.dump

Sources