Re: Insert a default timestamp when nothing given

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.