Get the table creation DDL
Hi, Is there a query I can execute that will give me CREATE TABLE() command used to create a table? Thank you.
Re: Get the table creation DDL
On Sun, Jul 10, 2022 at 10:28 AM 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. > Use pg_dump --schema-only -- Mike Nolan
Re: Get the table creation DDL
Hi, Michael, On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan wrote: > > > > On Sun, Jul 10, 2022 at 10:28 AM Igor Korot 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 > -- > Mike Nolan
Re: Get the table creation DDL
Hi, On Sun, Jul 10, 2022 at 11:47 AM Igor Korot wrote: > > Hi, Michael, > > On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan wrote: > > > > > > > > On Sun, Jul 10, 2022 at 10:28 AM Igor Korot 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. > > -- > > Mike Nolan
Re: Get the table creation DDL
On 10/07/2022 17:47, Igor Korot wrote: Hi, Michael, On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan wrote: On Sun, Jul 10, 2022 at 10:28 AM Igor Korot 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 It will show you the SQL used to generate the output... this may help. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: Get the table creation DDL
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
Re: Get the table creation DDL
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
Re: Get the table creation DDL
Hi, On Sun, Jul 10, 2022 at 12:47 PM Ray O'Donnell wrote: > > On 10/07/2022 17:47, Igor Korot wrote: > > Hi, Michael, > > > > On Sun, Jul 10, 2022 at 11:13 AM Michael Nolan wrote: > >> > >> > >> > >> On Sun, Jul 10, 2022 at 10:28 AM Igor Korot 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 It means it is possible to have an actal query getting it... Thank you. > > It will show you the SQL used to generate the output... this may help. > > Ray. > > > -- > Raymond O'Donnell // Galway // Ireland > r...@rodonnell.ie
Re: Get the table creation DDL
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
Re: Get the table creation DDL
Hi, On Sun, Jul 10, 2022 at 2:27 PM Mladen Gogala 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. > > -- > Mladen Gogala > Database Consultant > Tel: (347) 321-1217 > https://dbwhisperer.wordpress.com
Re: Get the table creation DDL
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
+---
Re: Get the table creation DDL
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
Re: Get the table creation DDL
Hi,
On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala 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 mgoga
Re: Get the table creation DDL
> On Jul 10, 2022, at 6:16 PM, Igor Korot wrote:
>
> Hi,
>
>> On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala
>> 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
>>
>>
>>|
>> +--
Re: Get the table creation DDL
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
Re: Get the table creation DDL
Hi,
On Sun, Jul 10, 2022 at 8:09 PM Rob Sargent wrote:
>
>
>
> > On Jul 10, 2022, at 6:16 PM, Igor Korot wrote:
> >
> > Hi,
> >
> >> On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala
> >> 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
> >>
> >>
Re: Get the table creation DDL
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
Re: Get the table creation DDL
Hi, On Sun, Jul 10, 2022 at 8:14 PM Mladen Gogala 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. > > -- > Mladen Gogala > Database Consultant > Tel: (347) 321-1217 > https://dbwhisperer.wordpress.com
Re: Get the table creation DDL
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.
