Count file size in PostgreSQL

Question

I have this simple table which I would like to use to store files.

CREATE TABLE table(
 ID INTEGER NOT NULL,
 FILE_NAME TEXT,
 FILE OID
)
;

Is there any way with SQL query to count the total size of the files into the table? Is this possible without function?

Possible duplicate of Get size of large object in PostgreSQL query? — Chris Travers
– Chris Travers, Commented Apr 5, 2016 at 8:55
In my case I prefer to use query not function? Is this possible? — Peter Penzov
– Peter Penzov, Commented Apr 5, 2016 at 10:19

Chris Travers · Accepted Answer · 2016-04-05 10:40:10Z

2

rzo is close but not quite right.

select file_name, pg_column_size(lo_get(oid)) from files;

Gives you the size in bytes.

If you want pretty printing:

select file_name, pg_size_pretty(pg_column_size(lo_get(oid))::numeric) from files;

answered Apr 5, 2016 at 10:40

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

Comments

rzo1 · Accepted Answer · 2016-04-05 07:46:55Z

1

If you are interested in the total disk size (disk usage), you could do something like:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

Other queries to measure the size of relations can be found in the PostgreSQL Wiki

answered Apr 5, 2016 at 7:46

rzo1

5,7874 gold badges28 silver badges66 bronze badges

1 Comment

Peter Penzov Over a year ago

Thanks, but I only need to get the size of the files into the table.

Collectives™ on Stack Overflow

Count file size in PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related