Select count(*) /*from*/ table

From: Alexander Shulgin <alex(dot)shulgin(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Select count(*) /*from*/ table
Date: 2011-07-05 10:27:55
Message-ID: CAM-UEKTeLuDaYYfTh0XvL+xhduwsyfO=bmEa4s1n=jsi5XLSXg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

Today I've mistyped a SELECT (effectively omitting the FROM clause):

$ SELECT COUNT(*) my_table;
my_table
----------
1
(1 row)

Apparently, my_table was treated as an alias to the COUNT(*)
expression. This has been discussed before, e.g. here:
http://archives.postgresql.org/pgsql-general/2011-03/msg00331.php

OK, but why it gives 1, and not 0?

A hint might be that if you try "SELECT 1+1, COUNT(*)" or any other
SELECT query w/o the FROM list, the result set always(?) consists of
exactly 1 row.

But does it really make sense? You cannot run a "SELECT *" w/o the
FROM list, and why "COUNT(*)" on empty FROM list would return a value
greater than on e.g. FROM empty_table?

I understand that there's really not much point in running COUNT w/o
the FROM list, but maybe we should just disallow COUNT(*) with empty
FROM list? It will also save from interpreting the result "1" as the
number of rows in the "table" when you mistakenly forgot to add FROM.

--
Regards,
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Condor 2011-07-05 11:31:13 Re: Dump large DB and restore it after all.
Previous Message Craig Ringer 2011-07-05 10:08:21 Re: Dump large DB and restore it after all.