CODEWITHSUNDEEP
postgresql
Peter
Peter

Reputation: 157

PostgreSQL CREATE an index and check if table exists

I run PostgreSQL version 9.6.4 and need to CREATE an index, but would like to check before index creation, if table and index already exist. I tried to do the following, but got syntax error:

DO $$
BEGIN
IF (SELECT to_regclass('public.areacrossing') != null) THEN 
    CREATE INDEX IF NOT EXISTS areac_d_idx
    ON public.areacrossing USING btree
    (d_id)
    TABLESPACE pg_default;
END 
$$


ERROR:  ERROR:  Syntaxerror at the end of input
LINE 27: $$
         ^


SQL state: 42601
Character: 508

Thanks to Laurenz Albe - the solution is:

DO $$
BEGIN
    IF (SELECT to_regclass('public.areacrossing') IS NOT null) 
    THEN 
        CREATE INDEX IF NOT EXISTS areac_d_idx
        ON public.areacrossing USING btree
        (d_id)
        TABLESPACE pg_default;
    END IF;
END $$;

Upvotes: 3

Views: 3152

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246453

You must close the IF block with an END IF;.

Proper indentation can help you to avoid such errors.

Upvotes: 2

Related Questions