I would like to use the following syntaxis in my PL/pgSQL code:
INSERT INTO table (table.column1, table.column2) VALUES (1, 2);
But somehow it doesn't work. Does anybody know if such a notation it is prohibited or I do something wrong?
Firstly, if you really want to use the name
table for your table then you will need to enclose it in quotes:
create table table (column1 integer, column2 integer); ERROR: syntax error at or near "table" create table "table" (column1 integer, column2 integer); CREATE TABLE
but assuming your table name is something different (which I highly recommended), the error returned indicates that the parser is interpreting
my_table as a column name:
create table my_table (column1 integer, column2 integer); CREATE TABLE insert into my_table (my_table.column1, my_table.column2) values (1, 2); ERROR: column "my_table" of relation "my_table" does not exist
This interpretation is sensible, as
column1 may be a composite type, for example:
create type my_type as (column1 integer, column2 integer); create table my_table (col my_type); insert into my_table (col.column1, col.column2) values (1, 2); select * from my_table; col ------- (1,2) (1 row)
You probably just want the following as already suggested by DrColossos:
insert into table (column1, column2) values (1, 2);
table.column1 can be confused with a schema lookup. It thinks that it needs to look for a schema named table.
Be sure to check out the docs on how to create a schema. It says, that
[..] the even more general syntax
This would also make no syntactical sense since you already know what table you are referring to. IF you need to query the schema, you would write it.
INSERT INTO your_schema.table (column1, column2) VALUES (1, 2);