SQL vs PostgreSQL / psql Reference

Start with the mental model: SQL is the language, PostgreSQL is the database system, and psql is one client used to talk to PostgreSQL.

SQL / CONCEPT

POSTGRESQL

psql / CLIENT

Panel 1 / 6

01 — What SQL, PostgreSQL & psql Are

What SQL, PostgreSQL & psql Are

Core Difference

SQL

A language for defining, querying, changing, and controlling relational data.

Used inside psql, applications, GUI clients, migrations, and other database tools.

PostgreSQL

A relational database management system that implements SQL plus PostgreSQL-specific features.

psql connects to a running PostgreSQL server and sends work to it.

psql

A command-line client for PostgreSQL, not a database and not a replacement for SQL.

Accepts SQL plus psql-only backslash meta-commands such as \dt and \d.

What SQL, PostgreSQL & psql Are

Where Code Executes

SELECT * FROM users;

SQL statement interpreted and executed by the PostgreSQL server.

Type it at the psql prompt; psql sends it to PostgreSQL.

CREATE TABLE ...;

SQL data-definition statement executed by PostgreSQL.

psql is only the interactive client carrying the statement.

\dt

Not SQL.

psql meta-command; psql itself uses PostgreSQL metadata to list tables.

\d users

Not SQL.

psql meta-command that describes a relation.

pg_dump

Not SQL.

Separate PostgreSQL client utility for backups; it is not a psql command.

What SQL, PostgreSQL & psql Are

Portable vs PostgreSQL-Specific

Portable SQL

SELECT, INSERT, UPDATE, DELETE, JOIN, GROUP BY, CREATE TABLE, constraints, and transactions are broadly shared across relational databases.

PostgreSQL dialect

Adds PostgreSQL syntax, operators, data types, functions, extensions, and implementation details.

psql layer

Adds interactive conveniences: slash commands, variables, query buffer tools, formatting, scripting, timing, and file execution.

Rule of thumb

If it begins with an unquoted backslash at the psql prompt, it is normally a psql meta-command rather than SQL.

What SQL, PostgreSQL & psql Are

Typical Workflow

1. Connect

Use psql or another client to open a PostgreSQL connection.

2. Inspect

Use psql meta-commands such as \l, \dn, \dt, and \d.

3. Query

Write SQL such as SELECT, JOIN, GROUP BY, INSERT, and UPDATE.

4. Use PostgreSQL features

Apply PostgreSQL types/operators such as JSONB, arrays, RETURNING, ILIKE, or ON CONFLICT when useful.

5. Diagnose

Use EXPLAIN / EXPLAIN ANALYZE and psql timing/output tools.

6. Automate

Move repeatable SQL into migration/query files and run with application tooling or psql -f.

The SQL fundamentals stay recognizable in PostgreSQL; psql supplies the prompt, connection, query buffer, and output controls.

SQL / CONCEPT

POSTGRESQL

psql / CLIENT

Panel 2 / 6

02 — SQL Querying in PostgreSQL

SQL Querying in PostgreSQL

SELECT Anatomy

SELECT columns

Choose result expressions or columns.

Enter normally at the psql prompt.

FROM table

Choose source relations.

Use \dt first when you need to discover tables.

JOIN ... ON ...

Combine related rows.

Use \d table to inspect keys and columns.

WHERE condition

Filter individual rows before grouping.

\p shows the current query buffer before execution.

GROUP BY columns

Group rows for aggregate calculations.

\g sends the current query buffer to the server.

HAVING condition

Filter aggregate groups.

A semicolon normally terminates and sends a complete SQL statement.

ORDER BY expression

Sort result rows.

\pset controls how psql formats returned rows.

LIMIT n OFFSET n

PostgreSQL row-limiting syntax commonly used for pagination.

\timing can show execution duration.

SQL Querying in PostgreSQL

Joins

INNER JOIN

Return matching rows from both sides.

LEFT JOIN

Keep every left row and matching right rows.

RIGHT JOIN

Keep every right row and matching left rows.

FULL OUTER JOIN

Keep matched rows plus unmatched rows from both sides.

CROSS JOIN

Produce the Cartesian product.

SELF JOIN

Join a relation to itself using aliases.

USING (column)

Concise PostgreSQL/SQL join syntax when join-column names match.

Qualified names

Use aliases such as u.id and o.id to avoid ambiguity.

SQL Querying in PostgreSQL

Aggregates & Windows

COUNT(*)

Count rows.

COUNT(DISTINCT id)

Count distinct non-NULL values.

SUM(amount)

Total numeric values.

AVG(amount)

Average non-NULL values.

MIN / MAX

Smallest or largest value.

ROW_NUMBER() OVER (...)

Number rows without collapsing them.

RANK() / DENSE_RANK()

Ranking window functions.

LAG() / LEAD()

Read previous or following row values.

PARTITION BY

Restart a window calculation per group.

SQL Querying in PostgreSQL

Subqueries, CTEs & Sets

WHERE EXISTS (...)

Test whether a subquery returns a row.

WHERE id IN (SELECT ...)

Filter against a subquery result set.

WITH name AS (...)

Create a common table expression for one statement.

WITH RECURSIVE

Express recursive hierarchies and graph/tree traversal.

UNION

Combine compatible result sets and remove duplicates.

UNION ALL

Combine compatible result sets without duplicate removal.

INTERSECT

Rows common to both result sets.

EXCEPT

Rows in the first result but not the second.

SQL Querying in PostgreSQL

NULL & Expressions

IS NULL / IS NOT NULL

Correctly test for NULL.

COALESCE(a,b)

Return the first non-NULL expression.

NULLIF(a,b)

Return NULL when two expressions compare equal.

CASE WHEN ... THEN ... END

Conditional SQL expression.

CAST(value AS type)

Standard-style explicit type conversion.

value::type

PostgreSQL shorthand cast syntax.

CURRENT_DATE

Current SQL date.

CURRENT_TIMESTAMP

Current date and time.

SQL Querying in PostgreSQL

psql Query Buffer

\p

Display the current query buffer.

\g

Send the current query buffer to PostgreSQL.

\gx

Send the query and show expanded output where supported.

\r

Reset/clear the query buffer.

\e

Open the current query buffer in an editor.

\ef function

Edit a function definition.

\ev view

Edit a view definition.

\w file.sql

Write the current query buffer to a file.

PostgreSQL speaks SQL, but its dialect includes powerful types, operators, expressions, and data-changing features that are not universally portable.

SQL / CONCEPT

POSTGRESQL

psql / CLIENT

Panel 3 / 6

03 — PostgreSQL Extensions to Everyday SQL

PostgreSQL Extensions to Everyday SQL

PostgreSQL Data Types

BOOLEAN

Native true/false type.

SMALLINT / INTEGER / BIGINT

Signed integer types.

NUMERIC(p,s)

Exact arbitrary-precision numeric values.

TEXT / VARCHAR / CHAR

Character types.

BYTEA

Binary string data.

UUID

Native universally unique identifier type.

JSON / JSONB

JSON text and binary-decomposed JSON storage; JSONB supports indexing and rich operators.

ARRAY

Native arrays of supported element types.

ENUM

User-defined enumerated type.

RANGE / MULTIRANGE

Native range types for values such as numbers and timestamps.

INET / CIDR / MACADDR

Network-address data types.

TIMESTAMP / TIMESTAMPTZ

Timestamp without and with time-zone semantics.

PostgreSQL Extensions to Everyday SQL

Identity & Generated Values

SQL standard identity

GENERATED ... AS IDENTITY is the modern identity-column concept.

PostgreSQL supports identity columns directly.

SERIAL

Not standard SQL; historical PostgreSQL shorthand backed by a sequence.

Still encountered in existing schemas.

nextval(sequence)

PostgreSQL sequence function.

psql \d table exposes default/identity information.

GENERATED ALWAYS AS (...) STORED/VIRTUAL

Generated-column syntax/features are PostgreSQL-version dependent.

Inspect current definition with \d+ table.

PostgreSQL Extensions to Everyday SQL

PostgreSQL Operators

ILIKE

Case-insensitive pattern matching.

~ / ~*

Regular-expression match, case-sensitive / insensitive.

!~ / !~*

Regular-expression non-match.

-> / ->>

Extract JSON/JSONB object/array values as JSON or text.

@>

Containment operator used by types including JSONB and arrays.

<@

Contained-by operator.

&&

Overlap operator for supported types such as arrays/ranges.

||

Concatenate strings and, in PostgreSQL contexts, supported collection-like values.

ANY(array)

Compare against any element of an array.

ALL(array)

Compare against every element of an array.

PostgreSQL Extensions to Everyday SQL

High-Value PostgreSQL Syntax

INSERT ... RETURNING id

Return inserted values without issuing a separate SELECT.

UPDATE ... RETURNING *

Return rows changed by UPDATE.

DELETE ... RETURNING *

Return deleted rows.

INSERT ... ON CONFLICT ...

PostgreSQL upsert / conflict handling.

DISTINCT ON (expr)

PostgreSQL extension for selecting the first row per distinct expression under a defined ORDER BY.

FILTER (WHERE ...)

Apply a condition to an aggregate.

LATERAL

Allow a FROM item/subquery to reference columns from preceding FROM items.

CREATE EXTENSION name

Install an available PostgreSQL extension into the current database.

PostgreSQL Extensions to Everyday SQL

JSONB

payload->'user'

Extract a JSON object value.

payload->>'name'

Extract a JSON scalar as text.

payload @> '{"active":true}'

Test JSONB containment.

jsonb_build_object(...)

Construct a JSONB object.

jsonb_array_elements(...)

Expand a JSON array into rows.

GIN index

Common index family for searchable JSONB containment/key workloads.

PostgreSQL Extensions to Everyday SQL

Arrays

ARRAY[1,2,3]

Construct an array.

values[1]

Access an array element; PostgreSQL arrays normally use one-based subscripts.

array_length(values,1)

Return a dimension length.

unnest(values)

Expand an array to rows.

3 = ANY(values)

Test whether an array contains a matching value.

values @> ARRAY[3]

Array containment test.

PostgreSQL Extensions to Everyday SQL

Schemas & Search Path

schema.table

Fully qualify an object.

CREATE SCHEMA app

Create a PostgreSQL schema namespace.

SHOW search_path

Inspect schema lookup order.

SET search_path TO app, public

Change lookup order for the session/transaction scope.

public

Default schema commonly present in PostgreSQL databases.

\dn

psql meta-command listing schemas.

psql adds a command-line layer around PostgreSQL: connections, discovery, descriptions, variables, formatting, files, timing, and scripting.

SQL / CONCEPT

POSTGRESQL

psql / CLIENT

Panel 4 / 6

04 — psql Interactive Reference

psql Interactive Reference

Connect from the Shell

psql

Connect using defaults/environment values.

psql -d appdb

Connect to a database.

psql -U user -d appdb

Choose user and database.

psql -h localhost -p 5432 -U user -d appdb

Specify host, port, user, and database.

psql 'postgresql://user@host:5432/db'

Connect using a PostgreSQL connection URI.

psql -c 'SELECT 1;'

Run one command and exit.

psql -f script.sql appdb

Execute a SQL/psql script file.

psql -X

Do not read startup files such as ~/.psqlrc.

psql --set=ON_ERROR_STOP=1 -f script.sql db

Make scripted execution stop after errors.

psql Interactive Reference

Connection & Session Meta-Commands

\conninfo

Show current connection information.

\c dbname

Connect/switch to another database.

\c dbname user host port

Reconnect with explicit parameters.

\password [user]

Interactively change a PostgreSQL password.

\encoding

Show client encoding.

\encoding UTF8

Set client encoding.

\q

Quit psql.

psql Interactive Reference

Discover Databases & Objects

\l

List databases.

\l+

List databases with additional details.

\dn

List schemas.

\dt

List tables visible in the search path.

\dt schema.*

List tables in a specific schema.

\dv

List views.

\dm

List materialized views.

\di

List indexes.

\ds

List sequences.

\df

List functions.

\dT

List data types.

\dx

List installed extensions.

\du

List roles.

psql Interactive Reference

Describe Objects

\d table

Describe a table/relation.

\d+ table

Describe with additional storage/details.

\d index

Describe an index.

\d view

Describe a view.

\df+ function

Describe functions with more detail.

\dp table

Show access privileges.

\dd object

Show object comments/descriptions where available.

psql Interactive Reference

Output & Display

\x

Toggle expanded row display.

\pset format aligned

Use aligned table output.

\pset format unaligned

Use unaligned output.

\pset format csv

Use CSV output where supported.

\pset border 0

Change table border style.

\pset null 'NULL'

Choose how NULL is displayed.

\a

Toggle aligned/unaligned output.

\t

Toggle tuples-only output.

\H

Toggle HTML output.

\o file

Send future query output to a file.

\o

Restore output to standard output.

psql Interactive Reference

Timing & Execution

\timing

Toggle display of statement execution time.

\g

Execute current query buffer.

\gexec

Execute each field of query output as SQL commands; powerful and potentially destructive.

\gset prefix_

Store a one-row result into psql variables.

\watch 2

Repeat the current query every two seconds.

\errverbose

Repeat the most recent server error at maximum verbosity.

psql Interactive Reference

Files & Scripts

\i file.sql

Read and execute commands from a file.

\ir relative.sql

Read a file relative to the current script file.

\e

Edit current query buffer or a file.

\w file.sql

Write current query buffer to a file.

\cd path

Change psql's current working directory.

\! command

Run a shell command from inside psql.

\qecho text

Write text to the current query-output stream.

\echo text

Write text to standard output.

psql Interactive Reference

psql Variables

\set name value

Set a psql variable.

:name

Interpolate a variable without quoting; use only when the inserted text is already safe SQL syntax.

:'name'

Interpolate and quote a variable as an SQL literal.

:"name"

Interpolate and quote a variable as an SQL identifier.

\unset name

Remove a psql variable.

\set

List currently defined psql variables.

ON_ERROR_STOP

When enabled, scripts stop after an error rather than continuing.

AUTOCOMMIT

psql variable controlling whether successful statements are automatically committed outside explicit transactions.

Most database changes are SQL executed by PostgreSQL; psql mainly helps inspect, execute, script, and verify them.

SQL / CONCEPT

POSTGRESQL

psql / CLIENT

Panel 5 / 6

05 — DDL, DML, Transactions & Security

DDL, DML, Transactions & Security

Create & Change Tables

CREATE TABLE users (...);

SQL DDL creates a table in PostgreSQL.

\d users verifies the resulting definition.

ALTER TABLE users ADD COLUMN ...;

SQL changes table structure.

\d+ users inspects the current structure.

ALTER TABLE ... ADD CONSTRAINT ...;

SQL adds constraints.

\d table shows many constraints and indexes.

DROP TABLE users;

SQL removes a table.

\dt confirms what remains.

CREATE VIEW name AS SELECT ...;

SQL creates a view.

\dv lists views.

CREATE MATERIALIZED VIEW ...;

PostgreSQL creates a stored query result.

\dm lists materialized views.

DDL, DML, Transactions & Security

Constraints

PRIMARY KEY

Uniquely identifies a row.

FOREIGN KEY

Enforces a relationship to referenced rows.

UNIQUE

Prevents duplicate values/combinations.

NOT NULL

Requires a value.

CHECK (...)

Enforces a row-level predicate.

DEFAULT expression

Supplies a value when INSERT omits the column.

EXCLUDE

PostgreSQL constraint preventing rows from matching specified operator combinations.

DEFERRABLE

Allows supported constraint checking to be deferred within a transaction.

DDL, DML, Transactions & Security

Data Changes

INSERT INTO ... VALUES ...

Create rows.

INSERT INTO ... SELECT ...

Insert rows produced by a query.

UPDATE ... SET ... WHERE ...

Change matching rows.

DELETE FROM ... WHERE ...

Delete matching rows.

TRUNCATE table

Quickly remove all rows; PostgreSQL-specific options affect identity/cascading behavior.

RETURNING

Return affected rows from INSERT/UPDATE/DELETE/MERGE-style changes.

ON CONFLICT

Handle unique/exclusion conflicts during INSERT.

DDL, DML, Transactions & Security

Transactions

BEGIN;

Start an explicit SQL transaction.

psql prompt typically changes to indicate transaction state.

COMMIT;

Commit SQL transaction changes.

\echo can annotate scripts around transaction boundaries.

ROLLBACK;

Undo the current transaction.

\errverbose can inspect the prior error.

SAVEPOINT name;

Create an intermediate rollback point.

Still SQL; psql simply sends it to PostgreSQL.

ROLLBACK TO SAVEPOINT name;

Undo work after a savepoint.

Useful interactively while testing multi-step changes.

SET TRANSACTION ...;

Choose transaction characteristics.

SHOW transaction_isolation can inspect current settings.

DDL, DML, Transactions & Security

Roles & Privileges

CREATE ROLE name LOGIN

Create a PostgreSQL login role.

ALTER ROLE name ...

Change role properties.

GRANT SELECT ON table TO role

Grant object privileges.

REVOKE SELECT ON table FROM role

Remove object privileges.

GRANT role_a TO role_b

Grant membership in another role.

SET ROLE name

Assume another role within permitted membership.

\du

psql: list roles and attributes.

\dp table

psql: display table/view/sequence privileges.

DDL, DML, Transactions & Security

Schemas & Ownership

CREATE SCHEMA app

Create a namespace.

ALTER SCHEMA app OWNER TO role

Change schema ownership.

GRANT USAGE ON SCHEMA app TO role

Allow name resolution/access within a schema.

GRANT CREATE ON SCHEMA app TO role

Allow object creation in a schema.

ALTER TABLE t OWNER TO role

Change table owner.

\dn+

psql: inspect schemas with additional information.

DDL, DML, Transactions & Security

Prepared SQL from Applications

$1, $2 ...

PostgreSQL server-side positional parameter notation used in prepared statements/protocol contexts.

Client placeholders

Libraries may expose ?, :name, $1, or another API-specific placeholder syntax.

Values vs identifiers

Parameters represent data values, not arbitrary table/column names.

Security rule

Never concatenate untrusted values directly into executable SQL.

psql variables

Useful for scripts, but they are a separate psql interpolation mechanism, not application prepared statements.

PostgreSQL adds implementation-specific performance and administration tools; psql is a convenient place to run and inspect them.

SQL / CONCEPT

POSTGRESQL

psql / CLIENT

Panel 6 / 6

06 — Indexes, Performance, Maintenance & Tools

Indexes, Performance, Maintenance & Tools

Indexes

CREATE INDEX idx ON table(column);

Create a B-tree index by default.

CREATE UNIQUE INDEX ...

Create an index that also enforces uniqueness.

CREATE INDEX ... USING GIN (...)

Create a GIN index for supported collection/search workloads.

CREATE INDEX ... USING GiST (...)

Create a GiST index for supported generalized search workloads.

CREATE INDEX ... WHERE condition

Create a PostgreSQL partial index.

CREATE INDEX ... (LOWER(email))

Create an expression index.

REINDEX ...

Rebuild an index/table/database index set using PostgreSQL commands.

\di+

psql: list indexes with additional information.

Indexes, Performance, Maintenance & Tools

EXPLAIN

EXPLAIN SELECT ...

PostgreSQL shows the planned execution strategy without normally executing the SELECT.

Run directly in psql and inspect the plan text.

EXPLAIN ANALYZE SELECT ...

Executes the query and reports actual timing/rows; use carefully.

\timing can show total client-observed statement time.

EXPLAIN (ANALYZE, BUFFERS) ...

Adds execution and buffer-I/O details.

\x can improve readability for some diagnostic outputs.

Seq Scan

Sequentially scans table pages/rows.

Not automatically bad; can be correct for small/broad queries.

Index Scan / Index Only Scan

Uses a suitable index path.

\d table and \di help inspect available indexes.

Indexes, Performance, Maintenance & Tools

VACUUM & ANALYZE

VACUUM table;

Reclaim reusable space and maintain MVCC-related storage metadata.

VACUUM (ANALYZE) table;

Vacuum and refresh planner statistics.

VACUUM FULL table;

Rewrite/compact a table and take stronger locks; use deliberately.

ANALYZE table;

Refresh planner statistics.

autovacuum

PostgreSQL background maintenance mechanism for vacuum/analyze work.

\d+ table

psql: inspect table details while diagnosing storage/schema issues.

Indexes, Performance, Maintenance & Tools

Session & Server Settings

SHOW setting;

Read one PostgreSQL configuration parameter.

SHOW ALL;

Display session-visible configuration parameters.

SET setting = value;

Change a setting for the current session where permitted.

SET LOCAL setting = value;

Change a setting for the current transaction.

RESET setting;

Restore a setting to its default for the session.

SELECT current_setting('name');

Read a configuration value through SQL.

\conninfo

psql: show current connection endpoint/user/database.

Indexes, Performance, Maintenance & Tools

COPY vs \copy

COPY table TO '/server/path/file.csv' CSV HEADER;

SQL COPY reads/writes files from the PostgreSQL server's filesystem and uses server privileges.

\copy table TO 'local.csv' CSV HEADER

COPY table FROM '/server/path/file.csv' CSV HEADER;

Server-side SQL import.

\copy table FROM 'local.csv' CSV HEADER

Privileges

Server COPY can require elevated file privileges.

\copy uses the psql/client machine's filesystem and client access.

Use case

Fast bulk transfer between tables/queries and files.

\copy is usually more convenient from a developer workstation.

Indexes, Performance, Maintenance & Tools

Backup & Restore Utilities

pg_dump appdb > backup.sql

Create a plain-SQL logical backup using the pg_dump client utility.

pg_dump -Fc appdb -f backup.dump

Create PostgreSQL custom-format backup.

psql appdb < backup.sql

Restore a plain SQL dump through psql.

pg_restore -d appdb backup.dump

Restore a non-plain pg_dump archive.

pg_dumpall

Dump all databases plus global objects in a PostgreSQL cluster in SQL form.

createdb / dropdb

Command-line utilities for creating/dropping PostgreSQL databases.

Note

pg_dump, pg_restore, createdb, and dropdb are PostgreSQL client utilities, not psql slash commands.

Indexes, Performance, Maintenance & Tools

Developer Troubleshooting

Connection refused

Confirm PostgreSQL is running, host/port are correct, and network/firewall rules allow the connection.

password authentication failed

Verify role, password, pg_hba.conf rules, and chosen connection target.

relation does not exist

Check current database, schema, search_path, quoted/case-sensitive identifiers, and object spelling.

permission denied

Check role membership, object privileges, schema USAGE, and ownership.

current transaction is aborted

A statement failed inside a transaction; ROLLBACK or rollback to a savepoint before continuing.

slow query

Inspect EXPLAIN ANALYZE, row estimates, indexes, predicates, join order/strategy, and statistics.

psql aid

\conninfo, \d, \dn, \dt, \timing, \errverbose, and \x are high-value diagnostics.

Indexes, Performance, Maintenance & Tools

Which Tool Should I Use?

SQL

Use when expressing data queries, schema changes, transactions, privileges, and database logic.

Portable concept; PostgreSQL executes its supported SQL dialect.

PostgreSQL feature

Use when PostgreSQL provides a capability valuable to the application, such as JSONB, arrays, RETURNING, extensions, or specialized indexes.

Document dialect dependence when portability matters.

psql

Use for interactive querying, discovery, diagnostics, scripts, formatting, and quick administration.

Do not confuse psql meta-commands with SQL embedded in application code.

PostgreSQL utility

Use pg_dump, pg_restore, createdb, dropdb, vacuumdb, and related tools for their dedicated jobs.

These are separate executables, even though they ship with PostgreSQL client tooling.