How to assert the expect number of result rows of a sub-query in PostgreSQL?-CodePudding

Often times I find myself writing code such as:

const fooId = await pool.oneFirst(sql`
  SELECT id
  FROM foo
  WHERE nid = 'BAR'
`);

await pool.query(sql`
  INSERT INTO bar (foo_id)
  VALUES (${fooId})
`);

oneFirst is a Slonik query method that ensures that the query returns exactly 1 result. It is needed there, because foo_id also accepts NULL as a valid value, i.e. if SELECT id FROM foo WHERE status = 'BAR' returned no results, this part of the program would fail silently.

The problem with this approach is that it causes two database roundtrips for what should be a single operation.

In a perfect world, postgresql supported assertions natively, e.g.

INSERT INTO bar (foo_id)
VALUES
(
  (
    SELECT id
    FROM foo
    WHERE nid = 'BAR'
    EXPECT 1 RESULT
  )
)

EXPECT 1 RESULT is a made up DSL.

The expectation is that EXPECT 1 RESULT would cause PostgreSQL to throw an error if that query returns anything other than 1 result.

Since PostgreSQL does not support this natively, what are the client-side solutions?

CodePudding user response：

You can use

const fooId = await pool.oneFirst(sql`
  INSERT INTO bar (foo_id)
  SELECT id
  FROM foo
  WHERE nid = 'BAR'
  RETURNING foo_id;
`);

This will insert all rows matched by the condition in foo into bar, and Slonik will throw if that was not exactly one row.

Alternatively, if you insist on using VALUES with a subquery, you can do

INSERT INTO bar (foo_id)
SELECT tmp.id
FROM (VALUES (
  SELECT id
  FROM foo
  WHERE nid = 'BAR'
)) AS tmp
WHERE tmp.id IS NOT NULL

Nothing would be inserted if no row did match the condition and your application could check that. Postgres would throw an exception if multiple rows were matched, since a subquery in an expression must return at most one row.

CodePudding user response：

That's a cool idea.

You can cause an error on extra results by putting the select in a context where only one result is expected. For example, just writing

SELECT (SELECT id from foo WHERE nid = 'bar')

will get you a decent error message on multiple results: error: more than one row returned by a subquery used as an expression.

To handle the case where nothing is returned, you could use COALESCE.