PostgreSQL Commands Explained with Examples (Complete 2026 Guide)

PostgreSQL commands divide into two jobs. psql backslash commands inspect or control the client, while SQL statements create, read, change, and remove data on the PostgreSQL server. Keeping that boundary clear prevents a common beginner mistake: entering a psql command where PostgreSQL expects SQL, or treating a data-changing statement as if it were only a local shortcut.

PostgreSQL commands fall into two groups

Use psql when you need to connect, inspect the current database, list objects, or leave the interactive client. Use SQL when the database must define a table, add rows, query rows, or change stored data.

psql meta-commands stay in the client

psql processes commands that begin with a backslash before it sends anything to PostgreSQL. PostgreSQL documents these as meta-commands, so they do not end with a SQL semicolon and they are available only inside psql.

A backslash at the beginning is the quickest visual cue.

\conninfo
\l
\c inventory
\dt
\d products
\q

• \conninfo prints the current connection details.
• \l lists databases that the connected role can see.
• \c inventory switches the session to the inventory database.
• \dt lists tables in the active schema search path.
• \d products describes the products table.
• \q exits psql.

Run \conninfo before a data change when several databases use similar table names. It shows the database, role, host, and port for the open session, which gives you a direct check before a command reaches the server.

SQL statements run on the server

SQL statements describe work for PostgreSQL itself. CREATE TABLE changes database structure, INSERT adds rows, SELECT reads rows, UPDATE changes matching rows, and DELETE removes matching rows.

In interactive psql, PostgreSQL receives a SQL statement after its terminating semicolon. The semicolon belongs to SQL syntax. Do not append one to a backslash meta-command.

Connect and inspect the database before changing it

A connection command needs the server address, database name, and PostgreSQL role. The -W flag asks psql for the password instead of placing it in the command line.

psql -h db.example.com -U app_user -d inventory -W

After the prompt opens, begin with \conninfo and \dt. If \dt reports no relations, you may be connected to the intended database but looking in a schema that has no tables visible through your current search path. Use \d table_name only after you have identified the table you mean to inspect.

PostgreSQL’s current psql documentation also covers connection options, input behavior, and the full meta-command reference. It is the right place to check less common client commands rather than guessing from a short list.

Create a small table and add rows

Start with a small table that makes each later command visible. The primary key gives every product a stable identifier, while NOT NULL rejects rows that omit a required name.

Define a table

CREATE TABLE products (
    product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL
);

CREATE TABLE defines the columns and constraints before any row exists. If the table name is already present, PostgreSQL returns an error instead of replacing the table. Choose a new name only when you want a separate object, not as a way to hide an unexpected schema conflict.

Insert rows and verify them

INSERT adds rows. RETURNING asks PostgreSQL to send back values from the row it accepted, so you can verify the generated identifier and stored values without writing a separate follow-up query.

INSERT INTO products (name, price)
VALUES ('Notebook', 12.50), ('Pen', 2.25)
RETURNING product_id, name, price;

SELECT product_id, name, price
FROM products
ORDER BY product_id;

The SELECT statement is also your safest preview tool. Use the same filter in SELECT that you plan to use in UPDATE or DELETE. You can inspect the affected rows before any change occurs.

The current CREATE TABLE and INSERT references document the full syntax. The INSERT reference also documents RETURNING, including its result for inserted rows.

Check the statement reference whenever a column type or optional clause changes the intended result.

Update or delete rows inside a transaction

UPDATE and DELETE become dangerous when they omit a WHERE clause. Without that condition, PostgreSQL applies the statement to every row in the target table.

Preview rows with SELECT

Run a SELECT first and examine the rows it returns. This step turns a broad assumption into a visible set of records before the transaction changes anything.

SELECT product_id, name, price
FROM products
WHERE name = 'Notebook';

Use RETURNING to verify a change

A transaction groups related statements. BEGIN starts the transaction, COMMIT keeps its changes, and ROLLBACK discards its changes. Put the verification query between the change and the decision to commit.

BEGIN;

UPDATE products
SET price = 10.00
WHERE name = 'Notebook'
RETURNING product_id, name, price;

SELECT product_id, name, price
FROM products
WHERE name = 'Notebook';

ROLLBACK;

This sequence leaves the Notebook price unchanged because ROLLBACK ends the transaction without saving the update. Replace ROLLBACK with COMMIT only after the returned row and verification query match the intended change.

DELETE follows the same rule. Start with SELECT using the exact WHERE condition, use DELETE with RETURNING to inspect removed rows, and keep the work inside a transaction when you need a final decision point. PostgreSQL’s UPDATE reference describes RETURNING for rows actually updated, while the transaction tutorial explains the commit and rollback boundary.

Use conditions and constraints to protect the data model

Commands become easier to trust when the table rejects values that do not belong in the model. PostgreSQL checks a constraint every time a statement attempts to create or change a row, so the database can stop an invalid value even when an application sends an incomplete request.

Constraints move a rule from application code into the database where every client must meet it.

A web service, import script, scheduled job, and interactive psql session can use different languages, but PostgreSQL applies the same primary-key, unique, not-null, foreign-key, and check rules before it accepts a row. Read the returned error and named constraint, then fix the value or model instead of removing a guard simply to make an insert succeed.

NOT NULL makes a column required, a primary key identifies each row, and UNIQUE prevents duplicate values in a column or combination of columns.

ALTER TABLE products
ADD CONSTRAINT products_price_nonnegative
CHECK (price >= 0);

The CHECK constraint rejects a negative price before it reaches the table and reports the failed constraint, so you know the statement was rejected rather than partially accepted.

Use \d products after an ALTER TABLE command. It shows the constraints attached to the table, which is more reliable than assuming the change reached the intended database.

Filter, sort, and limit a query deliberately

SELECT can return every column and row, but that is rarely the best first query against a working table. Name the columns you need, add a WHERE condition when you know the subset, and use ORDER BY when the order changes the decision you make from the result.

SELECT product_id, name, price
FROM products
WHERE price >= 5.00
ORDER BY price DESC
LIMIT 10;

This query reads only the selected columns, keeps products priced at five or more, sorts the remaining rows by price, and returns at most ten rows. LIMIT is useful while you inspect an unfamiliar table because it prevents a diagnostic query from printing an unbounded result set into the terminal.

LIMIT only bounds SELECT output, while UPDATE and DELETE need a WHERE condition that identifies the intended rows and a preview SELECT using that same condition.

Know when a command changes structure or rows

Data definition language changes database objects through commands such as CREATE TABLE, ALTER TABLE, and DROP TABLE, while INSERT, UPDATE, DELETE, and SELECT work with rows inside that structure.

The distinction changes how you prepare. A table-definition command can affect every application that depends on that table, while a data-change command needs a row-level filter and verification output.

DROP TABLE and TRUNCATE TABLE need extra care because they can remove many rows at once. Use a transaction where the command supports it, confirm the object name with \d or \dt, and keep a backup plan for data you cannot recreate.

Permissions are another boundary. A command can have valid SQL syntax and still fail because the connected role lacks access to the database, schema, table, or operation. Read the PostgreSQL error text before changing the command, since the error identifies whether the failure is syntax, object lookup, constraint enforcement, or privilege.

PostgreSQL command reference for daily work

Use this compact reference after you understand whether the task belongs to psql or SQL. It is a starting point for daily work, not a substitute for checking the exact syntax and permissions for your PostgreSQL version.

Where to go next

Use PostgreSQL’s SQL Commands reference when a statement needs options beyond these examples. The reliable daily sequence remains the same: confirm the connection, inspect the target rows, make a constrained change, verify the returned result, then commit or roll back.

Keep the official reference open when a command affects production data, schema ownership, or privileges.