INSERT INTO with subquery that might return 0 results

Question

I have a status table that I am using to sync other long running processes, and my following query/system is functioning, but also filling up my error logs:

CREATE TABLE status(subject_id UUID PRIMARY KEY REFERENCES users(subject_id) 
ON DELETE CASCADE, status1 varchar, status2 varchar)


CREATE TABLE users(subject_id UUID PRIMARY KEY, start_date BIGINT)

problem query:

INSERT INTO status
(status1, subject_id) VALUES ('processing',
(SELECT u.subject_id FROM users AS u
    LEFT OUTER JOIN status ON (u.subject_id = status.subject_id)
        WHERE (status.status1 IS NULL or status.status1 = 'ready')
        and usrs.start_date IS NOT NULL LIMIT 1)) 
ON CONFLICT (subject_id) DO update SET status1 = 'processing'

The ON CONFLICT clause correctly handles the case where the subject_id already exists in status, but no the case where my subquery returns 0 results. In that case I don't want anything to happen, but I would also prefer not to get errors in my log when that happens.

I need to handle cases where:

1. Claim a subject_id that has no entry in status. This works well.
2. Claim a subject_id that has an entry in status for another status column. The current ON CONFLICT clause handles this.
3. Handle the case where there are no more users available. The current query tries to insert a NULL subject_id in this case.

This should be a single query because there are several worker processes that are using the status1, status2, ... columns to synchronize their work and two could both start a long running process on the same subject if the select and insert are separate queries.

Answer 1

INSERT INTO status (status1, subject_id) 
SELECT 'processing', u.subject_id 
    FROM users AS u
    LEFT OUTER JOIN status ON (u.subject_id = status.subject_id)
    WHERE (status.status1 IS NULL or status.status1 = 'ready')
      and usrs.start_date IS NOT NULL LIMIT 1
ON CONFLICT (subject_id) DO update SET status1 = 'processing'

PS. LIMIT without ORDER BY is a lottery...