Get size for Bytea column in postgreSQL

Question

I have table for store file information. File content is stored in column (column datatype is bytea in PostgreSQL). Now I want to get file content size, based on bytea column data.

In SQL Server, I am able to get by using below query:

select convert(nvarchar(50),DATALENGTH(FileDataInVarbinary)/ 1048576.0 ) +' MB' as FileSize from Table_FileInfo

Could you suggest me, how we can write in postgreSQL for same above sql query.

Answer 1

You can use the length() function to get the length in bytes:

select length(bytea_column) as filesize
from file_info;

If you want to format the output you can use pg_size_pretty()

select pg_size_pretty(length(bytea_column)) as filesize
from file_info;

That will adapt the "kb", "mb" output based on the size. If you always want MB, you can use:

select concat(length(bytea_column) / 1048576.0, ' MB') as filesize
from file_info;