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..

Tue, 16 Oct 2001 12:25:51 +0100 (BST)
Lee Kindness <lkindness@csl.co.uk>
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

Tue, 16 Oct 2001 17:19:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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.

Wed, 17 Oct 2001 09:36:35 +0100 (BST)
Lee Kindness <lkindness@csl.co.uk>
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

Wed, 17 Oct 2001 10:21:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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


Wed, 17 Oct 2001 22:37:48 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Column names - time 