SQL이 포함된 Postgres db 8.1의 모든 시퀀스 나열
db를 postgres에서 mysql로 변환하고 있습니다.
트릭 자체를 수행하는 툴을 찾을 수 없기 때문에 모든 postgres 시퀀스를 mysql의 autoincrement ids로 자동 증분 값으로 변환하려고 합니다.
그렇다면 어떻게 Postgres DB(8.1 버전)의 모든 시퀀스를 SQL 쿼리를 통해 사용되는 테이블, 다음 값 등에 대한 정보와 함께 나열할 수 있습니까?
사용할 수 없습니다.information_schema.sequences8.4 릴리스에서 볼 수 있습니다.
다음 쿼리는 모든 시퀀스의 이름을 제공합니다.
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
일반적으로 시퀀스 이름은 다음과 같습니다.${table}_id_seq단순 정규식 패턴 일치를 통해 테이블 이름을 얻을 수 있습니다.
시퀀스의 마지막 값을 가져오려면 다음 쿼리를 사용합니다.
SELECT last_value FROM test_id_seq;
참고로, Postgre부터 시작합니다.SQL 8.4 데이터베이스에서 사용되는 시퀀스에 대한 모든 정보는 다음을 통해 얻을 수 있습니다.
SELECT * FROM information_schema.sequences;
저는 PostgreSQL(9.1)의 상위 버전을 사용하고 있고, 높고 낮은 답을 찾고 있었기 때문에 후세와 미래의 검색자를 위해 이 답을 추가했습니다.
시작하다psql와 함께-Eflag ("에 의해 생성된 실제 쿼리를 표시합니다.\d기타 백슬래시 명령어")를 입력한 다음\ds모든 시퀀스를 나열하는 명령입니다.다음과 같은 내용이 표시됩니다.
# \ds
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+--------------------------------+----------+-------
public | assignments_id_seq | sequence | root
public | framework_users_id_seq | sequence | root
public | lending_items_id_seq | sequence | root
public | proxy_borrower_requests_id_seq | sequence | root
public | roles_id_seq | sequence | root
public | stack_requests_id_seq | sequence | root
(6 rows)
특정 시퀀스를 검사하려면 다음을 실행합니다.\d <sequence name>:
# \d lending_items_id_seq
********* QUERY **********
(...about four queries...)
**************************
Sequence "public.lending_items_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.lending_items.id
약간의 고통 후에, 나는 그것을 얻었습니다.
이를 달성하는 가장 좋은 방법은 모든 테이블을 나열하는 것입니다.
select * from pg_tables where schemaname = '<schema_name>'
그런 다음 각 테이블에 대해 속성이 있는 모든 열을 나열합니다.
select * from information_schema.columns where table_name = '<table_name>'
그런 다음 각 열에 대해 시퀀스가 있는지 여부를 검정합니다.
select pg_get_serial_sequence('<table_name>', '<column_name>')
그런 다음 이 시퀀스에 대한 정보를 가져옵니다.
select * from <sequence_name>
시퀀스 정보: 최대값
SELECT * FROM information_schema.sequences;
시퀀스 정보: 마지막 값
SELECT * FROM <sequence_name>
자동으로 생성된 시퀀스(SERIAL 열에 대해 생성된 시퀀스 등)와 상위 테이블 간의 관계는 시퀀스 소유자 속성에 의해 모델링됩니다.
ALTER SEQUENCE 명령의 OWNED BY 절을 사용하여 이 관계를 수정할 수 있습니다.
예: foo_schema가 소유한 ALTER SEQUENCE foo_id.탁상의
테이블 foo_table에 연결되도록 설정합니다.
또는 다른 시퀀스 foo_id 소유자가 없음
시퀀스와 테이블 간의 연결을 끊다
이 관계에 대한 정보는 pg_depend 카탈로그 테이블에 저장됩니다.
조인 관계는 pg_depend.objid -> pg_class.oid WHERE relkind = 'S' 사이의 링크입니다. 이 링크는 시퀀스를 조인 레코드에 연결한 다음 pg_depend.refobjid -> pg_class.oid WHERE relkind = 'r'입니다. 이 링크는 조인 레코드를 소유 관계(표).
이 쿼리는 데이터베이스의 모든 시퀀스 -> 테이블 종속성을 반환합니다.where 절은 자동 생성 관계만 포함하도록 필터링하며, 이는 직렬 유형 열에 의해 생성된 시퀀스만 표시하도록 제한합니다.
WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname ,
c.relkind, c.relname AS relation
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),
sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),
tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )
SELECT
s.fqname AS sequence,
'->' as depends,
t.fqname AS table
FROM
pg_depend d JOIN sequences s ON s.oid = d.objid
JOIN tables t ON t.oid = d.refobjid
WHERE
d.deptype = 'a' ;
모든 시퀀스 가져오기:
select * from pg_sequences;
PSQL:
\ds
\ds+
\ds *actor*
\ds *actor*시퀀스 이름에 배우 문자가 포함된 모든 시퀀스를 가져옵니다.
이 게시물이 꽤 오래된 것은 알지만, 저는 시퀀스를 테이블과 열에 연결하는 자동화된 방법을 찾고 공유하고 싶었기 때문에 CMS의 솔루션이 매우 유용하다는 것을 알게 되었습니다.pg_depend 카탈로그 테이블 사용이 핵심이었습니다.수행한 작업을 다음과 같이 확장했습니다.
WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname ,
c.relkind, c.relname AS relation
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),
sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),
tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )
SELECT
s.fqname AS sequence,
'->' as depends,
t.fqname AS table,
a.attname AS column
FROM
pg_depend d JOIN sequences s ON s.oid = d.objid
JOIN tables t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid and a.attnum = d.refobjsubid
WHERE
d.deptype = 'a' ;
이 버전에서는 반환되는 필드 목록에 열을 추가합니다.테이블 이름과 열 이름을 모두 손에 쥐고 pg_set_serial_sequence를 호출하면 데이터베이스의 모든 시퀀스가 올바르게 설정되었는지 쉽게 확인할 수 있습니다.예:
CREATE OR REPLACE FUNCTION public.reset_sequence(tablename text, columnname text)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
_sql VARCHAR := '';
BEGIN
_sql := $$SELECT setval( pg_get_serial_sequence('$$ || tablename || $$', '$$ || columnname || $$'), (SELECT COALESCE(MAX($$ || columnname || $$),1) FROM $$ || tablename || $$), true)$$;
EXECUTE _sql;
END;
$function$;
이것이 시퀀스를 재설정하는 데 도움이 되기를 바랍니다!
질문이 postgresql 버전 8에 대한 것이었다는 것을 알지만 버전 10 이상에서 시퀀스를 얻고 싶은 사람들을 위해 이 간단한 방법을 여기에 썼습니다.
아래 쿼리를 사용할 수 있습니다.
select * from pg_sequences
이 문에는 각 시퀀스와 관련된 테이블과 열이 나열됩니다.
코드:
SELECT t.relname as related_table,
a.attname as related_column,
s.relname as sequence_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid
JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid
JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
JOIN pg_namespace n ON n.oid = s.relnamespace
WHERE s.relkind = 'S'
AND n.nspname = 'public'
이전 답변의 개선:
select string_agg('select sequence_name, last_value from ' || relname, chr(13) || 'union' || chr(13) order by relname)
from pg_class where relkind ='S'
select sequence_name, (xpath('/row/last_value/text()', xml_count))[1]::text::int as last_value
from (
select sequence_schema,
sequence_name,
query_to_xml(format('select last_value from %I.%I', sequence_schema, sequence_name), false, true, '') as xml_count
from information_schema.sequences
where sequence_schema = 'public'
) new_table order by last_value desc;
부분적으로 테스트되었지만 대부분 완료된 것으로 보입니다.
select *
from (select n.nspname,c.relname,
(select substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
from pg_catalog.pg_attrdef d
where d.adrelid=a.attrelid
and d.adnum=a.attnum
and a.atthasdef) as def
from pg_class c, pg_attribute a, pg_namespace n
where c.relkind='r'
and c.oid=a.attrelid
and n.oid=c.relnamespace
and a.atthasdef
and a.atttypid=20) x
where x.def ~ '^nextval'
order by nspname,relname;
신용이 있어야 할 곳에 신용...시퀀스가 있는 알려진 테이블의 \d에서 로그온한 SQL에서 부분적으로 역설계되었습니다.더 깨끗해질 수도 있겠지만, 성능은 문제가 되지 않았습니다.
도와주셔서 고마워요.
다음은 데이터베이스의 각 시퀀스를 업데이트하는 pl/pgsql 함수입니다.
---------------------------------------------------------------------------------------------------------
--- Nom : reset_sequence
--- Description : Générique - met à jour les séquences au max de l'identifiant
---------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION reset_sequence() RETURNS void AS
$BODY$
DECLARE _sql VARCHAR := '';
DECLARE result threecol%rowtype;
BEGIN
FOR result IN
WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname ,c.relkind, c.relname AS relation FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),
sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),
tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )
SELECT
s.fqname AS sequence,
t.fqname AS table,
a.attname AS column
FROM
pg_depend d JOIN sequences s ON s.oid = d.objid
JOIN tables t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid and a.attnum = d.refobjsubid
WHERE
d.deptype = 'a'
LOOP
EXECUTE 'SELECT setval('''||result.col1||''', COALESCE((SELECT MAX('||result.col3||')+1 FROM '||result.col2||'), 1), false);';
END LOOP;
END;$BODY$ LANGUAGE plpgsql;
SELECT * FROM reset_sequence();
시퀀스 이름 옆에 스키마 이름이 있는 다른 이름이 있습니다.
select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname
DEFAULT 절의 구문 분석을 통해 각 테이블의 각 열별로 시퀀스를 가져옵니다.이 방법은 연결된 열 시퀀스에 대한 정보를 제공하며 일부 시퀀스에는 존재하지 않을 수 있는 종속성을 사용하지 않습니다.심지어.pg_get_serial_sequence(sch.nspname||'.'||tbl.relname, col.attname)함수가 모든 시퀀스를 찾지 못했습니다!
솔루션:
SELECT
seq_sch.nspname AS sequence_schema
, seq.relname AS sequence_name
, seq_use."schema" AS used_in_schema
, seq_use."table" AS used_in_table
, seq_use."column" AS used_in_column
FROM pg_class seq
INNER JOIN pg_namespace seq_sch ON seq_sch.oid = seq.relnamespace
LEFT JOIN (
SELECT
sch.nspname AS "schema"
, tbl.relname AS "table"
, col.attname AS "column"
, regexp_split_to_array(
TRIM(LEADING 'nextval(''' FROM
TRIM(TRAILING '''::regclass)' FROM
pg_get_expr(def.adbin, tbl.oid, TRUE)
)
)
, '\.'
) AS column_sequence
FROM pg_class tbl --the table
INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
--schema
INNER JOIN pg_attribute col ON col.attrelid = tbl.oid
--columns
INNER JOIN pg_attrdef def ON (def.adrelid = tbl.oid AND def.adnum = col.attnum) --default values for columns
WHERE tbl.relkind = 'r' --regular relations (tables) only
AND col.attnum > 0 --regular columns only
AND def.adsrc LIKE 'nextval(%)' --sequences only
) seq_use ON (seq_use.column_sequence [1] = seq_sch.nspname AND seq_use.column_sequence [2] = seq.relname)
WHERE seq.relkind = 'S' --sequences only
ORDER BY sequence_schema, sequence_name;
하나의 시퀀스를 여러 테이블에서 사용할 수 있으므로 여기에 여러 행으로 나열할 수 있습니다.
이 함수는 각 시퀀스의 last_value를 표시합니다.
시퀀스 이름에 마지막으로 생성된 값을 더한 2개의 열 테이블을 출력합니다.
drop function if exists public.show_sequence_stats();
CREATE OR REPLACE FUNCTION public.show_sequence_stats()
RETURNS TABLE(tablename text, last_value bigint)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
declare r refcursor; rec record; dynamic_query varchar;
BEGIN
dynamic_query='select tablename,last_value from (';
open r for execute 'select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = ''S'' order by nspname';
fetch next from r into rec;
while found
loop
dynamic_query=dynamic_query || 'select '''|| rec.nspname || '.' || rec.relname ||''' "tablename",last_value from ' || rec.nspname || '.' || rec.relname || ' union all ';
fetch next from r into rec;
end loop;
close r;
dynamic_query=rtrim(dynamic_query,'union all') || ') x order by last_value desc;';
return query execute dynamic_query;
END;
$BODY$;
select * from show_sequence_stats();
일종의 해킹이지만, 이것을 시도해 보십시오.
select 'select ''' || relname || ''' as sequence, last_value from ' || relname || ' union'
FROM pg_catalog.pg_class c
WHERE c.relkind IN ('S','');
마지막 UNION을 제거하고 결과를 실행합니다.
을 가정하여exec()이 게시물 https://stackoverflow.com/a/46721603/653539 에서 선언된 함수는 단일 쿼리를 사용하여 시퀀스와 마지막 값을 가져올 수 있습니다.
select s.sequence_schema, s.sequence_name,
(select * from exec('select last_value from ' || s.sequence_schema || '.' || s.sequence_name) as e(lv bigint)) last_value
from information_schema.sequences s
다음은 사용 방법의 예입니다.psql.last_value:
psql -U <username> -d <database> -t -c "SELECT 'SELECT ''' || c.relname || ''' as sequence_name, last_value FROM ' || c.relname || ';' FROM pg_class c WHERE (c.relkind = 'S')" | psql -U <username> -d <database> -t
SELECT nextval(sequence_schema || '.' || sequence_name)
FROM information_schema.sequences
언급URL : https://stackoverflow.com/questions/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql
'source' 카테고리의 다른 글
| 외부 스크립트가 로드되었는지 확인 (0) | 2023.05.07 |
|---|---|
| 클러스터된 인덱스와 비클러스터된 인덱스의 차이 (0) | 2023.05.07 |
| 컬렉션에서 마지막 "x"개의 레코드를 몽고 덤프할 수 있습니까? (0) | 2023.05.07 |
| Excel Interop - 효율성 및 성능 (0) | 2023.05.07 |
| mac OS X에서 mongodb 서비스를 시작하는 방법은 무엇입니까? (0) | 2023.05.07 |