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?

This is impossible to answer without the actual code. Please edit your question and add the complete CREATE scripts for all tables involved and the code where you use pg_get_serial_sequence() — user330315
– user330315, Commented Feb 19, 2019 at 11:53
Maybe my issue is related to this: postgresql.org/message-id/flat/… — peter.petrov
– peter.petrov, Commented Feb 19, 2019 at 12:07
@a_horse_with_no_name No, I didn't touch it. I just inherited this stuff when I started looking at it. Problem fixed now, thanks. — peter.petrov
– peter.petrov, Commented Feb 19, 2019 at 12:17

klin · Accepted Answer · 2019-02-19 12:08:20Z

19

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)

answered Feb 19, 2019 at 12:08

klin

123k15 gold badges240 silver badges262 bronze badges

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

4 Comments

peter.petrov Over a year ago

Yeah, I just figured it out myself. I don't know how the table was created. I didn't do it myself. Other folks do it and sometimes they do sloppy stuff. I just figured out that I have to say: ALTER SEQUENCE t_name_seq OWNED BY t_name.id

peter.petrov Over a year ago

Interesting enough, none of my tools (DBeaver, pgAdmin) showed me that this particular sequence was not owned by any table column, while the other sequences were owned. Strange why the CREATE scripts of sequences don't show this info (which column owns the seq).

peter.petrov Over a year ago

Apparently they used the 2nd approach which you mentioned. Thanks a lot.

klin Over a year ago

I don't use PgAdmin4 but PgAdmin III shows the difference in CREATE TABLE... script, though actually there is no info in CREATE SEQUENCE ....

coterobarros · Accepted Answer · 2020-08-19 15:33:40Z

Chances are this table of yours is created with a like clause, i.e., out of a table template.

I use table templates a lot, to factorize some common columns, and I have found that if you include the primary key into the template then only one sequence generator is used for all of the derived tables.

This has two drawbacks:

maybe this is not the expected behaviour as you planned an individual sequence for each derived table.
when you run pg_get_serial_sequence() with the name of the derived table you get a null result.

Here you have a complete example, where table1 and table2 are derived from a table template named template_basic, including a sequence on id, and table3 is a normal table.

drop table if exists table3 cascade;
drop table if exists table2 cascade;
drop table if exists table1 cascade;
drop table if exists template_basic cascade;

-- The template table
create table template_basic (
  id          serial,
  title       varchar,
  created_dt  timestamp,
  
  primary key (id)
);

-- A table derived from template_basic
create table table1 (
  like template_basic including all,
  
  phone_number  varchar,
  city          varchar,
  country       varchar
);

-- Another table derived from template_basic
create table table2 (
  like template_basic including all,
  
  first_name  varchar,
  family_name varchar
);

-- A normal table, but with an equivalent structure to table1 and table2
create table table3 (
  id            serial,
  title         varchar,
  created_dt    timestamp,
  
  phone_number  varchar,
  city          varchar,
  country       varchar,
  
  primary key (id)
);

-- Populate

insert into table1 default values;
insert into table1 default values;
insert into table1 default values;

insert into table2 default values;
insert into table2 default values;
insert into table2 default values;

insert into table3 default values;
insert into table3 default values;
insert into table3 default values;

Now, if you inspect these three tables you will see how table2.id share the same sequence as table1.id. Also, table3.id has its own sequence.

select id from table1;

id|
--|
 1|
 2|
 3|
 
select id from table2;

id|
--|
 4|
 5|
 6|
 
select id from table3;

id|
--|
 1|
 2|
 3|

When you retrieve the name of the sequence of table1.id or table2.id you get a null, as describe before.

select  pg_get_serial_sequence ('table1', 'id'); 

pg_get_serial_sequence|
----------------------|
                      |
select  pg_get_serial_sequence ('table2', 'id');

pg_get_serial_sequence|
----------------------|

select  pg_get_serial_sequence ('table3', 'id');

pg_get_serial_sequence|
----------------------|
public.table3_id_seq  |

To get the sequence of those two tables you have to invoke the template table name.

select  pg_get_serial_sequence ('template_basic', 'id');
                      
pg_get_serial_sequence      |
----------------------------|
public.template_basic_id_seq|

Collectives™ on Stack Overflow

pg_get_serial_sequence returns nothing (empty string)

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related