Creating a partial index based on declared variables inside a DO block

Question

I have a table on which I want to have two different partial unique indexes that include unique constraints on different columns, based on a the value of another column in the table (which is a foreign key). Here's an example:

id | col1 | col2 | col3 | col4
------------------------------
1  | 3    | 4    | 'a'  | 13   
2  | 2    | 2    | 'b'  | 431   
3  | 3    | 4    | 'b'  | 18   
4  | 10   | 8    | 'b'  | 211

Let's say in this table I want:

to put a partial index on all the rows where col4=13 OR col4=18 with a unique constraint on col1, col2, and col3
to put a partial index on all the rows where col4<>13 AND col4<>18 with a unique constraint on col1 and col2

The problem is that I want to do this based on the value of a column in another table since col4 is a foreign key. This SO post asks a similar question but there isn't really a solution. Here is what I've done with pl/pgsql:

DO
$$
    DECLARE
        -- typical subquery
        option1_id INTEGER := (SELECT id FROM option_table WHERE name = 'option1');
        option2_id INTEGER := (SELECT id FROM option_table WHERE name = 'option2');
    BEGIN
        
        RAISE INFO '%, %', option1_id, option2_id;
        
        -- option1
        CREATE UNIQUE INDEX option1_index ON ex_table (col1, col2) WHERE (
            col4 NOT IN (option1_id, option1_id)
        );

        -- option2
        CREATE UNIQUE INDEX option2_index ON ex_table (col1, col2, col3) WHERE (
            reference_type IN (option1_id, option2_id)
        );
        
        -- this works!
        CREATE UNIQUE INDEX this_works ON ex_table (col1, col2, col3) WHERE (
            reference_type IN (13, 18)
        );
    END
$$;

Here is the error that I'm getting: ERROR: column "option1_id" does not exist
I know the variables are properly declared because the RAISE INFO is returning INFO: 13, 18

Pavel Stehule · Accepted Answer · 2020-10-21T07:10:08.380

2

DDL statements doesn't support parametrization - you cannot to use any variables there. You should to use dynamic SQL (in this case without clause USING):

EXECUTE format('CREATE UNIQUE INDEX ... WHERE reference_type IN (%L, %L)', option1_id, option2_id);

edited Oct 21 '20 at 07:10

answered Oct 21 '20 at 05:15

Pavel Stehule

42,331
5
91
94

Thank you, this is great. Small change needed: `(%I, %I)` -> `(%L, %L)` since we are formatting an integer instead of a SQL name – mel Oct 21 '20 at 06:08
@mel - I fixed placeholder type – Pavel Stehule Oct 21 '20 at 07:10

Creating a partial index based on declared variables inside a DO block

1 Answers1