Speaking for PostgreSQL, I found this question to be somewhat interesting.
Omitted FROM Clauses
This isn't at all permitted in the SQL spec. PostgreSQL calls this feature an "Omitted FROM clause", and says this about it
PostgreSQL allows one to omit the FROM clause. [...] Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT. Note that if a FROM clause is not specified, the query cannot reference any database tables.
Here is what isn't explicit. PostgreSQL is simply introducing an implicit one-row dummy table for you. The example of the syntax is
SELECT 2+2 so you can see it's only really made for trivial cases.
# SELECT count(*);
This makes sense so long as you remember that it's operating on an implicit dummy table.
CREATE TABLE dummy_table ();
INSERT INTO dummy_table DEFAULT VALUES;
SELECT count(*) FROM dummy_table;