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
It’s the recurring problem of SELECT or INSERT, related to (but different from) an UPSERT. The new UPSERT functionality in Postgres 9.5 is still instrumental. WITH ins AS ( INSERT INTO names(name) VALUES (‘bob’) ON CONFLICT ON CONSTRAINT names_name_key DO UPDATE SET name = NULL WHERE FALSE — never executed, but locks the row RETURNING … Read more
Postgres hasn’t implemented an equivalent to INSERT OR REPLACE. From the ON CONFLICT docs (emphasis mine): It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. Though it doesn’t give you shorthand for replacement, ON CONFLICT DO UPDATE … 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
I believe xmax::text::int > 0 would be the easiest trick: so=# DROP TABLE IF EXISTS tab; NOTICE: table “tab” does not exist, skipping DROP TABLE so=# CREATE TABLE tab(id INT PRIMARY KEY, col text); CREATE TABLE so=# INSERT INTO tab(id, col) VALUES (1,’a’), (2, ‘b’); INSERT 0 2 so=# INSERT INTO tab(id, col) VALUES (3, … 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
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