Last Updated: February 25, 2016
·
3.07K
· higorae

Creating sequence if it not exists using postgresql

if you want to create a sequence besead in a specific query

DO $$
    DECLARE
        seq record;
    BEGIN
        for seq in 
            SELECT * FROM mytable
        loop
            IF EXISTS (
                SELECT 1
                    FROM   pg_class c
                    JOIN   pg_namespace n ON n.oid = c.relnamespace
                    WHERE  c.relname = 'sequence_name'
                    AND    n.nspname = 'schema'
                ) THEN
                create sequence mytable.id
        END IF;  
    end loop;
END$$;