Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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);
share|improve this answer
    
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. –  MinimeDJ May 26 '11 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? –  Jack Douglas May 27 '11 at 3:11
    
Hi Jack, for sure. I just have to fid out how I submit a new ticket for them :-) –  MinimeDJ May 29 '11 at 7:10
    
Seems that Navicat is not about acepting client's feedback: navicat.com/en/support/support_ticket/submit_ticket.html –  MinimeDJ May 29 '11 at 8:52
add comment

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

database.schema.table

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);
share|improve this answer
    
Thank you for your reference. Now I can say: Now I know for sure! –  MinimeDJ May 26 '11 at 21:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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