Hi all. I've got two tables:
Table "unbc_users"
Column | Type
-----------+--------------
user_id | integer
firstname | character varying(255)
lastname | character varying(255)
username | character varying(25)
password | character varying(25)
email | character varying(255)
clearance | integer
hint | integer
answer | character varying(255)
status | integer
added | timestamp(0) without time zone
apps | integer[]
Table "user_apps"
Column | Type
---------------+----------
app_id | integer
app_name | character varying(255)
app_location | character varying(255)
clearance_req | integer
status | integer
The guts of my query rests on being able to check if the app_id from the user_apps table is an element of the apps array in the unbc_users table.
Here's my query:
select u.user_id,u.username,ua.app_id,ua.app_name from unbc_users u, user_apps ua where u.apps *= ua.app_id;
And here's the error I get:
ERROR: Unable to identify an operator '*=' for types 'integer[]' and 'integer'
You will have to retype this query using an explicit cast
However, I've modelled my query (specifically the part which looks at the array) after the sample query I got from the PostgreSQL docs, which is as follows:
SELECT FROM sal_emp WHERE pay_by_quarter = 10000;
In this query 'pay_by_quarter' column is an integer array, which is the same as the ua.apps column in my query.
Does anyone have any idea what could be causing this? I'm going to look into explicitly casting the ua.app_id field as an array, but I don't think I should have to since what I'm trying to do is see if an element exists in an array, not compare the two to see if they're equal.
Any help provided is greatly appreciated.
Thanks in advance,
Pablo
