postgres on heroku sql statement fails

Question

I have just uploaded my node.js app onto heroku and one of my sql queries is failing. The query is:

INSERT INTO countries (name, user_id, created_timestamp)
SELECT $1, $2, CURRENT_TIMESTAMP
WHERE NOT EXISTS (SELECT 1 FROM countries WHERE name = $1 FOR UPDATE)

It is failing with

error: SELECT FOR UPDATE/SHARE is not allowed in subqueries

Does anyone know why? Is there a work around I can use if I can't select for update?

I'm trying to insert into the table if the record doesn't exist, and not fail with a primary key violation if it does exist — dan
– dan, Commented Apr 5, 2012 at 22:09

Community · Accepted Answer · 2017-05-23 11:56:27Z

1

This might work for you:

BEGIN;

LOCK TABLE countries IN SHARE MODE;

INSERT INTO countries (name, user_id, created_timestamp)
SELECT $1, $2, CURRENT_TIMESTAMP
WHERE NOT EXISTS (SELECT * FROM countries WHERE name = $1);

COMMIT;

Explanation and links in this closely related answer.

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Apr 5, 2012 at 17:31

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres on heroku sql statement fails

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related