PostgreSQL Inserting Null works using COPY but fails using INSERT

Question

I have a bigint column named mycolumn. I execute SQL scripts using the PSQL command.

Using COPY command:

COPY public.mytable (myothercol, mycolumn) FROM stdin;
1   \N
\.

This works. But the following does not work:

EXECUTE 'insert into public.mytable (myothercol, mycolumn) values ($1,$2);' USING 
1,NULL;

This gives me error:

column "mycolumn" is of type bigint but expression is of type text

Why does insert not work for null value, whereas COPY works?

Why the dynamic SQL to begin with? You could simply use insert into public.mytable (myothercol, mycolumn) values ($1,$2); — user330315
– user330315, Commented Jan 16, 2019 at 15:56
Those are apparently parameters to your function, aren't they? — user330315
– user330315, Commented Jan 16, 2019 at 15:58
@a_horse_with_no_name I'm not using a function. Its a simple plpgsql script between DO END — user5155835
– user5155835, Commented Jan 16, 2019 at 16:32
Then use insert into public.mytable (myothercol, mycolumn) values (1, null); — user330315
– user330315, Commented Jan 16, 2019 at 16:49

Laurenz Albe · Accepted Answer · 2019-01-16 15:46:44Z

2

You best tell PostgreSQL to convert the parameter to bigint explicitly:

EXECUTE 'insert into public.mytable (myothercol, mycolumn) values ($1,$2::bigint);'
   USING 1,NULL;

The problem is that PostgreSQL does not automatically know what data type a NULL is, so it guesses text. COPY does not have to guess a data type.

answered Jan 16, 2019 at 15:41

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1