Statements (PostgreSQL)

SELECT

LIKE and ILIKE

DISTINCT

LIMIT and OFFSET

LIMIT and OFFSET are used to return a portion of data from a resultset generated by a query; the LIMIT clause is used to limit the number of records in output and the OFFSET clause is used to provide PostgreSQL with the position in the resultset from which to start returning data.

(Ferrari and Pirozzi 2023, 111)

Combining Queries

UNION / UNION ALL

The UNION operator is used to combine the resultset of two or more SELECT statements. We can use the UNION statement only if the following rules are respected:

  • Each SELECT statement within UNION must have the same number of columns.
  • The columns must have similar data types.
  • The columns in each SELECT statement must be in the same order.

(Ferrari and Pirozzi 2023, 133)

  • UNION is an alias to UNION DISTINCT.

EXCEPT / INTERSECT

Upsert

In PostgreSQL, the UPSERT statement does not exist as in other DBMSes. An UPSERT statement is used when we want to insert a new record on top of the existing record or update an existing record.

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error: 

  INSERT INTO table_name(c1, c2, ..., cn) VALUES(v1, v2, ..., vn)
  ON CONFLICT (...);

and the conflict action can be either:

  • DO NOTHING.
  • DO UPDATE + (SET and/or WHERE)
  INSERT INTO table_name(c1, c2, ..., cn) VALUES(v1, v2, ..., vn)
  ON CONFLICT
  DO UPDATE SET c1 = EXCLUDED.v1;

where EXCLUDED references the proposed new insertion row.

RETURNING

Reduces the need for an extra query as it enables you to output columns.

Inserts

Updates

Deletes

References:

Ferrari, Luca, and Enrico Pirozzi. 2023. Learn Postgresql: Use, Manage, and Build Secure and Scalable Databases with Postgresql 16. 2nd ed. Packt Publishing Ltd.