Sorry. The table visualize display doesn't look right in the message, as I am using tab instead of space. I fix them. 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 (Current) ================== unit_id [PK] fk_lot_id status value 1 11 1 100 2 11 1 101 Table backup_table (What I wish to have) ================== unit_id [PK] fk_lot_id status value 1 99 1 100 2 99 1 101 Thanks and Regards Yan Cheng CHEOK --- On Mon, 2/21/11, Yan Cheng CHEOK <yccheok@xxxxxxxxx> wrote: > From: Yan Cheng CHEOK <yccheok@xxxxxxxxx> > Subject: Duplicated tables of certain columns > To: pgsql-general@xxxxxxxxxxxxxx > Date: Monday, February 21, 2011, 4:09 PM > 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 > > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
