Counting using window function with partition over an empty table

Question

If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that?

select count(*) over (partition by field_name) 
from my_empty_table

Right now the postgres doesn't emmit any results as the table is empty.

• Victor: I have tried COALESCE in the proyection clause and doesn't work, i mean: ` select COALESCE(count(*) over (partition by org_id) + 1,0) from gastro_studies`
• CL.: If there are multiple rows with the same org_id, you get multiple output rows. Is that really what you want?
• Victor: yes @CL, i will tell what i really need: i need to assign an internal id to each row of this table regarding his org_id field besides the primary key (that exists on other column, named "id" btw).
• Victor: So, i'm trying to assing that internal_id within the insertion (using something like insert into <table> (a,b,c) values select 1,3,4,(<insert here the subquery for getting the internal id>)... not sure if the best strategy.. i know that i could make this within the selection (instead of the insertion) using row_number() over partition by... an assing an internal id "on the fly" as that internal_id has no meaning in the data model
• CL.: You cannot use a subquery this way to return multiple IDs, but it looks as if you want only one ID. Anyway, create a new question, describe what you actually want to do, and show example data and the desired results for all cases.

Answer 1

Your query outputs one row for each row in the table, so an empty table must result in an empty output. (COALESCE handles NULL values, but what you have is not a NULL value, but no value at all.)

You could add another query:

select count(*) over (partition by field_name) 
from my_empty_table
union all
select 0
from my_empty_table
where not exists (select * from my_empty_table);

• Victor: Let's starts by a thanks! I have update the needs behind my question in the case you may have a tip for me. This answer solves the question an shall be considered the corrrect one!.
• Victor: take a look at this snippet it also works! It is based on your idea about using "not exists" SELECT CASE WHEN NOT EXISTS (SELECT TRUE FROM my_table) THEN 1 ELSE (select count(*) over (partition by org_id) + 1 FROM my_table) END AS next_internal_id