pg_get_serial_sequence returns nothing (empty string)

Question

I have many tables with auto-inc PKs, and for most of them pg_get_serial_sequence returns (as I would expect) the sequence name behind the auto-inc i.e. SERIAL PK. But for one table pg_get_serial_sequence returns just an empty string?!

Now... I generated CREATE scripts for these tables but I don't see any principle difference between the create scripts of these tables (those for which pg_get_serial_sequence works OK, and the table for which it does not work OK i.e. for which it returns an empty string).

Both have SERIAL PK columns backed by some sequences. So the situation seems identical to me. Yet for one of my tables pg_get_serial_sequence does not return the name of that backing sequence, it just returns empty string.

What could this particular table be missing?

Do I need to make something special to set a given sequence as a SERIAL sequence?!

If not, what should I be looking for? What am I missing?

Answer 1

This depends on how the table has been created. When you use serial, the sequence is created and associated with the table automatically:

create table my_table_auto(id serial);
select pg_get_serial_sequence('my_table_auto', 'id');

   pg_get_serial_sequence    
-----------------------------
 public.my_table_auto_id_seq
(1 row) 

When you create a sequence manually, it is not associated with the table:

create sequence my_sequence;
create table my_table_manually(id int default nextval('my_sequence'));
select pg_get_serial_sequence('my_table_manually', 'id');

 pg_get_serial_sequence 
------------------------

(1 row)

You can alter a sequence to associate it with a table:

alter sequence my_sequence owned by my_table_manually.id;
select pg_get_serial_sequence('my_table_manually', 'id');

 pg_get_serial_sequence 
------------------------
 public.my_sequence
(1 row)