Selecting arrays yields nullable type

[timvandam]

Describe the bug A clear and concise description of what the bug is.

When selecting an array column (eg VARCHAR(255)[]) the inferred type is nullable, even when the column is non nullable.

To Reproduce Steps to reproduce the behavior:

await sql`
    CREATE TABLE users
    (
        id         SERIAL PRIMARY KEY,
        public_id  VARCHAR(255) UNIQUE NOT NULL,
        username   VARCHAR(255) UNIQUE NOT NULL,
        roles      VARCHAR(255)[]      NOT NULL,
        created_at TIMESTAMPTZ         NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ         NOT NULL DEFAULT NOW()
    );`;

// this is the inferred type, but it should be string[]
const [user] = await sql<{ public_id: string; username: string; roles: string[] | null }>`
    SELECT public_id, username, roles
    FROM users
    WHERE public_id = ${publicId}
    LIMIT 1;`;

Expected behavior A clear and concise description of what you expected to happen.

It should yield roles: string[]

Screenshots If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• PostgreSQL version [e.g. 13, 14]
• Version [e.g. 22]

Additional context Add any other context about the problem here.

I've checked whether postgres can somehow return NULL here, but it seems not. Inserting NULL is also not possible, i.e.

insert into users (public_id, username, roles)
VALUES ('test', 'test', NULL);

fails

[timvandam]

Closing for now since this seems to have fixed itself

[Newbie012]

Technically, it is possible that an array would contain null values:

CREATE TEMP TABLE example_table (
    id SERIAL PRIMARY KEY,
    array_column TEXT[]
);

INSERT INTO example_table (array_column) VALUES
('{"element1", NULL, "element3"}'),
('{"element4", "element5", NULL}'),
(NULL);

select * from example_table;

maybe I should add a strict.* options

[timvandam]

In your table you do not have a NOT NULL on array_column. If I add that the insert fails

[Newbie012]

Oh you're right... my bad

[Newbie012]

Oh wait, I wasn't wrong:

DROP TABLE IF EXISTS example_table;

CREATE TEMP TABLE example_table (
    id SERIAL PRIMARY KEY,
    array_column TEXT[] NOT NULL
);

INSERT INTO example_table (array_column) VALUES
('{"element1", NULL, "element3"}'),
('{"element4", "element5", NULL}');

select * from example_table;

[timvandam]

A I see what you mean now, but then the type would is still wrong. I got roles: string[] | null instead of roles: (string | null)[]. However I'm getting string[] now

[Newbie012]

Yeah, the original issue was probably due to a caching issue, restarting ESLint would fix that.

In any case, As I wrote previously, I might consider adding strict flags, or change the behavior with a major version