What happens?

DuckDBs list_position (with its alias array_position) behaves differently from the postgres array_position function, when searching for the position of null values in arrays.

The statement

select array_position(array[1,2, null], null)

returns null in DuckDB but 3 (the position of null) in postgres.

The difference should either be noted down in the "PostgreSQL Compatibility" guide or the function should work the same way.

PS: Thanks for maintaining DuckDB, I hope this issue does not sound rude :)

To Reproduce

DuckDB:

import duckdb
print(duckdb.sql("select array_position(array[1,2, null], null)"))

Postgres:
simply run the query:

select array_position(array[1,2, null], null)

for getting a postgres up quickly you can use docker with the following command:

docker run -d --name my-postgres-container -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 postgres:latest

docker exec -it my-postgres-container bash

psql -h localhost -U postgres -p 5432 -d postgres

OS:

Linux Debian

DuckDB Version:

1.2

DuckDB Client:

Python

Hardware:

No response

Full Name:

Pascal Stehling

Affiliation:

Catenion

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have