PostgreSQL 15: Three features you can try with Neon
Announcing support for PostgreSQL 15
Today, we’re pleased to announce support for PostgreSQL 15.
PostgreSQL 15 includes improved sort performance, write-ahead log (WAL) file compression, and other improvements. Here are some features you can try using Neon’s SQL Editor.
MERGE query can
DELETE rows with a single SQL statement. PostgreSQL will insert a new row if it doesn’t exist, otherwise, it will
DO NOTHING as specified in the query clause.
PostgreSQL supports the
INSERT ON CONFLICT statement with similar functionality since version 9.5.
INSERT ON CONFLICT and
MERGE handle concurrent insertions differently.
INSERT ON CONFLICT DO UPDATE guarantees either
UPDATE outcome, while
MERGE may fail with a unique violation.
Thus, it is preferred to run
UPDATE in the case of concurrent
MERGE in action to understand how it works.
We have created a
hero table with the following attributes:
Since the table is empty, the following query will not find a match. As a result, the query will insert a row with
If we run the same query a second time, PostgreSQL will find a match with the same value as before, so it will do nothing.
Let’s make a few changes to the query shown above. We kept the same
id=11 but changed the
hero_name columns’ respective values. Can you guess what happens when we execute the following query?
You got it! PostgreSQL updates the
hero_name columns with the values
CREATE privilege on public schema removed
PostgreSQL 15 removes the global write privilege from the public schema to limit possible security issues. For example, if you have a database owned by the user
alice and connect to it from the user
bob, you will see
ERROR: permission denied for schema public when running this query:
To enable this functionality, connect from the database owner and run the following statement:
Only grant these permissions to trusted users, as those can be exploited. Refer to the Guide to CVE-2018-1058 for more information about protecting your search path.
Allow unique constraints and indexes to treat NULL values as not distinct
In previous PostgreSQL versions, you could insert multiple rows with a
NULL attribute, even if the column is specified as
Let’s look at an example. Create a table called
superhero with a unique
Superheroes are known to keep their identity secret, so let’s insert their data into the database with the
hero_name attribute set to
NULL. Use the following
INSERT queries to keep the superheroes’ names anonymous.
Superpowers, on the other hand, have to be specified. I can only allow one type of superpower to be
NULL. With the change to PostgreSQL 15, we can ensure that only one
NULL in the
superpower_name column is permitted:
If I try to run the following SQL statement a second time, I’ll get
Error: duplicate key value violates unique constraint.
Try out Postgres 15
Get started today at https://neon.tech and create a PostgresSQL 15 project.
Do you want to migrate your current project to PostgreSQL 15 with Neon? Follow the instructions in import data from PostgreSQL.
Let us know what other features you liked in this release. Share your ideas at https://community.neon.tech/.