PostgreSQL: SET equivalent to MySQL

Question

I want to insert the id of one table into another in PostgreSQL. First I want to select the id of table1 where it equals a specific value and store that into a variable. In MySQL I can do:

SET @last_id_in = (SELECT id FROM table1 WHERE col1 = 1);

Then I can insert where the value would equal @last_id_in. How can I do something similar in PostgreSQL?

This might help: http://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-postgresql. With that said, given your first comment, you might not need the variable. Depends on the rest of your sql. — sgeddes, May 22 '14 at 00:19
Recent related answer about variables in Postgres: http://stackoverflow.com/questions/23561408/how-do-i-declare-variables-in-pgadmin/23771292#23771292 — Erwin Brandstetter, May 22 '14 at 03:03

score 2 · Accepted Answer · answered May 22 '14 at 00:29

Depending on where you want your variable -- i.e. in a PL/pgSQL function, or in a script from which you're calling into Postgres...

PL/pgSQL, something like this:

INSERT INTO table2
SELECT id FROM table
WHERE col1 = 1
RETURNING id INTO last_id_in;

where last_id_in is previously declared as integer (or whatever type id is).

Script:

INSERT INTO table2
SELECT id FROM table1
WHERE col1 = 1
RETURNING id;

And then the cursor in whatever library you're interacting with Postgres with should contain that value for you to use elsewhere.

If the goal is simply to insert an element from one table into another, then the script version above can leave off the RETURNING clause, as there's no intervening steps.

PostgreSQL: SET equivalent to MySQL

1 Answers1