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

Question

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

You are declaring indiaCountry as integer, and then asigning it a string ... — nacho
– nacho, Commented Sep 14, 2022 at 6:14
I am not a postgresql expert, but i think you need select into — nacho
– nacho, Commented Sep 14, 2022 at 6:18
In SqlServer, you would use DECLARE @indiaCountry int (note the added @) - could that be the same in postgres? (still, the next line wouldn't make sense) — Hans Keﬆing
– Hans Keﬆing, Commented Sep 14, 2022 at 7:12

Jonathan Willcock · Accepted Answer · 2022-09-14 06:59:24Z

1

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.

answered Sep 14, 2022 at 6:59

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stefanov.sm · Accepted Answer · 2022-09-14 09:42:34Z

1

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;

edited Sep 14, 2022 at 9:42

answered Sep 14, 2022 at 6:49

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Comments

Veselina Aleksandrova · Accepted Answer · 2022-09-14 06:37:27Z

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 $$;

edited Sep 14, 2022 at 6:37

answered Sep 14, 2022 at 6:28

Veselina Aleksandrova

113 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related