SQL Error [42601]: ERROR: syntax error at or near "int" Position: 22-CodePudding

Here I wanted to insert data in the child table using primary key id of parent table but getting an execution error ERROR: syntax error at or near "int"

I want to use IndiaCountry variable in Insert query for this task. How can I implement this?

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');
DECLARE indiaCountry int;
@indiaCountry =  'SELECT id FROM COUNTRIES WHERE description = 'asdf';'
INSERT INTO cities VALUES (@indiaCountry, 1 , 'abc');

CodePudding user response：

In postgresql you can do it like this:

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

Or if you must use variables, you could use PL/pgSQL within anonymous block or a function. An example with anonymous block:

do $$ 
declare
  indiaCountry int;
begin
   SELECT id INTO indiaCountry FROM COUNTRIES WHERE description = 'asdf';
   INSERT INTO cities VALUES (indiaCountry, 1 , 'abc');
end $$;

Documentation for anonymous block: https://www.postgresql.org/docs/current/sql-do.html

Or you could skip the select statement and do this:

do $$ 
declare
  indiaCountry int;
begin
   INSERT INTO COUNTRIES (description) VALUES('asdf') RETURNING id INTO indiaCountry;
   INSERT INTO cities VALUES (indiaCountry, 1 , 'abc');
end $$;

CodePudding user response：

It looks to me as though id is a serial column in your countries table, getting automatically generated, and you need to find out what this value is, before inserting into cities.

In PostgreSQL a serial column is just convenient syntax for a sequence. To find out the last value added to the sequence we use the currval function. Unfortunately the sequence name is a generated name (there are rules), so it is easiest in addition to use another helper function pg_get_serial_sequence.

Putting all this together, what you can do in your case is the following:

INSERT INTO countries(description) VALUES('asdf');

INSERT INTO cities 
SELECT currval(pg_get_serial_sequence('countries','id')), 1, 'abc';

Note that currval is session safe. Even if there are concurrent insertions, it will return the value from the insert within your session.

CodePudding user response：

Your query uses SQL Server-like syntax that is not supported in PostgreSQL.
You may use a Data-Modifying CTE like this:

with t as
(
 INSERT INTO COUNTRIES (description) VALUES('asdf') returning id
)
insert into cities select id, 1, 'abc' from t;