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%';
참고
- 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