Kamsi's Blog

암복호화를 하기 위한 pgcrypto 확장 기능 적용

CREATE EXTENSION pgcrypto;

암호화 하기

SELECT
   encode(
      encrypt(
         convert_to('암호화 하기 위한 문자열', 'utf8'),
         '암호화Key',
         'aes'
      ),
      'hex'
);

복호화 하기

SELECT
   convert_from(
      decrypt(
         decode('d359bf2938e5ef1cb6dca49dce459654b10e82e4cb3a62c63ef7511df01b6...',
         'hex'
      ),
      '암호화Key',
      'aes'
   ),
   'utf8'
);

테이블 목록 조회

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;

기본키 조회

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

출처 : https://jmap.tistory.com/549

ORACLE의 MERGE INTO와 같은 역할을 하는 구문이 PostgreSQL에서는 다음과 같다.

INSERT INTO example (

USERID, NAME, EMAIL

) VLAUES (

'kamsi76', '홍길동', 'kamsi76@webmaster.com'

)

ON CONFLICT (USERID) DO

UPDATE 

SET 

NAME = '임꺽정', 

EMAIL = 'kamsi76@webmaster.com';

위와 같은 방식으로 하면 된다.

또한 INSERT 할 경우 KEY값이 중복 되었을 때 아무것도 하지 않고자 할 경우

INSERT INTO example (

USERID, NAME, EMAIL

) VLAUES (

'kamsi76', '홍길동', 'kamsi76@webmaster.com'

) ON CONFLICT (USERID) DO NOTHING;

위와 같이 하면 됨..