On 10 Jul 2022, at 17:40, Igor Korot wrote:
Hi,
Is there a query I can execute that will give me CREATE TABLE() command
used to create a table?
Thank you.
I am using the following query for that:
select a.attname, a.attnum, a.attnotnull, pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type, case when d.adrelid is not null then pg_catalog.pg_get_expr(d.adbin, d.adrelid) else null end as default_value
from pg_catalog.pg_class c
join pg_catalog.pg_attribute a on c.oid = a.attrelid and a.attnum > 0 and not a.attisdropped
join pg_catalog.pg_namespace n on c.relnamespace = n.oid
left outer join pg_catalog.pg_attrdef d on d.adrelid = a.attrelid and d.adnum = a.attnum and a.atthasdef
where c.relname = %s and n.nspname = %s
order by attnum
;
and the following Python code to format the create table statement:
sql = f"create table {schema}.{name}\n"
sql += f"(\n"
for (last, column) in islast(cursor): column_term = "" if last else "," notnull = " not null" if column.attnotnull else "" default = f" default {column.default_value}" if column.default_value is not None else "" sql += f"\t{column.attname} {column.column_type}{default}{notnull}{column_term}\n"
sql += f");"
return sql
Servus,
Walter