PostgreSQL 9.5: what's new? Part 1. INSERT... ON CONFLICT DO NOTHING/UPDATE and ROW LEVEL SECURITY

Part 2. TABLESAMPLE
Part 3. GROUPING SETS, CUBE, ROLLUP
Q4 2015 will see the release of PostgreSQL 9.5. As always, new version is in addition to new bugs brings new features and Goodies. This article will focus on two of them, namely, INSERT... ON CONFLICT DO NOTHING/UPDATE, and Row-level security. Already released the second alpha version, so the most impatient can install it and try the new functionality.
You can download it here

the

the
• INSERT... ON CONFLICT DO NOTHING/UPDATE
the
• Row-level security

INSERT... ON CONFLICT DO NOTHING/UPDATE

/ > It is colloquially UPSERT. Allows in the event of a conflict on the insert to update the fields or to ignore the error.

What was previously proposed to implement by using stored functions, will be available out of the box. In the expression INSERT you can use the condition ON CONFLICT DO NOTHING/UPDATE. In the expression shown separately conflict_target (which field/condition will be considered a conflict) and conflict_action (what to do when the conflict occurred: DO NOTHING or DO UPDATE SET).

The complete syntax of the expression INSERT is the this:
the

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name

and conflict_action is one of:

DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]

For us, the fun starts after ON CONFLICT.

Let's look at examples. Create a table that will be based on the credentials of certain persons:
the

CREATE TABLE account
(
id bigserial,
name varchar,
surname varchar,
address varchar,
PRIMARY KEY (id),
Unique_person CONSTRAINT UNIQUE (name, surname, address)
);
Query returned successfully with no result in 31 ms.

Run the request to insert
the

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Vasya', 'Pupkin', 'Kremlin, Moscow')
ON CONFLICT (id) DO NOTHING;
Query returned successfully: one row affected, 12 ms execution time.

SELECT * FROM ACCOUNT;

the the

id	name	surname	address
1	VA		the Kremlin, Moscow

Here conflict_target is (id) and conflict_action DO NOTHING.
If you try to run this query a second time, the insert will not happen, and you will not receive any error messages:
the

Query returned successfully: 0 rows affected, 12 ms execution time.

If we did not specify ON CONFLICT (id) DO NOTHING, you would get an error:
the

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Vasya', 'Pupkin', 'Kremlin, Moscow');

********** Error **********

ERROR: duplicate key value violates unique constraint "account_pkey"
SQL state: 23505
Detail: Key (id)=(1) already exists.

The same behavior (like ON CONFLICT (id) DO NOTHING) will request:
the

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Bob', 'DOE', 'Kremlin, Moscow')
ON CONFLICT (name, surname, address) DO NOTHING;

Query returned successfully: 0 rows affected, 12 ms execution time.

We have taken the value of id by default (out of sequence), but specify different conflict_target — the three fields on which a unique constraint.

As mentioned above, you can also specify conflict_target design ON CONSTRAINT, putting directly the name of the constraint:
the

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Bob', 'DOE', 'Kremlin, Moscow')
ON CONFLICT ON CONSTRAINT unique_person DO NOTHING;
Query returned successfully: 0 rows affected, 11 ms execution time.

This is particularly beneficial if you have eliminating the restriction (exclusion constraint) to which you can refer by name only, not the set of columns, as in the case of unique constraint.

If you have built a partial unique index, it can also be specified in the condition. Let the table a unique combination of name+address will be only for people with the name Bob:
Unique_vasya CREATE UNIQUE INDEX ON account (surname, address) WHERE name='Bob';
Then we can write the following query:
the

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Bob', 'DOE', 'Kremlin, Moscow')
ON CONFLICT (surname, address) WHERE name='Bob' DO NOTHING;
Query returned successfully: 0 rows affected, 12 ms execution time.

And finally, if you want to DO NOTHING is triggered when any conflict of uniqueness/exception when inserting, it can be written as follows:
the

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Bob', 'DOE', 'Kremlin, Moscow')
ON CONFLICT DO NOTHING;
Query returned successfully: 0 rows affected, 12 ms execution time.

It should be noted that to specify multiple conflict_action impossible, so if you specify one, will it work same error during insert:
the

INSERT INTO account (id, name, surname, address)
VALUES (DEFAULT, 'Bob', 'DOE', 'Kremlin, Moscow')
ON CONFLICT (id) DO NOTHING;

********** Error **********

ERROR: duplicate key value violates unique constraint "unique_person"
SQL state: 23505
Detail: Key (name, surname, address)=(John, DOE, Moscow, Kremlin) already exists.

Let us turn to the possibilities DO UPDATE SET.

For DO UPDATE SET unlike DO NOTHING indication conflict_action required.

DO UPDATE SET updates the fields that it contains. These field values can be set explicitly, by default, obtained from the subquery or drawn from special expressions EXCLUDED, from which you can take the data that was initially proposed to be inserted.

the

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Petya', 'Petrov', 'Kremlin, Moscow')
ON CONFLICT (id)
DO UPDATE SET
name='Peter',
surname='Smith';
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT;

the the

id	name	surname	address
1	Peter	Petrov	the Kremlin, Moscow

the

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Petya', 'Petrov', 'Kremlin, Moscow')
ON CONFLICT (id)
DO UPDATE SET
name=EXCLUDED.name || '(formerly ' || a.name || ')',
surname=EXCLUDED.surname || '(formerly ' || a.surname || ')';
Query returned successfully: one row affected, 13 ms execution time.

SELECT * FROM ACCOUNT;

the the

id	name	surname	address
1	Peter (formerly Bob)	Petrov (former DOE)	the Kremlin, Moscow

the

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Vasya', 'Pupkin', 'Kremlin, Moscow')
ON CONFLICT (id) UPDATE SET DO
name=DEFAULT,
surname=DEFAULT;
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT;

the the

id	name	surname	address
1	NULL	NULL	the Kremlin, Moscow

the

INSERT INTO account (id, name, surname, address)
VALUES (1, 'Vasya', 'Pupkin', 'Kremlin, Moscow')
ON CONFLICT (id) UPDATE SET DO
name=(SELECT some_field FROM other_table LIMIT 1);

Can also be used the condition WHERE. For example, we want to field name was not updated, if the field address in the string table already contains the text "the Kremlin", otherwise — Obnovlentsy:
the

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Vasya', 'Pupkin', 'Kremlin, Moscow')
ON CONFLICT (id) UPDATE SET DO
name=EXCLUDED.name
WHERE a.name not like '%Kremlin%';

Query returned successfully: 0 rows affected, 12 ms execution time.

And if you want to field name was not updated, if the field address in the pasted data contains the text "the Kremlin", otherwise — Obnovlentsy:
the

INSERT INTO account AS a (id, name, surname, address)
VALUES (1, 'Vasya', 'Pupkin', 'Moscow Red square')
ON CONFLICT (id) UPDATE SET DO
name=EXCLUDED.name
WHERE EXCLUDED.name not like '%Kremlin%';
Query returned successfully: one row affected, 11 ms execution time.

SELECT * FROM ACCOUNT

the the

id	name	surname	address
1	VA	NULL	the Kremlin, Moscow

ROW LEVEL SECURITY

Row-level security or security on the row level — the mechanism of differentiation of access to information to the database, allowing you to restrict user access to individual rows in tables.

This functionality can be interesting for those who use a database with a large number of users.

It works as follows: describes the rules for a specific table, according to which restricting access to specific rows when you execute certain commands, by using the expression CREATE POLICY. Each rule contains a Boolean expression that must be true, that line was visible in the query. Then the rules are activated by the expression ALTER TABLE... ENABLE ROW LEVEL SECURITY. Then when you try to access, for example, when the query SELECT, it is checked whether the user has right to access a particular row, and if not, they are not shown. Super user by default can see all the lines, as it has default flag BYPASSRLS, which means that for this role checks are not performed.
The syntax of the expression CREATE POLICY this:
the

CREATE POLICY name ON table_name
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ]

Rules are created for specific tables, so the database can be several rules with the same name for different tables.
After the expression FOR specifies what kind of requests the rule applies, the default ALL, i.e. for all queries.

After TO — for any role, the default is PUBLIC, that is, for all roles.

Further, in the expression USING indicates a Boolean expression that must be true to the exact line was visible to the user in the queries that use data already available (SELECT, UPDATE, DELETE). If the Boolean expression returned null or false, the string will not be seen.

In the expression WITH CHECK indicates a Boolean expression that must be true to the request, adding or modifying data (INSERT or UPDATE) was successful. In the case that a Boolean expression will return null or false, it will error. The expression WITH CHECK is executed after triggers BEFORE (if present) and before any other checks. Therefore, if the trigger is BEFORE modify the line so that the condition will not return true, there is an error. For the successful implementation of the UPDATE requires that both conditions are returned true, in particular, if the query INSERT... ON CONFILCT DO UPDATE a conflict will occur and the query will attempt to modify the data. If the expression WITH CHECK is omitted, instead substituted a condition of expression USING.
In the circumstances, do not use aggregious or window functions.

Usually, you want to control access based on which user database queries data, so we will need functions that return information about the system (System Information Functions).

Let us turn to the examples:

Add to table account db_user, populate this field for existing records and add new record:
the

ALTER TABLE account ADD COLUMN db_user varchar;
Query returned successfully with no result in 16 ms.
UPDATE account SET db_user='pupkin' WHERE surname='DOE';
INSERT INTO account (name, surname, address, db_user)
VALUES ('Peter', 'Smith', 'Moscow Red square', 'petrov'), 
('Ivan', 'Sidorov', 'Saint Petersburg, the Winter Palace', 'sidorov');
Query returned successfully: 2 rows affected, 31 ms execution time.

Create a role:
the

CREATE ROLE pupkin WITH LOGIN PASSWORD 'pupkin';
Petrov CREATE ROLE WITH LOGIN PASSWORD 'petrov';
Query returned successfully with no result in 31 ms.

Create a rule and enable RLS on the table:
the

CREATE POLICY select_self ON account
FOR SELECT
USING (db_user=current_user);
ALTER TABLE account ENABLE ROW LEVEL SECURITY;
Query returned successfully with no result in 12 ms.

In this query we created the rule according to which, the user in the query SELECT will be visible only those rows in which the value of the field db_user coincides with the name of the current user database.

We can run the query from the postgres user:
the

SELECT * FROM account

the the the the

id	name	surname	address	db_user
1	VA		the Kremlin, Moscow	pupkin
5	Peter	Petrov	Moscow, Red square	petrov
6	Ivan	Sidorov	Saint Petersburg, the Winter Palace	robt

Let's execute the same query from the user pupkin:
the the

id	name	surname	address	db_user
1	VA		the Kremlin, Moscow	pupkin

Create a rule that rows with the last name "DOE" can only insert the user pupkin:
the

CREATE POLICY insert_update_pupkin ON account
WITH CHECK (surname<>'DOE' OR current_user='pupkin')

Try to run the query from the user pupkin:
the

INSERT INTO account (name, surname, address)
VALUES ('Dmitry', 'DOE', 'Kiev Maidan')
Query returned successfully: one row affected, 13 ms execution time.

Check out:
the

select * from account;

the the

id	name	surname	address
1	VA		the Kremlin, Moscow	pupkin

Op-PA! We forgot to specify a field db_user and the tape we have inserted, we will not see. Well, let's fix this logic using a trigger which will fill field db_user the name of the current user:
the

CREATE OR REPLACE FUNCTION fill_db_user() RETURNS TRIGGER AS 
$BODY$
BEGIN
NEW.db_user = current_user;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Fill_db_user CREATE TRIGGER BEFORE INSERT ON account
FOR EACH ROW EXECUTE PROCEDURE fill_db_user();

Try again:
the

INSERT INTO account (name, surname, address)
VALUES ('Ivan', 'DOE', 'Kiev Maidan');
select * from account;

the the the

id	name	surname	address	db_user
1	VA		the Kremlin, Moscow	pupkin
21	Ivan		Kiev, Maidan	pupkin

Try to change the data about Ivan petrov DOE user:
the

UPDATE account SET db_user='petrov'
WHERE id=21
Query returned successfully: 0 rows affected, 13 ms execution time.

As you can see, the data has not changed, it happened because the condition is USING rules of select_self not implemented.

If a single query matches multiple rules, they are combined using OR.

It is worth noting that the rules apply only when explicit queries to the tables and do not apply when inspections are made by the system (constaints, foreign keys, etc.). This means that a user with a query, determine that a value exists in the database. For example, if the user is able to insert into a table that references another table, which he can't do SELECT. In this case, he may try to do an INSERT to the first table and the result (insert occurred or an error occurred when validating referential integrity) to determine whether the value exists in the second table.

Options for the use of row-level security you can think of a lot:
the

the same database used by multiple applications with different functionality the

• multiple instances of the same application with different rights
the
• access by role or user groups
the
• , etc.

In the following sections, I will look at these new features of PostgreSQL 9.5:
the

the
• Part 2. TABLESAMPLE
the
• SKIP LOCKED
the
• BRIN-indexes
the
• GROUPING SETS, CUBE, ROLLUP,
the
• New functions for JSONB
the
• IMPORT FOREIGN SCHEMA
the
• and others

Article based on information from habrahabr.ru