Do you work with Postgres on a daily basis? Write application code that talks to Postgres? Then check out the bite-sized SQL snippets below that can help you work faster!
Insert Multiple Rows In One Statement
The INSERT statement can insert more than one row in a single statement:
Read more about what INSERT can do here.
Insert a Row and Return Automatically-assigned Values
Values auto-generated with DEFAULT/serial/IDENTITY constructs can be returned by the INSERT statement using the RETURNING clause. From the application code perspective, such an INSERT is executed like a SELECT that returns a recordset.
Autogenerated UUID Primary Keys
UUIDs are sometimes used instead of primary keys for various reasons. Here is how you can use an UUID instead of a serial or IDENTITY:
Insert If Not Existing, Update Otherwise
In Postgres 9.5 and later, you can upsert directly using the ON CONFLICT construct:
Copy Rows From One Table Into Another
The INSERT statement has a form where the values can be supplied by a SELECT statement. Use this to copy rows from one table into another:
If you’re looking to bulk-load tables, also check out the COPY command, which can be used to insert rows from a text or CSV file.
Delete and Return Deleted Information
You can use the
RETURNING clause to return values from the rows that were
deleted using a bulk-delete statement:
Move Rows From One Table Into Another
You can move rows from one table to another in a single statement, by using CTEs with DELETE .. RETURNING:
Update Rows and Return Updated Values
The RETURNING clause can be used in UPDATEs too. Note that only the new values of the updated columns can be returned this way.
If you need the original value of the updated columns: it is possible through a
self-join, but there is no guarantee of atomicity. Try using a
SELECT .. FOR
Update a Few Random Rows and Return The Updated Ones
Here’s how you can choose a few random rows from a table, update them and return the updated ones, all in one go:
Create a Table Just Like Another Table
Use the CREATE TABLE .. LIKE construct to create a table with the same columns as another:
By default this does not create similar indexes, constraints, defaults etc. To do that, ask Postgres explicitly:
See the full syntax here.
Extract a Random Set of Rows Into Another Table
Since Postgres 9.5, the TABLESAMPLE feature is available to extract a sample of rows from a table. There are two sampling methods currently, and bernoulli is usually the one you want:
The system tablesampling method is faster, but does not return a uniform distribution. See the docs for more info.
Create a Table From a Select Query
You can use the CREATE TABLE .. AS construct to create the table and populate it from a SELECT query, all in one go:
The resultant table is like a materialized view without a query associated with it. Read more about CREATE TABLE .. AS here.
Create Unlogged Tables
Unlogged tables are not backed by WAL records. This means that updates and deletes to such tables are faster, but they are not crash-tolerant and cannot be replicated.
Create Temporary Tables
Temporary tables are implicitly unlogged tables, with a shorter lifetime. They automatically self-destruct at the end of a session (default), or at the end of the transaction.
Data within temporary tables cannot be shared across sessions. Multiple sessions can create temporary tables with the same name.
Comments can be added to any object in the database. Many tools, including pg_dump, understand these. A useful comment might just avoid a ton of trouble during cleanup!
Advisory locks can be used to co-ordinate actions between two apps connected to the same database. You can use this feature to implement a global, distributed mutex for a certain operation, for example. Read all about it here in the docs.
Aggregate Into Arrays, JSON Arrays or Strings
Postgres provides aggregate functions that concatenate values in a GROUP to yield arrays, JSON arrays or strings:
Aggregates With Order
While we’re on the topic, here’s how to set the order of values that are passed to the aggregate function, within each group:
Yes, there is a trailing ORDER BY clause inside the function call paranthesis. Yes, the syntax is weird.
Array and Unnest
Use the ARRAY constructor to convert a set of rows, each with one column, into an array. The database driver (like JDBC) should be able to map Postgres arrays into native arrays and might be easier to work with.
The unnest function does the reverse – it converts each item in an array to a row. They are most useful in cross joining with a list of values:
Combine Select Statements With Union
You can use the UNION construct to combine the results from multiple similar SELECTs:
Use CTEs to further process the combined result:
There are also INTERSECT and EXCEPT constructs, in the same vein as UNION. Read more about these clauses in the docs.
Quick Fixes in Select: case, coalesce and nullif
The CASE, COALESCE and NULLIF to make small quick “fixes” for SELECTed data. CASE is like switch in C-like languages:
COALESCE can be used to substitute a certain value instead of NULL.
NULLIF works the other way, letting you use NULL instead of a certain value:
Generate Random and Sequential Test Data
Various methods of generating random data:
Use bernoulli table sampling to select a random number of rows from a table:
generate_series to generate sequential values of integers, dates and
other incrementable built-in types:
Get Approximate Row Count
The horrible performance of
COUNT(*) is perhaps the ugliest by-product of
Postgres’ architecture. If you just need an approximate row count for a huge
table, you can avoid a full COUNT by querying the statistics collector:
The result is accurate after an ANALYZE, and will be progressively incorrect as the rows are modified. Do not use this if you want accurate counts.
The interval type can not only be used as a column datatype, but can be added to and subtracted from date and timestamp values:
Turn Off Constraint Validation For Bulk Insert
Dump a Table or Query to a CSV File
Use More Native Data Types In Your Schema Design
Postgres comes with many built-in data types. Representing the data your application needs using one of these types can save lots of application code, make your development faster and result in fewer errors.
For example, if you are representing a person’s location using the data type
point and a region of interest as a
polygon, you can check if the person
is in the region simply with:
Here are some interesting Postgres data types and links to where you can find more information about them:
- C-like enum types
- Geometric types – point, box, line segment, line, path, polygon, circle
- IPv4, IPv6 and MAC addresses
- Range types – integer, date and timestamp ranges
- Arrays that can contain values of any type
- UUID – if you
need to use UUIDs, or just need to work with 129-byte random integers,
consider using the
uuidtype and the
uuid-oscpextension for storing, generating and formatting UUIDs
- Date and time intervals using the INTERVAL type
- and of course the ever-popular JSON and JSONB
Most Postgres installs include a bunch of standard “extensions”. Extensions are installable (and cleanly uninstallable) components that provide functionality not included in the core. They can be installed on a per-database basis.
Some of these are quite useful, and it is worth spending some time getting to know them:
- pg_stat_statements – statistics regarding the execution of each SQL query
- auto_explain – log the query execution plan of (slow) queries
- postgres_fdw, dblink and file_fdw – ways to access other data sources (like remote Postgres servers, MySQL servers, files on server’s file system) like regular tables
- citext – a “case-insensitive text” data type, more efficient than lower()-ing all over the place
- hstore – a key-value data type
- pgcrypto – SHA hashing functions, encryption
pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics.
pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, diagnostics, alerting, teams and more. Checkout the features here or signup today for a free trial.