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 INT mytable (mydate) values ('');
In this case MSSQL will insert '01.01.1900' as the date.
When I do the same on Postgresql it says:
"invalid input syntax for type timestamp with time zone: ''".
Unfortunately the application I have to port often uses '' as a date.
My question:
Is there any way to have MSSQLs behavior in PostgreSQL?
Thanks for answers

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 INT 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 INT mytable (mydate) values (fixdate(''));

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

am 19.01.2006, um 12:55:44 +0100 mailte Martin Pohl folgendes:

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 INT mytable (mydate) values ('');

wrong date!

In this case MSSQL will insert '01.01.1900' as the date.

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

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

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

You can write a function with exception handling.
Simple example: ,
02-Jan-2006, 'Insert or Update with Exception Handling'

, you can alter table and add a default date, but you can't insert a
wrong date.

HTH, Andreas

Hi,

Not directly. I suppose you could create a view that converted the
value to the right date on insert.
I think a trigger might make more sense.

That was a very good idea! I tought it would solve my problem. Unfortunately
it didn't: I still get the "invalid syntax" error (I ensured that the
trigger worked by using other values). Apparently the syntax check is done,
before the trigger is called:

create or replace function test() returns trigger as '
begin

if NEW.datum = '''' THEN
NEW.datum := ''01.01.1900'';
end if;
return NEW;
end;
' language plpgsql;

create trigger test before insert or update on foo
for each row execute procedure test();

Adding a default value will also not work, since the given date is not a
correct timestampz when inserting. So the default value doesn't help.

I know that inserting '' is wrong in the first place, and that Postgre works
correctly at this point. But I can't help it - the application I have to
port does it and I can't change it. Therefore I need a smart workaround for
a sloppy programming in the application and a sloppy MS SQL.
(This is not meant rude in any way, it's just the situation I was given in a
task)

Does anyone have any other suggestions or ideas?

Change the column type in the view to text, then in the insert/update rule, if the value is '' insert null or what ever,
else insert the date (as text) into the real date column (as a date)

Jim

Message
From: " Martin Pohl" <Nilpherd (AT) gmx (DOT) net>
To: Doug McNaught <doug (AT) mcnaught (DOT) org>
Cc: pgsql-general (AT) postgresql (DOT) org
Sent: Thu, 19 Jan 2006 14:43:26 +0100 (MET)
Subject: Re: [GENERAL] Insert a default timestamp when nothing given

Hi,

Not directly. I suppose you could create a view that converted the
value to the right date on insert.
I think a trigger might make more sense.

That was a very good idea! I tought it would solve my problem. Unfortunately
it didn't: I still get the "invalid syntax" error (I ensured that the
trigger worked by using other values). Apparently the syntax check is done,
before the trigger is called:

create or replace function test() returns trigger as '
begin

if NEW.datum = '''' THEN
NEW.datum := ''01.01.1900'';
end if;
return NEW;
end;
' language plpgsql;

create trigger test before insert or update on foo
for each row execute procedure test();

Adding a default value will also not work, since the given date is not a
correct timestampz when inserting. So the default value doesn't help.

I know that inserting '' is wrong in the first place, and that Postgre works
correctly at this point. But I can't help it - the application I have to
port does it and I can't change it. Therefore I need a smart workaround for
a sloppy programming in the application and a sloppy MS SQL.
(This is not meant rude in any way, it's just the situation I was given in a
task)

Does anyone have any other suggestions or ideas?

Thu, Jan 19, 2006 at 02:43:26PM +0100, Martin Pohl wrote:
Hi,

Not directly. I suppose you could create a view that converted the
value to the right date on insert.
I think a trigger might make more sense.

That was a very good idea! I tought it would solve my problem. Unfortunately
it didn't: I still get the "invalid syntax" error (I ensured that the
trigger worked by using other values). Apparently the syntax check is done,
before the trigger is called:

Yeah, it's done in the type input function. I suppose you could create
your own msdate type that behaved the way you wanted. There are some
packages out there to make porting easier, perhaps one of those can
help?

Have a nice day,

Change your table definition and specify a defeault value for your timestamp
column this way -- when nothing is given on insert it will
populate

CREATE TABLE test (

id serial not null primary key,
defaultdate timestamp not null default now()

);

"" Martin Pohl"" <Nilpherd (AT) gmx (DOT) netwrote in message
news:31905.1137671744 (AT) www74 (DOT) gmx.net

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 INT mytable (mydate) values ('');

In this case MSSQL will insert '01.01.1900' as the date.

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

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

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

Thanks for answers