CODEWITHSUNDEEP
arrayspostgresqlcastinginsertnull
user1676521
user1676521

Reputation:

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

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?

Upvotes: 1

Views: 2761

Answers (1)

Lukas Eklund
Lukas Eklund

Reputation: 6138

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

Upvotes: 1

Related Questions