Column names - time

Started by Lee Kindnessabout 24 years ago5 messages
#1Lee Kindness
lkindness@csl.co.uk

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
column
constraint
copy
cross
current_date
current_time
current_timestamp
current_user
dec
decimal
default
deferrable
desc
distinct
do
else
end
except
exists
explain
extract
false
float
for
foreign
freeze
from
full
global
group
having
ilike
initially
in
inner
intersect
into
inout
is
isnull
join
leading
left
like
limit
listen
load
local
lock
move
natural
nchar
new
not
notnull
nullif
null
numeric
off
offset
old
on
only
or
order
out
outer
overlaps
position
precision
primary
public
references
reset
right
select
session_user
setof
show
some
substring
table
then
time
timestamp
to
trailing
transaction
trim
true
union
unique
unknown
user
using
vacuum
varchar
verbose
when
where

Best Regards, Lee Kindness..

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Lee Kindness (#1)
Re: Column names - time

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

#3Lee Kindness
lkindness@csl.co.uk
In reply to: Tom Lane (#2)
Re: Column names - time

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.

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Lee Kindness (#3)
Re: Column names - time

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

#5Peter Eisentraut
peter_e@gmx.net
In reply to: Tom Lane (#4)
Re: Column names - time

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