Safely rename tables using serial primary key columns
serial
is not an actual data type. The manual states:
The data types
smallserial
,serial
andbigserial
are not true types,
but merely a notational convenience for creating unique identifier columns
The pseudo data type is resolved doing all of this:
create a sequence named
tablename_colname_seq
create the column with type
integer
(orint2
/int8
respectively forsmallserial
/bigserial
)make the column
NOT NULL DEFAULT nextval('tablename_colname_seq')
make the column own the sequence, so that it gets dropped with it automatically
The system does not know whether you did all this by hand or by way of the pseudo data type serial
. pgAdmin checks on the listed features and if all are met, the reverse engineered DDL script is simplified with the matching serial
type. If one of the features is not met, this simplification does not take place. That is something pgAdmin does. For the underlying catalog tables it's all the same. There is no serial
type as such.
There is no way to automatically rename owned sequences. You can run:
ALTER SEQUENCE ... RENAME TO ...
like you did. The system itself doesn't care about the name. The column DEFAULT
stores an OID
('foo_pkey_seq'::regclass
), you can change the name of the sequence without breaking that - the OID stays the same. The same goes for foreign keys and similar references inside the database.
The implicit index for the primary key is bound to the name of the PK constraint, which will not change if you change the name of the table. In Postgres 9.2 or later you can use
ALTER TABLE ... RENAME CONSTRAINT ..
to rectify that, too.
There can also be indexes named in reference to the table name. Similar procedure:
ALTER INDEX .. RENAME TO ..
You can have all kinds of informal references to the table name. The system cannot forcibly rename objects that can be named anything you like. And it doesn't care.
Of course you don't want to invalidate SQL code that references those names. Obviously, you don't want to change names while application logic references them. Normally this wouldn't be a problem for names of indexes, sequences or constraints, since those are not normally referenced by name.
Postgres also acquires a lock on objects before renaming them. So if there are concurrent transaction open that have any kind of lock on objects in question, your RENAME
operation is stalled until those transactions commit or roll back.
System catalogs and OIDs
The database schema is stored in tables of the system catalog in the system schema pg_catalog
. All details in the manual here. If you don't know exactly what you are doing, you shouldn't be messing with those tables at all. One false move and you can break your database. Use the DDL commands Postgres provides.
For some of the most important tables Postgres provides object identifier types and type casts to get the name for the OID and vice versa quickly. Like:
SELECT 'foo_pkey_seq'::regclass
If the schema name is in the search_path
and the table name is unique, that gives you the same as:
SELECT oid FROM pg_class WHERE relname = 'foo_pkey_seq';
The primary key of most catalog tables is oid
and internally, most references use OIDs.
PostgreSQL, reconfigure existing table, changing primary key to type=serial
CREATE SEQUENCE kalksad1_kalk_id_seq;
ALTER TABLE kalksad1
ALTER COLUMN kalk_id SET DEFAULT nextval('kalksad1_kalk_id_seq' )
;
ALTER SEQUENCE kalksad1_kalk_id_seq OWNED BY kalksad1.kalk_id;
WITH mx AS (SELECT max(kalk_id) AS mx FROM kalksad1)
SELECT setval( 'kalksad1_kalk_id_seq' , mx.mx)
FROM mx
;
-- Test it ...
INSERT INTO kalksad1(brkalk, brred, description) VALUES (42, 666, 'Tralala' );
SELECT * FROM kalksad1;
Result:
NOTICE: table "kalksad1" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 12
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
setval
--------
47
(1 row)
INSERT 0 1
kalk_id | brkalk | brred | description
---------+--------+-------+---------------------------
12 | 2 | 5 | text index 12 doc 2 row 5
26 | 2 | 1 | text index 26 doc 2 row 1
30 | 2 | 2 | text index 30 doc 2 row 2
32 | 4 | 1 | text index 32 doc 4 row 1
36 | 1 | 1 | text index 36 doc 1 row 1
37 | 1 | 2 | text index 37 doc 1 row 2
38 | 5 | 1 | text index 38 doc 5 row 1
39 | 5 | 2 | text index 39 doc 5 row 2
42 | 2 | 3 | text index 42 doc 2 row 3
43 | 2 | 4 | text index 43 doc 2 row 4
46 | 3 | 1 | text index 46 doc 3 row 1
47 | 3 | 2 | text index 47 doc 3 row 2
48 | 42 | 666 | Tralala
(13 rows)
PostgreSQL rename table - cascade renaming to objects like indices, constraints that contain table name
There is no technical relation between the names of indices, sequences, etc and any table names. So there is no reliable way to rename them automatically.
You can write a script that goes through the schema and renames things by search & replace, but that carries the risk of hitting false positives.
Is using SERIAL fine for the primary key when creating a table of all the comments made by users?
Since the actual value of the comment id does not interest you (just the fact that it's there and it's unique), serial
is a good choice for such a column. Note that in modern PostgreSQL databases (since 7.3), creating a serial
does not automatically mean it will have a unique constraint, so you'd have to handle that explicitly. E.g.:
CREATE TABLE comments (
comment_id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id), -- You should probably also index it
comment VARCHAR(200) -- Or any other reasonable size
)
EDIT:
To answer the question in the comments, a similar behavior could be created for a UUID
column by giving it a default value of a newly generated UUID
.
First, you'd have to install the postgres-contrib` package (if you don't have it installed yet). E.g., On Red Hat based linuxes, you could run (as root):
$ dnf install postgresql-contrib
Then, from a privileged user, you need to create the extension:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
This will create a generate_uuid_v1
function you could use:
CREATE TABLE comments (
comment_id UUID DEFAULT UUID_GENERATE_V1() PRIMARY KEY,
user_id INT REFERENCES users(id), -- You should probably also index it
comment VARCHAR(200) -- Or any other reasonable size
)
How can I atomically swap table names and its references in postgres without any issues?
The true identity of an object is its object ID, a number that is used to refer to the object in foreign key constraints an other internal database matters (with the notable exception of most function bodies). Renaming an object does not change its identity. You have to drop and re-create foreign key constraints.
Data type smallserial in Postgres not highlighted in Visual Studio Code
That's probably because smallserial
(like serial
or bigserial
) are not actual data types. See:
- How to convert primary key from integer to serial?
- Safely rename tables using serial primary key columns
In Postgres 10 or later, consider an IDENTITY
column instead. See:
- Auto increment table column
If serial
and bigserial
are highlighted, but smallserial
is not, then that's inconsistent and should be fixed.
Related Topics
How to Return Multiple Values in One Column (T-Sql)
Custom Serial/Autoincrement Per Group of Values
Hive Select Count(*) Non Null Returns Higher Value Than Select Count(*)
How to Find Gaps in Sequential Numbering in MySQL
Possible to Perform Cross-Database Queries With Postgresql
Doing a Where .. in Subquery in Doctrine 2
Add Foreign Key Relationship Between Two Databases
How to Roll Back Create Table and Alter Table Statements in Major SQL Databases
Performing SQL Queries on an Excel Table Within a Workbook With Vba Macro
Generate a Resultset of Incrementing Dates in Tsql
Why No Windowed Functions in Where Clauses
Creating a "Numbers Table" in MySQL
Create a Pivot Table With Postgresql
What Are Best Practices For Multi-Language Database Design
How to Dump the Data of Some Sqlite3 Tables
Why Is There No Product Aggregate Function in SQL
How to Fetch the Row Count for All Tables in a SQL Server Database