Re: [SQL] Looking for a "show create table " equivalent
On 2011-07-12, B.Rathmann 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 ". 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [NOVICE] Compare rows
- Original Message - From: LALIT KUMAR To: Oliveiros d'Azevedo Cristina Hi, I do have order of rows. The problem originally is: I need to set all flag with value 0 to -1 whose next row has flag as 1. This is to be done for each city separetly, i.e. flag of last row of city A need not be changed due to first row of city B. City Date Flag A 31-01-1991 0 A 03-03-1991 1 A 04-04-1991 0 A 06-08-1991 0 A13-01-19921 A12-03-19921 B12-01-19880 B19-03-1999 1 B01-04-19990 B09-06-2001 1 The dates for a villages are in sorted order.Expected output: CityDate Flag A03-03-1991 1 A31-01-1991 -1 A04-04-1991 0 A 06-08-1991 -1 A 13-01-19921 A 12-03-19921 B 12-01-1988-1 B 19-03-1999 1 B 01-04-1999-1 B09-06-2001 1 The (city,Date) is unique for each tuple.Hope I am able to state the problem correctly. Thanks Lalit * Hi, Lalit, First, please always CC to mailing list. You may get help faster from someone with more "savoir faire" than me. Second, Try this out and tell me if it produced the output you intended UPDATE yourTable SET "Flag" = -1 FROM ( SELECT x."Date" as "Date",x."City" as "City",x."Flag" as "Flag" ,MAX(y."Date") as anterior FROM yourTable x JOIN yourTable y ON y."City" = x."City" AND y."Date" < x."Date" GROUP BY x."Date",x."City",x."Flag" ) umq WHERE yourTable."City" = umq."City" and anterior = yourTable."Date" AND yourTable."Flag" = 0 AND umq."Flag" = 1 HTH, Best, Oliver
Re: [SQL] Looking for a "show create table " equivalent
On 14/07/2011 6:22 PM, Jasen Betts wrote: On 2011-07-12, B.Rathmann 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/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
