Listar todas las secuencias en un Postgres db 8.1 con SQL
Estoy convirtiendo una base de datos de postgres a mysql.
Dado que no puedo encontrar una herramienta que haga el truco por sí misma, voy a convertir todas las secuencias postgres a autoincrement ids en mysql con el valor autoincrement.
Entonces, ¿cómo puedo enumerar todas las secuencias en un Postgres DB (8.1 versión) con información sobre la tabla en la que se utiliza, el siguiente valor, etc con una consulta SQL?
Tenga en cuenta que no puedo usar la vista information_schema.sequences
en la versión 8.4.
14 answers
La siguiente consulta da nombres de todas las secuencias.
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
Típicamente una secuencia se nombra como ${table}_id_seq
. La coincidencia simple de patrones regex le dará el nombre de la tabla.
Para obtener el último valor de una secuencia use la siguiente consulta:
SELECT last_value FROM test_id_seq;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-03-11 10:48:36
Ejecutar: psql -E
y luego \ds
Tenga en cuenta que a partir de PostgreSQL 8.4 puede obtener toda la información sobre las secuencias utilizadas en la base de datos a través de:
SELECT * FROM information_schema.sequences;
Dado que estoy usando una versión superior de PostgreSQL (9.1), y estaba buscando la misma respuesta alta y baja, agregué esta respuesta por el bien de la posteridad y para futuros buscadores.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-06 11:31:01
Después de un poco de dolor, lo tengo.
La mejor manera de lograr esto es enumerar todas las tablas
select * from pg_tables where schemaname = '<schema_name>'
Y luego, para cada tabla, enumere todas las columnas con atributos
select * from information_schema.columns where table_name = '<table_name>'
Luego, para cada columna, pruebe si tiene una secuencia
select pg_get_serial_sequence('<table_name>', '<column_name>')
Y luego, obtener la información acerca de esta secuencia
select * from <sequence_name>
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-10-01 08:08:15
La relación entre las secuencias generadas automáticamente ( como las creadas para columnas SERIE ) y la tabla padre es modelada por el atributo sequence owner.
Puede modificar esta relación utilizando la cláusula OWNED BY del comando ALTER SEQUENCE
E. g. ALTERE LA SECUENCIA foo_id PROPIEDAD de foo_schema.foo_table
Para establecer que se vincule a la tabla foo_table
O ALTER SEQUENCE foo_id PROPIEDAD de NONE
Para romper la conexión entre la secuencia y cualquier tabla
La información sobre esta relación se almacena en la tabla de catálogo pg_depend.
La relación de unión es el enlace entre pg_depend.objid - > pg_class.oid WHERE relkind = ' S ' - que vincula la secuencia al registro de unión y luego pg_depend.refobjid - > pg_class.oid WHERE relkind = 'r', que vincula el registro de unión a la relación propietaria ( tabla)
Esta consulta devuelve toda la secuencia - > tabla dependencias en una base de datos. La cláusula where lo filtra para que solo incluya relaciones generadas automáticamente, lo que lo restringe para que solo muestre secuencias creadas por columnas con tipo SERIE.
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' ;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-07-26 06:49:15
Información de secuencia: valor máximo
SELECT * FROM information_schema.sequences;
Información de secuencia: último valor
SELECT * FROM <sequence_name>
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-10-20 12:28:21
Parcialmente probado pero parece casi completo.
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;
Crédito cuando el crédito es debido... es parcialmente ingeniería inversa a partir del SQL registrado desde una \d en una tabla conocida que tenía una secuencia. Estoy seguro de que podría ser más limpio también, pero bueno, el rendimiento no era una preocupación.
Sé que este post es bastante antiguo, pero encontré la solución de CMS para ser muy útil, ya que estaba buscando una forma automatizada de vincular una secuencia a la tabla y la columna, y quería compartir. El uso de la tabla de catálogo pg_depend fue la clave. Expandí lo que se hizo a:
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' ;
Esta versión añade columna a la lista de campos devueltos. Con el nombre de la tabla y el nombre de la columna a mano, una llamada a pg_set_serial_sequence hace que sea fácil asegurarse de que todos las secuencias en la base de datos se establecen correctamente. Por ejemplo:
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$;
Espero que esto ayude a alguien con el restablecimiento de secuencias!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:02:50
Mejora de la respuesta anterior:
select string_agg('select sequence_name, last_value from ' || relname, chr(13) || 'union' || chr(13) order by relname)
from pg_class where relkind ='S'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-03-31 11:18:22
Una especie de truco, pero prueba esto:
Seleccione 'select "'| | relname / / "'como secuencia, last_value de' | | relname / / 'union' DE pg_catalog. pg_class c DONDE c. relkind IN ('S',");
Elimina la última UNIÓN y ejecuta el resultado
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-01-04 00:25:09
Esta instrucción enumera la tabla y la columna que está asociada con cada secuencia:
Código:
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'
Más ver aquí enlace a la respuesta
Gracias por su ayuda.
Aquí está la función pl/pgsql que actualiza cada secuencia de una base de datos.
---------------------------------------------------------------------------------------------------------
--- 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();
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-07 17:21:29
Aquí hay otro que tiene el nombre del esquema junto al nombre de la secuencia
select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-12-29 14:55:46
Obtiene secuencias por cada columna de cada tabla a través del análisis de la cláusula POR DEFECTO. Este método proporciona información sobre a qué secuencias de columna están vinculadas y no utiliza dependencias que pueden no existir para algunas secuencias. Incluso pg_get_serial_sequence(sch.nspname||'.'||tbl.relname, col.attname)
función encontrado no todas las secuencias para mí!
Solución:
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;
Tenga en cuenta que 1 secuencia se puede utilizar en varias tablas, por lo que se puede enumerar en varias filas aquí.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-08-16 14:12:04