2

I have 3 tables:

╔═════════════════════╗
║      companies      ║
╠═════════════════════╣
║ id                  ║
║ some_company_attrib ║
╚═════════════════════╝

╔════════════════════╗
║       people       ║
╠════════════════════╣
║ id                 ║
║ some_person_attrib ║
╚════════════════════╝

╔════════════════════════════════════════════════════════════╗
║                            jobs                            ║
╠════════════════════════════════════════════════════════════╣
║ id                                                         ║
║ company_id – foreign key pointing to the companies table   ║
║ person_id  – foreign key pointing to the people table      ║
║ some_job_attrib                                            ║
╚════════════════════════════════════════════════════════════╝

From an external source, I have a big list of pairs of attributes, which reference companies.some_company_attrib and people.some_person_attrib, like so:

('some_company_attrib123', 'some_person_attrib456'),
('some_company_attrib789', 'some_person_attribxyz'), 
etc...

where each pair should match a record in the jobs table, so that the pair ('some_company_attrib123', 'some_person_attrib456') gives me back the record in the jobs which is associated:

  • to the record in the companies table where some_company_attrib = 'some_company_attrib123'
  • and to record in the people table where some_person_attrib = 'some_person_attrib456'

After looking around and not finding any solution, I am using arrays in the following query:

SELECT * FROM jobs 
  INNER JOIN people ON people.id = jobs.person_id
  INNER JOIN companies ON companies.id = jobs.company_id 
WHERE (
    ARRAY[companies.some_company_attrib, people.some_person_attrib]::text[] in (
    '{"some_company_attrib123", "some_person_attrib456"}',
    '{"some_company_attrib789", "some_person_attribxyz"}',
    '{"and_many_many", "more_like_these"}'
);

Although it works, it feels rather clunky and it's actually pretty slow. I was wondering whether there was an idiomatic and more elegant way to achieve this.

2

Well, it all depends on your scenario. If it is a one-off import, you create a junction table and use it for your joins.

CREATE TABLE people_at_companies (
  company_attrib TEXT,  -- whatever type it is
  person_attrib TEXT    -- whatever type it is
)

INSERT INTO people_at_companies VALUES
('some_company_attrib123', 'some_person_attrib456'),
('some_company_attrib789', 'some_person_attribxyz'), 
...  -- remember to remove the last comma :)
;

SELECT j.* FROM jobs j
  INNER JOIN people p ON p.id = j.person_id
  INNER JOIN companies c ON c.id = j.company_id 
  INNER JOIN people_at_companies pac ON
    c.some_company_attrib = pac.company_attrib 
    AND
    p.some_person_attrib = pac.person_attrib
;

Having such junction table might improve performance, and it allows creating indexes.

If the junction data is dynamic in such a way that keeping a junction table in sync is too difficult (a very unusual situation, I’d say), we can avoid the junction table but, at the very least, using VALUES instead of arrays is way simpler:

SELECT j.* FROM jobs j
  INNER JOIN people p ON p.id = j.person_id
  INNER JOIN companies c ON c.id = j.company_id 
  INNER JOIN (VALUES
    ('some_company_attrib123', 'some_person_attrib456'),
    ('some_company_attrib789', 'some_person_attribxyz'), 
    ...  -- remember to remove the last comma :)
  ) AS pac(company_attrib, person_attrib) ON
    c.some_company_attrib = pac.company_attrib 
    AND
    p.some_person_attrib = pac.person_attrib
;

You might have to explicitly cast the first row of the VALUES with the types of companies.some_company_attrib and people.some_person_attrib if PostgreSQL doesn’t figure the correct ones out. E.g.

  ('some_company_attrib123'::TEXT, 'some_person_attrib456'::TEXT)
1
  • Thanks @Dario, actually the use of VALUES instead of arrays was exactly what I was looking for here! Feb 21 '17 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.