Re: Duplicated tables of certain columns

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(at)yahoo(dot)com> wrote:

> From: Yan Cheng CHEOK <yccheok(at)yahoo(dot)com>
> Subject: Duplicated tables of certain columns
> To: pgsql-general(at)postgresql(dot)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
>
>
>      
>