How do i use a CASE statement when creating a UNIQUE INDEX?
My statement looks like this
CREATE UNIQUE INDEX my_unique_creation ON junk ((CASE WHEN nlevel(path) > 1 THEN (subpath(path, 0, -1), name) ELSE (path, name) END)) WHERE my_col IS NULL;
This fails on the following line:
pq: column "" has pseudo-type record
Advertisement
Answer
The CASE
should only return a single column, not a tuple (“record”).
CREATE UNIQUE INDEX my_unique_creation ON junk ((CASE WHEN nlevel(path) > 1 THEN subpath(path, 0, -1) ELSE path END), name) WHERE my_col IS NULL;