Insert unnested array of null values into a double precision column, postgresql

Question

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?

Lukas Eklund · Accepted Answer · 2013-02-05 17:32:05Z

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[])

answered Feb 5, 2013 at 17:32

Lukas Eklund

6,1481 gold badge34 silver badges34 bronze badges

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

1 Comment

Erwin Brandstetter Over a year ago

@kpurdon: It's good to cast unnest(arr::float8[]), and not unnest(arr)::float8. I just found problems with the latter.

Collectives™ on Stack Overflow

Insert unnested array of null values into a double precision column, postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related