PostgreSQL Flashcards
Start psql in “explain backslash” command mode
psql -E
Good for learning about inner workings of builtin postgres dbs.
List databases
\l
List tables
\dt
\dt pg_*
\dt+ information_schema.* (also shows size, description)
Connect to a database
\c db_name
List columns in a table
\d completions
Arguments to \d (list tables?)
n = schemas t = tables v = views s = sequences i = indexes E = foreign tables C = type casts
Help with psql vs help with SQL
psql: \?
SQL: \h
Responsive formatting
\x auto
List users and their roles
\du
\du+
Write a command in your editor?
\e
What 4 things do you need to connect to a DB?
target db (-d), hostname (-h), port (-p), user (-U) env variables: PGDATABASE, PGHOST, PGPORT and/or PGUSER ~/.pgpass for passwords
Connect to a db with a “conninfo” string or a URI.
psql postgres://user@host:port/db
\conninfo
Set a variable and use command interpolation
\set foo bar
\echo :foo
SELECT * FROM :foo;
Export query output to file
COPY TO. You can also use COPY FROM to import data from a file
List all tables, including system catalogs
\dtv *.*
Command history file
~/.psql_history, along with the rest of the history files.
Show the search_path variable?
show search_path;
Check which schema a command is actually checking…
explain verbose select * from …;
show search_path;
What data type should you use for strings?
TEXT. More versatile than varchar(n) and char(n), same performance.
What’s wrong with this command?
CREATE TABLE justjsonb (id integer, doc JSONB)
No semicolon. You should see ‘CREATE TABLE’ when the command executes successfully.
