How to update or insert data with WITH subquery? and avoid `PG::UndefinedTable: ERROR: missing FROM-clause entry for table` error

Question

Im trying to run simple sql query:

    <<-SQL.squish
      WITH payloads as (
        INSERT into text_payloads (text, created_at, updated_at)
          SELECT text, created_at, updated_at
          FROM text_messages
        RETURNING id, text, created_at, updated_at
      )
      UPDATE text_messages
      SET
        payload_id = text_payloads.id,
        payload_type = 'TextPayload'
      WHERE
        text_messages.text = text_payloads.text AND
        text_messages.created_at = text_payloads.created_at AND
        text_messages.updated_at = text_payloads.updated_at
    SQL

but got this error:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "text_payloads"
LINE 1: ...d_type = 'TextPayload' WHERE text_messages.text = text_paylo...
                                                         

I've already tried to add FROM payloads and even LEFT OUTER JOIN text_payloads, but nothing helped. How can I implement this update or similar insert operation with WITH subquery and its result?

Thank you very much in advance!

Answer 1

The error message is clear: there is no FROM clause in your UPDATE statement to perform the join between the CTE (I guess that you want to use the CTE and not text_payloads) and text_messages.
The correct syntax is:

WITH payloads as (
  INSERT into text_payloads (text, created_at, updated_at)
    SELECT text, created_at, updated_at
    FROM text_messages
  RETURNING id, text, created_at, updated_at
)
UPDATE text_messages
SET
  payload_id = payloads.id,
  payload_type = 'TextPayload'
FROM payloads  
WHERE
  text_messages.text = payloads.text AND
  text_messages.created_at = payloads.created_at AND
  text_messages.updated_at = payloads.updated_at 

See the demo.