1

I want to compare two field values from different tables of PostgreSQL both are of different data types that is

CREATE TABLE IF NOT EXISTS departments(
  _id SERIAL NOT NULL UNIQUE PRIMARY KEY,
  departmentName text,
  active BOOLEAN
);

CREATE TABLE IF NOT EXISTS departments_users(
  _id SERIAL NOT NULL UNIQUE PRIMARY KEY,
  department_id integer[]
);

INSERT INTO departments(_id,departmentName)
VALUES
    ( 1, 'aa' ),
    ( 2, 'bb' ),
    ( 3, 'cc' ),
    ( 4, 'dd' );

INSERT INTO departments_users(_id,department_id)
VALUES
  ( 1, ARRAY[1,2,3] );

in the above 2nd table am using department_id which are all present in departments table but in second table am storing it in array if i want get the data which has the common department id expected output should be:

_id | departmentName
-----------------------
1   | aa
2   | bb
3   | cc

So is it possible to match 2 different datatypes?

  • _id = ANY(department_id) should work I think. – ypercubeᵀᴹ Oct 6 '17 at 13:38
  • The question doesn't actually reflect this situation. The data TYPES aren't different, the method of storage (singular versus in an array) are what's different. – Scott Marlowe Oct 9 '17 at 20:18
1

Yes, you can do this with either ANY as mentioned in @ypercube's comment, or with <@ (the containment operator)

SELECT *
FROM departments
JOIN departments_users
  ON departments._id = ANY(department_id);
 _id | departmentname | active | _id | department_id 
-----+----------------+--------+-----+---------------
   1 | aa             |        |   1 | {1,2,3}
   2 | bb             |        |   1 | {1,2,3}
   3 | cc             |        |   1 | {1,2,3}
(3 rows)

If that doesn't work with an index you may need.

SELECT *
FROM departments
JOIN departments_users
  ON department_id @> ARRAY[departments._id];

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.