Get the table creation DDL

On Sun, Jul 10, 2022 at 10:28 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
Is there a query I can execute that will give me CREATE TABLE() command
used to create a table?

Thank you.

Use pg_dump --schema-only
--
Mike Nolan

Hi, Michael,

On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan <htfoot@gmail.com> wrote:

On Sun, Jul 10, 2022 at 10:28 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
Is there a query I can execute that will give me CREATE TABLE() command
used to create a table?

So there is no "query" per se?
Also how do I pass the table name?

Thank you.

Hi,

On Sun, Jul 10, 2022 at 11:47 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi, Michael,

On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan <htfoot@gmail.com> wrote:

On Sun, Jul 10, 2022 at 10:28 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
Is there a query I can execute that will give me CREATE TABLE() command
used to create a table?

So there is no "query" per se?
Also how do I pass the table name?

Thank you.

Thank you.

Use pg_dump --schema-only

In addition:

Can I send it to execute with PQexec() or SQLExecDirect()?

It is not a query, but an external command, so I'm wondering...

Thank you.

On 10/07/2022 17:47, Igor Korot wrote:

Hi, Michael,

On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan <htfoot@gmail.com> wrote:

On Sun, Jul 10, 2022 at 10:28 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
Is there a query I can execute that will give me CREATE TABLE() command
used to create a table?

So there is no "query" per se?
Also how do I pass the table name?

If you connect to the database with psql including the -E option, then do

\d <table name>

It will show you the SQL used to generate the output... this may help.

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

I do not know those other tools, but there should be documentation for
them, as there is in the man page for how to process just one table using
pg_dump. You can pipe the output of pg_dump directly to psql, but I find
that's seldom useful.
--
Mike Nolan

On 7/10/22 13:57, Michael Nolan wrote:

I do not know those other tools, but there should be documentation for
them, as there is in the man page for how to process just one table
using pg_dump.   You can pipe the output of pg_dump directly to psql,
but I find that's seldom useful.
--
Mike Nolan

There is an extension which does precisely what you need:

https://github.com/MichaelDBA/pg_get_tabledef

In my opinion, that is a little inconsistency on the part of the
maintainers part because there are functions pg_get_functiondef,
pg_get_indexdef and pg_get_viewdef  but nothing for tables.

https://www.postgresql.org/docs/14/functions-info.html

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Hi,

On Sun, Jul 10, 2022 at 12:47 PM Ray O'Donnell <ray@rodonnell.ie> wrote:

On 10/07/2022 17:47, Igor Korot wrote:

Hi, Michael,

On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan <htfoot@gmail.com> wrote:

On Sun, Jul 10, 2022 at 10:28 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi,
Is there a query I can execute that will give me CREATE TABLE() command
used to create a table?

So there is no "query" per se?
Also how do I pass the table name?

If you connect to the database with psql including the -E option, then do

\d <table name>

It means it is possible to have an actal query getting it...

Thank you.

On 7/10/22 12:47, Igor Korot wrote:

So there is no "query" per se?
Also how do I pass the table name?

Thank you.

You can create one from the catalog tables. Personally, I would use
INFORMATION_SCHEMA to avoid pg_class and pg_attribute. However, there is
an extension which does that for you. Somebody else has already done the
hard work.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Hi,

On Sun, Jul 10, 2022 at 2:27 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:

On 7/10/22 12:47, Igor Korot wrote:

So there is no "query" per se?
Also how do I pass the table name?

Thank you.

You can create one from the catalog tables. Personally, I would use INFORMATION_SCHEMA to avoid pg_class and pg_attribute. However, there is an extension which does that for you. Somebody else has already done the hard work.

I understand.
The Problem is that I need to put this inside the C/ODBC interface for
my project.

I'm sure it is not a problem when people are working out of psql or
writing some scripts,
but for me it is painful to go and try to recreate it.

Now, I'm not sure if this extension can be freely re-used (query
extracted and placed
inside someone else's project).

Thank you.

On 7/10/22 17:00, Igor Korot wrote:

I understand.
The Problem is that I need to put this inside the C/ODBC interface for
my project.

I'm sure it is not a problem when people are working out of psql or
writing some scripts,
but for me it is painful to go and try to recreate it.

Now, I'm not sure if this extension can be freely re-used (query
extracted and placed
inside someone else's project).

Thank you.

Igor, https://github.com/MichaelDBA/pg_get_tabledef provides
"pg_get_tabledef" function which can be called from SQL and therefore
used from ODBC/C. This "extension" is nothing PL/PGSQL source code of
the function that returns DDL. That's about it. This is how it works:

mgogala@umajor Downloads]$ psql -h postgres -f
pg_get_tabledef-main/pg_get_tabledef.sql
Password for user mgogala:
DO
CREATE FUNCTION
[mgogala@umajor Downloads]$ psql -h postgres
Password for user mgogala:
psql (13.6, server 14.4)
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
Type "help" for help.

mgogala=# select pg_get_tabledef('mgogala','emp');
pg_get_tabledef
---------------------------------------------------------------------
 CREATE  TABLE mgogala.emp (                                        +
   empno smallint NOT NULL,                                         +
   ename character varying(10) NULL,                                +
   job character varying(9) NULL,                                   +
   mgr smallint NULL,                                               +
   hiredate timestamp without time zone NULL,                       +
   sal double precision NULL,                                       +
   comm double precision NULL,                                      +
   deptno smallint NULL,                                            +
   CONSTRAINT emp_pkey PRIMARY KEY (empno),                         +
   CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)+
 ) TABLESPACE pg_default;                                           +
                                                                    +

(1 row)

So, you clone the Git repository, run the "CREATE FUNCTION" script and,
voila, you can get the DDL for the desired table. Here is the same stuff
produced by the psql utility:

mgogala=# \d emp
                           Table "mgogala.emp"
  Column  |            Type             | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
 empno    | smallint                    |           | not null |
 ename    | character varying(10)       |           |          |
 job      | character varying(9)        |           |          |
 mgr      | smallint                    |           |          |
 hiredate | timestamp without time zone |           |          |
 sal      | double precision            |           |          |
 comm     | double precision            |           |          |
 deptno   | smallint                    |           |          |
Indexes:
    "emp_pkey" PRIMARY KEY, btree (empno)
Foreign-key constraints:
    "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

And here is using the function from an ODBC connection:

|[mgogala@umajor Downloads]$ isql mgogala-pg 
+---------------------------------------+ | Connected! | | | | 
sql-statement | | help [tablename] | | quit | | | 
+---------------------------------------+ SQL> select 
pg_get_tabledef('mgogala','emp'); 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| pg_get_tabledef | 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| CREATE TABLE mgogala.emp ( empno smallint NOT NULL, ename character 
varying(10) NULL, job character varying(9) NULL, mgr smallint NULL, 
hiredate timestamp without time zone NULL, sal double precision NULL, 
comm double precision NULL, deptno smallint NULL, CONSTRAINT emp_pkey 
PR...| 
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
SQLRowCount returns 1 1 rows fetched SQL> |||

|The function description looks like this:|

|mgogala=# \df pg_get_tabledef

                                List of functions
 Schema |      Name       | Result data type |
                                                   Argument data types
| Typ
e
--------+-----------------+------------------+----------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+----
--
 public | pg_get_tabledef | text             | in_schema character
varying, in_t
able character varying, in_fktype tabledef_fkeys DEFAULT
'FKEYS_INTERNAL'::table
def_fkeys, in_trigger tabledef_trigs DEFAULT
'NO_TRIGGERS'::tabledef_trigs | fun
c
(1 row)
|

|As expected, the function returns the "text" data type.
|

|Regards
|

||

||

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On 7/10/22 20:02, Mladen Gogala wrote:

This "extension" is nothing PL/PGSQL source code

This "extension" is nothing _*but*_ PL/PGSQL source code

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Hi,

On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:

On 7/10/22 17:00, Igor Korot wrote:

I understand.
The Problem is that I need to put this inside the C/ODBC interface for
my project.

I'm sure it is not a problem when people are working out of psql or
writing some scripts,
but for me it is painful to go and try to recreate it.

Now, I'm not sure if this extension can be freely re-used (query
extracted and placed
inside someone else's project).

Thank you.

Igor, https://github.com/MichaelDBA/pg_get_tabledef provides "pg_get_tabledef" function which can be called from SQL and therefore used from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function that returns DDL. That's about it. This is how it works:

I understand.

The question here - does this "extension'' is a part of standard
PostgreSQL install?
And if not - can I copy and paste that code in my program?

Thank you.

On Jul 10, 2022, at 6:16 PM, Igor Korot <ikorot01@gmail.com> wrote:

Hi,

On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:

On 7/10/22 17:00, Igor Korot wrote:

I understand.
The Problem is that I need to put this inside the C/ODBC interface for
my project.

I'm sure it is not a problem when people are working out of psql or
writing some scripts,
but for me it is painful to go and try to recreate it.

Now, I'm not sure if this extension can be freely re-used (query
extracted and placed
inside someone else's project).

Thank you.

Igor, https://github.com/MichaelDBA/pg_get_tabledef provides "pg_get_tabledef" function which can be called from SQL and therefore used from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function that returns DDL. That's about it. This is how it works:

I understand.

The question here - does this "extension'' is a part of standard
PostgreSQL install?
And if not - can I copy and paste that code in my program?

Thank you.

mgogala@umajor Downloads]$ psql -h postgres -f pg_get_tabledef-main/pg_get_tabledef.sql
Password for user mgogala:
DO
CREATE FUNCTION
[mgogala@umajor Downloads]$ psql -h postgres
Password for user mgogala:
psql (13.6, server 14.4)
WARNING: psql major version 13, server major version 14.
Some psql features might not work.
Type "help" for help.

mgogala=# select pg_get_tabledef('mgogala','emp');
pg_get_tabledef
---------------------------------------------------------------------
CREATE  TABLE mgogala.emp (                                        +
empno smallint NOT NULL,                                         +
ename character varying(10) NULL,                                +
job character varying(9) NULL,                                   +
mgr smallint NULL,                                               +
hiredate timestamp without time zone NULL,                       +
sal double precision NULL,                                       +
comm double precision NULL,                                      +
deptno smallint NULL,                                            +
CONSTRAINT emp_pkey PRIMARY KEY (empno),                         +
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)+
) TABLESPACE pg_default;                                           +
+

(1 row)

So, you clone the Git repository, run the "CREATE FUNCTION" script and, voila, you can get the DDL for the desired table. Here is the same stuff produced by the psql utility:

mgogala=# \d emp
Table "mgogala.emp"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
empno | smallint | | not null |
ename | character varying(10) | | |
job | character varying(9) | | |
mgr | smallint | | |
hiredate | timestamp without time zone | | |
sal | double precision | | |
comm | double precision | | |
deptno | smallint | | |
Indexes:
"emp_pkey" PRIMARY KEY, btree (empno)
Foreign-key constraints:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

And here is using the function from an ODBC connection:

[mgogala@umajor Downloads]$ isql mgogala-pg
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select pg_get_tabledef('mgogala','emp');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pg_get_tabledef                                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE  TABLE mgogala.emp (
empno smallint NOT NULL,
ename character varying(10) NULL,
job character varying(9) NULL,
mgr smallint NULL,
hiredate timestamp without time zone NULL,
sal double precision NULL,
comm double precision NULL,
deptno smallint NULL,
CONSTRAINT emp_pkey PR...|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

The function description looks like this:

mgogala=# \df pg_get_tabledef

List of functions
Schema | Name | Result data type |
Argument data types
| Typ
e
--------+-----------------+------------------+----------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+----
--
public | pg_get_tabledef | text | in_schema character varying, in_t
able character varying, in_fktype tabledef_fkeys DEFAULT 'FKEYS_INTERNAL'::table
def_fkeys, in_trigger tabledef_trigs DEFAULT 'NO_TRIGGERS'::tabledef_trigs | fun
c
(1 row)

As expected, the function returns the "text" data type.

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Do it properly. Make it part of your coding infrastructure so it’s available in the next environment. You’ll thank yourself

On 7/10/22 20:15, Igor Korot wrote:

I understand.

The question here - does this "extension'' is a part of standard
PostgreSQL install?
And if not - can I copy and paste that code in my program?

Thank you.

Of course this function is not a part of the standard install. If it was
a part of the standard install, it wouldn't need a separate Git repo on
Gitlab. And of course you can use it in your database, just send me
$1000 and you will have no problems with the license. I believe there is
license file in the repo and it says something like that.

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Hi,

On Sun, Jul 10, 2022 at 8:09 PM Rob Sargent <robjsargent@gmail.com> wrote:

On Jul 10, 2022, at 6:16 PM, Igor Korot <ikorot01@gmail.com> wrote:

Hi,

On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:

On 7/10/22 17:00, Igor Korot wrote:

I understand.
The Problem is that I need to put this inside the C/ODBC interface for
my project.

I'm sure it is not a problem when people are working out of psql or
writing some scripts,
but for me it is painful to go and try to recreate it.

Now, I'm not sure if this extension can be freely re-used (query
extracted and placed
inside someone else's project).

Thank you.

Igor, https://github.com/MichaelDBA/pg_get_tabledef provides "pg_get_tabledef" function which can be called from SQL and therefore used from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function that returns DDL. That's about it. This is how it works:

I understand.

The question here - does this "extension'' is a part of standard
PostgreSQL install?
And if not - can I copy and paste that code in my program?

Thank you.

mgogala@umajor Downloads]$ psql -h postgres -f pg_get_tabledef-main/pg_get_tabledef.sql
Password for user mgogala:
DO
CREATE FUNCTION
[mgogala@umajor Downloads]$ psql -h postgres
Password for user mgogala:
psql (13.6, server 14.4)
WARNING: psql major version 13, server major version 14.
Some psql features might not work.
Type "help" for help.

mgogala=# select pg_get_tabledef('mgogala','emp');
pg_get_tabledef
---------------------------------------------------------------------
CREATE  TABLE mgogala.emp (                                        +
empno smallint NOT NULL,                                         +
ename character varying(10) NULL,                                +
job character varying(9) NULL,                                   +
mgr smallint NULL,                                               +
hiredate timestamp without time zone NULL,                       +
sal double precision NULL,                                       +
comm double precision NULL,                                      +
deptno smallint NULL,                                            +
CONSTRAINT emp_pkey PRIMARY KEY (empno),                         +
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno)+
) TABLESPACE pg_default;                                           +
+

(1 row)

So, you clone the Git repository, run the "CREATE FUNCTION" script and, voila, you can get the DDL for the desired table. Here is the same stuff produced by the psql utility:

mgogala=# \d emp
Table "mgogala.emp"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
empno | smallint | | not null |
ename | character varying(10) | | |
job | character varying(9) | | |
mgr | smallint | | |
hiredate | timestamp without time zone | | |
sal | double precision | | |
comm | double precision | | |
deptno | smallint | | |
Indexes:
"emp_pkey" PRIMARY KEY, btree (empno)
Foreign-key constraints:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

And here is using the function from an ODBC connection:

[mgogala@umajor Downloads]$ isql mgogala-pg
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select pg_get_tabledef('mgogala','emp');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pg_get_tabledef                                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE  TABLE mgogala.emp (
empno smallint NOT NULL,
ename character varying(10) NULL,
job character varying(9) NULL,
mgr smallint NULL,
hiredate timestamp without time zone NULL,
sal double precision NULL,
comm double precision NULL,
deptno smallint NULL,
CONSTRAINT emp_pkey PR...|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

The function description looks like this:

mgogala=# \df pg_get_tabledef

List of functions
Schema | Name | Result data type |
Argument data types
| Typ
e
--------+-----------------+------------------+----------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------------------------+----
--
public | pg_get_tabledef | text | in_schema character varying, in_t
able character varying, in_fktype tabledef_fkeys DEFAULT 'FKEYS_INTERNAL'::table
def_fkeys, in_trigger tabledef_trigs DEFAULT 'NO_TRIGGERS'::tabledef_trigs | fun
c
(1 row)

As expected, the function returns the "text" data type.

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Do it properly. Make it part of your coding infrastructure so it’s available in the next environment. You’ll thank yourself

How should I do that?

Thank you.

On 7/10/22 21:13, Igor Korot wrote:

How should I do that?

Thank you.

Oh boy! I give up.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Hi,

On Sun, Jul 10, 2022 at 8:14 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:

On 7/10/22 21:13, Igor Korot wrote:

How should I do that?

Thank you.

Oh boy! I give up.

Does he mean I need to make it as a GitHub module?
Can I even do that given that you use GitLab and my project is on GitHub?

Thank you.

Do it properly. Make it part of your coding infrastructure so it’s available in the next environment. You’ll thank yourself

How should I do that?

Thank you.

I don't know what build system you are using.  How do get to a running
programme?  Are you alone or on a team? The instruction above on getting
and using the piece you need are spot-on.  It's how you tie that in to
your work configuration that is the next step.

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