Postgres select and merge rows

Question

I am working on a table which looks something like this:

user_id | key         | scope     | value
--------+-------------+-----------+-------
1       | someSetting | user      | false
1       | someSetting | group     | true
1       | someSetting | company   | false
2       | someSetting | user      | false
2       | someSetting | group     | true
3       | someSetting | user      | false
4       | someSetting | group     | true

The settings are in a hierarchy: company -> group -> user, with the user overriding the group which in turn overrides the company. When querying by user_id, I want to effectively merge the settings by this hierarchy, if it exists. For the above sample, I want to see this as the result:

user_id | key         | value
--------+-------------+-------
1       | someSetting | false
2       | someSetting | true
3       | someSetting | false
4       | someSetting | true

I am currently doing the merge operation after the rows are retrieved from Postgres, but it would be more efficient if this can be done in the query itself. I looked at aggregate functions, but doesn't look like any of them fit my requirement.

This seems simple enough that I'm sure it can be done using Postgres. Any pointers appreciated!

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You can use the ROW_NUMBER() window function with a PARTITION BY and a pretty cool ORDER BY.

Idea:

Get a ROW_NUMBER for every record with the same user_id and ORDER BY a custom sort order.
SELECT everything you want from the CTE a WHERE row number is 1.

Example:

WITH a AS
(
    SELECT user_id
         , key
         , scope
         , ROW_NUMBER() OVER(PARTITION BY user_id 
                             ORDER BY array_position(array['user','group','company'], scope)) AS rno
    FROM test
)
SELECT user_id
     , key
     , scope
FROM a
WHERE rno = 1;

DBFiddle to show it work.

Bonus: If you were to make a function to do this you could even pass in other arrays for setting a custom sort order.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 10, 2019 at 1:17

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Checksum Over a year ago

Thanks, that's a neat query! Didn't know about array_position.

Zack · Accepted Answer · 2019-04-10 02:03:39Z

What you want to do is to change your scope settings from separate rows to separate columns, so your recordset looks like this (note I'm using 0 for false and 1 for true):

+---------+-------------+--------------+---------------+-----------------+
| user_id |     key     | user_setting | group_setting | company_setting |
+---------+-------------+--------------+---------------+-----------------+
|       1 | someSetting | 0            | 1             | 0               |
|       2 | someSetting | 0            | 1             | NULL            |
|       3 | someSetting | 0            | NULL          | NULL            |
|       4 | someSetting | NULL         | 1             | NULL            |
+---------+-------------+--------------+---------------+-----------------+

To do this, you have a few options. Here's one of them, using conditional aggregation. Basically, you group by user_id and key, then combine an aggregate function (it can be either MIN or MAX) with a CASE statement:

WITH 
    settings_pivot AS
    (
        SELECT
            [user_id],
            [key],
            MIN(CASE WHEN [scope] = 'user' THEN [value] ELSE NULL END) AS user_setting,
            MIN(CASE WHEN [scope] = 'group' THEN [value] ELSE NULL END) AS group_setting,
            MIN(CASE WHEN [scope] = 'company' THEN [value] ELSE NULL END) AS company_setting
        FROM settings
        GROUP BY
            [user_id],
            [key]
    )
SELECT 
    [user_id], 
    [key], 
    COALESCE(user_setting, group_setting, company_setting) AS derived_setting
FROM settings_pivot

If you just SELECT * from the settings_pivot CTE, you'll get the pivoted data I have at the beginning. Using COALESCE, however, you give precedence as you indicate.

Note: I'm using SQL Server, since Postgres on my machine doesn't want to boot up. So you'll have to replace the square braces with double quotes: "user_id" instead of [user_id].

Collectives™ on Stack Overflow

Postgres select and merge rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related