Conditional constraint if scheme and table exists in PostgresSQL

Question

I am having multiple scheme defined, and while table creation I need to add conditional constraint that the constraint should only be added if the table and scheme exists in PostgresSQL. Below is the bit snippet which I am trying,

CREATE TABLE second.second_table
(
id serial NOT NULL,
another_id integer NOT NULL,
some_column boolean NOT NULL,
CONSTRAINT other_pkey PRIMARY KEY (id),
CONSTRAINT fk_other_another_id FOREIGN KEY (another_id) REFERENCES first.first_table (id)
MATCH SIMPLE ## CHECK (TABLE IF EXISTS first.first_table) ##
)

The conditional part I defined as '##', and its just example and not correct way of writing. So, basically the constraint should be only added if the scheme and table is exists else not. Thanks in advance. Just a note here, I am using Docker of PostgresSQL of 'postgres:11.5-alpine' if something related to version.

The overview about this conditional constraint is in our project we having single PostgresSQL with multiple scheme defined there. As defined above, there is some relation with one scheme to another scheme. Now, while in production environment definitely all schemes will be there with no issue. But while doing some Junit test cases, if you doing locally one scheme can be exists or not. Just to have the dependency constraint I would like to make the constraint as conditional. If the scheme is already created then constraint will be exists or not. As locally if first scheme project have not executed then the constraint will not be added, if first scheme is exists then constraint will also exists.

Not possible, and rather strange as well: How can there be a dependency when you're not even sure if the data structure exists? Are you sure this model is correct? At least it's not complete — Frank Heikens
– Frank Heikens, Commented Mar 16, 2023 at 9:40
You can of course check the various views in the information schema and issue different CREATE TABLE statements based on that. It does seem very strange that you don't know what the "shape" of your database is though. — Richard Huxton
– Richard Huxton, Commented Mar 16, 2023 at 9:49
Thanks for your answers @RichardHuxton I can describe my scenario in bit different way so it can be understandable, I have one single PostgresSQL project with multiple scheme defined there. As defined here, there is some relation with one scheme to another scheme. Now, while in production definitely all schemes will be there with no issue. But while doing some Junit test cases, if you doing locally one scheme can be exists or not. Just to have the dependency constraint I would like to make the constraint as conditional. — Ronak Joshi
– Ronak Joshi, Commented Mar 16, 2023 at 10:40
This model is bit sample just to showcase my scenario so might have some correction but I tried to defined all the details there, and about the dependency I defined in my above comment. Thanks for your answer @FrankHeikens — Ronak Joshi
– Ronak Joshi, Commented Mar 16, 2023 at 10:42

5ar · Accepted Answer · 2023-03-16 10:34:23Z

0

As others have said, this is an odd request, so please check if you can model what you need in some other way before attempting this.

That being said, a way you can do this is by implementing a trigger that will run on INSERT and UPDATE, and will check the information_schempa.tables as its first condition.

I don't have pg11 running anywhere on hand at the moment, so take this code as an idea how the trigger should be written.

CREATE OR REPLACE FUNCTION check_condition_if_first_exists()
    RETURNS trigger AS
$BODY$  
    BEGIN
        IF NOT EXISTS (
            SELECT true
            FROM information_schema.tables 
            WHERE table_schema = 'first' AND table_name = 'first_table'
        ) THEN
            -- table does not exist
            RETURN NEW;
        END IF;

        IF (##) THEN -- your condition instead of ##
            -- condition satisfied
            RETURN NEW;
        END IF;

        RAISE EXCEPTION 'New row does not satisfy condition'
    END;
$BODY$
    LANGUAGE plpgsql;


CREATE TRIGGER on_upser_and_first_exists
    BEFORE INSERT OR UPDATE ON second.second_table
    FOR EACH ROW
    EXECUTE PROCEDURE check_condition_if_first_exists();

If the first table is something you know will be added at some point and never deleted, then instead of a trigger you can use the same information_schema.tables select and just do an ALTER TABLE command to add the check constraint.

If the table can be deleted but you know that that will only be done as some kind of migration, you can always finish every migration with that same check and just do [ADD | DROP] CONSTRAINT IF [NOT] EXISTS accordingly.

answered Mar 16, 2023 at 10:34

5ar

2,2351 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ronak Joshi Over a year ago

Thanks for your suggestion and taking your time to write it. I have updated my system overview in original question, and it might not getting fit as I need to run in local environment while executing test cases and not sure those triggers will help there.

5ar Over a year ago

If executing tests is your only use case, I'd consider creating a separate DB user for tests and just add current_user = 'test_user' to the check constraint condition you have to short circuit its execution. That will probably be the cleanest and easiest solution if your environment allows for it.

Collectives™ on Stack Overflow

Conditional constraint if scheme and table exists in PostgresSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related