Re: [SQL] Desc Commnad in pgsql?
TJ O'Donnell wrote: > I like the table and other info from psql > and find I sometimes need that info in my client programs. > Are there any SQL/plpgsql functions that are analogous > to the psql \dt, \ds, etc. commands? If you start psql with the -E option it will show you the queries it used to build the response tables. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Having a mental block with (self) outer joins
Hi, I'm playing around with putting a hierarchy of items into the database. But for some reason I'm having a mental block understanding the following: I have a table category with id and parent_id implementing the typical adjacency model. To get the first two levels of the hierarchy I use: SELECT t1.name as lev1, t2.name as lev2 FROM category t1 LEFT JOIN category t2 ON t2.parent = t1.id WHERE t1.name = 'ROOT' ; Now what I don't understand is that the root node (which has a NULL parent_id) is not selected. My understanding from the outer join is that it would return all items from the "left" tables regardless whether they have a corresponding row in the "right" table. So given the data name, id, parent_id ROOT, 1, NULL CHILD1, 2, 1 CHILD2, 3, 1 I would have expected the following result: ROOT, NULL ROOT, CHILD1 ROOT, CHILD2 but the row with (ROOT,NULL) is not returned. I'm sure I'm missing something very obvious, but what? Thanks in advance Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Having a mental block with (self) outer joins
On Mon, Apr 21, 2008 at 03:48:23PM +0200, Thomas Kellerer wrote: > name, id, parent_id > ROOT, 1, NULL > CHILD1, 2, 1 > CHILD2, 3, 1 > > I would have expected the following result: > > ROOT, NULL > ROOT, CHILD1 > ROOT, CHILD2 > > but the row with (ROOT,NULL) is not returned. why would you expect it? the columns are: parent and child (on your output). you dont have any row that has *parent_id = 1* and id = NULL. you can get this output though: NULL, ROOT ROOT, CHILD1 ROOT, CHILD2 with this query: select p.name as parent, c.name as child from category c left outer join category p on c.parent_id = p.id depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Having a mental block with (self) outer joins
hubert depesz lubaczewski, 21.04.2008 16:05: ROOT, 1, NULL CHILD1, 2, 1 CHILD2, 3, 1 I would have expected the following result: ROOT, NULL ROOT, CHILD1 ROOT, CHILD2 but the row with (ROOT,NULL) is not returned. why would you expect it? the columns are: parent and child (on your output). you dont have any row that has *parent_id = 1* and id = NULL. Ah, of course that's where my mental block was ;) Thanks for the quick response you can get this output though: NULL, ROOT ROOT, CHILD1 ROOT, CHILD2 with this query: select p.name as parent, c.name as child from category c left outer join category p on c.parent_id = p.id If the table contains more levels (i.e. child1 being the parent of another item) this bring others back as well. And I was trying to retrieve the full path for each item (I do know the max. number of levels) Cheers, and thanks a lot for the quick response Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Error: could not find pathkey item to sort
Theerasak Maneeneim <[EMAIL PROTECTED]> writes: >I had some problem with postgresql 8.3.1. I had got an error > message, ERROR: could not find pathkey item to sort, when > I use this query statement: >SELECT INTO wk_fcstrec_dt DISTINCT MAX(fcstrec_dt) > FROM a_cusfcst This is already reported and fixed, thanks. http://archives.postgresql.org/pgsql-bugs/2008-03/msg00275.php regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
