Skip to content

Instantly share code, notes, and snippets.

@concosminx

concosminx/create_constraint_if_not_exists.sql

Forked from estysdesu/create_constraint_if_not_exists.sql
Created March 17, 2023 09:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save concosminx/e58bb10cdee4a6821b4cc8c8dbe16bec to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save concosminx/e58bb10cdee4a6821b4cc8c8dbe16bec to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @estysdesu estysdesu revised this gist Jul 21, 2019. No changes.
  2. @estysdesu estysdesu revised this gist Jul 21, 2019. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions create_constraint_if_not_exists.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,5 @@
    // vim: syntax=sql

    CREATE OR REPLACE FUNCTION create_constraint_if_not_exists (t_name text, c_name text, constraint_sql text)
    RETURNS void
    AS
  3. @estysdesu estysdesu renamed this gist Jul 19, 2019. 1 changed file with 0 additions and 0 deletions.
    0 createConstraintIfNotExists.sql → create_constraint_if_not_exists.sql
    View file
    File renamed without changes.
  4. @estysdesu estysdesu revised this gist May 7, 2019. No changes.
  5. @estysdesu estysdesu revised this gist May 7, 2019. No changes.
  6. @estysdesu estysdesu created this gist May 7, 2019.
    16 changes: 16 additions & 0 deletions createConstraintIfNotExists.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,16 @@
    CREATE OR REPLACE FUNCTION create_constraint_if_not_exists (t_name text, c_name text, constraint_sql text)
    RETURNS void
    AS
    $BODY$
    BEGIN
    -- Look for our constraint
    IF NOT EXISTS (SELECT constraint_name
    FROM information_schema.constraint_column_usage
    WHERE constraint_name = c_name) THEN
    EXECUTE 'ALTER TABLE ' || t_name || ' ADD CONSTRAINT ' || c_name || ' ' || constraint_sql;
    END IF;
    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE;

    SELECT create_constraint_if_not_exists('foo', 'bar', 'CHECK (foobies < 100);');