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

Return column names from CTE, even if it's empty – Postgresql

I built a dynamic query that returns

  1. Total count of entries in CTE
  2. Paginated entries from CTE
  3. List of column names used in CTE
  4. Requested page size and page number

The beauty of in returning total count + pagination in a single roundtrip. And you can insert any CTE in it (in my case CTEs are built dynamically on backend)

My issue is that when CTE has 0 rows (e.g. where condition is not satisfied) an outer query is empty as well and I can’t extract column names. How can I fix it?

with "cte" as (
select...)
select
    (
    select
        array_agg(column_name)
    from
        (
        select
            jsonb_object_keys(to_jsonb(cte)) as column_name) as column_names) as "ColumnNames",
    COUNT(*) over() as "TotalCount",
    (
    select
        array_agg(cte_subquery)
    from
        (
        select
            *
        from
            cte offset 30
        limit 15) as cte_subquery) as "PaginatedEntries",
    3 as "PageNumber",
    15 as "PageSize"
from
    "cte"
limit 1

Fiddle:
https://sqlize.online/sql/psql15/e5cd43dff492f9143e725c97cc6e0199/

Expected result

+-------------+------------+------------------+------------+----------+
|  ColumnNames | TotalCount | PaginatedEntries | PageNumber | PageSize |
+--------------+------------+------------------+------------+----------+
| {id,age,name,email} | 0   |         {}       |          3 |       15 |
+--------------+------------+------------------+------------+----------+
Tags:

2

Answers


  1. 0

    You can try this:

    with cte as (select id, name, age, email from dummy_table where false
             union all
             select null::int, null::varchar, null::int, null::varchar from dummy_table
             limit 1)
select
    (select array_agg(column_name) 
       from (select jsonb_object_keys(to_jsonb(cte)) as column_name) 
       as column_names) as "ColumnNames",
    COUNT(*) over() as "TotalCount",
    (select coalesce(array_agg(cte_subquery) filter (where id is not null), '{}') 
       from (select * from cte offset 0 limit 15) as cte_subquery) 
       as "PaginatedEntries",
    3 as "PageNumber",
    15 as "PageSize"
from cte;

    Here is an explanation.

    1. The trick is here to generate a row with empty values in cte query. This can be achieved using union all with null-values:

    select id, name, age, email 
from dummy_table 
where false

union all

select null::int, null::varchar, null::int, null::varchar 
from dummy_table
limit 1;

    As the cte query still generates one row, the final result will be as follows:

         ColumnNames     | TotalCount | PaginatedEntries | PageNumber | PageSize 
---------------------+------------+------------------+------------+----------
 {id,age,name,email} |          1 | {}               |          3 |       15
(1 row)

    2. TotalCount is 1 as cte generates an "empty" row.

    3. For '{}' value in PaginatedEntries column you can modify your query as follows:

    (select coalesce(array_agg(cte_subquery) filter (where id is not null), '{}') 
 from (select * from cte offset 0 limit 15) as cte_subquery) as "PaginatedEntries"

    For null values it generates {} as a result.

    Here is the demo.

    Login or Signup to reply.
  2. 0

    You can find column names of all existing relations in information_schema.columns system view. You can get them in a separate CTE, then left join...on true with that (demo).

    Since you aim to inject the query and you’re interested in whatever columns it turns out to have – which might not correspond to any specific table – you can do a similar thing the same with your current jsonb_object_keys() approach: demo

    with 
 cte   as (select * from dummy_table where true)
,cte_1 as (select * from (values (1)) as a(" ") left join cte on true limit 1)  
,cte_column_names("ColumnNames") as 
( select array_agg(column_name)
  from (select jsonb_object_keys(to_jsonb(cte_1)) as column_name
        from cte_1 offset 1) a)
,cte_paginated_entries("PaginatedEntries") as 
(select jsonb_agg(cte_subquery)
     from (select * from cte offset 0 limit 15) as cte_subquery)
select
    cn."ColumnNames"
  , COUNT(*) over() -(case when pe."PaginatedEntries" is null then 1 else 0 end) as "TotalCount" 
  , coalesce(pe."PaginatedEntries",'[]'::jsonb)
  , 3 as "PageNumber"
  , 15 as "PageSize"
from cte_column_names cn 
  left join cte on true 
  left join cte_paginated_entries pe on true
limit 1
    1. In cte_1 a left join with a single dummy value generates a row that starts with that value and keeps all cte fields filled with null. This solves your problem of not having a row at all to read column names from.
    2. That lets you then pass it to your field name aggregation, where offset 1 skips the previously added dummy field.
    3. This leaves your count(*) over () trick that wants to claim there’s a non-zero total count, still seeing the row with the column names whenever there were no actual rows.
    4. A case can correct that: even if there was a single column with a null, in that scenario "PaginatedEntries" will not be null but a an array with a null in it, which doesn’t match the is null case.
    5. Added coalesce to match your expected output.

    You clarified you actually do make sure there’s order by that keeps paging order consistent. Note that you still need the state to also stay consistent: you can hold on to the table snapshot by issuing subsequent page queries within the same transaction.

    This isn’t very pretty but it works the way you intended. Still, you might want to consider using cursors and cursor-returning functions as well as session-specific temp tables, all of which could get you all that info in a single round-trip a bit more cleanly. At the moment, your pagination keeps re-evaluating things it otherwise wouldn’t need to.

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

Back To Top
Search