From: Arjen van der Meijden <acmmailing@tweakers.net>
To: Hanu Kurubar <hanu.kurubar@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Append table
Date: Sat, 02 Jun 2007 18:04:19 +0200
Message-ID: <46619503.2050509@tweakers.net> (raw)
In-Reply-To: <912b58490706020852r61d308b3m595bf410d89a113@mail.gmail.com>
References: <912b58490705302036n3c528b6fq4bfaa693a9e58dd7@mail.gmail.com>
<912b58490706020852r61d308b3m595bf410d89a113@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: acmmailing@tweakers.net, hanu.kurubar@gmail.com
Subject: Re: Append table
In-Reply-To: <46619503.2050509@tweakers.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox