PostgreSQL – How to Make a Query Match Exactly One Row or Fail

postgresqlpostgresql-11

I am making an arbitrary DELETE or SELECT query and I know this should effect or return only one row. How can I ensure that the query doesn't execute and I get some signal that something is wrong when more then one row matches?

Using LIMIT and ORDERBY allows the operation to continue, rather than doing a hard abort. Meaning multiple objects may be subject to DELETE with no chance to recover.

Preferably whatever signal results would be distinct from having zero match.

Best Answer

As mentioned by Arkhena, use a scalar subquery to determine the row to access:

DELETE FROM MyTable
WHERE ID = (SELECT ID
            FROM MyTable
            WHERE ...whatever ...);

If the subquery returns more than one ID, the query fails. This requires some unique key (here: ID).

If you do not want to get an error from the query, you can use CTE for the filter, and then check the number of rows in the CTE:

WITH r AS (
  SELECT ID
  FROM MyTable
  WHERE ...whatever...
)
DELETE FROM MyTable
WHERE (SELECT count(*) FROM r) = 1
  AND ID IN (SELECT ID FROM r)
RETURNING ID;

This statement will return an ID value only if one row was actually deleted.

Related Solutions

PostgreSQL – How to Make default_tablespace Work Properly

Per the PostgreSQL tablespaces documentation:

If a database is created without specifying a tablespace for it, it uses the same tablespace as the template database it is copied from.

Per the CREATE DATABASE documentation:

By default, the new database will be created by cloning the standard system database template1.

You could either alter the template1 database's tablespace to screwy, or create a new template database that has a tablespace of screwy, then specify that new database as a template when creating your screwy database.

PostgreSQL – How to Enforce an Exactly-One-Per-Group Constraint

The problem is similar to this one: How to have a one-to-many relationship with a privileged child?

The "at most one per group" part of the constraint can be solved with a partial index:

CREATE UNIQUE INDEX is_FavoriteChild
  ON x (yid)
  WHERE is_principal  ;

Another way to solve the problem is by removing the is_principal column and add a 3rd table. This doesn't solve the "exactly one" either:

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y (yid),
    UNIQUE (yid, xid)
);

CREATE TABLE x_principal (
    xid INTEGER NOT NULL,
    yid INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (yid, xid) REFERENCES x (yid, xid) 
);

If you want to enforce the "exactly one" restriction using DDL alone, it can be done in Postgres with deferrable constraints (I don't think this is an option in SQLite).

For more details and options, you can see the excellent answer in the SO question by @Erwin: Complex foreign key constraint in SQLAlchemy.

(Editing the answer for the additional detail that not all values of y.yid have to appear in table x. One table is added):

--- This table will hold all values of yid that appear in table x
CREATE TABLE y_x (
    yid INTEGER NOT NULL PRIMARY KEY REFERENCES y (yid),
    --- **no other columns**
    principal_xid INTEGER NOT NULL
);

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y_x (yid)
                         DEFERRABLE INITIALLY DEFERRED,
    UNIQUE (yid, xid)
);

ALTER TABLE y_x
ADD CONSTRAINT y_principal_x_fk
   FOREIGN KEY (yid, principal_xid)
   REFERENCES x (yid, xid)
   DEFERRABLE INITIALLY DEFERRED;

Best Answer

Related Solutions

PostgreSQL – How to Make default_tablespace Work Properly

PostgreSQL – How to Enforce an Exactly-One-Per-Group Constraint

Related Question