Thread: Duplicated tables of certain columns

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
carriedover. 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
Iwould 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

On Feb 21, 2011, at 1:39 PM, Yan Cheng CHEOK wrote:

> INSERT INTO backup_table SELECT * FROM unit_11 WHERE status = 1;



You can try something like,
INSERT INTO backup_table select unit_id, 99, status, value from unit_11 where status=1;


Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Cell: +91-932-568-2279
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

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
>
>
>      
>