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
