Is there any way to know which tables have Bytea type fields in a Postgresql database?

Asked

Viewed 343 times

2

I have a base with 500MB I consider small, but this slow to open some tables, checking are tables that have Bytea field, the other programmer saved images in the database, but it is impossible to work, left the application slow, because sometimes the images are not treated and become huge, in the end, it is a situation that I can put the images in a directory, it is about 270 table in the base.

It has to give a select so that Postgresql shows me which field type Bytea?

postgresql

2 answers

2


Make the following inquiry:

select table_schema, table_name, column_name from information_schema.columns where data_type like 'bytea'

1

Among the postgres catalog tables is the pg_attribute which contains column information for all database tables. In this table there is the column atttypid which stores the id of the data type, so it would be necessary to filter the records corresponding to the type bytea according to your oid in the table pg_type.

select 
    relname as tabela,
    relnamespace::regnamespace as schema,
    attname as coluna
from pg_attribute a
    inner join pg_class c
        on (a.attrelid=c.oid)
    inner join pg_type t
        on (a.atttypid=t.oid)
where typname='bytea'
    and relnamespace not in (
        'pg_catalog'::regnamespace,
        'pg_toast'::regnamespace    
    );

Below the links with information from the catalog tables used in the query, if you need to extract more information: https://www.postgresql.org/docs/10/static/catalog-pg-attribute.html https://www.postgresql.org/docs/10/static/catalog-pg-class.html https://www.postgresql.org/docs/10/static/catalog-pg-type.html

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.