Postgres Pro
Downloads
Home   >   mailing lists

Thread: In 7.4 ensure you have DEFAULT now () with no spaces

In 7.4 ensure you have DEFAULT now () with no spaces

From
"David B"
Date:
Just a heads up folks...

In converting from 7.3 to 7.4 one got-ya we had was...

We had been testing 7.4 for a few days and just noticed that some tables had
created_timestamp rows with a date/time of the date the DB was created...not
the date/time the insert was done.

Looking at those tables the create DDL's for those few tables contained now
()
as in:

created_timestamp  timestamp DEFAULT now ()   -- note the space between now
and ()

Most had correctly been defined without the space - as in now()


Simple matter of doing ALTER TABLE x ALTER COLUMN y SET DEFAULT now() while
still in 7.3 to fix.

7.4 Migration documentation does mentions this but it can easily slip by as
it did with us...


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.704 / Virus Database: 460 - Release Date: 6/12/2004

Re: In 7.4 ensure you have DEFAULT now () with no spaces

From
Peter Eisentraut
Date:
David B wrote:
> We had been testing 7.4 for a few days and just noticed that some
> tables had created_timestamp rows with a date/time of the date the DB
> was created...not the date/time the insert was done.
>
> Looking at those tables the create DDL's for those few tables
> contained now ()
> as in:
>
> created_timestamp  timestamp DEFAULT now ()   -- note the space
> between now and ()
>
> Most had correctly been defined without the space - as in now()

Whatever it was, that was not the problem.  With 7.4.1:

peter=# create table test1 (foo text, create_timestamp timestamp default now());
CREATE TABLE
peter=# create table test2 (foo text, create_timestamp timestamp default now ());
CREATE TABLE
peter=# create table test3 (foo text, create_timestamp timestamp default now
peter(# /* blah */
peter(# (
peter(# /* blah */
peter(# )
peter(# )
peter-# ;
CREATE TABLE
peter=# \d test1                     Table "public.test1"     Column      |            Type             |   Modifiers
------------------+-----------------------------+---------------foo              | text
|create_timestamp| timestamp without time zone | default now()
 

peter=# \d test2                     Table "public.test2"     Column      |            Type             |   Modifiers
------------------+-----------------------------+---------------foo              | text
|create_timestamp| timestamp without time zone | default now()
 

peter=# \d test3                     Table "public.test3"     Column      |            Type             |   Modifiers
------------------+-----------------------------+---------------foo              | text
|create_timestamp| timestamp without time zone | default now()
 

They're all identical.

Re: In 7.4 ensure you have DEFAULT now () with no spaces

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> David B wrote:
>> Looking at those tables the create DDL's for those few tables
>> contained now ()
>> as in:
>> 
>> created_timestamp  timestamp DEFAULT now ()   -- note the space
>> between now and ()

> Whatever it was, that was not the problem.  With 7.4.1:

Nope.  I suppose this was a garbled report of the problem withdefault 'now'
as opposed todefault now()

The timestamp input parser will take 'now()' and variants on that,
since it basically ignores punctuation.  So probably what was really
at stake wasdefault 'now()'
(wrong because a string literal) versusdefault now()
(correct because a function call).
        regards, tom lane