Where does Postgres store table sequence information

user_6613831 0
Viewed 1232

I have a model in Postgres – say Student – which has primary key id serial, which means there is another table student_id_seq which keeps track of the sequence information for student.

If I change the table name of student to, say, StudentYear, will there be any problem? Would I have to do anything to make sure renaming the table is not going to crash anything?

rename database-design postgresql
2 Answers

You can rename the table or the sequence however you want, and everything will keep working.

The relationship between a table column and its sequence is stored in two places:

  1. In pg_attrdef, where the default values of attributes are stored.
  2. In pg_depend where dependencies between objects are tracked.

All objects and columns are internally referenced by their object IDs or attribute numbers, so renaming something is not a problem.

An example:

CREATE TABLE serialdemo (id serial);

SELECT oid FROM pg_class WHERE relname = 'serialdemo';

  oid  
-------
 69427
(1 row)

SELECT attnum FROM pg_attribute WHERE attrelid = 69427 AND attname = 'id';

 attnum 
--------
      1
(1 row)

The dependent objects:

SELECT classid::regclass, objid, deptype
FROM pg_depend
WHERE refobjid = 69427 AND refobjsubid = 1;

  classid   | objid | deptype 
------------+-------+---------
 pg_attrdef | 69430 | a
 pg_class   | 69425 | a
(2 rows)

One of these dependent objects is the column default definition:

SELECT adbin, pg_get_expr(adbin, adrelid) AS adsrc
FROM pg_attrdef WHERE oid = 69430;

-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
adbin | {FUNCEXPR :funcid 480 :funcresulttype 23 :funcretset false :funcvariadic false :funcformat 2 :funccollid 0 :inputcollid 0 :args ({FUNCEXPR :funcid 1574 :funcresulttype 20 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({CONST :consttype 2205 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location -1 :constvalue 4 [ 49 15 1 0 0 0 0 0 ]}) :location -1}) :location -1}
adsrc | nextval('serialdemo_id_seq'::regclass)

adsrc is just for the human eye. adbin will not change when the sequence is renamed, although everything keeps working.

The relevant reference to the sequence is :constvalue 4 [ 49 15 1 0 0 0 0 0 ]. This is a 4-byte unsigned integer, the object id (1 × 256² + 15 × 256 + 49 = 69425).

The other dependent object is the sequence:

SELECT relname, relkind FROM pg_class WHERE oid = 69425;

      relname      | relkind 
-------------------+---------
 serialdemo_id_seq | S
(1 row)
user_64643080

if change the table name of student to say StudentYear will there be any problem?

Nope. Column of table related with this sequence. If i am not mistake like following:

nextval('student_id_seq'::regclass)

So you don't need to care about renaming tables or columns.

user_104908570
Related