CODEWITHSUNDEEP
sqlpostgresqlconstraintsunique-constraint
BlueBomber
BlueBomber

Reputation: 302

In Postgresql, can you express and check a constraint without adding it to an entity?

What I'm looking for is behavior like what you would observe when adding a constraint (e.g., unique) to an existing table, validating it against the existing data, then removing the constraint. I don't want the constraint to persist on the table, I just want to express it, validate it on existing data, then move on (or raise an exception of the check fails); Does Postgresql (9.2) support a way to do this sort of thing directly?

Upvotes: 0

Views: 394

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125434

Create the constraint:

alter table t
add constraint the_pk primary key (user_id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "the_pk" for table "t"
ERROR:  could not create unique index "the_pk"
DETAIL:  Key (user_id)=(1) is duplicated.

If there was no error drop it:

alter table t
drop constraint the_pk;

If you don't want to persist it even for some time then do it inside a transaction:

begin;

alter table t
add constraint the_pk primary key (user_id);

Once satisfied roll the transaction back:

rollback;

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657982

IF EXISTS is generally simpler and faster for this purpose in plpgsql:

DO
$$
BEGIN
    IF EXISTS (SELECT 1 FROM tbl GROUP BY tbl_id HAVING count(*) > 1) THEN
        RAISE EXCEPTION 'Duplicate "tbl_id" in table "tbl"!';
    END IF;
END
$$ LANGUAGE plpgsql;

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324751

Setup:

CREATE TABLE testu ( id integer );
INSERT INTO testu SELECT generate_series(1,100);
INSERT INTO testu VALUES (1),(10),(20);

Demo query to find possible problem rows:

regress=> select id, count(id) FROM testu GROUP BY id HAVING count(id) > 1;
 id | count 
----+-------
 20 |     2
  1 |     2
 10 |     2
(3 rows)

Wrap it in DO block:

DO
$$
BEGIN
    PERFORM id FROM testu GROUP BY id HAVING count(id) > 1 LIMIT 1;
    IF FOUND THEN
        RAISE EXCEPTION 'One or more duplicate IDs in table testu';
    END IF;
END;
$$ LANGUAGE plpgsql;

If you want to report the individual colliding IDs you might do that by building a string from the results of the query, by looping over the results and raising NOTICEs, etc. Lots of options. I'll leave that as an exercise to you, the reader, with the PL/PgSQL documentation.

Upvotes: 1

Related Questions