PostgreSQL

Question 1

Start psql in “explain backslash” command mode

Answer 1

psql -E

Good for learning about inner workings of builtin postgres dbs.

Question 2

List databases

Answer 2

\l

Question 3

List tables

Answer 3

\dt
\dt pg_*
\dt+ information_schema.* (also shows size, description)

Question 4

Connect to a database

Answer 4

\c db_name

Question 5

List columns in a table

Answer 5

\d completions

Question 6

Arguments to \d (list tables?)

Answer 6

n = schemas
t = tables
v = views
s = sequences
i = indexes
E = foreign tables
C = type casts

Question 7

Help with psql vs help with SQL

Answer 7

psql: \?

SQL: \h

Question 8

Responsive formatting

Answer 8

\x auto

Question 9

List users and their roles

Answer 9

\du
\du+

Question 10

Write a command in your editor?

Answer 10

\e

Question 11

What 4 things do you need to connect to a DB?

Answer 11

target db (-d), hostname (-h), port (-p), user (-U)
env variables: PGDATABASE, PGHOST, PGPORT and/or PGUSER
~/.pgpass for passwords

Question 12

Connect to a db with a “conninfo” string or a URI.

Answer 12

psql postgres://user@host:port/db
\conninfo

Question 13

Set a variable and use command interpolation

Answer 13

\set foo bar
\echo :foo
SELECT * FROM :foo;

Question 14

Export query output to file

Answer 14

COPY TO. You can also use COPY FROM to import data from a file

Question 15

List all tables, including system catalogs

Answer 15

\dtv *.*

Question 16

Command history file

Answer 16

~/.psql_history, along with the rest of the history files.

Question 18

Show the search_path variable?

Answer 18

show search_path;

Question 19

Check which schema a command is actually checking…

Answer 19

explain verbose select * from …;

show search_path;

Question 20

What data type should you use for strings?

Answer 20

TEXT. More versatile than varchar(n) and char(n), same performance.

Question 21

What’s wrong with this command?

CREATE TABLE justjsonb (id integer, doc JSONB)

Answer 21

No semicolon. You should see ‘CREATE TABLE’ when the command executes successfully.