Re: Trigger function to change data to correct datatype

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