From: | Yan Cheng CHEOK <yccheok(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Duplicated tables of certain columns |
Date: | 2011-02-21 08:09:07 |
Message-ID: | 90268.22784.qm@web65715.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I try to duplicate a tables of certain columns by using
CREATE TABLE backup_table AS SELECT * FROM unit_11 WHERE status = 1;
I realize the above command will duplicate content of table unit_11 to backup_table. However, the index is not being carried over. Hence, I change my command to
create table backup_table ( like unit_11 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES );
INSERT INTO backup_table SELECT * FROM unit_11 WHERE status = 1;
It works fine with the following output
Table unit_11
=============
unit_id [PK] fk_lot_id status value
1 11 1 100
2 11 1 101
3 11 0 102
Table backup_table
==================
unit_id [PK] fk_lot_id status value
1 11 1 100
2 11 1 101
However, this is not what I want. I wish to have all columns being duplicated over except for column "fk_lot_id", where I would like to define my own "fk_lot_id". My final wished table is as follow.
Table backup_table
==================
unit_id [PK] fk_lot_id status value
1 99 1 100
2 99 1 101
May I know how I can achieve these by using combination of SQL command?
Thanks!
Thanks and Regards
Yan Cheng CHEOK
From | Date | Subject | |
---|---|---|---|
Next Message | Vibhor Kumar | 2011-02-21 08:16:26 | Re: Duplicated tables of certain columns |
Previous Message | Allan Kamau | 2011-02-21 07:44:05 | Worst case scenario of a compromised non super-user PostgreSQL user account |