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?

2 Answers 2


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

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

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;

(1 row)

You probably just want the following as already suggested by DrColossos:

insert into table (column1, column2) values (1, 2);
  • I see the trick you've suggested. Very nice, but how it may influence overall perfomance? Anyway, I asked this question because I just started to use Navicat app for working with PostgreSQL from my Mac. So, in Navicat code autocompletion works properly with table-name.column-name way... Strange thing indeed.
    – Worker
    May 26, 2011 at 21:54
  • I'm not suggesting you use composite types, just trying to explain why the parser interprets your syntax a certain way. It sounds like the question has arisen because of a quirk of Navicat - that sort of autocompletion makes sense in a select statement but not in the column list on an insert statement. Maybe you should let Navicat know there is a bug in their software? May 27, 2011 at 3:11
  • Hi Jack, for sure. I just have to fid out how I submit a new ticket for them :-)
    – Worker
    May 29, 2011 at 7:10
  • Seems that Navicat is not about acepting client's feedback: navicat.com/en/support/support_ticket/submit_ticket.html
    – Worker
    May 29, 2011 at 8:52

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);
  • Thank you for your reference. Now I can say: Now I know for sure!
    – Worker
    May 26, 2011 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.