Algorithm for popular tables in PostgresSQL

1

I'm writing an algorithm to populate all tables in a database in Postgres. I already get popular all tables that do not have a relationship , as follows:

  • I get all the tables with the following query:

    SELECT table_name
    FROM information_schema.tables
       WHERE table_type = 'BASE TABLE' AND
    table_schema NOT IN ('pg_catalog', 'information_schema')
    
  • For the list of obtained tables, I make a loop, and for each table I get its properties (column name, data type, whether it accepts null or not, maximum characters):

    SELECT COLUMN_NAME, DATA_TYPE,
       IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = 'NOME_DA_TABELA'
    
  • With this list of properties I dynamically add INSERT

    INSERT INTO NOME_TABELA(prop1, prop2, prop3) VALUES(value1, value2, value3)
    

    Each value is randomly generated based on the type of the property, eg:

    • Integer: 65422
    • Character: "Lorem ipsum ..."
    • Date: 2016-12-12 20:00
  • And so on for each data type accepted by the algorithm. If it finds some kind of data that is not accepted, the application terminates (which is expected).

  • Finally, with INSERT mounted, I execute the query in the database
  • The steps in this algorithm work perfectly for tables where there are no relationships .

    For tables that have relationships (1: N, 1: 1, N: N), I would need to first find all the foreign keys and understand which tables they see, to insert the data in this table "Father" save the IDs and then insert into the "Daughters" tables thus associating their foreign keys. But how to do it in a simple way?

    It's interesting to note that I can already get the list of all foreign keys through the following query:

    SELECT conrelid::regclass AS table_from
          ,conname
          ,pg_get_constraintdef(c.oid)
    FROM   pg_constraint c
    JOIN   pg_namespace n ON n.oid = c.connamespace
    WHERE  contype IN ('f', 'p ')
    AND    n.nspname = 'public' -- your schema here
    ORDER  BY conrelid::regclass::text, contype DESC;
    

    I am grateful for suggestion.

    Note: The algorithm is being developed in Python.

        
    asked by anonymous 26.12.2016 / 16:33

    1 answer

    0

    You already have everything you need. Simply implement a recursive function that inserts a row into the FK table before inserting the row of the current table. It's basically doing a search on a tree.

    Here's an example:

    procedimento obter_fks(tabela)
      # Retorna um array com o nome das tabelas
      # Você disse que já tem sabe como obter essa informação
    fim procedimento
    
    procedimento inserir_linha(tabela)
      tabelas_ligadas = obter_fks(tabela)
      chaves_tabelas_ligadas = dicionário zerado
    
      para cada tabela_fk em tabelas_ligadas
        chave = inserir_linha(tabela_fk)
        chaves_tabelas_ligadas[tabela] = chave
      fim para
    
      # Você disse que sua rotina que já é funcional
      # Use a chaves_tabelas_ligadas para popular as FKs
    
      retornar a PK da linha inserida
    fim procedimento
    
        
    24.10.2018 / 22:38