Thread: Column names - time

In moving from 7.1.3 to 7.2devel (for bug fixes) we've encountered a
problem with a, previously valid, column name: time. In 7.1.3 the
following worked:
 CREATE TABLE test(time INTEGER);

while in 7.2devel it results in a parse error:
 ERROR:  parser: parse error at or near "time"

Looking at the source I see this is a result of 'time' being added to
ColLabel in backend/parser/gram.y earlier this month.

This effects interface code and database migration using pg_dump.

Obviously a new column name will have to be used, however is there a
definitive list of keywords to avoid so such an occurance wouldn't
happen in a production system? Currently these include:
 abort all analyse analyze and any asc between binary bit both case cast char character check cluster coalesce collate
columnconstraint copy cross current_date current_time current_timestamp current_user dec decimal default deferrable
descdistinct do else end except exists explain extract false float for foreign freeze from full global group having
ilikeinitially in inner intersect into inout is isnull join leading left like limit listen load local lock move natural
ncharnew not notnull nullif null numeric off offset old on only or order out outer overlaps position precision primary
publicreferences reset right select session_user setof show some substring table then time timestamp to trailing
transactiontrim true union unique unknown user using vacuum varchar verbose when where
 

Best Regards, Lee Kindness..

Lee Kindness <lkindness@csl.co.uk> writes:
> Obviously a new column name will have to be used, however is there a
> definitive list of keywords to avoid so such an occurance wouldn't
> happen in a production system?

There is an up-to-date list of keywords in the documentation:
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-keywords-appendix.html

As for predicting what keywords might become reserved in future PG
releases, my crystal ball is down at the moment ... but words that are
reserved in SQL99 would be good things to avoid.
        regards, tom lane

Tom Lane writes:> There is an up-to-date list of keywords in the documentation:>
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-keywords-appendix.html

Thanks for the info. Would I be right in saying that the status of
time (unreserved for PostgreSQL) for 7.2 needs to be changed to
reserved since it can no-longer be used as an unquoted column
identifier?

Perhaps the other time related identifiers too.

Regards, Lee Kindness.

Lee Kindness <lkindness@csl.co.uk> writes:
> Tom Lane writes:
>>> There is an up-to-date list of keywords in the documentation:
>>> http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-keywords-appendix.html

> Thanks for the info. Would I be right in saying that the status of
> time (unreserved for PostgreSQL) for 7.2 needs to be changed

Probably.  Peter has a script that generates that table directly from
gram.y, and I assume he'll run it sometime before 7.2 release...
        regards, tom lane

Tom Lane writes:

> Probably.  Peter has a script that generates that table directly from
> gram.y, and I assume he'll run it sometime before 7.2 release...

After beta has started.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter