X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2C1D4D85AA for ; Wed, 31 Aug 2005 10:19:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 42034-04 for ; Wed, 31 Aug 2005 13:19:00 +0000 (GMT) Received: from biglumber.com (biglumber.com [207.228.252.42]) by svr1.postgresql.org (Postfix) with SMTP id 1A8BAD8568 for ; Wed, 31 Aug 2005 10:18:56 -0300 (ADT) Received: (qmail 17280 invoked from network); 31 Aug 2005 13:19:00 -0000 Received: from unknown (HELO localhost) (207.228.252.42) by 0 with SMTP; 31 Aug 2005 13:19:00 -0000 From: "Greg Sabino Mullane" To: pgsql-sql@postgresql.org Subject: Re: How do I copy part of table from db1 to db2 (and rename the columns)? X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Request-PGP: http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 In-Reply-To: <1125482430.9456.6.camel@Panoramix> Date: Wed, 31 Aug 2005 13:19:00 -0000 X-Mailer: JoyMail 1.48 Message-ID: <4dbd178fd48b28d5ad56837a0d87fc2a@biglumber.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.097 required=5 tests=[AWL=0.097] X-Spam-Level: X-Archive-Number: 200508/303 X-Sequence-Number: 22531 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I want to copy several columns of a source table from db1 to db2, and > create the target table and rename the columns in the process. Ignoring the db part for now, you can do the first part of your request like this: CREATE TABLE mytable2 AS SELECT mycol AS newcolname, id, foo AS foo2 FROM mytable WHERE id > 100; Then pg_dump -t mytable2 and pg_restore, as the rest of this thread indicates. Note that you'll lose any triggers, indexes, etc. so only use this if all you really want is the table. Otherwise, it's probably easiest to pg_dump the whole database and then drop/rename columns in the new database. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200508310915 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEARECAAYFAkMVrhIACgkQvJuQZxSWSsg3ZwCgs5kG/5P+RFr315+v3ia0g+Tr bAEAoKHfRhJYynxVqyo/U06WaSeDXh3s =hZIK -----END PGP SIGNATURE-----