Question 114809 in PostgreSQL Question posted in
The official documentation can be found here.

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

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, 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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search