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
– Erwin Brandstetter, Commented Aug 14, 2019 at 21:47

an4rei · Accepted Answer · 2019-08-14 18:43:43Z

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, 2019 at 18:43

an4rei

571 gold badge1 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

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

Erwin Brandstetter · Accepted Answer · 2019-08-14 21:41:59Z

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?

Collectives™ on Stack Overflow

How to use sub-queries correctly inside a Postgresql query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related