CODEWITHSUNDEEP
sqlpostgresql
martinomburajr
martinomburajr

Reputation: 1265

Postgresql Upsert based on conditition

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:

  1. For a given user_id in the tags table, all entries must have unique tags
  2. Different user_ids 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
  1. If an entry exists, I should return the existing tag id. If not, we insert the new tag and return the new tag's id.

What I currently have

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

Answers (1)

Edouard
Edouard

Reputation: 7075

As stated by @SebDieBln :

  1. You add a unique constraint in the tags table definition : CONSTRAINT unique_constraint UNIQUE (user_id, tag)

  2. You add ON CONFLICT DO NOTHING in the INSERT statement

  3. You add the RETURNING clause in the INSERT statement in order to get the new tag when inserted

  4. 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

Related Questions