1

I'm trying to calculate all the projects size of one user across multiple tables.

a unique project is separated into 4 tables with multiple rows assigned to it.

for the moment I'm only able to calculate one, here is what I have so far:

select  pg_size_pretty((
  select COALESCE(sum(pg_column_size(s)), 0) as ts
  from editor_s s
  where project_uid = 'z_5oyUFV6615Lh1d-FabX' )
  +
  ( select COALESCE(sum(pg_column_size(o)), 0) as os
  from editor_o o
  where project_uid = 'z_5oyUFV6615Lh1d-FabX' )
  +
  ( select COALESCE(sum(pg_column_size(e)), 0) as es
  from editor_c e
  where project_uid = 'z_5oyUFV6615Lh1d-FabX' )
  +
  ( select COALESCE(sum(pg_column_size(ss)), 0) as sss
  from editor_ss ss
  where project_uid = 'z_5oyUFV6615Lh1d-FabX' ))

as total

So let's say I want all projects sizes of user 3 I would like to receive back

|-------------------|
|project_uid|size   |
|-------------------|
| 1234ua    | 20kb  |
|-------------------|
| ze64ua    | 120kb |
|-------------------|
| bv76y1    | 93kb  |
|-------------------|

is this possible?

EDIT

As it would take a huge amount of time to reproduce the issue and create examples and as we are close to the point I'll try describe a bit more my attempt:

So one project has its data separated into 4 tables each project can have one or many rows refering to it's project_uid in a table.

  1. table1 have composite primary key user_uid and project_uid ( 1 row per project )
  2. table2 have composite primary key uid, user_uid and project_uid( multiple rows per project )
  3. table3 have composite primary key uid, user_uid and project_uid
  4. table4 have composite primary key uid, user_uid and project_uid

uid and project_uid are of type TEXT where user_uid is of type UUID

all tables have different numbers of columns with different data types (TEXT, JSONB, FLOAT...)

So what I would like is get all the projects total sizes ( across all 4 tables ) grouped by project_uid and where user_uid = 'SOME_USER_UID'

so in pseudo code this would look something like ( and im sorry but I'm a newby in SQL databases so I'll write it as clear as I can )

PSEUDO CODE:

SELECT SUMS(
  SELECT ( 

    sum(pg_column_size(table1)),

    sum(pg_column_size(table2)),

    sum(pg_column_size(table3)),

    sum(pg_column_size(table4))

  ) GROUP BY project_uid ) WHERE user_uid = 'SOME USER UID';

ps: I've invented the operator SUMS for this example that would return multiple sums (rows)

1

2 Answers 2

1

Some guessing:

SELECT project_uid, sum(col_size) 
FROM (
    SELECT project_uid, COALESCE(sum(pg_column_size(s)), 0) as col_size
    FROM editor_s s
    GROUP BY project_uid

    UNION ALL

    SELECT project_uid, COALESCE(sum(pg_column_size(o)), 0) as col_size
    FROM editor_o o
    GROUP BY project_uid

    UNION ALL

    ...

) AS t
GROUP BY project_uid
9
  • Just saw your answer, thanks a lot but there are errors columns are not of the same size. I'll do what you ask and provide an example. THank again for your time.
    – JSmith
    Aug 4, 2022 at 10:06
  • I've added an edit to my post. Sorry but creating an example would take a huge amount of time and I would need to obfuscate my data. So I've tried to explain as precisely as possible. Please tell me if you need more infos. Thanks in advance for your time.
    – JSmith
    Aug 4, 2022 at 11:40
  • I'm afraid it will take me even longer to create an working example. You can try to add a WHERE user_uid = 'SOME USER UID' to each leg in the UNION sub-select Aug 4, 2022 at 16:04
  • too bad, the thing is that as mentionned before, because of different number of collumns the union all is not working any alternatives? Thanks in advance.
    – JSmith
    Aug 5, 2022 at 1:51
  • 1
    You can create "dummy" columns to make the legs union compatible, but without an example it's difficult to be more specific than that Aug 5, 2022 at 8:14
1

I've managed to make it work by doing:

select project_uid, sum(col_size) FROM (
  (select COALESCE(sum(pg_column_size(s)), 0) as col_size, project_uid
  from editor_s s
  where user_uid = 'some_user_uid'
  group by project_uid )
  UNION ALL
  ( select COALESCE(sum(pg_column_size(o)), 0) as col_size, project_uid
  from editor_o o
  where user_uid = 'some_user_uid'
  group by project_uid )
  UNION ALL
  ( select COALESCE(sum(pg_column_size(e)), 0) as col_size, project_uid
  from editor_c e
  where user_uid = 'some_user_uid'
  group by project_uid )
  UNION ALL
  ( select COALESCE(sum(pg_column_size(ss)), 0) as col_size, project_uid
  from editor_ss ss
  where user_uid = 'some_user_uid'
  group by project_uid )
) 
 as total group by project_uid;

Thank you to @Lennart

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.