Search code examples
postgresqldistinct-values

Postgresql: Select unique rows from two tables

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:

  1. I should find unique values in t2 that not in t1: select * from t2 except select * from t1
  2. 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

Solution

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

    SELECT * FROM table1
UNION
SELECT * FROM table2