How to Merge Identical Tables in PostgreSQL?

Question

How to Merge Identical Tables in PostgreSQL forcing different serial numbers.

My scenario: first column is a bigserial number that are primary keys set to auto grow by increment 1 in a sequence. but they all start with 1,2,3,4.... in both tables.

Table: Data1

1|A
2|B
3|C

Table: Data2

1|D
2|E
3|F

Results should be:

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

Required criteria: the serial numbers should remain unique for all rows after merging.

I have two questions:

Can someone help me figure out how to create a query to merge two identical tables in PostgreSQL 8.4+ or 9+?
Is there a way within PostgreSQL commands or PgAdmin or some utility to directly merge the two tables?

Please guide me.

Marco van der Plas · Accepted Answer · 2017-11-13 20:21:50Z

1

 SELECT *
 FROM (    
    SELECT row_number() over () as rn, 
          code
    FROM ( 
      SELECT code 
      FROM table_1
      UNION 
      SELECT code
      FROM table_2
    ) as t1
 ) as t2
 ORDER BY rn

Using the above result you can create a new table:

CREATE TABLE new_table 
AS
SELECT ....

edited Nov 13, 2017 at 20:21

Marco van der Plas

14 bronze badges

answered Feb 23, 2012 at 8:47

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Huxton Over a year ago

You want UNION ALL there rather than UNION (which will eliminate duplicate "code" values)

user330315 Over a year ago

I thought about that as well, but I'm not entirely sure. It's not clear from the examples and user1227809 didn't mention anything that would indicate that duplicates should be kept.

Erwin Brandstetter · Accepted Answer · 2012-03-06 04:14:42Z

0

This way you

keep all rows (including duplicates)
keep unchanged IDs for values from the first table
keep IDs you can match to the second table

WITH x AS (
    SELECT max(id) AS x
    FROM tbl1
    ) 
SELECT id, val
FROM   tbl1
UNION  ALL
SELECT x + id, val
FROM   tbl2, x
ORDER  BY id;

You need PostgreSQL 8.4 or later for the CTE.
You could just have a look at the maximum of tbl1 and increment IDs from tbl2 by a nice round number greater than that.

If you don't care about previous IDs, get rid of duplicate values and have IDs without gaps (initially), then @a_horse'e answer is better for you.

edited Mar 6, 2012 at 4:14

answered Feb 23, 2012 at 23:48

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

How to Merge Identical Tables in PostgreSQL?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related