Postgresql: Select unique rows from two tables

Question

I have this two tables with values. I need to combine all unique values to 1 table. So the result must be:

reffnum   leftb  rightb desc date
tes1   1  0    Tes 1    14/10/2016
tes 1  10 0    Tes siji    14/10/2016
tes2   0  12   Tes nomor 2 14/10/2016
tes 3  0  1002 Data baru  15/10/2016
tes1   0  11   Tes 1 baru 15/10/2016
tes1   0  123  Tes 123    15/10/2016

Please help, thanks in advance

Table t1:

reffnum leftb   rightb  desc    timestamp
tes1    1   0   Tes 1   2016-10-12 13:47:06.945581
tes1    1   0   Tes siji    2016-10-12 13:47:06.921685
tes 1   10  0   Tes siji    2016-10-03 14:55:32.126814
tes2    0   12  Tes nomor 2 2016-10-03 14:55:32.11081
tes 3   0   1002    Data baru   2016-10-03 14:55:32.094884
tes1    0   11  Tes 1 baru  2016-10-03 14:55:32.078833

And this t2:

reffnum leftb righb desc date
tes1    1   0   Tes 1   2016-10-03 14:49:15.817506
tes1    1   0   Tes siji    2016-10-03 14:33:40.285849
tes 1   10  0   Tes siji    2016-10-03 14:33:40.269887
tes2    0   12  Tes nomor 2 2016-10-03 14:30:57.376459
tes1    0   123 Tes 123 2016-10-03 14:33:40.285849
tes2    0   12  Tes no2 2016-10-03 14:33:40.269887

Edited:

This is the closest I can do:

I should find unique values in t2 that not in t1: select * from t2 except select * from t1
Then I insert values in no. 1 to t1

But now, the problem is, query in no. 1 throws an error:

[Err] ERROR: EXCEPT types smallint and timestamp without time zone cannot be matched

So, I find a way to find duplicates first: wiki.postgresql.org/wiki/Deleting_duplicates and then I do some work-around with excel to left one original row. So, I could add that unique datas to combine with table t1. — bandungeuy
– bandungeuy, Commented Oct 13, 2016 at 22:12

Mureinik · Accepted Answer · 2016-10-13 18:53:48Z

4

The union operator removes duplicates, so you can use a pretty straight-forward query:

SELECT * FROM table1
UNION
SELECT * FROM table2

answered Oct 13, 2016 at 18:53

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

4 Comments

bandungeuy Over a year ago

Thanks, i still don't understand, union removes duplicates based on what coloumn? In excel, I could remove duplicate based on reffnum, leftb, rightb

Mureinik Over a year ago

@bandungeuy Based on all the columns in the select list

bandungeuy Over a year ago

With new data (I add one duplicate data in t2: tes2-0-12-tesno2), union still give me duplicate values: tes2-0-12-tes nomor2 and tes2-0-12-tesno2.

bandungeuy Over a year ago

How to remove duplicates based on reffnum, leftb, righbt coloumn?

Collectives™ on Stack Overflow

Postgresql: Select unique rows from two tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related