I have two tables in a PostgreSQL database I cannot modify (COTS software that I'm just trying to fetch information from).
One with user information:
| id | username | mail | +----+----------+---------------+ | 1 | admin | [email protected] | | 2 | user | [email protected] | ...and an additional table with user attributes:
| user_id | attribute_name | attribute_value | +---------+-----------------+-------------------+ | 1 | active | true | | 1 | loginDateMillis | 1652176627 | | 2 | active | true | What I'd need is all users with their loginDateMillis, if existing, otherwise "null" or empty, e.g.:
| id | username | mail | attribute_value | +----+----------+---------------+-----------------+ | 1 | admin | [email protected] | 1652176627 | | 2 | user | [email protected] | null | At first I tried to join the two tables with a where condition to select only the attribute_name = 'loginDateMillis', but then I'm filtering out the null rows:
> select u.id, u.username, u.mail, a.attribute_value from users u left outer join user_attributes a on a.user_id = u.id where a.attribute_name = 'loginDateMillis'; | id | username | mail | attribute_value | +----+----------+---------------+-----------------+ | 1 | admin | [email protected] | 1652176627 | The only solution I could find to get one line for each user, but also the attributes, was to group by the user columns and use the array_agg function:
> select u.id, u.username, u.mail, array_agg(a.attribute_name), array_agg(a.attribute_value) from users u left outer join user_attributes a on a.user_id = u.id group by (u.id, u.username, u.mail); | id | username | mail | attribute_name | attribute_value | +----+----------+---------------+---------------------------+--------------------+ | 1 | admin | [email protected] | {active, loginDateMillis} | {true, 1652176627} | | 2 | user | [email protected] | {active} | {true} | Which is "okayish", because I can then filter the arrays in my application, but maybe I'm missing something obvious here? Many thanks in advance.
