[Postgresql]포스트그레스 postgres 접속 DB환경 카탈로그 조회

1. Linux 환경

      

      

      

    
    

      <bash />
    
#Linux 환경
#Postgresql 접속
#유저명 - U , 디비명 -d
psql -U postgres -d postgres

#sql 파일 실행
psql -h "서버 ip주소" -p "포트번호" -U "유저 아이디" -d "데이터베이스 이름" < "sql 파일 경로" ("은 생략)

ex) psql -U postgres -d postgres < /sw/data/test.sql

 

​

2. postgres 테이블 정보 조회

      

      

      

    
    

      <bash />
    
#테이블 목록 조회

SELECT *
FROM PG_STAT_USER_TABLES;


#컬럼 목록 조회
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = '데이터베이스명'
AND TABLE_NAME = '테이블명'
ORDER BY ORDINAL_POSITION;

#기본키 조회
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


#테이블 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;