Re: Get the table creation DDL

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:

```sql
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:

```python
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