Thread: Looking for a "show create table " equivalent
Hello, I've been trying to find out how to find out which sql was run to create a certain table. As I need this in a program which may access the database remotely, using pg_dump --schema-only or psql is not an option (the system my program runs on may not even have those tools installed). Looking at the source of pgadmin3 it seems as if the program collects all info about the given table (columns, indeces etc) and creates the needed SQL by itself, is there no easier way? E.g. MySQL offers a simple "show create table <name>". I am using Postgresql 9.0.4 on Gentoo. Thanks for your help (if there is any documentation which I haven't found yet, just point me to it). Sincerely Bettina
On Tue, 2011-07-12 at 10:33 +0200, B.Rathmann wrote: > [...] > I've been trying to find out how to find out which sql was run to create > a certain table. As I need this in a program which may access the > database remotely, using pg_dump --schema-only or psql is not an option > (the system my program runs on may not even have those tools installed). > Looking at the source of pgadmin3 it seems as if the program collects > all info about the given table (columns, indeces etc) and creates the > needed SQL by itself, is there no easier way? > No. pgAdmin does it this way because it has no other way to do it. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Tue, Jul 12, 2011 at 2:33 AM, B.Rathmann <B.Rathmann@ping.de> wrote: > Hello, > > I've been trying to find out how to find out which sql was run to create > a certain table. As I need this in a program which may access the > database remotely, using pg_dump --schema-only or psql is not an option Maybe turning on some more verbose logging you can see what command is doing that. I've poked around in the pg docs, but don't see it right off. -wes
On Tue, Jul 12, 2011 at 2:33 AM, B.Rathmann <B.Rathmann@ping.de> wrote: > Hello, > > I've been trying to find out how to find out which sql was run to create > a certain table. As I need this in a program which may access the > database remotely, using pg_dump --schema-only or psql is not an option > (the system my program runs on may not even have those tools installed). Look at this for logging: http://www.depesz.com/index.php/2011/05/06/understanding-postgresql-conf-log/ -wes
On 2011-07-12, B.Rathmann <B.Rathmann@ping.de> wrote: > Hello, > > I've been trying to find out how to find out which sql was run to create > a certain table. That's like trying to find what change was used to create $1.83 there several possible answers all but one of them wrong, but many of them may . > As I need this in a program which may access the database remotely, > using pg_dump --schema-only or psql is not an option > (the system my program runs on may not even have those tools installed). why do you think you need this information? > Looking at the source of pgadmin3 it seems as if the program collects > all info about the given table (columns, indeces etc) and creates the > needed SQL by itself, is there no easier way? > > E.g. MySQL offers a simple "show create table <name>". I am using > Postgresql 9.0.4 on Gentoo. can you find what you need to know in the information schema? http://www.postgresql.org/docs/8.4/static/information-schema.html It's an industry standard, and thus should work with every SQL database. -- ⚂⚃ 100% natural
On 14/07/2011 6:22 PM, Jasen Betts wrote: > On 2011-07-12, B.Rathmann<B.Rathmann@ping.de> wrote: >> Hello, >> >> I've been trying to find out how to find out which sql was run to create >> a certain table. > > That's like trying to find what change was used to create $1.83 Yep, and just like that question, sometimes any valid answer is fine. You might not care if it's $1 + .5 + .2 + .02 + .01 or 183 * 0.01, so long as the result is the same. This is turning out to be a reasonably common question, where people want to be able to use functionality of pg_dump from _within_ the database system. If the INFORMATION_SCHEMA isn't sufficient for the OP's purposes or is too clumsy for re-creating DDL from, there's another option. It's ugly, but you can add a plpythonu or plperlu server-side function that calls the server's copy of pg_dump and spits the resulting text back to the client. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/