Modifying tables in PostgreSQL

Author: Nediam <javier@nediam.com.mx>
Publication date: 2005-12-05

Note: The examples shown here were tested on PostgreSQL 8.0.1.

From the following example table:

db_example=> \d tbl_users

Table "public.tbl_users"
       Column        |          Type           |     Modifiers
---------------------+-------------------------+--------------------
 login               | character varying(10)   | not null
 pass                | character varying(34)   | not null
 nome                | character varying(40)   | not null
 lastname            | character varying(30)   | not null
 sex                 | smallint                |
 email               | character varying(35)   | not null
 id_country          | character(2)            | not null
 state               | character varying(40)   | not null
 city                | character varying(40)   | not null
 id_status           | smallint                | not null default 1
 registration_date   | character varying(19)   | not null 
 observations        | character varying(200)  | not null
Indexes:
    "tbl_users_pkey" PRIMARY KEY, btree (login)
Foreign-key constraints:
    "tbl_users_id_country_fkey" FOREIGN KEY (id_country) REFERENCES tbl_countries(id_country)

Next, examples of SQL queries are shown for:

• Add a column:
  db_example=> ALTER TABLE tbl_users ADD COLUMN id_state CHAR(2);
• Drop a column:
  db_example=> ALTER TABLE tbl_users DROP COLUMN state;
• Rename a column:
  db_example=> ALTER TABLE tbl_users RENAME COLUMN login TO id_user;
• Change the data type of a column:
  db_example=> ALTER TABLE tbl_users ALTER COLUMN email TYPE VARCHAR(40);
• Do not allow that a column accepts null values:
  db_example=> ALTER TABLE tbl_users ALTER COLUMN sex SET NOT NULL;
• Remove the null values restriction to a column:
  db_example=> ALTER TABLE tbl_users ALTER COLUMN city DROP NOT NULL;
• Assign a default value to a column:
  db_example=> ALTER TABLE tbl_users ALTER COLUMN id_country SET DEFAULT 'MX';
• Drop its default value to a column:
  db_example=> ALTER TABLE tbl_users ALTER COLUMN id_status DROP DEFAULT;
• Add a foreign key:
  db_example=> ALTER TABLE tbl_users ADD FOREIGN KEY(id_status) REFERENCES tbl_status(id_status);
• Remove a constraint:
  db_example=> ALTER TABLE tbl_users DROP CONSTRAINT tbl_users_id_country_fkey;

For more information, check the PostgreSQL documentation:
http://www.postgresql.org/docs/


The latest version of this document is available at: http://nediam.com.mx/en/tips/modify_tables_pgsql.php

<< 0 comments >>