Re: Append table
От | Arjen van der Meijden |
---|---|
Тема | Re: Append table |
Дата | |
Msg-id | 46619503.2050509@tweakers.net обсуждение исходный текст |
Ответ на | Re: Append table ("Hanu Kurubar" <hanu.kurubar@gmail.com>) |
Ответы |
Re: Append table
(Gregory Stark <stark@enterprisedb.com>)
Re: Append table ("Hanu Kurubar" <hanu.kurubar@gmail.com>) Re: Append table (Chander Ganesan <chander@otg-nc.com>) |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: