Обсуждение: Trigger function to change data to correct datatype

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

On 5/8/06 9:52 AM, "Arnaud Lesauvage" <thewild@freesurf.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

Hi Sean, thanks for your answer !

Sean Davis a écrit :
> The type checking occurs before the trigger is run, so you can't use a
> trigger for this type of data cleanup.

I suspected this... Too bad...


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

What about creating a temporary table with the exact same
structure but for the 'date' field, adding a trigger on this table
which would insert the reformated row in the 'real' table, and
doing the insert into the temporary table instead ?
I'll give this a try. I wonder what kind of performance
degradation I will have with this kind of workaround ?

--
Arnaud