Hi list !

I have a MSAccess table in which dates are stored as strings. Some
dates are null but are stored as '-', and I cannot change this
because many queries use this value.
I need to insert theses values in a PostgreSQL table, but with a
real 'date' datatype.
Since '-' is not correct, I thought about creating a trigger that
would change the '-' to NULL before the INSERT took place, but my
function does not work :

CREATE OR REPLACE FUNCTION check_date()
RETURNS "trigger" AS
$BODY$
BEGIN
IF NEW.mydate = '-' THEN
NEW.mydate = NULL;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

And :

CREATE TRIGGER check_mydate
BEFORE INSERT
ON mytable
FOR EACH ROW
EXECUTE PROCEDURE check_date();


But when I try to insert a row in this table I have an error :
ERROR: invalid input syntax for type date: "-"

I would like to avoid using a function in the INSERT to replace
the "-" by NULL, because I execute this query on linked tables in
MSAccess, and since both table have the exact same structure, I
use the syntax 'INSERT INTO psql_table SELECT * FROM msaccess_table'.

Is there a way to achieve this ?

Thanks for helping !

Regards
--
Arnaud


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings