[SQL] to count no of columns in a table

[AKHILESH GUPTA]

hi all,

i just want to know one thing that is there any function in PGSQL which gives me the total number of columns in a table.

OR

just like we are using count(*), it gives us total number or rows in a
table, just like i want to know the total number of columns present in
the table

plz help me it's urgent

THANKS IN ADVANCE!

-- 
Thanks & Regards,
Akhilesh
DAV Institute of Management
Faridabad(Haryana)-INDIA

"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"

Re: [SQL] to count no of columns in a table

[Juris]

Create view/storedproc on  pg_class & pg_attribute  tables

2006/2/16, AKHILESH GUPTA <[EMAIL PROTECTED]>:
> hi all,
>  i just want to know one thing that is there any function in PGSQL which
> gives me the total number of columns in a table.
>  OR
>  just like we are using count(*), it gives us total number or rows in a
> table, just like i want to know the total number of columns present in the
> table
>  plz help me it's urgent
>  THANKS IN ADVANCE!
>
>  --
>  Thanks & Regards,
>  Akhilesh
>  DAV Institute of Management
>  Faridabad(Haryana)-INDIA
>
>  "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
>

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] to count no of columns in a table

[A. Kretschmer]

am  16.02.2006, um 14:43:43 +0530 mailte AKHILESH GUPTA folgendes:
> hi all,
> i just want to know one thing that is there any function in PGSQL which
> gives me the total number of columns in a table.

select count(column_name) from information_schema.columns where 
table_name='foo';


HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47215,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] to count no of columns in a table

[Ragnar]

On fim, 2006-02-16 at 14:43 +0530, AKHILESH GUPTA wrote:
> i just want to know one thing that is there any function in PGSQL
> which gives me the total number of columns in a table.
> OR
> just like we are using count(*), it gives us total number or rows in a
> table, just like i want to know the total number of columns present in
> the table

this is the kind of thing the information_schema is for

  select count(*) from information_schema.columns 
  where table_name='x';


gnari



---(end of broadcast)---
TIP 1: 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

[SQL] VIEWs and TEMP tables problem

[Antal Attila]

Hi!

I found a problem with the views in PostgreSQL if I want to use 
temporary tables in it.  See the next case!


CREATE TABLE a(, code INT4,...);
INSERT INTO a(...,code,...) VALUES (...,23,...);
CREATE TABLE actual_code(code INT4);

If I execute the next query, the result is empty.
   SELECT * FROM actual_code;

CREATE VIEW a_view AS SELECT * FROM a JOIN actual_code AC ON (a.code = 
AC.code);


CREATE TEMP TABLE actual_code AS SELECT 23::INT4 AS code;

If I execute the next query, the result contains exactly one row (code: 
23). This is perfect.

   SELECT * FROM actual_code;

After it if I compare the the next two queries, there will be 
differences in the results.


1) SELECT * FROM a JOIN actual_code AC ON (a.code = AC.code);  (Result 
has one row!)

2) SELECT * FROM a_view; (Result is empty!)

In my opinion this queries should be equivalent with same results.
The problem is that the view use the original permanent table, but the 
1) query use the temporary actual_code table.
I read the temporally table definition in the documentation 
(http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html) 
and I concluded it should be working.


Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?

This construction came to my mind, because I tried to solve my another 
question: 
http://groups.google.co.hu/group/pgsql.performance/browse_thread/thread/c7aec005f4a1f3eb/83fa0053cad33dea


Thanks your ideas!
Regards,
Antal Attila

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] VIEWs and TEMP tables problem

[Richard Huxton]

Antal Attila wrote:

Hi!

I found a problem with the views in PostgreSQL if I want to use 
temporary tables in it.  See the next case!

[snip]
The problem is that the view use the original permanent table, but the 
1) query use the temporary actual_code table.
I read the temporally table definition in the documentation 
(http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html) 
and I concluded it should be working.


Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?


Feature, I'd guess.

When the view was built there was no TEMP TABLE. If you do \d a_view 
you'll see it actually will link to something like "public.a". If it 
didn't, your view could change depending on your search_path settings 
and even break.


You'll see a similar problem with plpgsql functions.

This construction came to my mind, because I tried to solve my another 
question: 
http://groups.google.co.hu/group/pgsql.performance/browse_thread/thread/c7aec005f4a1f3eb/83fa0053cad33dea 


I think you need to explain why you're trying to do this:
   CREATE VIEW ab_view AS
SELECT a.id AS id,
   a.userid AS userid_a, b.userid AS userid_b,
   a.col AS col_a, b.col AS col_b
FROM a LEFT JOIN b ON (a.id = b.a_id);

EXPLAIN ANALYSE SELECT * FROM ab_view
WHERE userid_a = 23 AND userid_b = 23 AND col_a LIKE 's%'
ORDER BY col_b
LIMIT 10 OFFSET 10;

If you want userid_a=userid_b ALWAYS to be the same, just put it in the 
view. If you sometimes want them different, then you'll have to provide 
two parameters anyway. It's not clear how you intend to use this.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 1: 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] VIEWs and TEMP tables problem

[Markus Schaber]

Hi, Antal,

Antal Attila wrote:

> CREATE VIEW a_view AS SELECT * FROM a JOIN actual_code AC ON (a.code =
> AC.code);

Here, you bind the View to the permanent table.

> CREATE TEMP TABLE actual_code AS SELECT 23::INT4 AS code;

And here you create the temp table that will hide the permanent table.

> I read the temporally table definition in the documentation
> (http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html)
> and I concluded it should be working.

I would not think so, as it states "unless they are referenced with
schema-qualified names". So the permanent table is not removed, only
hidden in the default (non-qualified) name space.

Views don't look up the tables by name. Views bind to the table via
internal ids, and this binding is strong enough to survive even a table
rename, still referencing the same table.

> Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?

It is a feature.

> This construction came to my mind, because I tried to solve my another
> question:
> http://groups.google.co.hu/group/pgsql.performance/browse_thread/thread/c7aec005f4a1f3eb/83fa0053cad33dea

Maybe you try:
CREATE VIEW ab_view AS
SELECT a.id AS id,
   a.userid AS userid,
   a.col AS col_a, b.col AS col_b
FROM a LEFT JOIN b ON (a.id = b.a_id AND a.userid=b.userid);

EXPLAIN ANALYSE SELECT * FROM ab_view
WHERE userid = 23 AND col_a LIKE 's%'
ORDER BY col_b
LIMIT 10 OFFSET 10;

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] How to force PostgreSQL using an index

[Daniel Caune]

> -Message d'origine-
> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : mercredi, février 15, 2006 17:47
> À : Daniel Caune
> Cc : Andrew Sullivan; pgsql-sql@postgresql.org
> Objet : Re: [SQL] How to force PostgreSQL using an index
> 
> "Daniel Caune" <[EMAIL PROTECTED]> writes:
> > SELECT 
> >   FROM GSLOG_EVENT
> >   WHERE EVENT_NAME = 'player-status-update'
> > AND EVENT_DATE_CREATED >= 
> > AND EVENT_DATE_CREATED < 
> 
> > I have an index on EVENT_DATE_CREATED that does it job.  But I though
> > that I can help my favourite PostgreSQL if I create a composite index on
> > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
> > is more dense that EVENT_NAME).
> 
> Wrong ... should be EVENT_NAME first.  Think about the sort order of the
> data to see why --- your query represents a contiguous subset of the
> index if EVENT_NAME is first, but not if EVENT_DATE_CREATED is first.
> 
>   regards, tom lane

Yes, you're right!

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] VIEWs and TEMP tables problem

[Tom Lane]

Markus Schaber <[EMAIL PROTECTED]> writes:
> Antal Attila wrote:
>> Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?

> It is a feature.

Right.  Note that one possible answer is, after you make the temp table,
to create a temp view referencing the temp table (and hiding the
permanent view in the same way as the temp table hides the permanent table).

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] Using calculated columns as arguments in same SELECT

[Michael Burke]

Hello,

I am looking to simplify this query (uses PostGIS, but I have encountered this 
scenario with other chains of functions):

gtest=# SELECT X(
  SetSRID(
Transform(
  GeomFromText(
'POINT(142512 1020225)', 26910
  ), 4326
),
  -1)
) as xcoord, Y(
  SetSRID(
Transform(
  GeomFromText(
'POINT(142512 1020225)', 26910
  ), 4326
),
  -1)
) AS ycoord;

This SELECT works, but the rather long arguments to X(geom) and Y(geom) are 
the same.  Is it possible and feasible to pre-calculate the argument, such 
as:

gtest=# SELECT 
  SetSRID(
Transform(
  GeomFromText(
'POINT(142512 1020225)', 26910
  ), 4326
),
  -1) AS transformed_geom,
  X(transformed_geom) AS xcoord,
  Y(transformed_geom) AS ycoord

Where I don't really care about transformed_geom being returned, but it saves 
double-calling the inside functions  This doesn't work -- it complains that 
transformed_geom is not a column.

SELECT version(); gives:
PostgreSQL 8.0.6 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2 
[FreeBSD] 20040728

Thanks in advance!
Mike.

-- 
Michael Burke
Engineering Technologies Canada Ltd.
[EMAIL PROTECTED]  1 (902) 628-1705

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Using calculated columns as arguments in same SELECT

[Michael Burke]

On February 16, 2006 11:07 am, Michael Burke wrote:
> Hello,
>
> I am looking to simplify this query (uses PostGIS, but I have encountered
> this scenario with other chains of functions):
>
-- snip --

Immediately after sending this one, I realized I could do it with a 
sub-select:

gtest=# SELECT X(SubSel.transformed_geom), Y(SubSel.transformed_geom) FROM (
  SELECT SetSRID(
Transform(
  GeomFromText(
'POINT(142512 1020225)', 26910
  ), 4326
),
  -1) AS transformed_geom) SubSel;

This works fine.
Mike.

-- 
Michael Burke
Engineering Technologies Canada Ltd.
[EMAIL PROTECTED]  1 (902) 628-1705

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] group by complications

[Mark Fenbers]

Wow!  I didn't know you could have a (select ...) as a replacement for
a 'from' table/query.  Your SQL worked as-is, except I had to add a
'limit 1' to the first subquery.

Thanks!  I would have never figured that out on my own!

Mark

chester c young wrote:

  --- Mark Fenbers <[EMAIL PROTECTED]> wrote:

  
  
select l.lid,l.fs,max(h.obstime) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;

The above query works as expected in that is fetches the lid, fs and 
time of the latest observation in the height table (for the 
corresponding lid), but I also want to fetch (i.e., add to the select
list) the corresponding reading (h.obsvalue) which occurs at 
max(h.obstime).  I'm having trouble formulating the correct SQL
syntax 
to pull out the l.lid, l.fs, and the most recent h.obvalue (with or 
without the time that it occurred).

Logistically, I want to do something like this:

select l.lid,l.fs,most_recent(h.obsvalue) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;


  
  
use your original query as part of the from clause, then add columns to
it through a subquery or a join.  try something like this:

select q1.*,
(select obsvalue from height where lid=q1.lid and obstime=q1.obstime)
  as obsvalue
from
(select l.lid,l.fs,max(h.obstime) as obstime1 from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs ) q1;


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

  

[SQL] Using EXPLAIN-ANALYZE

[Kashmira Patel \(kupatel\)]

Hi 
all,
   I am 
pretty new to using Postrgres, and have been looking at the messages in this 
forum for a while. I have noticed that the use of EXPLAIN-ANALYZE is recommended 
quite a lot. I read the Postgres docs, but am not quite sure I understand how 
this works. Is there some tutorial or any other documentation how this can be 
used?
 
Thanks a 
lot,
Kashmira 
Patel

Re: [SQL] Using EXPLAIN-ANALYZE

[Andrew Sullivan]

On Thu, Feb 16, 2006 at 12:07:10PM -0800, Kashmira Patel (kupatel) wrote:
> Hi all,
>I am pretty new to using Postrgres, and have been looking at the
> messages in this forum for a while. I have noticed that the use of
> EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres docs,
> but am not quite sure I understand how this works. Is there some
> tutorial or any other documentation how this can be used?

Well, here's the short version:

EXPLAIN [query] tells you what the planner _thinks_ it should do.

EXPLAIN ANALYZE [query] tells you what the planner thinks it should
do, and also executes the query and reports back how long every step
took, how many rows were returned, &c.  (For this reason, you want to
wrap it in BEGIN;...;ROLLBACK; if it changes data.)

For more, see the EXPLAIN EXPLAINED tutorial on
techdocs.postgresql.org
()

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
--Brad Holland

---(end of broadcast)---
TIP 1: 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] Using EXPLAIN-ANALYZE

[Abhishek]

http://www.postgresql.org/docs/7.2/static/performance-tips.html
 
EXPLAIN is explaied quite nicely with examples here. Hope that helps
 
Bests
AJ 
On 2/16/06, Kashmira Patel (kupatel) <[EMAIL PROTECTED]> wrote:

Hi all,
   I am pretty new to using Postrgres, and have been looking at the messages in this forum for a while. I have noticed that the use of EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres docs, but am not quite sure I understand how this works. Is there some tutorial or any other documentation how this can be used?

 
Thanks a lot,
Kashmira Patel-- Bests,AJ 

Re: [SQL] Using EXPLAIN-ANALYZE

[Kashmira Patel \(kupatel\)]

So I would have to put in lots of rows of data in the table before using
the explain command? 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
Sent: Thursday, February 16, 2006 12:39 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Using EXPLAIN-ANALYZE

On Thu, Feb 16, 2006 at 12:07:10PM -0800, Kashmira Patel (kupatel)
wrote:
> Hi all,
>I am pretty new to using Postrgres, and have been looking at the 
> messages in this forum for a while. I have noticed that the use of 
> EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres docs, 
> but am not quite sure I understand how this works. Is there some 
> tutorial or any other documentation how this can be used?

Well, here's the short version:

EXPLAIN [query] tells you what the planner _thinks_ it should do.

EXPLAIN ANALYZE [query] tells you what the planner thinks it should do,
and also executes the query and reports back how long every step took,
how many rows were returned, &c.  (For this reason, you want to wrap it
in BEGIN;...;ROLLBACK; if it changes data.)

For more, see the EXPLAIN EXPLAINED tutorial on techdocs.postgresql.org
()

A

--
Andrew Sullivan  | [EMAIL PROTECTED]
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. 
--Brad Holland

---(end of broadcast)---
TIP 1: 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Using EXPLAIN-ANALYZE

[Owen Jacobson]

Kashmira Patel wrote:

> So I would have to put in lots of rows of data in the table 
> before using the explain command? 

No, but PostgreSQL's query planner may take a different approach for a small 
table than a large one.  The statistics used are generated during VACUUM 
ANALYZE/VACUUM FULL operations and, under 8.1, are probably maintained by 
autovacuum, but you can always vacuum manually and see if that changes the 
query plan.

-Owen

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Using EXPLAIN-ANALYZE

[Andrew Sullivan]

On Thu, Feb 16, 2006 at 01:08:40PM -0800, Kashmira Patel (kupatel) wrote:
> So I would have to put in lots of rows of data in the table before using
> the explain command? 

Well, no, but you won't get useful information without it. 
PostgreSQL has a cost-based optimizer.  The query plan is affected
by the nature of your data.  That's what the ANALYZE command (on its
own, or with VACUUM) is for.

A

> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
> Sent: Thursday, February 16, 2006 12:39 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Using EXPLAIN-ANALYZE
> 
> On Thu, Feb 16, 2006 at 12:07:10PM -0800, Kashmira Patel (kupatel)
> wrote:
> > Hi all,
> >I am pretty new to using Postrgres, and have been looking at the 
> > messages in this forum for a while. I have noticed that the use of 
> > EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres docs, 
> > but am not quite sure I understand how this works. Is there some 
> > tutorial or any other documentation how this can be used?
> 
> Well, here's the short version:
> 
> EXPLAIN [query] tells you what the planner _thinks_ it should do.
> 
> EXPLAIN ANALYZE [query] tells you what the planner thinks it should do,
> and also executes the query and reports back how long every step took,
> how many rows were returned, &c.  (For this reason, you want to wrap it
> in BEGIN;...;ROLLBACK; if it changes data.)
> 
> For more, see the EXPLAIN EXPLAINED tutorial on techdocs.postgresql.org
> ( _Explain_Public.sxi>)
> 
> A
> 
> --
> Andrew Sullivan  | [EMAIL PROTECTED]
> In the future this spectacle of the middle classes shocking the avant-
> garde will probably become the textbook definition of Postmodernism. 
> --Brad Holland
> 
> ---(end of broadcast)---
> TIP 1: 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

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Using EXPLAIN-ANALYZE

[Kashmira Patel \(kupatel\)]

Thanks for the explanations and the links. Will try some of this out and
come back to you guys if I still don't understand :)

-Kashmira

-Original Message-
From: Andrew Sullivan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 16, 2006 1:14 PM
To: Kashmira Patel (kupatel)
Cc: Andrew Sullivan; pgsql-sql@postgresql.org
Subject: Re: [SQL] Using EXPLAIN-ANALYZE

On Thu, Feb 16, 2006 at 01:08:40PM -0800, Kashmira Patel (kupatel)
wrote:
> So I would have to put in lots of rows of data in the table before 
> using the explain command?

Well, no, but you won't get useful information without it. 
PostgreSQL has a cost-based optimizer.  The query plan is affected by
the nature of your data.  That's what the ANALYZE command (on its own,
or with VACUUM) is for.

A

> 
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan
> Sent: Thursday, February 16, 2006 12:39 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Using EXPLAIN-ANALYZE
> 
> On Thu, Feb 16, 2006 at 12:07:10PM -0800, Kashmira Patel (kupatel)
> wrote:
> > Hi all,
> >I am pretty new to using Postrgres, and have been looking at the 
> > messages in this forum for a while. I have noticed that the use of 
> > EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres 
> > docs, but am not quite sure I understand how this works. Is there 
> > some tutorial or any other documentation how this can be used?
> 
> Well, here's the short version:
> 
> EXPLAIN [query] tells you what the planner _thinks_ it should do.
> 
> EXPLAIN ANALYZE [query] tells you what the planner thinks it should 
> do, and also executes the query and reports back how long every step 
> took, how many rows were returned, &c.  (For this reason, you want to 
> wrap it in BEGIN;...;ROLLBACK; if it changes data.)
> 
> For more, see the EXPLAIN EXPLAINED tutorial on 
> techdocs.postgresql.org 
> ( ng
> _Explain_Public.sxi>)
> 
> A
> 
> --
> Andrew Sullivan  | [EMAIL PROTECTED] In the future this spectacle of

> the middle classes shocking the avant- garde will probably become the 
> textbook definition of Postmodernism.
> --Brad Holland
> 
> ---(end of 
> broadcast)---
> TIP 1: 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

--
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the
marketplace.
--Philip Greenspun

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] group by complications

[Markus Schaber]

Hi, Mark,

Mark Fenbers schrieb:
> Wow!  I didn't know you could have a (select ...) as a replacement for a
> 'from' table/query.  Your SQL worked as-is, except I had to add a 'limit
> 1' to the first subquery.
> 
> Thanks!  I would have never figured that out on my own!

SQL has more power than most think, and PostgreSQL does a good job in
implementing most of it. Much more than some other well known "free"
databases.

Have a look at http://www.postgresql.org/docs/8.1/static/sql-select.html
and the other SQL commands at
http://www.postgresql.org/docs/8.1/static/sql-commands.html (or your
local copy of the manual).

HTH,
Markus

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match