4 ms·
Hm... looks like it's mainly focused on distributed stuff. Instead, I would hope they focus on the bare essentials as well. I've recently started working with
by tomp 9y ago
Hm... looks like it's mainly focused on distributed stuff. Instead, I would hope they focus on the bare essentials as well.
I've recently started working with PostgreSQL and stumbled upon a problem with their UPSERT implementation.
Basically, I want to store an id -> name mapping in a table, with the usual characteristics.
CREATE TABLE entities (id SERIAL PRIMARY KEY, name VARCHAR(10) UNIQUE NOT NULL);
Then, I want to issue a query (or queries) with some entity names that would return their indices, inserting them if necessary.
It appears that's impossible to do with PostgreSQL. The closest I can get is this:
INSERT INTO entities (name) VALUES ('a'), ('b'), ('c')
ON CONFLICT (name) DO NOTHING
RETURNING id, name;
However, this (1) only returns the newly inserted rows, and (2) it increments the `id` counter even in case of conflicts (i.e. if the name already exists). While (2) is merely annoying, (1) means I have to issue at least one more SELECT query.
We like to mock Java, C++ and Go for being stuck in the past, but that's nothing compared to the state of SQL. Sure, there are custom extensions that each database provider implements, but they're often lacking in obvious ways. I really wish there was some significant progress, e.g. TypeSQL or CoffeeSQL or something.
- zimpenfish 9y agoWhy not use a stored procedure?
- fusiongyro 9y agoI'm having trouble imagining why you need this. Often when people come up with "interesting" scenarios involving UPSERT, they are trying to use the database as persistent memory for some algorithm, rather than as a relational database. What are you actually trying to do? Why would it be insufficient to do something like: BEGIN; INSERT INTO entities (name) (VALUES ('a'), ('b'), ('c') EXCEPT SELECT name FROM entities); SELECT id, name FROM entities WHERE name IN ('a', 'b', 'c'); COMMIT;
- kuschku 9y agoYou can even go further. Use WITH select id, name from entities where name in :data AS existing Then return a UNION of existing and (INSERT into entities (name) :data except existing returning id, name)
- jimktrains2 9y agoThat still executes two queries under the hood. The basic issue is a misunderstanding of what INSERT RETURNING does. I've actually seen the specific misunderstanding many times on forums and on IRC. I wonder if it'd be possible to change the syntax to something like "RETURNING [ ALL | NEW ] * | output_expression [ [ AS ] output_name ]" where when empty "NEW" would be used and be equivalent to the current semantics where "ALL" would include rows that were not inserted on conflict. "ALL" would have no different meaning on "ON CONFLICT DO UPDATE" or when no conflict resolution was specified.
- fusiongyro 9y agoI balk at the added complexity. Just use two queries. This is why God created transactions.
- tomp 9y agoI think your solution is good. I'll try it. Assuming entries are only ever added (and never changed), you don't even need a transaction!
- fusiongyro 9y agoYou always need a transaction, even just for reads. Otherwise you are not reading from a single consistent snapshot of the world.
- merb 9y agonot everybody needs strong serializability. most people do read (sometimes even in another thread / other connection) -> transaction -> write. Also the basic transaction isolation in pg would still have these inconsistentsis of phantom/non repeatable reads and serialization anomalies.
- fusiongyro 9y ago
- deleted 9y ago[deleted]
- grzm 9y agoIf I understand what you're looking for, you can likely accomplish this using a WITH expression, something along the lines of (untested): WITH input_rows (name) AS (VALUES ('a'), ('b'), ('c')), insert_rows AS (INSERT INTO entities (name) SELECT ir.name FROM input_rows AS ir ON CONFLICT DO NOTHING RETURNING id, name), -- returned when insert was successful SELECT 'new' AS src, i.id, i.name FROM insert_rows AS i UNION ALL -- return when there's a conflict SELECT 'existing' AS src, e.id, e.name FROM entities AS e JOIN input_rows USING (name); As for the sequence incrementing, that's the nature of sequences: it's a trade-off between getting fast, unique values versus the overhead required tracking whether the id is used.
- jimktrains2 9y agoI'm having trouble justifying semantics that return rows which havn't been inserted or updated. Actually, I find SQL pretty nice. It's strongly typed in non-MySQL engines. It's pretty expressive, and can be highly optimized without having to update queries. Analytical features found in non-MySQL databases such as windows are also invaluable for many types of analysis. The biggest issue I've ever had is that nesting queries when doing some analytical work can be a pain. WITH does handle this to some extent, but it doesn't seem quite as nice as something like PIG where results an "flow" through the query. The other issue is transformations like pivot tables where you don't know the column names ahead of time. pg has extensions for that, but they aren't quite as nice as a native SQL solution would be.
- trinitry3 9y agoThere was a patch for fixing #1 with new syntax for the next version on the mailing list, so perhaps in a year this'll get fixed?