Reputation: 1265
I have the following tables
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (),
...
CREATE TABLE tags (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (),
user_id UUID NOT NULL references users (id),
tag VARCHAR(200) NOT NULL,
...
}
I would like to form a query that inserts a tag based on the following constraints:
user_id
in the tags
table, all entries must have unique tagsuser_id
s can have the same tag. For example:The following should be valid in the tag
table
id | user_id | tag |
---|---|---|
some-tag-uuid-1 |
some-user-uuid-1 |
foo |
some-tag-uuid-2 |
some-user-uuid-1 |
bar |
some-tag-uuid-3 |
some-user-uuid-2 |
foo |
Note the differences in user_id .
The following should NOT be valid in the tag
table
id | user_id | tag |
---|---|---|
some-tag-uuid-1 |
some-user-uuid-1 |
foo |
some-tag-uuid-2 |
some-user-uuid-1 |
foo |
As of now, the only query I can come up with is split into two parts and the app handles the intermediate logic.
For a given tag to insert e.g.
{id: 'some-tag-uuid-1', user_id: 'some-user-uuid-1', tag: 'busy'};
SELECT id FROM tag WHERE user_id = 'some-user-uuid-1' AND tag = 'busy'
From the resulting rows, I then check if it exists, if so, I return the existing id
, if not I insert the new id in the tag table returning the new id
.
I'm not sure if this approach is the best approach, and would like a single more performant query (if possible)
Upvotes: 0
Views: 56
Reputation: 7075
As stated by @SebDieBln :
You add a unique constraint in the tags
table definition : CONSTRAINT unique_constraint UNIQUE (user_id, tag)
You add ON CONFLICT DO NOTHING
in the INSERT
statement
You add the RETURNING
clause in the INSERT
statement in order to get the new tag when inserted
But when the tag
value already exists for the user_id
, the returned value is NULL
, so you need to catch the tag
input value instead.
Finaly you can do everything within a sql function :
CREATE OR REPLACE FUNCTION test (IN _user_id UUID , INOUT _tag VARCHAR(200), OUT _rank INTEGER)
RETURNS record LANGUAGE sql AS
$$
WITH cte AS (INSERT INTO tags (user_id, tag) VALUES (_user_id, _tag) ON CONFLICT DO NOTHING RETURNING tag)
SELECT tag, 1 FROM cte
UNION
SELECT _tag, 2
ORDER BY 2
LIMIT 1 ;
$$
And you call the sql function to get the expected behavior :
SELECT _tag FROM test('some-user-uuid-1', 'busy')
see the test result in dbfiddle.
Upvotes: 1