PostgreSQL Table명과 Sequence 함께 찾기

today'work

PostgreSQL Table명과 Sequence 함께 찾기

msna 2023. 12. 24. 17:32

결론

간단하게 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%';

참고

저작자표시 (새창열림)