[SQL] How to view the list of tables?
Hello, list! I need to view the list of tables in a database. In MySQL I can do it with the command "SHOW TABLES". What about PostgreSQL? Can I also see somehow the datatypes of tables' fields? Konstantin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to view the list of tables?
* Konstantin Danilov <[EMAIL PROTECTED]> [0222 10:22]: > > Hello, list! > I need to view the list of tables in a database. In MySQL I can do it with > the command "SHOW TABLES". What about PostgreSQL? > Can I also see somehow the datatypes of tables' fields? \dt in psjl lists tables ( \d gives you things like sequences as well) \d tablename shows its layout. -- 'Tempers are wearing thin. Let's hope some robot doesn't kill everybody.' -- Bender Rasputin :: Jack of All Trades - Master of Nuns ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [GENERAL] How to view the list of tables?
Konstantin Danilov wrote: Hello, list! I need to view the list of tables in a database. In MySQL I can do it with the command "SHOW TABLES". What about PostgreSQL? Can I also see somehow the datatypes of tables' fields? Konstantin "man psql" will show you details of how to operate the psql application, or when in it try "\?" and "\h" to get help. We also now support the SQL-standard "information schema". You'll also find the manuals have this information - available with your installation and also online at http://www.postgresql.org/docs/ HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to view the list of tables?
* Dick Davies <[EMAIL PROTECTED]> [0241 10:41]: > * Konstantin Danilov <[EMAIL PROTECTED]> [0222 10:22]: > > > > Hello, list! > > I need to view the list of tables in a database. In MySQL I can do it with > > the command "SHOW TABLES". What about PostgreSQL? > > Can I also see somehow the datatypes of tables' fields? > > \dt in psjl lists tables that should be 'psql', obviously. :) -- 'Tempers are wearing thin. Let's hope some robot doesn't kill everybody.' -- Bender Rasputin :: Jack of All Trades - Master of Nuns ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to view the list of tables?
Hello, Konstantin. You wrote February, 15 2005 г., 15:16:57: KD> I need to view the list of tables in a database. In MySQL I KD> can do it with the command "SHOW TABLES". What about PostgreSQL? All tables: select ... from pg_catalog.pg_class where c.relkind='r'; All tables of the public schema: select ... from pg_catalog.pg_class c join pg_catalog.pg_namespace n on c.relnamespace=n.oid where c.relkind='r' and n.nspname='public'; KD> Can I also see somehow the datatypes of tables' fields? select ... from pg_catalog.pg_class c join pg_catalog.pg_attribute a on c.oid=a.attrelid join pg_catalog.pg_type t on a.atttypid=t.oid See also: \d pg_class \d pg_namespace \d pg_attribute \d pg_type -- rgds, Mihail mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Constraint doesn't see a currently insertet record
Thanks. I managed it via a trigger. > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Sonntag, 13. Februar 2005 02:57 > To: KÖPFERL Robert > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Constraint doesn't see a currently insertet record > > > On Fri, Feb 11, 2005 at 07:10:50PM +0100, KÖPFERL Robert wrote: > > > Another Idea was to make a trigger. But BTW how do I access > a trigger > > parameter if my trigger function must not have any > parameter?? > > PL/pgSQL triggers can access arguments via TG_ARGV. > > http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Working with XML.
Title: Message Hi Theo, I'm not sure if it can be done with the xml contrib module. You may want to install and work with the xml2 contrib module, which is more recent, has more extensive capabilities, and is easier to work with. It will give you the result you want: jan28-05=# select xpath_string(jan28-05(# '284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//query/@rows') as rows; rows-- 100(1 row) Another example: jan28-05=# select xpath_string(jan28-05(# '284122789648 {ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//row[cola=284122]/colb') as colb; colb 789648(1 row) HTH, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: 'pgsql-sql@postgresql.org' Sent: Monday, February 14, 2005 9:46 PM Subject: RE: [SQL] Working with XML. Thanks George. How do you get an attributes value the following returns the attribute tag. i.e. rows="100", all i want is the 100. select pgxml_xpath('284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','') Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39 AMTo: Theo Galanakis; pgsql-sql@postgresql.orgSubject: Re: [SQL] Working with XML. Hi Theo, I am not aware of any means of passing xml to stored procedures, apart from writing your own function to parse the xml. In regard to your second question - how to access the second record - try using a more explicit xpath query incorporating a "where" component. For example, if you wanted to access the second row based upon the value of cola, you could use '//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of the record if you know its position: '//query/row[2]/text()'. Microsoft has a very good reference on xpath expressions: http://msdn.microsoft.com/library/default.asp?url="">. Regards, George - Original Message - From: Theo Galanakis To: pgsql-sql@postgresql.org Sent: Sunday, February 13, 2005 11:48 PM Subject: [SQL] Working with XML. Hi Folks, Is there a way to pass in an xml string into a stored proc and thenplace this into a temp table? I use to be able to do this in sql server, it was quite handy as I could call one stored proc to update multiple records, here is a sample in sql server: select CoverTypeID, ItemSQ, SituationID, ItemDescription, CoverAmount From OpenXML ( @XmlHandle, '/cover/covertype/item',1 ) With ( CoverTypeID int '../@id', ItemSQ int '@id', SituationID int '@situationID', ItemDescription varchar(100) '@description', CoverAmount money '@amount' ) I have managed to get get pgxml_xpath working, however Im not sure how to access specific rows in an xml document. E.g below there are two records, how do I access the second record, the following returns both ,'//query/row/cola values being (284122,525887): select pgxml_xpath( '284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65525887493253{ts ''2005-02-14 16:13:18''}6uydk442uz247ga45kpys7htkxznkn8La31qhn942wu2cu2pdr25mv2nup2zh3vcbh3c4vdauak3p3w093cvtkeyga692b455cr3' ,'//query/row/cola/text()','','') __This email, inclu
[SQL] Making NULL entries appear first when ORDER BY ASC
Hi, I have the following table, with the query below to list entries from it where start_date IS NOT NULL: CREATE TABLE onp_crm_activity_log( id serial PRIMARY KEY, start_date timestamp, start_time timestamp, end_time timestamp, title varchar NOT NULL ); SELECT start_date, start_time, end_time, title FROM onp_crm_activity_log WHERE start_date IS NOT NULL ORDER BY start_date ASC, start_time ASC; start_date | start_time | end_time | title -+-+-+--- 2005-02-03 00:00:00 | 2005-02-03 08:00:00 | | Something 2005-02-03 00:00:00 | 2005-02-03 09:00:00 | 2005-02-03 12:00:00 | Something 2005-02-03 00:00:00 | | | Something Now, as you see, touples with NULL in the "start_time"-field appear "after" the others. I would like to make all entries where start_time IS NULL apear *before* all the others. Any idea how to achieve this? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ pgpcVCtrEQCSu.pgp Description: PGP signature
Re: [SQL] Working with XML.
Title: Message Hi Theo, You can find the source code for xml2 it in the 8 source tree http://www.postgresql.org/download/. If you're working with an earlier version of PostgreSQL than 8, you may have to make some modifications to the contrib code to get it to compile and link properly - I should be able to help you with that. I don't think you can use it on any version earlier than 7.2. Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Sent: Tuesday, February 15, 2005 4:30 PM Subject: RE: [SQL] Working with XML. Hi George, Thanks for your help once again. However I cant seem to find XML2, the contrib package for RedHat ES3 I downloaded only has xml. Where can I find the compiled or source code. The only link I have is http://developer.postgresql.org/docs/pgsql/contrib/ , it there ftp access or cvs access to contrib? Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, 16 February 2005 12:37 AMTo: Theo GalanakisCc: pgsql-sql@postgresql.orgSubject: Re: [SQL] Working with XML. Hi Theo, I'm not sure if it can be done with the xml contrib module. You may want to install and work with the xml2 contrib module, which is more recent, has more extensive capabilities, and is easier to work with. It will give you the result you want: jan28-05=# select xpath_string(jan28-05(# '284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//query/@rows') as rows; rows-- 100(1 row) Another example: jan28-05=# select xpath_string(jan28-05(# '284122789648 {ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,'//row[cola=284122]/colb') as colb; colb 789648(1 row) HTH, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: 'pgsql-sql@postgresql.org' Sent: Monday, February 14, 2005 9:46 PM Subject: RE: [SQL] Working with XML. Thanks George. How do you get an attributes value the following returns the attribute tag. i.e. rows="100", all i want is the 100. select pgxml_xpath('284122789648{ts ''2005-02-14 16:13:18''}unbnda8m5946z55sgi1xco34h1q9tLonoys3nyk1d5tgtLaw8h5wya0zdv7vigeiuk9xqLuacdy0dsisopb8g1o4o76090otmq65243151750292{ts ''2005-02-14 16:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r','//query/@rows','','') Theo -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 February 2005 12:39 AMTo: Theo Galanakis; pgsql-sql@postgresql.orgSubject: Re: [SQL] Working with XML. Hi Theo, I am not aware of any means of passing xml to stored procedures, apart from writing your own function to parse the xml. In regard to your second question - how to access the second record - try using a more explicit xpath query incorporating a "where" component. For example, if you wanted to access the second row based upon the value of cola, you could use '//query/[EMAIL PROTECTED]'525887']/text()'. Or you could specify the position of the record if you know its position: '//query/row[2]/text()'. Microsoft has a very good reference on xpath expressions: http://msdn.microsoft.com/library/default.asp?url="">. Regards, George - Original Message - From: Theo Galanakis To: pgsql-sql@postgresql.org Sent: Sunday, February 13, 2005 11:48 PM Subject: [SQL] Working with
Re: [SQL] Making NULL entries appear first when ORDER BY ASC
while you weren't looking, Andreas Joseph Krogh wrote: > Any idea how to achieve this? ... ORDER BY coalesce(start_date, '1900-01-01') ASC , coalesce(start_time, '1900-01-01') ASC; /rls -- :wq ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Making NULL entries appear first when ORDER BY
On Wed, 2005-02-16 at 00:55 +, Andreas Joseph Krogh wrote: > SELECT start_date, start_time, end_time, title > FROM onp_crm_activity_log > WHERE start_date IS NOT NULL > ORDER BY start_date ASC, start_time ASC; > > start_date | start_time | end_time | title > -+-+-+--- > 2005-02-03 00:00:00 | 2005-02-03 08:00:00 | | Something > 2005-02-03 00:00:00 | 2005-02-03 09:00:00 | 2005-02-03 12:00:00 | Something > 2005-02-03 00:00:00 | | | Something > > > Now, as you see, touples with NULL in the "start_time"-field appear "after" > the others. I would like to make all entries where start_time IS NULL apear > *before* all the others. Any idea how to achieve this? how about ORDER BY start_date , COALESCE(start_time,'0001-01-01 00:00:00'::timestamp) ? gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Making NULL entries appear first when ORDER BY ASC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Now, as you see, touples with NULL in the "start_time"-field > appear "after" the others. I would like to make all entries > where start_time IS NULL apear *before* all the others. ORDER BY start_date, CASE WHEN start_time IS NULL THEN 0 ELSE 1 END, start_time - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200502152309 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCEseYvJuQZxSWSsgRAlipAJwKAyqAyLbo9hfpoWkz0SOlTY3feACfa+ng DqNY4DAJ5TeeGQbI+smNilg= =LRhP -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Making NULL entries appear first when ORDER BY ASC
> > Now, as you see, touples with NULL in the "start_time"-field appear "after" > the others. I would like to make all entries where start_time IS NULL apear > *before* all the others. Any idea how to achieve this? SELECT start_date, start_time, end_time, title FROM onp_crm_activity_log WHERE start_date IS NOT NULL ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC; This assumes you want the NULL start times first within a particular date. Otherwise change the order in the ORDER BY clause. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
