Re: Copy table structure

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.Elphick@lfix.co.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