postgresqlunion

Select table names with amount of rows in PostgreSQL


I use PostgreSQL 13. I have several tables. And I want to make a select-request, that get the names of these tables and the number of lines with an NULL updated_date-column.

I can output the number of lines with an NULL date. I do this:

select count(*)
from TABLE_1
where updated_date is null
UNION
select count(*)
from TABLE_2
where updated_date is null;

-------
 count |
-------
     1 |
     2 |
-------  

I can get the names of the tables like this:

select t.tableoid::regclass as table_name
from TABLE_1 t
where updated_date is null;

------------
 table_name |
------------
    TABLE_1 |
------------

select t.tableoid::regclass as table_name
from TABLE_2 t
where updated_date is null;

------------
 table_name |
------------
    TABLE_2 |
    TABLE_2 |
------------

Finally, I need a SQL-request, that will give to me a result:

--------------------
 table_name | count |
--------------------
    TABLE_1 |     1 |
    TABLE_2 |     2 |
--------------------

I try to do this:

select t1.tableoid::regclass, count(*)
from TABLE_1 t1
where updated_date is null
UNION
select t2.tableoid::regclass, count(*)
from TABLE_2 t2
where updated_date is null;

but I get the mistake:

[42803] ERROR: column "t1.tableoid" must appear in the GROUP BY clause or be used in an aggregate function 

How can I fix this?


Solution

  • Seems you need something like:

    select t1.tableoid::regclass t_name, count(*) t_count
    from TABLE_1 t1
    where t1.updated_date is null
    group by t_name
    union 
    select t2.tableoid::regclass t_name, count(*) t_count
    from TABLE_2 t2
    where t2.updated_date is null
    group by t_name;