Как получить информацию о таблицах и столбцах из Redshift?

Ответ 1

Pg_table_def может предоставить некоторую полезную информацию, но это не говорит о размерах столбцов, по умолчанию или размерах символов. Вот запрос, который может показать вам все это (обратите внимание, что я обновил этот запрос с исходного сообщения, и теперь он включает в себя кодировку столбца, diststyle/distkey, sortkey и первичный ключ, а также распечатку оператора, который показывает владельца таблицы ):

select pk.pkey, tm.schemaname||'.'||tm.tablename, 'create table '||tm.schemaname||'.'||tm.tablename
  ||' ('
  ||cp.coldef
  -- primary key
  ||decode(pk.pkey,null,'',pk.pkey)
  -- diststyle and dist key
  ||decode(d.distkey,null,') diststyle '||dist_style||' ',d.distkey)
  --sort key 
  || (select decode(skey,null,'',skey) from  (select 
    ' sortkey(' ||substr(array_to_string(
                     array( select ','||cast(column_name as varchar(100))  as str from
                           (select column_name from information_schema.columns col where  col.table_schema= tm.schemaname and col.table_name=tm.tablename) c2
                            join 
                            (-- gives sort cols
                              select attrelid as tableid, attname as colname, attsortkeyord as sort_col_order from pg_attribute pa where 
                              pa.attnum > 0  AND NOT pa.attisdropped AND pa.attsortkeyord > 0
                            ) st on tm.tableid=st.tableid and c2.column_name=st.colname   order by sort_col_order
                          )
                    ,'')
                  ,2,10000) || ')' as skey
   ))
  ||';'
  -- additional alter table queries here to set owner
  || 'alter table '||tm.schemaname||'.'||tm.tablename||' owner to "'||tm.owner||'";'   
   from 
-- t  master table list
(
SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid ,use2.usename as owner, decode(c.reldiststyle,0,'EVEN',1,'KEY',8,'ALL') as dist_style
FROM pg_namespace n, pg_class c,  pg_user use2 
WHERE n.oid = c.relnamespace 
  AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  AND c.relname <> 'temp_staging_tables_1'
  and c.relowner = use2.usesysid
) tm 
-- cp  creates the col params for the create string
join
(select 
  substr(str,(charindex('QQQ',str)+3),(charindex('ZZZ',str))-(charindex('QQQ',str)+3)) as tableid
  ,substr(replace(replace(str,'ZZZ',''),'QQQ'||substr(str,(charindex('QQQ',str)+3),(charindex('ZZZ',str))-(charindex('QQQ',str)+3)),''),2,10000) as coldef
 from
 ( select array_to_string(array(
  SELECT  'QQQ'||cast(t.tableid as varchar(10))||'ZZZ'|| ','||column_name||' '|| decode(udt_name,'bpchar','char',udt_name) || decode(character_maximum_length,null,'', '('||cast(character_maximum_length as varchar(9))||')'   )
  -- default
  || decode(substr(column_default,2,8),'identity','',null,'',' default '||column_default||' ')
  -- nullable
  || decode(is_nullable,'YES',' NULL ','NO',' NOT NULL ') 
  -- identity 
  || decode(substr(column_default,2,8),'identity',' identity('||substr(column_default,(charindex('''',column_default)+1), (length(column_default)-charindex('''',reverse(column_default))-charindex('''',column_default)   ) )  ||') ', '')
  -- encoding
  || decode(enc,'none','',' encode '||enc)
   as str 
   from  
  -- ci  all the col info
  (
    select cast(t.tableid as int), cast(table_schema as varchar(100)), cast(table_name as varchar(100)), cast(column_name as varchar(100)), 
    cast(ordinal_position as int), cast(column_default as varchar(100)), cast(is_nullable as varchar(20)) , cast(udt_name as varchar(50))  ,cast(character_maximum_length as int),
     sort_col_order  , decode(d.colname,null,0,1) dist_key , e.enc
    from 
    (select * from information_schema.columns c where  c.table_schema= t.schemaname and c.table_name=t.tablename) c
    left join 
    (-- gives sort cols
    select attrelid as tableid, attname as colname, attsortkeyord as sort_col_order from pg_attribute a where 
     a.attnum > 0  AND NOT a.attisdropped AND a.attsortkeyord > 0
    ) s on t.tableid=s.tableid and c.column_name=s.colname
    left join 
    (-- gives encoding
    select attrelid as tableid, attname as colname, format_encoding(a.attencodingtype::integer) AS enc from pg_attribute a where 
     a.attnum > 0  AND NOT a.attisdropped 
    ) e on t.tableid=e.tableid and c.column_name=e.colname
    left join 
    -- gives dist col
    (select attrelid as tableid, attname as colname from pg_attribute a where
     a.attnum > 0 AND NOT a.attisdropped  AND a.attisdistkey = 't'
    ) d on t.tableid=d.tableid and c.column_name=d.colname
    order by ordinal_position
  ) ci 
  -- for the working array funct
  ), '') as str
 from 
 (-- need tableid
 SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid 
 FROM pg_namespace n, pg_class c
 WHERE n.oid = c.relnamespace 
   AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
 ) t 
)) cp on tm.tableid=cp.tableid
-- primary key query here
left join 
 (select c.oid as tableid, ', primary key '|| substring(pg_get_indexdef(indexrelid),charindex('(',pg_get_indexdef(indexrelid))-1 ,60) as pkey
  from pg_index i , pg_namespace n, pg_class c 
  where i.indisprimary=true 
  and i.indrelid =c.oid
  and n.oid = c.relnamespace
 )  pk on tm.tableid=pk.tableid
-- dist key
left join
(  select 
  -- close off the col defs after the primary key 
  ')' ||
  ' distkey('|| cast(column_name as varchar(100)) ||')'  as distkey, t.tableid
  from information_schema.columns c
  join 
  (-- need tableid
  SELECT substring(n.nspname,1,100) as schemaname, substring(c.relname,1,100) as tablename, c.oid as tableid 
  FROM pg_namespace n, pg_class c
  WHERE n.oid = c.relnamespace 
    AND nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  ) t on c.table_schema= t.schemaname and c.table_name=t.tablename
  join 
  -- gives dist col
  (select attrelid as tableid, attname as colname from pg_attribute a where
   a.attnum > 0 AND NOT a.attisdropped  AND a.attisdistkey = 't'
  ) d on t.tableid=d.tableid and c.column_name=d.colname

) d on tm.tableid=d.tableid 
where tm.schemaname||'.'||tm.tablename='myschema.mytable'

Ответ 2

Для получения этой информации используйте таблицу PG_TABLE_DEF:

Это выглядит так:

select * from pg_table_def where tablename = 't2';

schemaname|tablename|column|  type   | encoding | distkey |sortkey| notnull 
----------+---------+------+---------+----------+---------+-------+---------
public    | t2      | c1   | bigint  | none     | t       |     0 | f
public    | t2      | c2   | integer | mostly16 | f       |     0 | f
public    | t2      | c3   | integer | none     | f       |     1 | t
public    | t2      | c4   | integer | none     | f       |     2 | f
(4 rows)

Ответ 3

С http://www.postgresonline.com/journal/archives/20- The- Anatomy-of- PostgreSQL- Part-2- Database- Objects.html:

Информационная_схема является очень важной схемой и является частью стандарта ANSI, но не настолько стандартная. Было бы неплохо, если бы все реляционные базы данных его поддерживали, но они не все - MySQL 5, SQL Server (2000+) и PostgreSQL (7. 4+) поддерживают их. Oracle и DB2, очевидно, до сих пор этого не делают, но есть надежда. Для СУБД, поддерживающих информационную_схему, существуют разные уровни, но во всех случаях вы можете быть уверены в том, что найдете таблицы, представления, столбцы с одинаковыми именованными полями, которые содержат полные списки всех таблиц в базе данных, списки просмотров и просмотреть определение DDL и все столбцы, размеры столбцов и типы данных.

Схема pg_catalog - это стандартная метаданные PostgreSQL и основная схема. Здесь вы найдете предопределенные глобальные функции postgres, а также полезные метаданные о вашей базе данных, которые очень специфичны для postgres. Это схема, используемая postgres для управления вещами внутри страны. Большая часть этой информации перекрывается с информацией, найденной в информационной_схеме, но для данных, присутствующих в информационном_схеме, информационная_схема намного проще запрашивать и требует меньше или вообще не объединяется для получения базовой информации.

Таким образом, для базовых запросов вам лучше с помощью information_schema.

На этой странице (http://www.alberton.info/postgresql_meta_info.html) показан прекрасный список запросов для получения информации о вашей схеме. Вот что относится к этому вопросу:

SELECT a.attname
  FROM pg_class c, pg_attribute a, pg_type t
 WHERE c.relname = 'test2'
   AND a.attnum > 0
   AND a.attrelid = c.oid
  AND a.atttypid = t.oid

-- with INFORMATION_SCHEMA:

SELECT column_name
  FROM information_schema.columns
 WHERE table_name = 'test2';

Для подробной информации есть:

SELECT a.attnum AS ordinal_position,
         a.attname AS column_name,
         t.typname AS data_type,
         a.attlen AS character_maximum_length,
         a.atttypmod AS modifier,
         a.attnotnull AS notnull,
         a.atthasdef AS hasdefault
    FROM pg_class c,
         pg_attribute a,
         pg_type t
   WHERE c.relname = 'test2'
     AND a.attnum > 0
     AND a.attrelid = c.oid
     AND a.atttypid = t.oid
ORDER BY a.attnum;

-- with INFORMATION_SCHEMA:

  SELECT ordinal_position,
         column_name,
         data_type,
         column_default,
         is_nullable,
         character_maximum_length,
         numeric_precision
    FROM information_schema.columns
   WHERE table_name = 'test2'
ORDER BY ordinal_position;