RETURNING ... INTO: syntax error at or near "INTO"

Question

I am trying to return the generated id into a variable so i can use it for inserting referenced values in other tables.

The error does not appear when I delete the 'INTO foo_id_0' part. But this is kind of essential for waht i am trying to do.

What am I doing wrong?

CREATE TABLE IF NOT EXISTS bar(
    foo_id SERIAL UNIQUE,
    one VARCHAR(20) NOT NULL UNIQUE,
    two VARCHAR(60) NOT NULL,
    
    CONSTRAINT pk_foo_id PRIMARY KEY (foo_id)
);


INSERT INTO foo(foo_id, one, two) VALUES 
(DEFAULT, 'green' , 'blue' ) ON CONFLICT ON CONSTRAINT pk_foo_id DO NOTHING RETURNING foo_id INTO foo_id_0;


ERROR:  syntax error at or near "INTO"
LINE 11: ...ONSTRAINT pk_foo_id DO NOTHING RETURNING foo_id INTO foo_...
                                                            ^

`RETURNING foo_id AS foo_id_0` [Insert](https://www.postgresql.org/docs/current/sql-insert.html) — Adrian Klaver, Nov 23 '20 at 20:59

score 0 · Answer 1 · answered Nov 23 '20 at 21:00

0

That's cause you need to quote the VARCHAR column values like

INSERT INTO foo(foo_id, one, two) VALUES (DEFAULT, 'green', 'blue')
ON CONFLICT ON CONSTRAINT pk_foo_id DO NOTHING RETURNING foo_id;

answered Nov 23 '20 at 21:00

Rahul

76,197
13
71
125

RETURNING ... INTO: syntax error at or near "INTO"

1 Answers1