Is it possible to store a query in a variable and use that variable in Insert query? "@countrid =SELECT id FROM COUNTRIES WHERE description = 'asdf';"

Question

So I've been going through SQL migrations to insert data in a SEQUENTIAL manner specifically from parent to child.

I've inserted data in the parent table. Now I've to store the primary key value of that specific row (WHERE condition is defined in query for reference " where description = '1234'") in a variable.

And while inserting data to the child table I've to use that primary key value stored in a variable in place of a foreign key column("country_code_id") of the child table.

I'm using Postgresql

CREATE TABLE Countries
(
    id        SERIAL,
    description VARCHAR(100),
    CONSTRAINT coutry_pkey PRIMARY KEY (id)
);


CREATE TABLE Cities
(
      country_code_id  int ,
      city_id     int,
      description   VARCHAR(100),
      CONSTRAINT cities_pkey PRIMARY KEY (city_id),
      CONSTRAINT fk_cities_countries FOREIGN KEY  (country_code_id) REFERENCES Countries (id)
);


INSERT INTO COUNTRIES (description) VALUES('asdf');
@countrid =  SELECT id FROM COUNTRIES WHERE description = 'asdf';
INSERT INTO cities VALUES (countrid, 1 , 'abc');

Laurenz Albe · Answer

SQL does not have variables. The normal way to do this is to use INSERT ... RETURNING:

INSERT INTO countries (description) VALUES ('1234')
RETURNING id;

This will return the automatically generated primary key. You store that in a variable on the client side and run a second statement:

INSERT INTO cities (country_code_id, city_id, description)
VALUES (4711, 1, 'abc');

where 4711 is the value returned from the first statement. To avoid hard-coding the value, you can use a prepared statement, which also will boost performance.

An alternative, more complicated, solution is to run both statements in a single statement using a common table expression:

WITH country_ids AS (
   INSERT INTO countries (description) VALUES ('1234')
   RETURNING id
INSERT INTO (country_code_id, city_id, description)
SELECT id, 1, 'abc'
FROM country_ids;

Is it possible to store a query in a variable and use that variable in Insert query? "@countrid =SELECT id FROM COUNTRIES WHERE description = 'asdf';"

1 Answers