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, Apr 05 '12 at 22:09

score 1 · Answer 1 · edited May 23 '17 at 11:56

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 '17 at 11:56

Community

1
1

answered Apr 05 '12 at 17:31

Erwin Brandstetter

605,456
145
1,078
1,228

postgres on heroku sql statement fails

1 Answers1