Postgres: array of NULL values

Question

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?

MarcinJ · Accepted Answer · 2022-02-21 21:22:18Z

3

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

answered Feb 21, 2022 at 21:22

MarcinJ

3,6592 gold badges16 silver badges19 bronze badges

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

1 Comment

Cameron Over a year ago

Thanks, @MarcinJ, that makes total sense. Much appreciated!

Collectives™ on Stack Overflow

Postgres: array of NULL values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related