I'm writing an algorithm to populate all tables in a database in Postgres. I already get all tables that have no relationship populated, 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 tables obtained, I make a loop, and for each table I get its properties (column name, data type, accepts null or not, maximum characters):
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME'
With this list of properties I dynamically create the INSERT
INSERT INTO TABLE_NAME(prop1, prop2, prop3) VALUES(value1, value2, value3)
Each value is randomly generated based on the property type, 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).
- And 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 enter the data in this table "Father" save the IDs and then insert In the "Daughters" tables thus associating their foreign keys. But how to do it in a simple way?
It is 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.