Enhance pgimport/pgexport

[turicas]

Tasks:

• (pgimport) Fix --no-create-table so we don't need to pass =true
• (pgimport) Fix error when quotechar is detected as ' (the escape does not work)
• (pgexport) Be able to specify a query instead of table name, so we can export more complex data (this could be used to optimize rows query)
• (pgimport and pgexport) Be able to specify field names, like --fields
• (pgimport and pgexport) Be able to force the CSV dialect
• (pgimport and pgexport) Create docs
• (pgimport) If the table does not exist and we pass --no-create-table an error will not be raised. Decide if rows should raise an error and how PostgreSQL creates the table (field types)
• Update sequences (more info, possible solution)
• Check chunk size for some files (taking too long to detect dialect). Also, print "detecting CSV dialect" while it does not start)

[turicas]

The easiest solution for the sequence update problem is to take schema and table_name, as in:

table_name = ... # (got from the user)
if '.' in table_name:
    schema, table_name = table_name.split('.')
else:
    schema = 'public'

And run the following query on PostgreSQL:

SELECT
 PGT.schemaname AS schema,
 PGT.tablename AS table,
 C.attname AS column,
 S.relname AS sequence
FROM
 pg_class AS S,
 pg_depend AS D,
 pg_class AS T,
 pg_attribute AS C,
 pg_tables AS PGT
WHERE
 S.relkind = 'S' AND
 S.oid = D.objid AND
 D.refobjid = T.oid AND
 D.refobjid = C.attrelid AND
 D.refobjsubid = C.attnum AND
 T.relname = PGT.tablename AND
 PGT.schemaname = '{schema}' AND
 PGT.tablename = '{table}';

Then, for each result, run:

SELECT SETVAL({sequence}, max({column})) FROM {table};

[turicas]

About sequences, @juliano777 sent this file:

-- Criação explícita da sequence:
CREATE SEQUENCE sq_campo;
-- Criação da tabela de teste:
CREATE TABLE tb_sequencia (
    id serial primary key,
    campo int DEFAULT nextval('sq_campo'));

-- Na tabela anterior o campo id usa o pseudo tipo "serial", que nada mais é do
-- que um campo INT com uma sequence atrelada via default.
-- Isso é feito implicitamente.

-- Visualizando a estrutura da tabela com "\d tb_sequencia"
-- Repare que foi criada uma sequence tb_sequencia_id_seq, cujo nome segue um
-- padrão nomedatabela_nomedocampo_seq.
-- Na coluna campo consta o valor padrão como próximo valor da sequence criada.

-- Visualizando via catálogo information schema:

SELECT
    column_default

    FROM information_schema.columns
    WHERE table_name = 'tb_sequencia'
        AND table_schema = 'public';

-- Usando regex para melhorar o resultado:

SELECT
    regexp_replace(
                   column_default,
                   $marcador$nextval\('(.*)'::.*\)$$marcador$,
                   '\1')

    FROM information_schema.columns
    WHERE table_name = 'tb_sequencia'
        AND table_schema = 'public';

[turicas]

https://tapoueh.org/blog/2010/02/resetting-sequences.-all-of-them-please/