How to use sub-queries correctly inside a Postgresql query

Question

I'm having troubles resetting the sequences as automatically as possible. I'm trying to use the next query from phpPgAdmin:

SELECT SETVAL('course_subjects_seq', (SELECT MAX(subject_id) FROM course_subjects));

Somehow this query returns:

> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

pointing to the first SELECT SETVAL

The next query will give the same error:

SELECT setval("course_subjects_seq", COALESCE((SELECT MAX(subject_id) FROM course_subjects), 1))

Can anyone point me to what am I doing wrong?

I assume the double quotes in your 2nd example are a typo? To make the question unambiguous, we'd need to see the actual table definition. Assuming `subject_id` is a `serial` or `bigserial` column? (And please *always* disclose your version of Postgres.) — Erwin Brandstetter, Aug 14 '19 at 21:47

score 0 · Answer 1 · answered Aug 14 '19 at 18:43

0

Fixed by doing so:

setval function requires regclass, bigint and boolean as arguments, therefore I added the type casts:

SELECT setval('course_subjects_seq'::regclass, COALESCE((SELECT MAX(subject_id) FROM course_subjects)::bigint, 1));

::regclass

and ::bigint

answered Aug 14 '19 at 18:43

an4rei

57
1
7

The cast to `regclass` isn't really necessary. The main difference is that you used single quotes `'` to pass a string constant, not double quotes `"` which indicate an identifier – Aug 14 '19 at 18:55

Erwin Brandstetter · Answer 2 · 2019-08-14T21:41:59.343

You don't need a subquery at all here. Can be a single SELECT:

SELECT setval(pg_get_serial_sequence('course_subjects', 'subject_id')
            , COALESCE(max(subject_id) + 1, 1)
            , false)  -- not called yet
FROM   course_subjects;

Assuming subject_id is a serial column, pg_get_serial_sequence() is useful so you don't have to know the sequence name (which is an implementation detail, really).

SELECT with an aggregate function like max() always returns a single row, even if the underlying table has no rows. The value is NULL in this case, that's why you have COALESCE in there.

But if you call setval() with 1, the next sequence returned number will be 2, not 1, since that is considered to be called already. There is an overloaded variant of setval() with a 3rd, boolean parameter: is_called, which makes it possible to actually start from 1 in this case like demonstrated.

How to reset postgres' primary key sequence when it falls out of sync?

How to use sub-queries correctly inside a Postgresql query

2 Answers2