when data in table is empty, show a record in a custom column

user_12995609 0
Viewed 24

I have a view as below in PostgresSQL where if view returns no data, i have to show the column "view_lastmodifieddate" to be populated and every other column/row as empty. Again, only when the table has no data

CREATE OR REPLACE VIEW test AS 
Select Column 1,
Column 2,
Column 3,
'2020-08-19 12:01:14' AS view_lastmodifieddate
From Table_1
postgresql
2 Answers

You could use a union here:

WITH cte AS (
    SELECT Col1, Col2, Col3, Col4, COUNT(*) FILTER (WHERE source = 1) OVER () AS cnt
    FROM
    (
        SELECT Col1, Col2, Col3, Col4, 1 AS source FROM Table_1
        UNION ALL
        SELECT NULL, NULL, NULL, '2020-08-19 12:01:14'::timestamp, 2
    ) t
)

SELECT Col1, Col2, Col3, Col4
FROM cte
WHERE (cnt > 0 AND source = 1) OR (cnt = 0 AND source = 2);

If you really wanted this as a view, you could inline the CTE I provided above into the lower select query, and instead define that select as a view.

user_18632290

I would use a left join to accomplish this.

CREATE OR REPLACE VIEW test AS 
select t.column1, t.column2, t.column3, 
       coalesce(t.view_lastmodifieddate, f.fixed_date) as view_lastmodifieddateb
  from (values ('2020-08-19 12:01:14'::timestamp)) as f(fixed_date)
       left join table_1 t
              on true;
user_138083190
Related