Postgres: array of NULL values-CodePudding

I have a table defined as:

CREATE TABLE IF NOT EXISTS test (
    id INT NOT NULL,
    depth_mm REAL[]
);

If I insert something with both numbers and null values in the depth_mm array, there is no problem:

INSERT INTO test (id, depth_mm) VALUES (2, ARRAY[1,NULL,NULL]);

But if I insert an array of values, it complains:

INSERT INTO test (id, depth_mm) VALUES (2, ARRAY[NULL,NULL,NULL]);

ERROR:  column "depth_mm" is of type real[] but expression is of type text[]
LINE 1: INSERT INTO test (id, depth_mm) VALUES (2, ARRAY[NULL,NULL,NU...
                                                   ^
HINT:  You will need to rewrite or cast the expression.

How do I get Postgres to understand that I want to insert an array of null values?

CodePudding user response：

The engine can't infer the type of the array since all you have is NULLs there, and NULL can be of any type. So you'd somehow need to tell it it's a real NULL by casting it to real:

INSERT INTO test (id, depth_mm) VALUES (2, ARRAY[NULL::real,NULL,NULL]);

Or you can cast the entire array:

INSERT INTO test (id, depth_mm) VALUES (2, ARRAY[NULL,NULL,NULL]::real[]);