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
