CODEWITHSUNDEEP
arrayspostgresqlcomparison
Michael Sheaver
Michael Sheaver

Reputation: 2119

PostgreSQL: Exact Match on Multiple Columns

Background

I have a table that contains multiple ID fields like so:

work_id TEXT DEFAULT NULL,
card_id TEXT DEFAULT NULL,
employee_id TEXT DEFAULT NULL,
school_id TEXT DEFAULT NULL

Find exact matches

And I need to find the rows where all of these fields match exactly. My initial syntax for this comparison would be:

SELECT * FROM table
WHERE
  work_id = card_id AND
  work_id = employee_id AND
  work_id = school_id;

Find any non-matching rows

Conversely, I also need to find the rows where all of these fields do not match exactly. My initial syntax for this comparison would be:

SELECT * FROM table
WHERE
  work_id NOT = card_id OR
  work_id NOT = employee_id OR
  work_id NOT = school_id;

Question

Is there an easier way to do this comparison across more than two columns at a time? Is there some sort of array operator that will find matching or non-matching values across multiple columns?

Future-Proofing the Code

I ask this because if the number of columns being compared is high, this type of code could prove unwieldly and hard to maintain down the road. For example, if later on, we add another ID column to the table, I would need to manually add a new line to the comparison logic to include the new column.

Upvotes: 1

Views: 2284

Answers (2)

user330315
user330315

Reputation:

You can use ANY or ALL with an array

SELECT * 
FROM the_table
WHERE work_id = all (array[card_id, employee_id, school_id])


SELECT * 
FROM the_table
WHERE work_id <> any (array[card_id, employee_id, school_id])

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246163

You could use composite values:

WHERE (work_id, work_id, work_id) <>
      (card_id, employee_id, school_id)

Upvotes: 0

Related Questions