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@yahoo.com> wrote:
> From: Yan Cheng CHEOK <yccheok@yahoo.com>
> Subject: Duplicated tables of certain columns
> To: pgsql-general@postgresql.org
> 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
>
>
>
>
