PostgreSQL Modifying Tables

1. Adding Column

ALTER TABLE products ADD COLUMN description text;
ALTER TABLE products ADD COLUMN description text CHECK (description ”);

2. Removing a Column
ALTER TABLE products DROP COLUMN description;
ALTER TABLE products DROP COLUMN description CASCADE;

3. Adding a Constraint
ALTER TABLE products ADD CHECK (name ”);
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

4. Removing a Constraint
ALTER TABLE products DROP CONSTRAINT some_name;
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

5. Changing a Column’s Default Value
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

6. Changing a Column’s Data Type
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
ALTER TABLE ordering ALTER COLUMN ordering_id TYPE integer USING ordering_id::integer

7. Renaming a Column
ALTER TABLE products RENAME COLUMN product_no TO product_number;

8. Renaming a TableRenaming a Table
ALTER TABLE products RENAME TO items;

Source: http://www.postgresql.org/docs/8.1/static/ddl-alter.html
source

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s