Question 114638 in PostgreSQL Question posted in
The official documentation can be found here.

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

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');
Tags:

3

Answers


  1. 0

    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 $$;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search