Search code examples
postgresqlltree

How to select empty paths with POSTGRES LTREE

I have a simple table defined in Postgres, where I'm using LTREE, but some rows can have empty paths:

CREATE TABLE films (
    id     serial PRIMARY KEY,
    title  varchar(40) NOT NULL,
    path   ltree DEFAULT NULL 
);

If I insert the following values into the table,

INSERT INTO films (title, path)
VALUES ('first', 'A'),('second', 'A.B'),('third', NULL);

then try to select the rows with empty paths,

SELECT * FROM films WHERE path=NULL;

I get empty rows:

 id | title | path 
----+-------+------
(0 rows)

How should I modify the query to return rows with empty paths? Thanks!

Solution

  • The result of comparison operation with at least one operand is NULL always returns NULL. Thus your predicate where path = null always returns null, but the to select a row the expression must return True. Use instead:

    SELECT * FROM films WHERE path is NULL;