Re: Copy table structure

Hi all,

How do I copy only the table structure from one tabe to another.

Table A has some data but I just want to copy the number of columns and the
column names from this table and create a table B. What is the best way of
doing this ?

Thanks in advance,
-ansh

CREATE TABLE table2 AS SELECT * FROM table1 WHERE 1=2;

(This creates the structure only -- no data will be transferred because
1 will never equal 2). :)

Louise

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Anshuman Kanwar
Sent: Saturday, October 04, 2003 1:22 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Copy table structure

Hi all,

How do I copy only the table structure from one tabe to another.

Table A has some data but I just want to copy the number of columns and
the
column names from this table and create a table B. What is the best way
of
doing this ?

Thanks in advance,
-ansh

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On 4 Oct 2003 at 12:22, Anshuman Kanwar wrote:

here's one way:

mfx=# begin; create table NEWTABLE as select * from OLDTABLE; delete from
NEWTABLE; end;

Hope this helps

Paul Butler

> Hi all,
>
> How do I copy only the table structure from one tabe to another.
>
> Table A has some data but I just want to copy the number of columns and the
> column names from this table and create a table B. What is the best way of
> doing this ?
>
> Thanks in advance,
> -ansh
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

On Wed, 8 Oct 2003 paul(at)entropia(dot)co(dot)uk wrote:

> On 4 Oct 2003 at 12:22, Anshuman Kanwar wrote:
>
> here's one way:
>
> mfx=# begin; create table NEWTABLE as select * from OLDTABLE; delete from
> NEWTABLE; end;

Whats wrong with

CREATE TABLE newtable AS SELECT * FROM oldtable WHERE false;

The first method will mean having to vacuum the table after putting loads
of data in and then removing it. Also WHERE false should be faster
especially if oldtable is full of data.
Using either method you will only get the column types not the
constraints, primary keys, indexes, defaults, not null, etc. Which I guess
is what you really want :(

Peter Childs

>
> Hope this helps
>
> Paul Butler
>
> > Hi all,
> >
> > How do I copy only the table structure from one tabe to another.
> >
> > Table A has some data but I just want to copy the number of columns and the
> > column names from this table and create a table B. What is the best way of
> > doing this ?
> >
> > Thanks in advance,
> > -ansh
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

noob alert.

> SELECT * FROM oldtable WHERE false;

What exactly is this doing? What is the 'WHERE false' doing for the
query?

Chris

On Wed, 2003-10-08 at 14:31, Chris Boget wrote:
> noob alert.
>
> > SELECT * FROM oldtable WHERE false;
>
> What exactly is this doing? What is the 'WHERE false' doing for the
> query?

"WHERE false" ensures that no rows are selected. When combined with
SELECT INTO (as I believe the original message suggested) the end result
is to create a new table with the same columns as oldtable but with no
rows:

junk=# select * from xxx;
id | xx
----+--------
1 | ????????????
(1 row)

junk=# select * into zzz FROM xxx WHERE false;
SELECT
junk=# select * from zzz;
id | xx
----+----
(0 rows)

But note that the table structure is not exactly the same:

junk=# \d xxx
Table "public.xxx"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('public.xxx_id_seq'::text)
xx | text | not null
Indexes:
"xxx_pkey" primary key, btree (id)

junk=# \d zzz
Table "public.zzz"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
xx | text |

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Let no man say when he is tempted, I am tempted of
God; for God cannot be tempted with evil, neither
tempteth he any man; But every man is tempted, when he
is drawn away of his own lust, and enticed."
James 1:13,14