PostgreSQL – Using DISTINCT and ORDER BY in json_agg

There are 3 possible kinds of duplicates:

1. Duplicates within the rows of the bulk insert.

2. Duplicates between inserted rows and existing rows.

3. Duplicates between inserted rows and concurrently inserted / updated rows from other transactions.

Just like I explained in this closely related answer:

• Using EXCEPTION to ignore duplicates during bulk inserts

But things have become easier for 2. and 3. since Postgres 9.5 introduced UPSERT (INSERT .. ON CONFLICT DO NOTHING).

INSERT INTO emails(tag,email)
SELECT DISTINCT 655, email
FROM   emails_temp
ON CONFLICT (email) DO NOTHING;

If your duplicates only stem from duplicate entries in the source (1.), like you indicated, then all you need is DISTINCT. Works in any version of Postgres:

INSERT INTO emails(tag,email)
SELECT DISTINCT 655, email
FROM   emails_temp;

"VAL_X" and "VAL_Y" chosen through some aggregate function

You should consider using GROUP BY for the columns whose values you consider that should be "distinct" (as a group), and, for the rest of columns, choose an appropriate aggregate function (for instance, MIN):

CREATE TABLE my_result AS 
SELECT
  city, street, streetnum, min(val_x) AS val_x, min(val_y) AS val_y
FROM
  tableA
WHERE
  true /* your condition goes here */ 
GROUP BY
  city, street, streetnum

If you need to put together values from several tables, UNION ALL of them before you GROUP BY:

CREATE TABLE my_result AS 
SELECT
  city, street, streetnum, min(val_x) AS val_x, min(val_y) AS val_y
FROM
  (
  SELECT city, street, streetnum, val_x, val_y FROM tableA
  UNION ALL
  SELECT city, street, streetnum, val_x, val_y FROM tableB
  UNION ALL
  SELECT city, street, streetnum, val_x, val_y FROM tableC
  ) AS s0
WHERE
  true /* your condition goes here */ 
GROUP BY
  city, street, streetnum ;

Using always "VAL_X" and "VAL_Y" from same row, using a WINDOW

If you need to make sure your values are always from the same row, the best way is to use a WINDOW in your query: PARTITION BY "CITY", "STREET", "STREET_NUM" and ORDER BY "VAL_X", "VAL_Y", and choose the first row of every partition.

You can do this with two steps:

1) Add the row_num() to every partition:

SELECT 
  *,   
  (row_number() OVER (PARTITION BY "CITY", "STREET", "STREET_NUM" ORDER BY "VAL_X", "VAL_Y")) AS rn
FROM 
  table_a

  |  CITY |     STREET | STREET_NUM | VAL_X | VAL_Y | rn |
  |-------|------------|------------|-------|-------|----|
  | CityA | Street abc |          5 |  11.5 |   0.5 |  1 |
  | CityA | Street abc |          5 |  12.4 |   2.8 |  2 |
  | CityA | Street abc |          5 |  15.4 |   1.8 |  3 |
  | CityB | Street xyz |         18 |   5.4 |   1.9 |  1 |
  | CityB | Street xyz |         18 |   8.4 |   1.1 |  2 |
  | CityC | Street klm |         55 |   9.6 |   0.8 |  1 |

2) At this point, choose only the rows WHERE rn=1 (and ORDER them, if necessary):

SELECT
   "CITY", "STREET", "STREET_NUM", "VAL_X", "VAL_Y"
FROM
  (
  SELECT 
    *,   
    (row_number() OVER (PARTITION BY "CITY", "STREET", "STREET_NUM" ORDER BY "VAL_X", "VAL_Y")) AS rn
  FROM 
    table_a
  ) AS table_a_grouped 
WHERE
  rn = 1
ORDER BY 
  "CITY", "STREET", "STREET_NUM" 

The result is:

|  CITY |     STREET | STREET_NUM | VAL_X | VAL_Y |
|-------|------------|------------|-------|-------|
| CityA | Street abc |          5 |  11.5 |   0.5 |
| CityB | Street xyz |         18 |   5.4 |   1.9 |
| CityC | Street klm |         55 |   9.6 |   0.8 |

You can see the example at SQLFiddle