Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL 9.5 features

PostgreSQL 9.5 release has a bunch of great features. I describe below the ones I find most interesting.
UPSERT is simply a combination of INSERT and UPDATE. This works like this: if a row exists, then update it, if it doesn't exist, create it.

Before Postgres 9.5 when I wanted to insert or update a row, I had to write this:
INSERT INTO test(username, login)
SELECT 'hey', 'ho ho ho'
WHERE NOT EXISTS (SELECT 42 FROM test WHERE username='hey');
UPDATE test SET login='ho ho ho' WHERE username='hey' AND login <> 'ho ho ho';
Which was a little bit problematic. You need to make two queries, and both can have quite complicated WHERE clauses.
In PostgreSQL 9.5 there is much simpler version:
INSERT INTO test(username, login) VALUES ('hey', 'ho ho ho')
ON CONFLICT (username)
DO UPDATE SET login='ho ho ho';
The only requirement is that there should be a UNIQUE constraint on a column which should fail while inserting a row.
The version above makes the UPDATE when the INSERT fails. There is also another form of the UPSERT query, which I used in this blog post. You can just ignore the INSERT failure:
INSERT INTO test(username, login) VALUES ('hey', 'ho ho ho')
ON CONFLICT (username)

Switching Tables to Logged and Unlogged
PostgreSQL keeps a transaction write ahead log, which helps restore the database after a crash, and is used in replication, but it comes with some overhead, as additional information must be stored on disk.
In PostgreSQL 9.5 you can simply switch a table from logged to unlogged. The unlogged version can be much faster when filling it with data, processing it etc. However at the end of such operations it might be good to make it a normal logged table. Now it is simple:

JSONB Operators and Functions
This is the binary JSON type, and these new functions allow us to perform more operations without having to convert our data first to the slower, non-binary JSON alternative.
Now you can remove a key from a JSONB value:
SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"x": 1, "y": 2, "c": 42}'::jsonb;
 {"b": 2, "c": 3}

And merge JSONB values (the last value's keys overwrite the first's one):
SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"x": 1, "y": 2, "c": 42}'::jsonb;
 {"a": 1, "b": 2, "c": 42, "x": 1, "y": 2}
And we have the nice jsonb_pretty() function which instead of this:
SELECT jsonb_set('{"name": "James", "contact": {"phone": "01234 567890",
                   "fax": "01987 543210"}}'::jsonb,
                   '{contact,phone}', '"07900 112233"'::jsonb);
 {"name": "James", "contact": {"fax": "01987 543210", "phone": "07900 112233"}}

prints this:
SELECT jsonb_pretty(jsonb_set('{"name": "James", "contact": {"phone": "01234 567890",
                   "fax": "01987 543210"}}'::jsonb,
                   '{contact,phone}', '"07900 112233"'::jsonb));
  {                              ↵
      "name": "James",           ↵
      "contact": {               ↵
          "fax": "01987 543210", ↵
          "phone": "07900 112233"↵
      }                          ↵


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger