Postgresql – Having Updatable Views that obey initial select conditions

postgresql

Let's say I have a table like this:

+--------------+----------+-------------+
| name         | is       | in          |
|--------------+----------+-------------|
| Study        | <null>   | <null>      |
| Sport        | <null>   | <null>      |
| Biology      | Study    | <null>      |
| Linguistics  | Study    | <null>      |
| Art          | <null>   | <null>      |
+--------------+----------+-------------+

And I want to build a view as such:

CREATE VIEW v AS SELECT name FROM t WHERE "is" = 'Study';

Now if I want to insert into this view, it does not automatically set "is" to 'Study', I have to write a trigger and a function to adjust this behavior. However, this is a highly repeated pattern in my application, and I was wondering if there is a way to automatically achieve this (an updatable view that obeys the select condition).

Best Answer

maybe it works, it is in SQL server but concept maybe the same in your case:

If its always required you can set default. It will update always when you insert the view [is] with 'Study':

ALTER TABLE t ADD CONSTRAINT  df_is DEFAULT 'Study' FOR [is];

Or you can create another view only for insert:

CREATE VIEW v_insert AS SELECT name,[is] FROM t WHERE [is] = 'Study';
DECLARE @is NVARCHAR(max)='Study';
INSERT INTO dbo.v_insert (name, [is]) VALUES ('nametoinsert', @is )

Related Solutions

PostgreSQL – How to Select a Count with Conditions

It's unclear whether you want the total count or the count in the past 24 hours. For the latter:

SELECT count(*), page
FROM   page_views
GROUP  BY 2
WHERE  created_at >= now() - interval '24 hours' -- guessing column
HAVING count(*) > 20
ORDER  BY 1 DESC;

The condition count(*) > 20 has to go into the HAVING clause.

You did not provide a table definition. Guessing you have a created_at to store the event time.

Total counts, but only for pages having a minimum count of 20 in the past 24 hours:

SELECT count(*), page
FROM  (  
    SELECT page
    FROM   page_views
    GROUP  BY 1
    WHERE  created_at >= now() - interval '24 hours'
    HAVING count(*) > 20
    ) x
JOIN   page_views p USING (page)
GROUP  BY 2
ORDER  BY 1 DESC;

How to Make a Postgres View with Join Updatable

You can't use NEW.*, because the view has columns that don't exist in the underlying table. You have to explicitly list out the columns you want to add. As people explained above, the reason this didn't work is that you did not provide a default. You might want to consider using COALESCE() for this purpose.

Best Answer

Related Solutions

PostgreSQL – How to Select a Count with Conditions

How to Make a Postgres View with Join Updatable

Related Question