테이블 목록 조회
1 2 | SELECT RELNAME AS TABLE_NAME FROM PG_STAT_USER_TABLES | cs |
컬럼 목록 조회
1 2 3 4 5 | SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = '데이터베이스명' AND TABLE_NAME = '테이블명' ORDER BY ORDINAL_POSITION; | cs |
기본키 조회
1 2 3 4 5 6 7 8 9 10 | SELECT CC.COLUMN_NAME AS COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC WHERE TC.TABLE_CATALOG = '데이터베이스명' AND TC.TABLE_NAME = '테이블명' AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.TABLE_CATALOG = CC.TABLE_CATALOG AND TC.TABLE_SCHEMA = CC.TABLE_SCHEMA AND TC.TABLE_NAME = CC.TABLE_NAME AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME | cs |
테이블 COMMENT 조회
1 2 3 4 5 6 7 | 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 | cs |
컬럼 COMMENT 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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 | cs |
참조 : http://jmap.tistory.com/549
'DataBase > Postgresql' 카테고리의 다른 글
테이블 사이즈 확인 (0) | 2019.08.13 |
---|---|
role에 속한 계정 조회 (0) | 2019.08.13 |
테이블단위 autovacuum 끄기 (0) | 2019.08.13 |
postgresql 한글정렬 (0) | 2017.10.12 |
postgresql.conf 간단설정 (0) | 2017.10.12 |