Thread: Column qualifier issue

As far as I can tell, there isn't a way to get postgresql to accept column
qualifiers (e.g. tablenames).  A 'parse error at or near "."' gets returned.

I'm using a closed source application that has successfully handled a number
of other sql92 compatible datasources via odbc.  The problem is the app
insists on qualifying column names all the time, even in Insert and Update
statements.  That is not incorrect according to sql92, even though one could
assume that a qualifier is never required with update and insert.

Is it possible to get Postgres to accept these statements?  Anyone have a hack
to let this stuff through the parser?

Tia,

Jim

"Jim Wilson" <jimw@kelcomaine.com> writes:
> As far as I can tell, there isn't a way to get postgresql to accept column
> qualifiers (e.g. tablenames).  A 'parse error at or near "."' gets returned.

You're either very confused or using a *very* old version of Postgres.

What version is it, what query are you issuing exactly, and what exactly
is the error message?  (Cut-and-paste from a psql session would be the
best way to answer the last two ... or if the query is being issued from
another application, you could turn on query logging and send in an
excerpt from the postmaster log.)

            regards, tom lane

On Mon, 12 Jan 2004, Tom Lane wrote:

> "Jim Wilson" <jimw@kelcomaine.com> writes:
> > As far as I can tell, there isn't a way to get postgresql to accept column
> > qualifiers (e.g. tablenames).  A 'parse error at or near "."' gets returned.
>
> You're either very confused or using a *very* old version of Postgres.
>
> What version is it, what query are you issuing exactly, and what exactly
> is the error message?  (Cut-and-paste from a psql session would be the
> best way to answer the last two ... or if the query is being issued from
> another application, you could turn on query logging and send in an
> excerpt from the postmaster log.)

My guess from the rest of his message is something like:

sszabo=# insert into a (a.x) values ('3');
ERROR:  syntax error at or near "." at character 17

(from my 7.5 machine).

However, AFAICS in sql92 the above isn't valid, because a.x doesn't meet
the syntactic requirements for elements in the column name list.

Tom Lane <tgl@sss.pgh.pa.us> said:

> "Jim Wilson" <jimw@kelcomaine.com> writes:
> > As far as I can tell, there isn't a way to get postgresql to accept column
> > qualifiers (e.g. tablenames).  A 'parse error at or near "."' gets returned.
>
> You're either very confused or using a *very* old version of Postgres.
>
> What version is it, what query are you issuing exactly, and what exactly
> is the error message?  (Cut-and-paste from a psql session would be the
> best way to answer the last two ... or if the query is being issued from
> another application, you could turn on query logging and send in an
> excerpt from the postmaster log.)
>
>             regards, tom lane
>

This is version 7.2.3, and the same error was produced on 7.4.x (using the
phpadmin demo site).  Looking at the logs I don't see anything that is
addressing this.  This syntax isn't required by sql92,  but it is allowed (at
least that is my reading).

Following is a log from a psql session that demonstrates the problem.

Best,

Jim Wilson


bash$ ./bin/psql testdb
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

testdb=# select * from test;
 cola |    colb
------+------------
    1 | Record One
    2 | 2nd Record
(2 rows)

kelcodb=# \d test
                Table "test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 cola   | bigint                | not null
 colb   | character varying(10) | not null

testdb=# update test set test.colb = '2nd Record' where test.cola = 2;
ERROR:  parser: parse error at or near "."
testdb=# update test set colb = '2nd Record' where cola = 2;
UPDATE 1

Stephan Szabo <sszabo@megazone.bigpanda.com> said:

> On Mon, 12 Jan 2004, Tom Lane wrote:
>
> > "Jim Wilson" <jimw@kelcomaine.com> writes:
> > > As far as I can tell, there isn't a way to get postgresql to accept column
> > > qualifiers (e.g. tablenames).  A 'parse error at or near "."' gets returned.
> >
> > You're either very confused or using a *very* old version of Postgres.
> >
> > What version is it, what query are you issuing exactly, and what exactly
> > is the error message?  (Cut-and-paste from a psql session would be the
> > best way to answer the last two ... or if the query is being issued from
> > another application, you could turn on query logging and send in an
> > excerpt from the postmaster log.)
>
> My guess from the rest of his message is something like:
>
> sszabo=# insert into a (a.x) values ('3');
> ERROR:  syntax error at or near "." at character 17
>
> (from my 7.5 machine).
>
> However, AFAICS in sql92 the above isn't valid, because a.x doesn't meet
> the syntactic requirements for elements in the column name list.

Yes, I think you are correct on that.  I was misreading column reference for
column name.  Would it be difficult to patch my local copy to either permit
this or strip off the characters from the qualifier portion in the parser?

Best,

Jim

"Jim Wilson" <jimw@kelcomaine.com> writes:
> Yes, I think you are correct on that.  I was misreading column reference for
> column name.  Would it be difficult to patch my local copy to either permit
> this or strip off the characters from the qualifier portion in the parser?

[shrug...]  You could probably hack the grammar to throw away a
qualifier there, but wouldn't it be easier to fix your incorrect SQL?

AFAICS, SQL92 does not allow a qualified name there, and SQL99 does but
appears to assign it some completely other semantics than what you're
expecting --- looks like an object method call of some kind... so you
are in for trouble in the long run if you don't fix your code.

            regards, tom lane