Skip to content
Advertisement

case statement in index creation

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;
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement