postgresql

Import Table Structures

export tables.csv:
export_tables.sh
1
psql -d your_database_name <<-EOF > tables.csv
2
COPY (SELECT c.table_schema,
3
c.table_name,
4
c.column_name,
5
c.udt_name as column_type,
6
c.ordinal_position,
7
case when c.is_nullable = 'YES' then 't' else 'f' end as is_nullable,
8
case when kcu.constraint_name is null then 'f' else 't' end as primary_key,
9
pg_catalog.col_description(format('%s.%s', c.table_schema, c.table_name)::regclass::oid, c.ordinal_position) as column_comment,
10
obj_description(format('%s.%s', c.table_schema, c.table_name)::regclass::oid, 'pg_class') as table_comment
11
FROM information_schema.columns c
12
LEFT JOIN information_schema.key_column_usage as kcu ON
13
kcu.table_catalog = c.table_catalog and
14
kcu.table_schema = c.table_schema and
15
kcu.table_name = c.table_name and
16
kcu.column_name = c.column_name and
17
kcu.position_in_unique_constraint is NULL
18
WHERE c.table_schema not in ('information_schema', 'pg_catalog')
19
ORDER BY 1,2,5
20
) TO STDOUT DELIMITER ',' CSV HEADER
21
EOF
Copied!
You need to replace your_database_name & password & host & username
If there are foreign keys in your database schema, you can export it:
export_relations.sh
1
psql -d your_database_name <<-EOF > relations.csv
2
COPY (
3
SELECT rel_kcu.table_schema AS schema,
4
rel_kcu.table_name AS table,
5
rel_kcu.column_name AS column,
6
kcu.ordinal_position AS no,
7
kcu.table_schema AS relation_table_schema,
8
kcu.table_name AS relation_table,
9
kcu.column_name AS relation_column,
10
kcu.constraint_name
11
FROM information_schema.table_constraints tco
12
JOIN information_schema.key_column_usage kcu
13
ON tco.constraint_schema = kcu.constraint_schema
14
AND tco.constraint_name = kcu.constraint_name
15
JOIN information_schema.referential_constraints rco
16
ON tco.constraint_schema = rco.constraint_schema
17
AND tco.constraint_name = rco.constraint_name
18
JOIN information_schema.key_column_usage rel_kcu
19
ON rco.unique_constraint_schema = rel_kcu.constraint_schema
20
AND rco.unique_constraint_name = rel_kcu.constraint_name
21
AND kcu.ordinal_position = rel_kcu.ordinal_position
22
WHERE tco.constraint_type = 'FOREIGN KEY' AND kcu.table_schema NOT IN ('information_schema', 'pg_catalog')
23
ORDER BY kcu.table_schema,
24
kcu.table_name,
25
kcu.ordinal_position
26
) TO STDOUT DELIMITER ',' CSV HEADER
27
EOF
Copied!
If there are not any foreign keys in your database schema, you don't need to import relations CSV file.
You can use `auto draw` feature to detect relationship. It has rule:
1
users.account_id -> accouts.id
Copied!
and check the AUTO DRAW checkbox:
result:
Last modified 1yr ago
Copy link