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?
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:
pg_attrdef, where the default values of attributes are stored.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)
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.
