#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    19
    Rep Power
    0

    Merging Identical Tables


    Can someone help me figure out how to create a query to merge two identicle tables in PostgreSQL?

    For Example:

    Table: Data1
    1|A
    2|B
    3|C

    Table: Data2
    4|D
    5|E
    6|F

    Results should be:

    1|A
    2|B
    3|C
    4|D
    5|E
    6|F

    I tried the following query and found that it was extremely slow when there are more than 100,000 records in each table.

    SELECT * FROM data1 NATURAL FULL JOIN data2 WHERE column = 'A'
  2. #2
  3. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Try a UNION ALL if you know the values are distinct (much quicker than UNION as UNION checks for duplicates).


    SELECT * FROM "Data1" UNION ALL SELECT * FROM "Data2"

    HTH,
    -b
    PostgreSQL, it's what's for dinner...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    19
    Rep Power
    0
    It works! Thanks.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    19
    Rep Power
    0
    Is there a way to do a Group By with a Union
  8. #5
  9. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    What you trying to do? If you have duplicates and want to just have distinct values, just use UNION instead of UNION ALL.
    PostgreSQL, it's what's for dinner...

IMN logo majestic logo threadwatch logo seochat tools logo