PostgreSQL a table with no primary key in the database adds a primary key

There are multiple tables in the PostgreSQL database test environment that do not have a PRIMARY KEY constraint and only one serial field. Now you need to add to the table with no primary key, the field ID of the serial type.

First, how do I find out which tables in the PostgreSQL database do not have primary keys? Let's look at the Pg_class table, which has a relhaspkey field, and if T shows a primary key, F is not a primary key. For example, the following SQL.

SELECT n.nspname as "Schema", C.relname as "Table Name", C.relhaspkey as "has PK"
From
Pg_catalog.pg_class C
JOIN
Pg_namespace N
On (
C.relnamespace = n.oid
and N.nspname not in (' Information_schema ', ' Pg_catalog ')
and c.relkind= ' R '
)
WHERE C.relhaspkey = ' F '
Order by C.relhaspkey, C.relname
;
These tables are then added to the primary key constraint. The SQL for deleting and adding primary keys is as follows:

ALTER TABLE server DROP constraint Server_pkey;
ALTER TABLE server add primary key (ID);
After the primary key addition is complete, you can view it through \d.

zhangnq=# \d Server
Table "Public.server"
Column | Type | Modifiers
--------+---------------+------------------------------------------------------
ID | Integer | NOT NULL default Nextval (' Server_int_seq ':: Regclass)
IP | Character (50) |
Indexes:
"Server_pkey" PRIMARY KEY, Btree (ID)
The last is to write this idea into the script, run the script batch Add. In the script, the failed tables are placed in the Error.log file.

Script:

#!/bin/bash
Export Path=/opt/postgresql/93/bin: $PATH
Export Pgdata=/data/pgsql
Export PGHOME=/OPT/POSTGRESQL/93
Export pgport=5432
Dbname=$1
if [! $dbname];then
echo "Please enter the database name."
Exit 1
Fi
Psql-c "\dt"-D $dbname >/dev/null
If [$?-ne 0];then
Exit 1
Fi
Error_log= "Error.log"
echo "" > $error _log
Sql= ' Cat << EOF
SELECT n.nspname as "Schema", C.relname as "Table Name"
From
Pg_catalog.pg_class C
JOIN
Pg_namespace N
On (
C.relnamespace = n.oid
and N.nspname not in (' Information_schema ', ' Pg_catalog ')
and c.relkind= ' R '
)
WHERE C.relhaspkey = ' F '
Order by C.relhaspkey, C.relname
;
EOF '
schemas= ' psql-t-a-c "$sql"-D $dbname |cut-d "|"-F 1 '
tables= ' psql-t-a-c "$sql"-D $dbname |cut-d "|"-F 1 '
For res in ' psql-t-a-c ' $sql '-D $dbname '
Todo
Schema= ' echo $res |cut-d ' | "-F 1"
Table= ' echo $res |cut-d ' | "-F 2"
Tablename= ' echo ' $schema. $table "'
PSQL-E-C "ALTER TABLE $tablename add primary key (ID)"-D $dbname
If [$?-ne 0];then
echo "$dbname: Add primary key to $tablename error." >> $error _log
Fi
Done
The next encounter problem, in the test when found that if the primary key drop off after the Pg_class.relhaspkey value or T, but with \d view does not have a primary key. The solution is to manually vacuum the table, that is, vacuum server.

zhangnq=# Select Relname,relhaspkey from Pg_class where relname= ' server ';
 relname | Relhaspkey
---------+------------
 server | t
(1 row)
zhangnq=# ALTER TABLE Ser ver drop constraint Server_pkey;
ALTER TABLE
zhangnq=# Select Relname,relhaspkey from Pg_class where relname= ' server ';
 relname | relhas Pkey
---------+------------
 server | t
(1 row)
zhangnq=# vacuum server;
VACUUM
zhangnq=# Select Relname,relhaspkey from Pg_class where relname= ' server ';
 relname | Relhaspkey
---------+------------
 server | f
(1 row)
zhangnq=# ALTER TABLE server add primary key (ID);
ALTER TABLE
zhangnq=# Select Relname,relhaspkey from Pg_class where relname= ' server ';
 relname | relhaspkey
---------+------------
 server | t
(1 row)
To view the description of Pg_class, it turns out that Pg_class is automatically modified only when the status is changed from False to Ture. This design can improve concurrency.

Several of the Boolean flags in Pg_class are maintained Lazily:they are guaranteed to is true if that ' s the correct St Ate, but may isn't reset to false immediately when the condition is no longer true. For example, Relhasindex are set by CREATE index, but it are never cleared by DROP INDEX. Instead, VACUUM clears relhasindex if it finds the table has no indexes. This arrangement avoids race conditions and improves concurrency.