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?

• postgresql
• heroku

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.