How to Get or Create in PostgreSQL
hakibenita.com
hakibenita.com
wrote about it very roughly here https://blog.geekodour.org/posts/upserts/
The final query is very neat though! And special thanks for mentioning "MERGE ... RETURNING" in PG17, that's really cool
- Open connection
- Begin transaction
- Do INSERT
- If you get a PK violation, do an UPDATE instead
- Commit
The thing is, while it's a common pattern, it also has many equally common similar patterns. Sometimes you want to try the UPDATE first. Sometimes you want the program to throw an exception if the INSERT fails. Sometimes you're doing something with row versioning or pessimistic locking.
It's same reason why your file IO library might have a ReadAllText() method, but only after it has Open() and Read() and ReadLine().
- Do INSERT
- If you get a PK violation, do an UPDATE instead
- Commit
This is not safe in the default transaction mode, other transactions can delete the row between insert and update/select.
Also, as explained by the article, this has the table bloating problem
My understanding is that in general, if you hit an exception in postgres at all then you can't trust the isolation of the current transaction anymore.
That's what MERGE and speculative insertion (on conflict do update) addresses.
REPEATABLE READ or SERIALIZABLE isolation levels would help with that, as the name suggest repeatable read ensures that the read made by inserts constraint check is repeatable in successive select (or update) statements.
https://www.postgresql.org/docs/current/transaction-iso.html is pretty comprehensive.
Most RDBMSs don't behave this way. They allow you to correct exceptions that occur for anything less than a deadlock without a total rollback, but not Postgres.
Session 1:
psql (16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.
postgres=# CREATE TABLE tags (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE
postgres=# ALTER TABLE tags ADD CONSTRAINT tags_name_unique UNIQUE(name);
ALTER TABLE
postgres=# INSERT INTO tags (name) VALUES ('A'), ('B') RETURNING *;
id | name
----+------
1 | A
2 | B
(2 rows)
INSERT 0 2
postgres=# BEGIN;
BEGIN
postgres=*# INSERT INTO tags (name) VALUES ('B') ON CONFLICT DO NOTHING RETURNING *;
id | name
----+------
(0 rows)
INSERT 0 0
psql (16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.
postgres=# DELETE FROM tags WHERE name = 'B';
DELETE 1
postgres=*# SELECT * FROM tags WHERE name = 'B';
id | name
----+------
(0 rows)
postgres=*# END;
COMMIT
You can use a specific trigger function just to prevent this: suppress_redundant_updates_trigger
https://www.postgresql.org/docs/current/functions-trigger.ht...
> The suppress_redundant_updates_trigger function, when applied as a row-level BEFORE UPDATE trigger, will prevent any update that does not actually change the data in the row from taking place. This overrides the normal behavior which always performs a physical row update regardless of whether or not the data has changed.
It basically turns the ON CONFLICT DO UPDATE into ON CONFLICT DO NOTHING.
My intention with this project is to use it for storage and transactions. Most of our query heavy lifting is done via external search engines like Elasticsearch. And my attitude with databases is that if you are not querying on it, it doesn't need separate tables, columns, and indices. The point with a document store is that you only use limited querying strategies. I've used documents stores for various projects for over ten years and that style of persistence just fits a lot of things I do.
I'm currently planning to roll this out for one of our products. So, this is not just a hobby project. If somebody has time; I wouldn't mind some eyeballs on this project: https://github.com/formation-res/pg-docstore
I do wonder why in the first example of inserting multiple tags with get_or_create_tag the id of 'C' suddenly changes to 4. Were some queries perhaps not run in the order they appear?