Sean Davis <sdavis2@mail.nih.gov> writes:
> I have the following setup and data file that I want to load using copy
> (8.0.0, macos). The trigger function is to "clean" the input data (see
> earlier posts on the subject). However, it seems as if the trigger
> function is not doing what I would have thought. I want it to replace
> '-' in the data in certain columns with NULLs.
> CREATE TABLE g_refseq (
> ...
> chrom_gi integer,
> ...
> if (NEW.chrom_gi='-') THEN
> NEW.chrom_gi:= 'NULL';
> END IF;
This isn't gonna work, because '-' is not a legal value of an integer
column and so the data conversion would have failed long before your
trigger gets to execute.
(You are also wrong in using quotes around the keyword NULL, but that's
a secondary problem.)
If you have to import data that's defined like this, I'd suggest loading
into a temporary table that's declared as all unconstrained text
columns, and then converting with something like
INSERT INTO realtable
SELECT
...
(case when chrom_gi = '-' then null else chrom_gi::integer),
...
FROM temptable;
regards, tom lane