CODEWITHSUNDEEP
postgresql
Joseph Katzman
Joseph Katzman

Reputation: 2085

How to get max length (in bytes) of a variable-length column?

I want to get max length (in bytes) of a variable-length column. One of my columns has the following definition:

I tried to retrieve some info from the pg_attribute table, but the attlen column has -1 value for all variable-length columns. I also tried to use pg_column_size function, but it doesn't accept the name of the column as an input parameter.

It can be easily done in SQL Server.

enter image description here

enter image description here

enter image description here

enter image description here

Are there any other ways to get the value I'm looking for?

Upvotes: 0

Views: 1388

Answers (1)

user330315
user330315

Reputation:

You will need to use a CASE expression checks pg_attribute.attlen and then calculate the maximum size in bytes depending on that. To get the max size for a varchar column you can "steal" the expression used in information_schema.columns.character_octet_length for varchar or char columns

Something along the lines:

select a.attname, 
       t.typname,
       case 
          when a.attlen <> -1 then attlen
          when t.typname in ('bytea', 'text') then pg_size_bytes('1GB')
          when t.typname in ('varchar', 'char') then information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))
       end as max_bytes
from pg_attribute a
  join pg_type t on a.atttypid = t.oid
where a.attrelid = 'stuff.test'::regclass
  and a.attnum > 0
  and not a.attisdropped;

Note that this won't return a proper size for numeric as that is also a variable length type. The documentation says "he actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead".

As a side note: this seems an extremely strange thing to do. Especially with your mentioning of temp tables in stored procedures. More often than not, the use of temp tables is not needed in Postgres. Instead of blindly copying the old approach that might have worked well in SQL Server, you should understand how Postgres works and change the approach to match the best practices in Postgres.

I have seen many migrations fail or deliver mediocre performance because of the assumption that the best practices for "System A" can be applied without any change to "System B". You need to migrate your mindset as well.

If this checks the columns of a temp table, then why not simply check the actual size of the column values using pg_column_size()?

Upvotes: 1

Related Questions