Using WITH clause with INSERT statement in POSTGRESQL

Question

I have a requirement in which I need to get one column from another table and insert that column data with some other data into another table.

Example:

If the cust_id='11' then I need to get the cust_code from cust table (let's say it returns cust_code='ABCD') and then use that cust_code with some other data to insert into table_1 like so:

WITH get_cust_code_for_cust_id AS (
    SELECT cust_code FROM cust WHERE cust_id=11
)

INSERT INTO public.table_1(
    cust_code, issue, status, created_on)
    VALUES (SELECT cust_code FROM get_cust_code_for_cust_id, 'New Issue', 'Open', current_timestamp)

But this query does not work as we haven't called the get_cust_code_for_cust_id query.

My preference is some query with WITH clause but any other answer will also be appreciated.

user330315 · Accepted Answer · 2018-05-16 07:40:28Z

56

If the source of an insert statement is a select do not use the VALUES keyword.

WITH get_cust_code_for_cust_id AS (
    SELECT cust_code 
    FROM cust 
    WHERE cust_id=11
)
INSERT INTO public.table_1 (cust_code, issue, status, created_on)
SELECT cust_code, 'New Issue', 'Open', current_timestamp 
FROM get_cust_code_for_cust_id;

You don't really need the CTE for this though:

INSERT INTO public.table_1 (cust_code, issue, status, created_on)
SELECT cust_code, 'New Issue', 'Open', current_timestamp  
FROM cust 
WHERE cust_id=11

answered May 16, 2018 at 7:40

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

moooeeeep Over a year ago

I'd like to add a link to the docs, and especially hint to the examples at the bottom: postgresql.org/docs/current/static/sql-insert.html

Neeraj Wadhwa Over a year ago

Both of the queries are working perfectly for my scenario.

Collectives™ on Stack Overflow

Using WITH clause with INSERT statement in POSTGRESQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related