Thread: INSERT doesn't like (.)
.)
From
pgsql-bugs@postgresql.org
Date:
Adam Levine (adam_l_levine@hotmail.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
INSERT doesn't like  (<table>.<column>)

Long Description
I'm using Dynamo connected to Postgres.  The PreparedStatement generated for an INSERT works on other dbs, but Postgres
complainswith a parsing error. 
 specifically:
   INSERT into <table> (<table>.<column>) VALUES (<value>);

it will, however, accept:
   INSERT into <table> (<column>) VALUES (<value>);

I tested against SQL-Anywhere, just to make sure I wasn't losing my mind, and it takes either form.

This is using postgres 7.0.2.
----
output from postgres:


Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

goe=> create table test (col1 varchar(20), col2 varchar(20));
CREATE
goe=> insert into test (test.col1, test.col2) VALUES ('test1', 'test2');
ERROR:  parser: parse error at or near "."
goe=>


Sample Code
create table test (col1 varchar(20), col2 varchar(20));

insert into test (test.col1, test.col2) VALUES ('test1', 'test2');



No file was uploaded with this report
.)
From
Tom Lane
Date:
pgsql-bugs@postgresql.org writes:
> I'm using Dynamo connected to Postgres.  The PreparedStatement
> generated for an INSERT works on other dbs, but Postgres complains
> with a parsing error.
>  specifically:
>    INSERT into <table> (<table>.<column>) VALUES (<value>);

What's your definition of "other dbs"?  The above statement is quite
clearly in violation of the SQL92 and SQL99 specifications:

         <insert statement> ::=
              INSERT INTO <table name>
                <insert columns and source>

         <insert columns and source> ::=
                [ <left paren> <insert column list> <right paren> ]
              <query expression>
              | DEFAULT VALUES

         <insert column list> ::= <column name list>

         <column name list> ::=
              <column name> [ { <comma> <column name> }... ]

         <column name> ::= <identifier>

I'm not particularly excited about supporting non-SQL variant syntaxes
that add no functionality.

            regards, tom lane
.)
From
Peter Eisentraut
Date:
> INSERT doesn't like  (<table>.<column>)

This isn't allowed by SQL and is completely useless.

         <insert statement> ::=
              INSERT INTO <insertion target>
                <insert columns and source>

         <insertion target> ::=
              <table name>

         <insert columns and source> ::=
                <from subquery>
              | <from constructor>
              | <from default>

         <from constructor> ::=
              [ <left paren> <insert column list> <right paren> ]
                [ <override clause> ]
                <contextually typed table value constructor>

         <insert column list> ::= <column name list>

         <column name list> ::=
              <column name> [ { <comma> <column name> }... ]

         <column name> ::=
                <identifier>


--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/