Thread: 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.
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. >> >> Thank you. > > > Use pg_dump --schema-only > -- > Mike Nolan
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. > > -- > > Mike Nolan
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. > > It will show you the SQL used to generate the output... this may help. > > Ray. > > > -- > Raymond O'Donnell // Galway // Ireland > ray@rodonnell.ie
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. > > -- > Mladen Gogala > Database Consultant > Tel: (347) 321-1217 > https://dbwhisperer.wordpress.com
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 andtherefore used from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function that returns DDL. That'sabout 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
> 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 andtherefore used from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function that returns DDL. That'sabout 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 SQLand therefore used from ODBC/C. This "extension" is nothing PL/PGSQL source code of the function that returns DDL. That'sabout 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. > > -- > 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. 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:
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
On 7/10/22 22:52, Rob Sargent wrote:
Are you alone or on a team?
What are your pronouns?
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
On 7/11/22 06:31, Mladen Gogala wrote: > On 7/10/22 22:52, Rob Sargent wrote: >> Are you alone or on a team? > > What are your pronouns? > This did make me chuckle, but no I am just asking whether or not the OP is currently part of a team.
Hi, On Mon, Jul 11, 2022 at 7:56 AM Rob Sargent <robjsargent@gmail.com> wrote: > > On 7/11/22 06:31, Mladen Gogala wrote: > > On 7/10/22 22:52, Rob Sargent wrote: > >> Are you alone or on a team? No, this is my personal project. Thank you. > > > > What are your pronouns? > > > This did make me chuckle, but no I am just asking whether or not the OP > is currently part of a team. >
> On Jul 11, 2022, at 6:46 PM, Igor Korot <ikorot01@gmail.com> wrote: > > Hi, > >> On Mon, Jul 11, 2022 at 7:56 AM Rob Sargent <robjsargent@gmail.com> wrote: >> >>> On 7/11/22 06:31, Mladen Gogala wrote: >>> On 7/10/22 22:52, Rob Sargent wrote: >>>> Are you alone or on a team? > > No, this is my personal project. > > Thank you. > Do what ever works for you >>> >>> What are your pronouns? >>> >> This did make me chuckle, but no I am just asking whether or not the OP >> is currently part of a team. >>
Has anything been published on Oracle to Postgress migration. I am finding plenty of information about schema migration but, I think this is the easy part. I have a a tremendous amount of SQL and PL/SQL code that needs to be translated. I know Oracle "packages" will not translate. DECODE is not standard and will need to be rewritten as CASE. I have seen feature/function translation matricesto/from Oracle and other database but I can't seem to find one for Postgress. Please point me in the right direction. Dave
On Thu, Jul 14, 2022 at 03:06:58PM -0400, DAVID ROTH wrote:
> Has anything been published on Oracle to Postgress migration.
>
> I am finding plenty of information about schema migration but,
> I think this is the easy part.
> I have a a tremendous amount of SQL and PL/SQL code that needs to be translated. I know Oracle "packages" will not
translate. DECODE is not standard and will need to be rewritten as CASE. I have seen feature/function translation
matricesto/from Oracle and other database but I can't seem to find one for Postgress.
> Please point me in the right direction.
I would start here:
https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Looks good. Thanks > On 07/14/2022 3:10 PM Bruce Momjian <bruce@momjian.us> wrote: > > > On Thu, Jul 14, 2022 at 03:06:58PM -0400, DAVID ROTH wrote: > > Has anything been published on Oracle to Postgress migration. > > > > I am finding plenty of information about schema migration but, > > I think this is the easy part. > > I have a a tremendous amount of SQL and PL/SQL code that needs to be translated. I know Oracle "packages" will not translate. DECODE is not standard and will need to be rewritten as CASE. I have seen feature/function translation matricesto/from Oracle and other database but I can't seem to find one for Postgress. > > Please point me in the right direction. > > I would start here: > > https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion > > -- > Bruce Momjian <bruce@momjian.us> https://momjian.us > EDB https://enterprisedb.com > > Indecision is a decision. Inaction is an action. Mark Batterson
On 7/14/2022 3:06 PM, DAVID ROTH wrote: > Please point me in the right direction. https://www.enterprisedb.com -- Mladen Gogala Oracle DBA Tel: (347) 321-1217 Blog: https://dbwhisperer.wordpress.com