Postgres Insert/Update query not working as expected

Question

Here's a simplified version of a query I'm trying to write. It's intended to save a row as a variable combine, then UPDATE the media_hashtags table if a specific entry exists, else INSERT that entry.

    WITH 
      combine AS (
        SELECT * FROM hashtags WHERE hashtag_text='HOPPA'
      )
    UPDATE media_hashtags SET hashtag_id = (SELECT id FROM combine) WHERE user_id = 58 AND media_id=161;
      INSERT INTO media_hashtags (media_id, user_id, hashtag_id)
    SELECT 161, 58, (SELECT id FROM combine)
    WHERE NOT EXISTS (
      SELECT * FROM media_hashtags
      WHERE (
        user_id = 58 AND
        media_id = 161
      )
    )

    RETURNING *

However, I get this error:

ERROR:  relation "combine" does not exist
LINE 8:      SELECT 161, 58, (SELECT id FROM combine)

Interestingly, If I do a query with only the UPDATE or only the INSERT commands, then it executes as expected. The error only occurs when I do both at once. Any ideas on what the problem is, and a fix?

There seems to be two separate queries here (look for the semicolon). It's natural that the second one does not see the combine from the first — leonbloy
– leonbloy, Commented Mar 24, 2016 at 2:48
That makes sense. I suppose I could re-write the combine, but that would be verbose. Is there a better way to solve this issue? — dmwong2268
– dmwong2268, Commented Mar 24, 2016 at 2:58

marc_s · Accepted Answer · 2016-06-19 18:22:50Z

You have two queries, the first one ends with the ; after the UPDATE statement. The following INSERT does not see the CTE any more because it's a new statement.

If you want to run this as a single statement, you need to move the UPDATE into it's own CTE:

WITH combine AS (
    SELECT id 
    FROM hashtags 
    WHERE hashtag_text='HOPPA'
), changed AS (
  UPDATE media_hashtags 
     SET hashtag_id = (SELECT id FROM combine) 
  WHERE user_id = 58 
    AND media_id=161
)  
INSERT INTO media_hashtags (media_id, user_id, hashtag_id)
SELECT 161, 58, (SELECT id FROM combine)
WHERE NOT EXISTS (
  SELECT * FROM media_hashtags
  WHERE (
    user_id = 58 AND
    media_id = 161
  )
)
RETURNING *;

You should also only select the columns you need in the initial CTE. The fact that the subsequent statements only use a single column from combine won't be pushed into the first query so Postgres might not query hashtags as efficient as it potentially could.

If you are using Postgres 9.5 you can simplify the statement using the on conflict clause for the insert statement:

INSERT INTO media_hashtags (media_id, user_id, hashtag_id)
SELECT 161, 58, id
FROM hashtags 
WHERE hashtag_text='HOPPA'
ON CONFLICT (media_id, user_id) DO UPDATE 
  SET hashtag_id = excluded.hashtag_id;

This requires a unique index on media_hashtags(media_id, user_id)

Abhishek · Accepted Answer · 2016-03-24 04:24:23Z

1

You can use Temporary table to store data from hashtags and then insert and update operations.

Here is the way:

DROP TABLE IF EXISTS temp_hashtags;
CREATE TEMP TABLE temp_hashtags AS
SELECT * FROM hashtags WHERE hashtag_text='HOPPA';

UPDATE media_hashtags 
    SET hashtag_id = (SELECT id FROM temp_hashtags) 
WHERE user_id = 58 AND media_id=161;

INSERT INTO media_hashtags (media_id, user_id, hashtag_id)
SELECT 161, 58, (SELECT id FROM temp_hashtags)
WHERE NOT EXISTS (
        SELECT * FROM media_hashtags
        WHERE user_id = 58 AND media_id = 161
        );

answered Mar 24, 2016 at 4:24

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

Postgres Insert/Update query not working as expected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related