Postgresql 유용한 명령어 모음

 

## 테이블별 rows count

 

// COUNT_TABLE

CREATE OR REPLACE FUNCTION COUNT_TABLE(tableNm TEXT)

RETURNS INTEGER

LANGUAGE PLPGSQL AS

$FUNC$

DECLARE

V_TMP_QUERY varchar(100);

ROWS INTEGER;

BEGIN

V_TMP_QUERY:= 'SELECT COUNT(*) FROM ' || tableNm;

EXECUTE format(V_TMP_QUERY) into ROWS;

RETURN ROWS;

END

$FUNC$;

 

select * from (

SELECT c.relname as "Name",

pg_catalog.obj_description(c.oid, 'pg_class') as "Description",

count_table( c.relname ) as "ROWS_CNT",

TO_CHAR( pg_catalog.pg_table_size(c.oid)/1024 , '999,999,999,999,999') as "Size_MB"

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r','')

AND n.nspname <> 'pg_catalog'

AND n.nspname <> 'information_schema'

AND n.nspname <> 'sys'

AND n.nspname <> 'dbo'

AND n.nspname !~ '^pg_toast'

ORDER BY pg_catalog.pg_table_size(c.oid) desc

)A

where "ROWS_CNT" > 0;

 

 

 

 

# 조회하고자 하는 테이블이 외래키로 참조하고 있는 테이블 및 컬럼 목록 조회

 

SELECT tc.constraint_type,CCU.table_name AS search_table_name

, CCU.column_name AS search_column_name

, KCU.table_name AS foreign_table_name

, KCU.column_name AS foreign_column_name

, KCU.constraint_name AS foreign_constraint_name

FROM information_schema.table_constraints TC

JOIN information_schema.key_column_usage KCU ON TC.constraint_name = KCU.constraint_name

JOIN information_schema.constraint_column_usage CCU ON CCU.constraint_name = TC.constraint_name

WHERE TC.constraint_type = 'FOREIGN KEY'

AND CCU.table_name = 'ft_area_group';

 

 

 

# 조회하고자 하는 테이블을 외래키로 참조하고 있는 테이블 및 컬럼 목록 조회

 

SELECT TC.constraint_type,TC.table_name AS search_table_name

, KCU.column_name AS search_column_name

, CCU.table_name AS foreign_table_name

, CCU.column_name AS foreign_column_name

, CCU.constraint_name AS foreign_constraint_name

FROM information_schema.table_constraints TC

JOIN information_schema.key_column_usage KCU ON KCU.constraint_name = TC.constraint_name

JOIN information_schema.constraint_column_usage CCU ON CCU.constraint_name = TC.constraint_name

WHERE TC.constraint_type = 'FOREIGN KEY'

AND TC.table_name = 'ft_store';

 

 

 

 

# 전체 테이블 컬럼 조회

 

SELECT TABLE_NAME as 테이블이름, column_name as 컬럼명,

is_nullable as null가능여부,

data_type as 데이터타입,

character_maximum_length as 최대길이,

character_octet_length,

numeric_precision,

numeric_precision_radix,

numeric_scale

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = 'DB이름'

and table_schema = 'public'(혹은 스키마 이름)

ORDER BY 테이블이름, ORDINAL_POSITION;

 

 

 

# 테이블 목록 조회

 

SELECT RELNAME AS TABLE_NAME

FROM PG_STAT_USER_TABLES;

 

 

 

# 컬럼 목록 조회

 

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = '데이터베이스명'

AND TABLE_NAME = '테이블명'

ORDER BY ORDINAL_POSITION;

 

 

 

# 테이블 COMMENT 조회

 

SELECT PS.RELNAME AS TABLE_NAME

,PD.DESCRIPTION AS TABLE_COMMENT

FROM PG_STAT_USER_TABLES PS

,PG_DESCRIPTION PD

WHERE PS.RELNAME = '테이블명'

AND PS.RELID = PD.OBJOID

AND PD.OBJSUBID = 0;

 

 

 

# 컬럼 COMMENT 조회

 

 

SELECT PS.RELNAME AS TABLE_NAME

,PA.ATTNAME AS COLUMN_NAME

,PD.DESCRIPTION AS COLUMN_COMMENT

FROM PG_STAT_ALL_TABLES PS

,PG_DESCRIPTION PD

,PG_ATTRIBUTE PA

WHERE PS.SCHEMANAME = (SELECT SCHEMANAME

FROM PG_STAT_USER_TABLES

WHERE RELNAME = '테이블명')

AND PS.RELNAME = '테이블명'

AND PS.RELID = PD.OBJOID

AND PD.OBJSUBID <> 0

AND PD.OBJOID = PA.ATTRELID

AND PD.OBJSUBID = PA.ATTNUM

ORDER BY PS.RELNAME, PD.OBJSUBID ;