Lesser Known Postgres Features

Microsoft conducted a customer survey in 2006 and found that many users were unaware of existing features in Microsoft Office. To address this issue, they developed the Ribbon UI. Similarly, many users are unaware of lesser-known features in PostgreSQL. For example, the “INSERT ON CONFLICT” command, also known as “upsert,” is useful in ETL processes. It allows users to specify actions when a collision is detected in key columns. Granting permissions on specific columns in a table is another useful feature in PostgreSQL to control user access to sensitive information. Additional features include matching against multiple patterns using SIMILAR TO or regexp, finding the current value of a sequence, using COPY with multi-line SQL queries, and preventing users from setting auto-generated key values. Finally, there are two more ways to produce pivot tables in PostgreSQL, including using the crosstabview command in psql.


To top