Re: Append table - Mailing list pgsql-performance

Arjen van der Meijden wrote:
> 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;
Since they both have the same schema, you could also combine them by
creating a parent table and making both tables children.  Check out
PostgreSQL's inheritance features.  To make an existing table a child
you'll need to be using PostgreSQL 8.2 or newer.

create table emp_rollup (like tabelA);
alter table tabelA inherits emp_rollup;
alter table tabelB inherits emp_rollup;

Now issue your queries against emp_rollup...  You could also just make
tabelB a child of tabelA:

alter table tabelB inherits tabelA;

But that would mean that if you wanted to query only tabelA you'd have
to modify your query syntax.

select * from ONLY tabelA;

Would only retrieve records from tabelA ...


You could also allow PostgreSQL to limit its index usage based on the
EmpID field by defining some table constraints and enabling constraint
exclusion.
>
> 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
>>


--
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Expert PostgreSQL Training - http://test.otg-nc.com/training-courses/coursedetail.php?courseid=40&cat_id=8