Re: Trigger function to change data to correct datatype

On 5/8/06 9:52 AM, "Arnaud Lesauvage" <thewild(at)freesurf(dot)fr> wrote:

> 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: "-"

The type checking occurs before the trigger is run, so you can't use a
trigger for this type of data cleanup.

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

I would create a temporary table that contains a varchar field for columns
like this. Load your unformatted data into the temporary table and then use
the postgresql "case" statement (or other postgresql formatting functions)
to change the data into an acceptable format for insertion into a final
table. Alternatively, you can dump the table to disk as a tab-delimited
text file and then use psql to copy the data back into the database with '-'
as the NULL character. Both "case" and "copy" are in the postgresql docs.

Sean