psql Meta-Commands, Databases and Schemas in PostgreSQL

psql meta-commands

Commands starting with a backslash are handled by psql itself, not the server.

Command	Does
`\l`	List databases
`\c dbname`	Connect to another database
`\dt`	List tables
`\d tablename`	Describe a table
`\d+ tablename`	Describe with sizes and comments
`\dn`	List schemas
`\du`	List roles
`\di` `\dv` `\df`	Indexes, views, functions
`\dx`	Installed extensions
`\x`	Toggle expanded (vertical) output
`\timing`	Show how long each query takes
`\i file.sql`	Run a SQL file
`\e`	Edit the current query in your editor
`\copy`	Import/export CSV from the client side
`\?` `\h SELECT`	psql help / SQL syntax help
`\q`	Quit

\x on
SELECT * FROM students LIMIT 1;   -- now printed one field per line
\timing on

Creating databases

CREATE DATABASE school
  WITH ENCODING 'UTF8'
       LC_COLLATE 'en_US.UTF-8'
       LC_CTYPE   'en_US.UTF-8'
       TEMPLATE template0;

\c school
DROP DATABASE IF EXISTS old_db;

Unlike MySQL, you cannot query across databases in one statement. A single Postgres database is divided into schemas — that is the equivalent of what MySQL calls a database.

Schemas

A schema is a namespace for tables, views, functions and types.

CREATE SCHEMA academic;
CREATE SCHEMA billing AUTHORIZATION app_user;

CREATE TABLE academic.students (id serial PRIMARY KEY, name text);
SELECT * FROM academic.students;

\dn
DROP SCHEMA billing CASCADE;   -- CASCADE drops everything inside

Every database starts with a public schema. Since Postgres 15, ordinary users can no longer create objects in public by default — a security improvement.

search_path

The search_path decides which schemas are searched for an unqualified table name.

SHOW search_path;                       -- "$user", public
SET search_path TO academic, public;    -- session only
ALTER ROLE app SET search_path TO academic, public;   -- persistent for that role
ALTER DATABASE school SET search_path TO academic, public;

With the path above, SELECT * FROM students resolves to academic.students.

Useful system information

SELECT version();
SELECT current_database(), current_user, current_schema();
SELECT pg_size_pretty(pg_database_size('school'));

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog','information_schema');

Postgres exposes its own catalog in the pg_catalog schema (pg_class, pg_attribute, pg_stat_activity) and a standards-compliant view of it in information_schema. The pg_stat_* views are where you look when you want to know what the server is doing right now.

psql, Databases & Schemas