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.

  • 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` – Victor Oct 11 '18 at 22:37
  • 1
    If there are multiple rows with the same org_id, you get multiple output rows. Is that really what you want? – CL. Oct 12 '18 at 7:41
  • 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 Oct 12 '18 at 12:01
  • 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 – Victor Oct 12 '18 at 12:01
  • 1
    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. – CL. Oct 12 '18 at 16:03

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);
  • 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 Oct 12 '18 at 12:03
  • 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 – Victor Oct 12 '18 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.