Basically because it wasn’t possible to retroactively change how views work. I’d like to be able to support SECURITY INVOKER (or equivalent) for views but as far as I know no such feature presently exists. You can filter access to the view its self with row security normally. The tables accessed by the view will … Read more
I think that this is an interesting question that deserves an in-depth answer; please bear with me if it is a little bit lengthy. In short: Your guess is right, and you can use the following RETURNING clause to determine if the row was inserted and not updated: RETURNING (xmax = 0) AS inserted Now … Read more
A large OFFSET is always going to be slow. Postgres has to order all rows and count the visible ones up to your offset. To skip all previous rows directly you could add an indexed row_number to the table (or create a MATERIALIZED VIEW including said row_number) and work with WHERE row_number > x instead … Read more
ON CONFLICT requires a unique index* to do the conflict detection. So you just need to create a unique index on both columns: t=# create table t (id integer, a text, b text); CREATE TABLE t=# create unique index idx_t_id_a on t (id, a); CREATE INDEX t=# insert into t values (1, ‘a’, ‘foo’); INSERT … Read more