1

Say I have a double precision column in a postgresql table and try the following INSERTS

Case 1:

INSERT INTO schema.table (column_name) VALUES (null);

Case 2:

INSERT INTO schema.table (column_name) VALUES (unnest(array[1,null,null,4,5]));

Case 3:

INSERT INTO schema.table (column_name) VALUES (unnest(array[null,null,null,null,null]));

Case 1 and 2 execute succesfully, but case 3 returns the following error:

ERROR: column "column_name" is of type double precision but expression is of type text LINE 1: INSERT INTO schema.table (column_name) VALUES (unnest(array[nu... ^ HINT: You will need to rewrite or cast the expression.

How should I re-write case 3 to re-cast an unnested array of null values into something that can be inserted in a double precision (or any numeric) column?

1 Answers1

1

You need to cast the array of null values to double precision[] or float8[]

INSERT INTO schema.table (column_name)
VALUES (unnest(
    array[null,null,null,null,null]::float8[]
));

or the cast can be expressed as

cast(array[null,null,null,null,null] as double precision[])
Lukas Eklund
  • 6,068
  • 1
  • 32
  • 33
  • 1
    @kpurdon: It's good to cast `unnest(arr::float8[])`, and not `unnest(arr)::float8`. [I just found problems with the latter.](http://stackoverflow.com/questions/16452501/null-emements-lost-when-casting-result-of-unnest) – Erwin Brandstetter May 09 '13 at 00:45