Re: Insert a default timestamp when nothing given

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Martin Pohl <Nilpherd(at)gmx(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Insert a default timestamp when nothing given
Date: 2006-01-19 12:08:22
Message-ID: 20060119120822.GG9949@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Jan 19, 2006 at 12:55:44PM +0100, Martin Pohl wrote:
>
> Hi,
>
> I have to port an application from MS SQL7 to Postgresql (7.4).
>
> When I have a column with a datetime on MS SQL7 the following is possible:
> INSERT INTO mytable (mydate) values ('');
>
> In this case MSSQL will insert '01.01.1900' as the date.

Ugh! I thought that kind of data munging was purely the realm of MySQL.

> When I do the same on Postgresql it says:
> "invalid input syntax for type timestamp with time zone: ''".

Well yes, it's not a date and I don't think there's an easy way to make
PostgreSQL think it's a date.

> Unfortunately the application I have to port often uses '' as a date.

My question is why? Do they really mean NULL (ie unknown date) or did a
lot of things happen on that date we're only just finding out about?

> My question:
> Is there any way to have MSSQLs behavior in PostgreSQL?

Not directly. I suppose you could create a view that converted the
value to the right date on insert. Alternativly, you could write a
function to do the conversion for you, so you say:

INSERT INTO mytable (mydate) values (fixdate(''));

There may be other solution I havn't thought of.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeroen T. Vermeulen 2006-01-19 12:12:10 GBorg as a spam magnet
Previous Message Peter Zeltins 2006-01-19 12:04:52 Using rowtype parameter