Skip site navigation (1) Skip section navigation (2)

Re: Copy table structure

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Chris Boget <chris(at)wild(dot)net>
Cc: Peter Childs <blue(dot)dragon(at)blueyonder(dot)co(dot)uk>,pgsql-novice(at)postgresql(dot)org
Subject: Re: Copy table structure
Date: 2003-10-08 14:02:59
Message-ID: 1065621779.30038.102.camel@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
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 


In response to

pgsql-novice by date

Next:From: Jeff EckermannDate: 2003-10-08 14:09:52
Subject: Re: perlsub
Previous:From: Chris BogetDate: 2003-10-08 13:31:14
Subject: Re: Copy table structure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group