Обсуждение: Insert a default timestamp when nothing given

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.

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

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

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

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 INTO 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: http://www.varlena.com/varlena/GeneralBits/,
02-Jan-2006, 'Insert or Update with Exception Handling'


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


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Martijn van Oosterhout <kleptog@svana.org> writes:

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

-Doug

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?

--
10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail
+++ GMX - die erste Adresse f�r Mail, Message, More +++

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


---------- Original Message -----------
From: "  Martin Pohl" <Nilpherd@gmx.net>
To: Doug McNaught <doug@mcnaught.org>
Cc: pgsql-general@postgresql.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?
>
> --
> 10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail
> +++ GMX - die erste Adresse für Mail, Message, More +++
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
------- End of Original Message -------

On 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,
--
Martijn van Oosterhout   <kleptog@svana.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.

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@gmx.net> wrote in message
news:31905.1137671744@www74.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 INTO 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
>
> --
> Telefonieren Sie schon oder sparen Sie noch?
> NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>