PostgreSQL join with a distinct clause

Question

I’m doing a join between two tables in PostgreSQL, one has a primary key constraint on incidentnumber (opentickets) while the other table does not have the restraint and can have duplicate incidentnumbers (incommingtickets). The problem comes when trying to filter out duplicates. The query,

SELECT  incommingtickets.* 
FROM incommingtickets
    LEFT JOIN opentickets
        ON incommingtickets.incidentnumber = opentickets.incidentnumber 
WHERE opentickets.incidentnumber IS NULL
      AND incommingtickets.status NOT IN ('Closed','Cancelled', '')

works until it hits a duplicate, the I get the violates primary key message. If I add a distinct clause like,

SELECT  DISTINCT ON (incommingtickets.incidentnumber) incommingtickets.* 
FROM incommingtickets
    LEFT JOIN opentickets
        ON incommingtickets.incidentnumber = opentickets.incidentnumber 
WHERE opentickets.incidentnumber IS NULL
      AND incommingtickets.status NOT IN ('Closed','Cancelled', '')

I get an error,

pg_query(): Query failed: ERROR: missing FROM-clause entry for table "incommingtickets" LINE 30: WHERE opentickets.incidentnumber = incommingtickets.incident...

This looks like a bulk-upsert case. See http://stackoverflow.com/q/17267417/398670 . Assuming that the issue is not inserting rows that already exist in `opentickets`. — Craig Ringer, Jul 03 '15 at 03:29
I don't get an error when I run your second query, by the way, after creating some dummy tables. Check for typos etc in your original attempt to run it. Also, PostgreSQL version? — Craig Ringer, Jul 03 '15 at 03:35

score 0 · Answer 1 · answered Mar 19 '16 at 16:40

Use a WHERE clause that filters out the duplicates you don't want, although it's not very clear for me on why you want to join on a 'metric' such as number of tickets.

SELECT  incommingtickets.* 
FROM incommingtickets
WHERE incommingtickets.incidentnumber not in (
        select 
        distinct 
        incidentnumber 
        FROM opentickets)
AND incommingtickets.status NOT IN ('Closed','Cancelled', '')

This way you are filetring out duplicates between both tables.

If what you want is to check or update the ticket's status of any tickets inside the opentickets table then try to get from the incommingtickets the maximum status like this:

WITH ticket_rows AS(
    SELECT
    rank() OVER (PARTITION BY ticket_id ORDER BY ticket_timestamp desc) as row_number,
    ticket_id,
    ticket_status,
    ticket_timestamp
    from incommingtickets
)

SELECT  incommingtickets.*, opentickets_2.* 
FROM opentickets o
    LEFT JOIN ticket_rows ON ticket_rows.ticket_id= opentickets.ticket_id AND ticket_rows.row__number=1

If these are not your objectives pleae explain a bit better on what you are trying to achieve with that left join.

PostgreSQL join with a distinct clause

1 Answers1