Message-ID: <46619503.2050509@tweakers.net>
Date: Sat, 02 Jun 2007 18:04:19 +0200
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: Hanu Kurubar <hanu.kurubar@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Append table
References: <912b58490705302036n3c528b6fq4bfaa693a9e58dd7@mail.gmail.com>
	<912b58490706020852r61d308b3m595bf410d89a113@mail.gmail.com>
In-Reply-To: <912b58490706020852r61d308b3m595bf410d89a113@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

There are two solutions:
You can insert all data from tableB in tableA using a simple insert 
select-statement like so:
INSERT INTO tabelA SELECT EmpId, EmpName FROM tabelB;

Or you can visually combine them without actually putting the records in 
a single table. That can be with a normal select-union statement or with 
a view, something like this:
SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName FROM tabelB;

You can use this query as a table-generating subquery in a FROM-clause, 
like so:

SELECT * FROM (SELECT EmpId, EmpName FROM tabelA UNION EmpID, EmpName 
FROM tabelB) as emps WHERE EmpId = 1;

Or with the view:
CREATE VIEW tabelC AS SELECT EmpId, EmpName FROM tabelA UNION EmpID, 
EmpName FROM tabelB;

And then you can use the view as if it was a normal table (altough 
inserts are not possible without applying rules to them, see the manual 
for that).

SELECT * FROM tabelC WHERE EmpId = 1;

Best regards,

Arjen

On 2-6-2007 17:52 Hanu Kurubar wrote:
> Any luck on appending two table in PostgreSQL.
> Below are two table with same schema that have different values. In this 
> case EmpID is unique value.
>  
> tabelA
> ------------
> EmpId (Int) EmpName (String)
> 1               Hanu
> 2               Alvaro
>  
>  
> tabelB
> ------------
> EmpId (Int) EmpName (String)
> 3               Michal
> 4               Tom
>  
>  
> I would be looking below output after appending tableA with tableB. Is 
> this possible in PostgreSQL?
> 
>  
> tabelA
> ------------
> EmpId (Int) EmpName (String)
> 1               Hanu
> 2               Alvaro
> 3               Michal
> 4               Tom
> 
>  
> 
> Thanks,
> Hanu
> 
>  
> On 5/30/07, *Hanu Kurubar* <hanu.kurubar@gmail.com 
> <mailto:hanu.kurubar@gmail.com>> wrote:
> 
>     Can you help me appending two table values into single table without
>     performing INSERT?
>     Note that these tables are of same schema.
>      
>     Is there any sql command is supported?
>      
>     Thanks,
>     Hanu
> 
>      
>     On 5/29/07, *Alvaro Herrera* <alvherre@commandprompt.com
>     <mailto:alvherre@commandprompt.com>> wrote:
> 
>         Michal Szymanski wrote:
>          > There is another strange thing. We have two versions of our test
>          > >>environment one with production DB copy and second
>         genereated with
>          > >>minimal data set and it is odd that update presented above
>         on copy of
>          > >>production is executing 170ms but on small DB it executing
>         6s !!!!
>          > >
>          > >How are you vacuuming the tables?
>          > >
>          > Using pgAdmin (DB is installed on my laptop) and I use this
>         tool for
>          > vaccuminh, I do not think that vaccuming can help because
>         I've tested on
>          > both database just after importing.
> 
>         I think you are misunderstanding the importance of vacuuming the
>         table.
>         Try this: on a different terminal from the one running the test,
>         run a
>         VACUUM on the updated table with vacuum_cost_delay set to 20, on an
>         infinite loop.  Keep this running while you do your update
>         test.  Vary
>         the vacuum_cost_delay and measure the average/min/max UPDATE times.
>         Also try putting a short sleep on the infinite VACUUM loop and
>         see how
>         its length affects the UPDATE times.
> 
>         One thing not clear to me is if your table is in a clean
>         state.  Before
>         running this test, do a TRUNCATE and import the data
>         again.  This will
>         get rid of any dead space that may be hurting your measurements.
> 
>         --
>         Alvaro
>         Herrera                        http://www.advogato.org/person/alvherre
>         "The Postgresql hackers have what I call a "NASA space shot"
>         mentality.
>         Quite refreshing in a world of "weekend drag racer" developers."
>         (Scott Marlowe)
> 
>         ---------------------------(end of
>         broadcast)---------------------------
>         TIP 4: Have you searched our list archives?
> 
>                       http://archives.postgresql.org
>         <http://archives.postgresql.org/>
> 
> 
> 
> 
>     -- 
>     With best regards,
>     Hanumanthappa Kurubar
>     Mobile: 98 801 800 65 
> 
> 
> 
> 
> -- 
> With best regards,
> Hanumanthappa Kurubar
> Mobile: 98 801 800 65