[SOLVED] How to select empty paths with POSTGRES LTREE

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;