PostgreSQL Essentials: A Practical Command Reference

Start by separating three tasks: define a table, control which values it accepts, and insert complete rows. This PostgreSQL 18 reference focuses on CREATE TABLE, ALTER TABLE, constraints, and INSERT, with special attention to defaults, NULL values, and destructive changes. [1], [2], [3]

1. Choose the command by what you need to change

The most useful distinction is between table structure and stored rows. CREATE TABLE defines a table, which initially contains no data. INSERT adds rows. ALTER TABLE changes an existing table’s definition: it can add or remove columns and constraints, change defaults or column types, and rename columns or tables. Structural changes can affect stored information—dropping a column removes its data—so “changing the definition” does not mean “having no effect on data.” [3], [1]

Use the following reference to distinguish operations that can look similar but serve different purposes. In particular, adding a column is not the same as supplying a value during insertion, and removing a constraint is not the same as removing the constrained column. [1], [3]

2. Define a small table with meaningful rules

Consider a hypothetical product catalog in a practice database where these table names are unused. The following example combines documented table-definition and constraint syntax. The name must be present, and the price must both be present and satisfy a positive-value check. These are separate requirements, expressed separately. [2]

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

The important detail is CHECK does not replace NOT NULL. PostgreSQL considers a check satisfied when its expression evaluates to true or NULL. Consequently, CHECK (price > 0) alone does not prohibit a NULL price. NOT NULL closes that gap. Also, do not use CHECK as a general cross-row validation mechanism: PostgreSQL does not support checks that reference table data other than the row being inserted or updated. [2]

3. Insert rows with explicit column names

Continue the example by inserting two products. Naming the columns makes the value-to-column mapping explicit rather than relying on the table’s declared column order. PostgreSQL permits several rows in one VALUES clause, so both products can be expressed in one statement. These illustrative rows provide names and positive, non-NULL prices consistent with the rules above. [3], [2]

INSERT INTO products (product_no, name, price)
VALUES
    (1, 'Notebook', 4.50),
    (2, 'Pencil', 0.80);

Every insertion creates a complete row, even when the statement supplies only some columns. Omitted columns receive their defaults; DEFAULT can also be requested explicitly for an individual value, or through INSERT INTO products DEFAULT VALUES for an entire row. These forms are not a way around constraints: for this example, omitting required values without suitable defaults would not satisfy the table’s rules. [3], [2], [1]

4. Evolve the table without recreating it

Suppose the catalog now needs optional descriptions. ALTER TABLE lets you modify the existing structure instead of dropping and recreating a populated table. In this continuation, adding a text column without DEFAULT makes its initial value NULL for the existing rows. The subsequent insertion supplies a description explicitly for a new product. [1], [3]

ALTER TABLE products ADD COLUMN description text;

INSERT INTO products (product_no, name, price, description)
VALUES (3, 'Folder', 2.25, 'Blue document folder');

Defaults deserve a separate distinction. When adding a column, PostgreSQL 18 does not need to update every row immediately if the default is constant. That documented optimization should not be generalized to every default expression or every schema change. Any default must still satisfy the column’s constraints. Similarly, adding NOT NULL to an existing column checks the requirement immediately; prepare existing values before imposing that rule. [1]

5. Use foreign keys for relationships between tables

A foreign key is the appropriate documented mechanism for requiring an order’s product number to refer to an existing product. In this hypothetical continuation, orders is the referencing table and products is the referenced table. Non-NULL product numbers in orders must exist in products. Adding NOT NULL also makes the product reference mandatory rather than allowing it to be absent. [2]

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer NOT NULL REFERENCES products (product_no),
    quantity integer
);

For referenced-row deletion, NO ACTION and RESTRICT are not interchangeable. NO ACTION is the default: the foreign key must still be satisfied, so deletion will usually cause an error if references remain. Where constraint checking is deferred, other statements can repair the relationship before checking occurs. RESTRICT does not allow that check to be deferred. Neither setting should be described as an unconditional permission to delete referenced data. [2]

6. Inspect constraints before removing structure

To remove a constraint, first identify its name. A name you assigned is straightforward to reuse; otherwise, PostgreSQL generated one. In the psql client, \d products can help inspect the table’s details. Then use ALTER TABLE products DROP CONSTRAINT followed by the actual constraint name. This removes the named rule rather than dropping the column itself. [1]

Treat DROP COLUMN as a destructive operation. Removing description would discard its stored values and remove table constraints involving that column. If another table’s foreign key references a column being dropped, PostgreSQL does not silently remove that dependency. Adding CASCADE authorizes dropping dependent objects too. The practical rule is to inspect those dependencies and use CASCADE only when their removal is intentional—not merely to make an error disappear. [1]

Explore next

1. How should you choose between CHECK, NOT NULL, UNIQUE, and foreign-key constraints?
2. How do defaults affect existing rows versus newly inserted rows?
3. What should you inspect before dropping a column or constraint?
4. When should you use multi-row INSERT, INSERT ... SELECT, or COPY?

Sources

Open a source or expand its cited passages.

Relevance scores are model estimates of how well each source addresses this question, not probabilities or fact-check ratings. Reasoned guidance is identified on hover.

1. 98PostgreSQL: Documentation: 18: 5.7. Modifying Tableswww.postgresql.orgOfficial PostgreSQL 18 documentation directly supports ALTER TABLE operations, defaults, constraint inspection, and destructive-change distinctions.›
2. 98PostgreSQL: Documentation: 18: 5.5. Constraintswww.postgresql.orgOfficial PostgreSQL 18 documentation explains NULL behavior, CHECK limitations, foreign keys, and deletion-rule distinctions.›
3. 98PostgreSQL: Documentation: 18: 6.1. Inserting Datawww.postgresql.orgOfficial PostgreSQL 18 documentation directly supports INSERT syntax, explicit column mapping, defaults, multi-row inserts, and query-driven insertion.›