kuda.ai

Postgres: insert into where exists

Created on January 8, 2026

postgres

I have two tables:

• transcriptions
• transcription_feedbacks

When inserting into transcription_feedbacks, I want to make sure that the transcription belongs to the user that is giving the feedback.

For the following examples, set your variables in psql like so:

\set transcription_id 1
\set user_id 'abc123'
\set rating 0
\set comment 'great'

I found out that you can use INSERT INTO ... WHERE EXISTS as in the following query for this:

insert into transcription_feedback (
    transcription_id,
    user_id,
    rating,
    comment
)
select :transcription_id,
       :user_id,
       :rating,
       :'comment'
 where exists (
    select 1
      from transcriptions t
     where t.id = :transcription_id
       and t.user_id = :'user_id'
)

An alternative is using a join with INSERT INTO ... FROM:

insert into transcription_feedbacks (
        transcription_id,
        user_id,
        rating,
        comment
)
select t.id,
       :'user_id',
       :rating,
       :'comment'
  from transcriptions t
 where t.id = :transcription_id
   and t.user_id = :'user_id';

A somewhat similar situation is UPDATE ... FROM as in:

update cues AS c
   set text = :text
  from transcriptions AS t
 where t.id = c.transcription_id
   and c.id = :cue_id
   and t.user_id = :user_id