PostgreSQL - Determine column storage type

Question

I've been reading a lot about PostgreSQL's TOAST, and there's one thing I seem to be missing. They mention in the documentation that, "there are four different strategies for storing TOAST-able columns on disk," those being: PLAIN, EXTENDED, EXTERNAL, and MAIN. They also have a very clear way to define which strategy to use for your column, which can be found here. Essentially, it would be something like this:

ALTER TABLE table_name ALTER COLUMN column_name SET STORAGE EXTERNAL

The one thing I don't see is how to easily retrieve that setting. My question is, is there a simple way (either through commands or pgAdmin) to retrieve the storage strategy being used by a column?

user330315 · Accepted Answer · 2018-04-20 18:47:21Z

14

This is stored pg_attribute.attstorage, e.g.:

select att.attname, 
       case att.attstorage
          when 'p' then 'plain'
          when 'm' then 'main'
          when 'e' then 'external'
          when 'x' then 'extended'
       end as attstorage
from pg_attribute att  
  join pg_class tbl on tbl.oid = att.attrelid   
  join pg_namespace ns on tbl.relnamespace = ns.oid   
where tbl.relname = 'table_name'
  and ns.nspname = 'public'
  and not att.attisdropped;

Note that attstorage is only valid if attlen is > -1

answered Apr 20, 2018 at 18:47

user330315

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

Comments

Francis Bartkowiak · Accepted Answer · 2018-04-20 19:01:47Z

8

While I like @a_horse_with_no_name's method, after I posted this question, I expanded my search to just general table information and found that if you use psql, you can use the command described here, and the result will be a table listing all of the columns, their types, modifiers, storage types, stats targets, and descriptions.

So, using psql this info can be found with:

\d+ table_name

I just figured I'd post this in case anyone wanted another solution.

answered Apr 20, 2018 at 19:01

Francis Bartkowiak

1,4762 gold badges15 silver badges32 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Determine column storage type

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related