Postgres array append using a field from a different table

Question

I have two tables, users and groups:

users(user_id BIGSERIAL, email TEXT UNIQUE NOT NULL, state TEXT NOT NULL)
groups(group_id BIGSERIAL, name TEXT, members []BIGINT)

As you can see from above, the users table contains a user records, where each record is uniquely identified by an email address and an auto-generated user_id field. The groups table contains a members array which contains a bunch of BIGINTs that refer to user_id in the users table.

Now I need a query, which when given two incoming parameters, email and a group_id:

Checks if the users table already contains a record with that email address
INSERT a new record if no row already contains that email address
SELECT the user_id of the row (either newly created or already existing)
Append that user_id obtained in above step and append that into the members field of the groups table where the group_id matches with the incoming parameter. There is a chance that the members field already contains the user_id in which case no action needs to be done (IOW, there should be no duplicate values for the members field for each row in the groups table).

For, 1, 2 and 3, I have written the following query:

WITH new_row AS (
    INSERT INTO users (email, state)
    SELECT '[email protected]', 'Referred'
        WHERE NOT EXISTS (SELECT * FROM users WHERE email = '[email protected]')
        RETURNING *
)
SELECT user_id FROM new_row
UNION
SELECT user_id FROM users WHERE email='[email protected]';

For 4, I have written the following query:

UPDATE groups SET members = (
   SELECT ARRAY(
      SELECT DISTINCT unnest(members || array[RANDOM_USER_ID]::bigint[]) ) )
WHERE group_id = INCOMING_GROUP_ID 
RETURNING group_id, members;

where RANDOM_USER_ID in the second query should be replaced with the user_id that is obtained in the first query.

Now what is the way to combine both of these queries atomically as a single query, where the user_id from the first query is piped to the second query ? I do not want to create a transaction and run the two individual queries independently and keep the user_id field in a temporary place (in my server program). I want all of these to be done in a single query. Is it possible ?

I will be using Postgresql 9.6.4 (or later) on RDS/Google CloudSQL, if it matters.

Łukasz Kamiński · Accepted Answer · 2018-01-29 10:20:31Z

1

From what I see I think it is fair to assume it will be one user_id per query, so you could for example use another CTE (for clarity) and subquery:

WITH usr AS (
  WITH new_row AS (
      INSERT INTO users (email, state)
      SELECT '[email protected]', 'Referred'
          WHERE NOT EXISTS (SELECT * FROM users WHERE email = '[email protected]')
          RETURNING *
  )
  SELECT user_id FROM new_row
  UNION
  SELECT user_id FROM users WHERE email='[email protected]'
)
UPDATE groups SET members = (
   SELECT ARRAY(
      SELECT DISTINCT unnest(members || array[(SELECT user_id FROM usr)]::bigint[]) ) )
WHERE group_id = INCOMING_GROUP_ID 
RETURNING group_id, members;

Alternative is to use UPSERT:

CREATE UNIQUE INDEX ON users(email);

WITH usr AS (
  INSERT INTO users (email, state)
  SELECT '[email protected]', 'Referred'
  ON CONFLICT(email)
  DO UPDATE SET email = users.email --this does empty UPDATE so that RETURNING gives us a row
  RETURNING *
)
UPDATE groups SET members = (
   SELECT ARRAY(
      SELECT DISTINCT unnest(members || array[(SELECT user_id FROM usr)]::bigint[]) ) )
WHERE group_id = INCOMING_GROUP_ID 
RETURNING group_id, members;

answered Jan 29, 2018 at 10:20

Łukasz Kamiński

6,0301 gold badge26 silver badges33 bronze badges

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

2 Comments

Sankar Over a year ago

Brilliant. Thanks. I am using the UPSERT version as it is more readable. It just did not occur to me that I could have done a dummy UPDATE. I have accepted the answer. Can the SELECT DISTINCT unnest clauses be changed somehow to keep the user_id s in the members always sorted, for performance and readability reasons ? Thanks.

Łukasz Kamiński Over a year ago

@SankarP I think you would have to create your own function for that and then do something like UPDATE groups SET members = array_append_uniq(members, (SELECT user_id FROM usr)) [..]. You would have to search for element either with something like array_position or unnesting and if not found then using built-in array_append.

Collectives™ on Stack Overflow

Postgres array append using a field from a different table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related