Question 171496 in PostgreSQL Question posted in
The official documentation can be found here.

Why is Postgres acting strangely on an empty table?

I have two tables table_a and table_b in a Postgresql 13 database, having UUIDs primary key columns.

table_a has multiple entries, whereas table_b is empty (no entry).
The following query returns the expected result, namely entry_count_a larger than 0:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a FROM table_a ta

However, the following query returns 0 for both entry_counts:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, COUNT(DISTINCT tb.uuid) AS entry_count_b FROM table_a ta, table_b tb

What is the correct way to write the query, so that entry_count_a contains the correct (expected) value > 0, whereas entry_count_b is 0?

Bonus question: Why does Postgresql behave this way?

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    Workaround I use for now:

    SELECT a.count,
       b.count FROM
  (SELECT COUNT(DISTINCT uuid) AS count FROM table_a) a,
  (SELECT COUNT(DISTINCT uuid) AS count FROM table_b) b

  2. 0

    Your current query is equivalent to the following one:

    SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, 
       COUNT(DISTINCT tb.uuid) AS entry_count_b 
FROM       table_a ta
CROSS JOIN table_b tb

    When you apply the cartesian product between two tables, you multiply their cardinality. You get no rows because one of the two tables has cardinality 0, hence 0*n, for any n, is always 0.

    If you want to display the two counts correctly, you could use two subqueries as follows:

    SELECT
    (SELECT COUNT(DISTINCT uuid) FROM table_a) AS entry_count_a,
    (SELECT COUNT(DISTINCT uuid) FROM table_b) AS entry_count_b

    Check the demo here.

    Login or Signup to reply.
  3. 0

    Your query will produce the cartesian product table a x table b, because you didn’t established the way you want to relate them. Normally, we use WHERE conditions or JOIN clauses. So if you have n lines in A x 0 lines in B, you will get 0 rows as result with your SQL statement.
    The easiest way in this case is running two separate querys, one for each table, or using subselects like this:

    SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, SELECT COUNT(DISTINCT tb.uuid) FROM table_b tb) AS entry_count_b FROM table_a ta
    Login or Signup to reply.
  4. 0

    An other way to do it using CROSS JOIN :

    with cte_table_a as (
  SELECT COUNT(DISTINCT uuid) AS entry_count_a FROM table_a
),
cte_table_b as (
  SELECT COUNT(DISTINCT uuid) AS entry_count_b FROM table_b
)
select * from cte_table_a
cross join cte_table_b

    Or simply :

    SELECT * 
from (SELECT COUNT(DISTINCT uuid) AS entry_count_a FROM table_a) a
CROSS JOIN (SELECT COUNT(DISTINCT uuid) AS entry_count_b FROM table_b) b

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search