From: | Yan Cheng CHEOK <yccheok(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Duplicated tables of certain columns |
Date: | 2011-02-21 08:19:32 |
Message-ID: | 605889.27637.qm@web65715.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2011-02-21 08:37:15 | Re: Worst case scenario of a compromised non super-user PostgreSQL user account |
Previous Message | Vibhor Kumar | 2011-02-21 08:19:05 | Re: Schema Archive cant find table |