PostgreSQL Table명과 Sequence 함께 찾기

결론

• 간단하게 information_schame.columns를 찾아보자.

 

개요

• Sequence를 재설정하는 경우 SETVAL을 통해 가능하나 테이블명, Sequence 를 쓰는 컬럼명, Sequence 명이 필요
• DB전체의 테이블명, Sequence 를 쓰는 컬럼명, Sequence 명을 취득하는 SQL문을 작성해보기로 함.

 

PostgreSQL의 스키마 구조에 대해

• 우리가 만드는 테이블은 보통 'public'스키마에 담기고,
  내부에서 처리를 위한 테이블은 'information_schema', 'pg_catalog'등에 담긴다.
• 내부 처리용 스키마에는 테이블도 있고 뷰도 있는데, 뷰는 사용자가 원하는 정보를 쉽게 얻기 위해 만들어놓은 것이다.
  예) pg_stat_activity: 현재 활성화된 DB상태를 확인할 때 사용
• Sequence 에 대해서도 여러가지 뷰가 있는데, 테이블과 연계하여 값을 보기에 좋은 뷰는 찾지 못하였다.

 

복잡한 방법 : Query For TableName, PKName, SeqName

• 다른 사람들이 남긴 쿼리문을 보면서 아래와 같이 작성하였다.
• Table, PK, Seq별로 WITH를 만들고 조합하는 형태로 했고,
  SEQ가 없으면 쓸모 없는 쿼리라서 seq는 INNER JOIN으로 했다.
  만약에 SEQ가 없어도 결과를 원한다면 해당부분을 바꿔서 사용하면 될 것이다.
• 지금 하는 작업에서는 PK는 모두 serial이고 Sequence를 이용하기 때문에 PK를 가져왔다.
• Query

WITH tbl AS( 
  SELECT 
    pg_class.oid, 
    pg_class.relname 
  FROM 
    pg_class 
    INNER JOIN pg_namespace 
      ON pg_namespace.oid = pg_class.relnamespace 
  WHERE 
    pg_class.relkind = 'r' 
    AND pg_namespace.nspname = 'public' 
), 
pk AS( 
  SELECT 
    pg_attribute.attrelid ,pg_attribute.attname, pg_index.indrelid  
  FROM 
    pg_attribute 
    INNER JOIN pg_index 
      ON pg_index.indexrelid = pg_attribute.attrelid 
  WHERE 
    pg_index.indisprimary = TRUE 
), 
seq AS( 
  SELECT 
    pg_class.oid, 
    pg_class.relname, 
    pg_depend.refobjid 
  FROM 
    pg_class 
    INNER JOIN pg_depend 
      ON pg_depend.objid = pg_class.oid 
  WHERE 
    pg_class.relkind = 'S'  
) 
SELECT 
  tbl.relname AS table_name, 
  pk.attname AS pk_name, 
  seq.relname AS seq_name 
FROM tbl 
  LEFT JOIN pk 
    ON pk.indrelid = tbl.oid 
  INNER JOIN seq 
    ON seq.refobjid = tbl.oid 
ORDER BY 
  tbl.relname

 

간단한 방법 : information_schema.columns만 query

• 아래와 같은 쿼리문으로 결과를 얻어서
  column_default의 값을 잘 가공하면 seq를 얻을 수 있다.
• column_default의 형식 : nextval('{ sequence _name}'::regclass)

SELECT * FROM information_schema.columns WHERE column_default LIKE 'nextval%';

 

참고

• getPK: https://stackoverflow.com/questions/1214576/how-do-i-get-the-primary-keys-of-a-table-from-postgres-via-plpgsql
• getSeq: https://stackoverflow.com/questions/31540831/in-postgresql-how-to-find-which-table-uses-specific-sequence
• getSimpleSeq: https://dba.stackexchange.com/questions/260975/postgresql-how-can-i-list-the-tables-to-which-a-sequence-belongs/326935#326935