Queries Improvement in PostgreSQL 9.1

1. True serializable isolation level:

Before version 9.1, PostgreSQL did not support a full serializable isolation level. A request for serializable transaction isolation actually provided snapshot isolation. This has well known anomalies which can allow inconsistent views of the data during concurrent transactions; although these anomalies only occur when certain patterns of read-write dependencies exist within a set of concurrent transactions. Where these patterns exist, the anomalies can be prevented by introducing conflicts through explicitly programmed locks or otherwise unnecessary writes to the database. Snapshot isolation is popular because performance is better than serializable isolation and the integrity guarantees which it does provide allow anomalies to be avoided or managed with reasonable effort in many environments.

Following is a link on more details:

http://wiki.postgresql.org/wiki/SSI

2. INSERT/UPDATE/DELETE in WITH CLAUSE.

Now in 9.1, User would be able include INSERT/UPDATE/DELETE in WITH Clause and can pass data to the containing query. Following are some example:

-- INSERT ... RETURNING
WITH t AS (
    INSERT INTO y
    VALUES
        (11),
        (12),
        (13),
        (14),
        (15),
        (16),
        (17),
        (18),
        (19),
        (20)
    RETURNING *
)
SELECT * FROM t;

-- UPDATE ... RETURNING
WITH t AS (
    UPDATE y
    SET a=a+1
    RETURNING *
)
SELECT * FROM t;

-- DELETE ... RETURNING
WITH t AS (
    DELETE FROM y
    WHERE a <= 10
    RETURNING *
)
SELECT * FROM t;

Also, user can attache WITH CLAUSE to INSERT/UPDATE and DELETE Statements as given below:

-- data-modifying WITH in a modifying statement
WITH t AS (
    DELETE FROM y
    WHERE a <= 10
    RETURNING *
)
INSERT INTO y SELECT -a FROM t RETURNING *;

3. GROUP BY enhancement for missing columns

Previous version of PostgreSQL used to throw error message, if user forgets to specify any columns of target list in GROUP BY Clause, even if primary key is specified, as given below:

select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.5 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit

SELECT count(*), entity_name, entity_address as address
FROM entities JOIN employees using (entity_name)
GROUP BY entity_name;
ERROR:  column "entities.entity_address" must appear in the GROUP BY clause or be used in an aggregate function

Now in PostgreSQL 9.1, GROUP BY Clause can gues about the missing Column as given below:

SELECT count(*), entity_name, entity_address as address
FROM entities JOIN employees using (entity_name)
GROUP BY entity_name;
 count | entity_name | address
-------+-------------+----------
     2 | HR          | address1
     2 | SALES       | address2
(2 rows)

4. Per Column Collation.

In Previous version of PostgreSQL, collation (the sort ordering of text string) was supported only at database level. Now, in 9.1, user can set collation per column, index, or expression via COLLATE Clause. Some example is given below:

postgres=#  CREATE TABLE french_messages (message TEXT COLLATE "fr_FR");
CREATE TABLE
postgres=# select * from french_messages order by 1;
 message
---------
 Élève
 élever
 élevé
 élève
(4 rows)

postgres=# SELECT a, b, a < b as lt FROM
postgres-#   (VALUES ('a', 'B'), ('A', 'b' COLLATE "C")) v(a,b);
 a | b | lt
---+---+----
 a | B | f
 A | b | t
(2 rows)

5. Planner Improvements
a. Inheritance Table.
Now in 9.1, Some planner improvements has been done, like MIN/MAX for Inheritance tables. This improvement will boost up performance queries for user, if they are using paritioning.
lets see those improvements:

i) Create partition table as given below:

      CREATE TABLE main(id integer, val text);
      CREATE TABLE parition_1(CHECK(id >=1 and id <=20)) INHERITS(main);
      CREATE TABLE parition_2(CHECK(id >=21 and id <=40)) INHERITS(main);
      CREATE TABLE parition_3(CHECK(id >=41 and id <=60)) INHERITS(main);
      CREATE TABLE parition_other(CHECK(id >=61)) INHERITS(main);
      CREATE INDEX parition_1_idx on parition_1(id);
      CREATE INDEX parition_2_idx on parition_2(id);
      CREATE INDEX parition_3_idx on parition_3(id);
      CREATE INDEX parition_other_idx on parition_other(id);

ii) Create trigger as given below:

       CREATE OR REPLACE FUNCTION main_insert_direct( )
       RETURNS  trigger
       LANGUAGE plpgsql
       AS $function$
            BEGIN
                   IF NEW.id >=1 AND NEW.id <=20 THEN
                       INSERT INTO parition_1 values(NEW.*);
                   ELSIF NEW.id >=21 AND NEW.ID <=40 THEN
			INSERT INTO parition_2 values(NEW.*);
                   ELSIF NEW.id >=41 AND NEW.ID <=60 THEN
			INSERT INTO parition_3 values(NEW.*);
                   ELSE
                         INSERT INTO parition_other VALUES(NEW.*);
                   END IF;
                   RETURN NULL;
           END;
       $function$;

       CREATE TRIGGER insert_on_main
         BEFORE INSERT ON main
         FOR EACH ROW EXECUTE PROCEDURE main_insert_direct();

iii). INSERT some values as given below:

        INSERT INTO main SELECT * FROM generate_series(1,1000000000);

Now lets see plan. On previous version of PostgreSQL:

postgres=# explain analyze select id from main order by id desc limit 10

                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1148.68..1148.70 rows=10 width=4) (actual time=147.799..147.812 rows=10 loops=1)
   ->  Sort  (cost=1148.68..1220.94 rows=28905 width=4) (actual time=147.796..147.800 rows=10 loops=1)
         Sort Key: public.main.id
         Sort Method:  top-N heapsort  Memory: 17kB
         ->  Result  (cost=0.00..524.05 rows=28905 width=4) (actual time=0.018..117.908 rows=50000 loops=1)
               ->  Append  (cost=0.00..524.05 rows=28905 width=4) (actual time=0.017..74.136 rows=50000 loops=1)
                     ->  Seq Scan on main  (cost=0.00..22.30 rows=1230 width=4) (actual time=0.001..0.001 rows=0 loops=1)
                     ->  Seq Scan on parition_1 main  (cost=0.00..22.30 rows=1230 width=4) (actual time=0.014..0.073 rows=100 loops=1)
                     ->  Seq Scan on parition_2 main  (cost=0.00..22.30 rows=1230 width=4) (actual time=0.011..0.070 rows=100 loops=1)
                     ->  Seq Scan on parition_3 main  (cost=0.00..22.30 rows=1230 width=4) (actual time=0.009..0.057 rows=100 loops=1)
                     ->  Seq Scan on parition_other main  (cost=0.00..434.85 rows=23985 width=4) (actual time=0.021..32.197 rows=49700 loops=1)
 Total runtime: 147.921 ms
(12 rows)

However, in 9.1:

    postgres=# explain analyze select id from main order by id desc limit 10                                                          ;
                                                                                 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.07..6.44 rows=10 width=4) (actual time=6.828..6.849 rows=10 loops=1)
   ->  Result  (cost=1.07..10790.41 rows=20072 width=4) (actual time=6.825..6.844 rows=10 loops=1)
         ->  Merge Append  (cost=1.07..10790.41 rows=20072 width=4) (actual time=6.824..6.840 rows=10 loops=1)
               Sort Key: public.main.id
               ->  Sort  (cost=1.01..1.01 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=1)
                     Sort Key: public.main.id
                     Sort Method: quicksort  Memory: 25kB
                     ->  Seq Scan on main  (cost=0.00..1.00 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=1)
               ->  Index Scan Backward using parition_1_idx on parition_1 main  (cost=0.00..13.17 rows=61 width=4) (actual time=0.028..0.028 rows=1 loops=1)
               ->  Index Scan Backward using parition_2_idx on parition_2 main  (cost=0.00..13.15 rows=60 width=4) (actual time=0.024..0.024 rows=1 loops=1)
               ->  Index Scan Backward using parition_3_idx on parition_3 main  (cost=0.00..13.15 rows=60 width=4) (actual time=0.024..0.024 rows=1 loops=1)
               ->  Index Scan Backward using parition_other_idx on parition_other main  (cost=0.00..10233.63 rows=19890 width=4) (actual time=6.716..6.727 rows=10 loops=1)
 Total runtime: 6.932 ms
(13 rows)

Looking at above Plans, in PostgreSQL 9.0, Executor will take all from all child table will do sorting before returning 10 records. However, in PostgreSQL 9.1, Executor will take records from sorted child table and will use indexes (if available) to merge them with the sorted one to return 10 records. Looking at Total runtime, 9.1 is faster.

b. FULL OUTER JOIN improvement:
In 9.1, FULL OUTER JOIN can now use HASH Algorithms, which is faster than old method FULL OUTER JOIN (i.e. 2 Sorts) as given below:

i) Create tables and insert some values, as given below:

     CREATE TABLE test1 (a int);
     CREATE TABLE test2 (a int);
     INSERT INTO test1 SELECT generate_series(1,100000);
     INSERT INTO test2 SELECT generate_series(100,1000);

ii). EXPLAIN ANALYZE:

In Previous version of PostgreSQL:

        postgres=#  EXPLAIN ANALYZE SELECT * FROM test1 FULL OUTER JOIN test2 USING (a);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Merge Full Join  (cost=10952.05..11465.56 rows=100000 width=8) (actual time=214.420..370.898 rows=100000 loops=1)
   Merge Cond: (test1.a = test2.a)
   ->  Sort  (cost=10894.82..11144.82 rows=100000 width=4) (actual time=213.512..269.596 rows=100000 loops=1)
         Sort Key: test1.a
         Sort Method:  external sort  Disk: 1368kB
         ->  Seq Scan on test1  (cost=0.00..1393.00 rows=100000 width=4) (actual time=0.010..50.044 rows=100000 loops=1)
   ->  Sort  (cost=57.23..59.48 rows=901 width=4) (actual time=0.894..1.309 rows=901 loops=1)
         Sort Key: test2.a
         Sort Method:  quicksort  Memory: 38kB
         ->  Seq Scan on test2  (cost=0.00..13.01 rows=901 width=4) (actual time=0.012..0.436 rows=901 loops=1)
 Total runtime: 412.315 ms
(11 rows)

In PostgreSQL 9.1:

postgres=#  EXPLAIN ANALYZE SELECT * FROM test1 FULL OUTER JOIN test2 USING (a);
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Hash Full Join  (cost=24.27..1851.28 rows=100000 width=8) (actual time=0.588..74.434 rows=100000 loops=1)
   Hash Cond: (test1.a = test2.a)
   ->  Seq Scan on test1  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.011..21.683 rows=100000 loops=1)
   ->  Hash  (cost=13.01..13.01 rows=901 width=4) (actual time=0.563..0.563 rows=901 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 32kB
         ->  Seq Scan on test2  (cost=0.00..13.01 rows=901 width=4) (actual time=0.014..0.217 rows=901 loops=1)
 Total runtime: 82.555 ms
(7 rows)

Looking at above EXPLAIN ANALYZE, PostgreSQL 9.1 needs to create a HASH on the smallest table. However, in Previous Version, PostgreSQL required 2 sorts (one on smallest table test2 and one on test1) which is costly, which shows that 9.1 optimizer is smarter and giving better plan.

Updateable Views in PostgreSQL 9.1 using INSTEAD OF Trigger

About updateable views user ask many times. Is it supported in PostgreSQL? Can we write Complex updateable views?
Answer for above is yes. Till 9.0, we have to use RULE for implementing updateable view. Again, RULE Implementation used to be a bit tidious, since user has to write multiple RULES to implement this feature.

Following code can be use to see this.

CREATE TABLE person_detail(pid NUMERIC PRIMARY KEY, pname TEXT);
CREATE TABLE person_job(pid NUMERIC PRIMARY KEY references person_detail(pid), job TEXT);

INSERT INTO person_detail VALUES(1,'Angela');
INSERT INTO person_detail VALUES(2,'Tom');
INSERT INTO person_detail VALUES(3,'Heikki');

INSERT INTO person_job VALUES(1,'Documenter');
INSERT INTO person_job VALUES(2,'Developer');
INSERT INTO person_job VALUES(3,'Commiter');

CREATE VIEW person_detail_job_vw AS SELECT p.pid, p.pname, j.job FROM person_detail p LEFT JOIN person_job j ON (j.pid=p.pid);

SELECT * FROM person_detail_job_vw;
 pid | pname  |    job
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter

Till 9.0, User has to write rules something like given below:

1. INSERT RULE

CREATE RULE person_detaik_job_vw_INSERT AS ON INSERT TO person_detail_job_vw DO INSTEAD (
       INSERT INTO  person_detail VALUES(NEW.pid,NEW.pname);
       INSERT INTO  person_job VALUES(NEW.pid,NEW.job)
      );

2. UPDATE RULE:

CREATE RULE person_detaik_job_vw_UPDATE AS ON UPDATE TO person_detail_job_vw DO INSTEAD (
       UPDATE person_detail SET pid=NEW.pid, pname=NEW.pname WHERE pid=OLD.pid;
       UPDATE person_job SET pid=NEW.pid, job=NEW.job WHERE pid=OLD.pid
      );

3. DELETE RULE:

  CREATE OR REPLACE RULE person_detaik_job_vw_DELETE AS ON DELETE TO person_detail_job_vw DO INSTEAD (
       DELETE FROM person_job WHERE pid=OLD.pid;
       DELETE FROM person_detail WHERE pid=OLD.pid
      );

Lets see RULE WORK:

 INSERT INTO person_detail_job_vw VALUES(4,'Singh','New JOB');
 INSERT 0 1

 SELECT * FROM person_detail_job_vw;
 pid | pname  |    job
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
   4 | Singh  | New JOB
(4 rows)

 UPDATE person_detail_job_vw SET job='PATCHER'  WHERE pid=4;
 UPDATE 1

  SELECT * FROM person_detail_job_vw;
 pid | pname  |    job
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
   4 | Singh  | PATCHER

 DELETE FROM person_detail_job_vw WHERE pid=4;
 DELETE 1

 SELECT * FROM person_detail_job_vw;
 pid | pname  |    job
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter

If view has more complex query, then I need to break into more RULES.

Now in PostgreSQL 9.1, user can use INSTEAD OF Trigger. Following is an example:
Trigger Function

CREATE OR REPLACE FUNCTION person_detail_job_vw_dml()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
   BEGIN
      IF TG_OP = 'INSERT' THEN
        INSERT INTO  person_detail VALUES(NEW.pid,NEW.pname);
        INSERT INTO  person_job VALUES(NEW.pid,NEW.job);
        RETURN NEW;
      ELSIF TG_OP = 'UPDATE' THEN
       UPDATE person_detail SET pid=NEW.pid, pname=NEW.pname WHERE pid=OLD.pid;
       UPDATE person_job SET pid=NEW.pid, job=NEW.job WHERE pid=OLD.pid;
       RETURN NEW;
      ELSIF TG_OP = 'DELETE' THEN
       DELETE FROM person_job WHERE pid=OLD.pid;
       DELETE FROM person_detail WHERE pid=OLD.pid;
       RETURN NULL;
      END IF;
      RETURN NEW;
    END;
$function$;

Trigger:

CREATE TRIGGER person_detail_job_vw_dml_trig
    INSTEAD OF INSERT OR UPDATE OR DELETE ON
      person_detail_job_vw FOR EACH ROW EXECUTE PROCEDURE person_detail_job_vw_dml();

Lets see its work:

postgres=# SELECT VERSION();
                                                            version
--------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.0 on x86_64-apple-darwin, compiled by i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit

postgres=# CREATE TRIGGER person_detail_job_vw_dml_trig
postgres-#     INSTEAD OF INSERT OR UPDATE OR DELETE ON
postgres-#       person_detail_job_vw FOR EACH ROW EXECUTE PROCEDURE person_detail_job_vw_dml();
CREATE TRIGGER
postgres=# INSERT INTO person_detail_job_vw VALUES(4,'Singh','New JOB');
INSERT 0 1
postgres=# SELECT * FROM person_detail_job_vw;
 pid | pname  |    job
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
   4 | Singh  | New JOB
(4 rows)

postgres=#  UPDATE person_detail_job_vw SET job='PATCHER'  WHERE pid=4;
UPDATE 1
postgres=# SELECT * FROM person_detail_job_vw;
 pid | pname  |    job
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
   4 | Singh  | PATCHER
(4 rows)
postgres=# DELETE FROM person_detail_job_vw WHERE pid=4;
DELETE 1
postgres=# SELECT * FROM person_detail_job_vw;
 pid | pname  |    job
-----+--------+------------
   1 | Angela | Documenter
   2 | Tom    | Developer
   3 | Heikki | Commiter
(3 rows)

Wow! Now, in 9.1, if somebody has to do any implementation, they can also do using plpgsql function.

Utility Operations improvement in PostgreSQL 9.1

1. Transaction-level advisory locks:

PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly.
Till 9.0, postgreSQL had only Session level locks. Now, in PostgreSQL 9.1, we have transaction level advisory lock. Some examples are given below:

BEGIN;

-- grabbing txn locks multiple times

SELECT
        pg_advisory_xact_lock(1), pg_advisory_xact_lock(1),
        pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2),
        pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1),
        pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2);

SELECT locktype, classid, objid, objsubid, mode, granted
        FROM pg_locks WHERE locktype = 'advisory'
        ORDER BY classid, objid, objsubid;

COMMIT;

Advantage of Advisory lock are given below:
1. Advisory locks are faster
2. It avoid MVCC bloat,
3. and It can be automatically cleaned up by the server at the end of the session.

Due to above reason users/developers should think of using advisory lock over making Database level locks.
Important Transaction advisory locks in PostgreSQL 9.1 are given below:

pg_advisory_xact_lock(key bigint)              :      Obtain exclusive transaction level advisory lock
pg_advisory_xact_lock(key1 int, key2 int)      :      Obtain exclusive transaction level advisory lock
pg_advisory_xact_lock_shared(key bigint)       :      Obtain shared transaction level advisory lock
pg_advisory_xact_lock_shared(key1 int, key2 int):     Obtain shared advisory lock for the current transaction
pg_try_advisory_xact_lock(key bigint):                Obtain exclusive transaction level advisory lock if available
pg_try_advisory_xact_lock(key1 int, key2 int:         Obtain exclusive transaction level advisory lock if available
pg_try_advisory_xact_lock_shared(key bigint): 	      Obtain shared transaction level advisory lock if available
pg_try_advisory_xact_lock_shared(key1 int, key2 int): Obtain shared transaction level advisory lock if available

2. New version of TRUNCATE (TRUNCATE … RESTART IDENTITY)
This is a new improvement in PostgreSQL 9.1.

Till 9.0, “TRUNCATE … RESTART IDENTITY” used to use “ALTER SEQUENCE RESTART” in backend to rollback sequences, in which, on error between truncating and commiting, sequence may get of out of sync with the table contents. In previous approach resetting of associated sequences used to happen before TRUNCATE of table and at the time of backend crash sequence used to get out of sync.

To fix this, in PostgreSQL 9.1, same command will create a new refilenode for a sequence with reset due to RESTART IDENTITY. If transaction aborts, then PG will automatically revert to old reflfilenode file. This approach requires exclusing lock on sequence, since TRUNCATE has already exclusive lock on TABLE, therefore having exclusive lock on associated sequence won’t have any effect.

3. COPY command improvement:
In PostgreSQL 9.1, Now copy has ENCODING Option. ENCODING option is useful when user wants to copy data in some other ENCODING. Some example is given below:

    postgres=# COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\' ENCODING 'sql_ascii';
"Jackson, Sam","\\h"
"It is \"perfect\".","	"
"",

4. EXPLAIN VERBOSE:
In PostgreSQL 9.1, VERBOSE Option has been included with EXPLAIN Command. Using this command user would be able to see the funcation call expression in a functionscan node. Example is given below:

   postgres=# explain (verbose,analyze) select max(sal) from emp;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1.18..1.19 rows=1 width=5) (actual time=0.027..0.027 rows=1 loops=1)
   Output: max(sal)
   ->  Seq Scan on public.emp  (cost=0.00..1.14 rows=14 width=5) (actual time=0.008..0.012 rows=14 loops=1)
         Output: empno, ename, job, mgr, hiredate, sal, comm, deptno
 Total runtime: 0.077 ms
(5 rows)

5. New VACUUM FULL VERBOSE and CLUSTER VERBOSE

In PostgreSQL 9.0, due to new implementation of VACUUM FULL, VERBOSE option was not giving much information. However, this is now fixed in PostgreSQL 9.1. Now VERBOSE with VACUUM FULL and CLUSTER will new information, which includes live and dead tuple and also if CLUSTER is using an index to rebuild the tablel. Some Example is given below:
CLUSTER VERBOSE Example:

postgres=# cluster verbose;
INFO:  clustering "public.emp" using sequential scan and sort
INFO:  "emp": found 0 removable, 14 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
CLUSTER
postgres=#

VACUUM FULL VERBOSE Example

postgres=# VACUUM FULL VERBOSE emp;
INFO:  vacuuming "public.emp"
INFO:  "emp": found 0 removable, 14 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

6. autovacuum improvement.

Now, in 9.1 autovacuum process will not wait for locked tables, on which lock already acquired by some other process.
In 9.1, autovacuum process will skip locked tables and will continue for vacuuming other tables and will try to vacuum such tables later.

UPSERT/MERGE using Writable CTE in PostgreSQL 9.1

There is always discussion of having UPSERT/MERGE in postgresql. Since, oracle and other RDBMS has this feature, therefore people ask about this feature in PostgeSQL.

In previous version of PostgreSQL, we used to implement it using functions. Now in PostgreSQL 9.1, user can implement this feature using Writable CTE.

PostgreSQL 9.1, now has Writable CTE. WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

Lets see how we can use Writable CTE for UPSERT.

Following are SQL Code which can be use in Oracle and PostgreSQL for creating sample data:

For oracle:

create table myTable
  (pid number, sales number, status varchar2(6));

create table myTable2
  (pid number, sales number, status varchar2(6));

insert into myTable2 values(1,12,'CURR');
insert into myTable2 values(2,13,'NEW' );
insert into myTable2 values(3,15,'CURR');
insert into myTable  values(2,24,'CURR');
insert into myTable  values(3, 0,'OBS' );
insert into myTable  values(4,42,'CURR');
select * from myTable2;

For PostgreSQL 9.1:

create table myTable
  (pid numeric, sales numeric, status varchar(6));

create table myTable2
  (pid numeric, sales numeric, status varchar(6));

insert into myTable2 values(1,12,'CURR');
insert into myTable2 values(2,13,'NEW' );
insert into myTable2 values(3,15,'CURR');
insert into myTable  values(2,24,'CURR');
insert into myTable  values(3, 0,'OBS' );
insert into myTable  values(4,42,'CURR');
select * from myTable2;

In Oracle, people use Merge Something like given below:

merge into myTable2 m
         using myTable d
          on (m.pid = d.pid)
   when  matched
   then  update set   m.sales  = m.sales+d.sales
                ,     m.status = d.status
   when  not matched
   then  insert values (d.pid,d.sales,'NEW');

SQL> select * from myTable2;
       PID	SALES STATUS
---------- ---------- ------
	 1	   12 CURR
	 2	   37 CURR
	 3	   15 OBS
	 4	   42 NEW

In PostgreSQL 9.1, with writable CTE:

WITH upsert as
(update mytable2 m set sales=m.sales+d.sales, status=d.status from mytable d where m.pid=d.pid
  RETURNING m.*
)
insert into mytable2 select a.pid, a.sales,'NEW' from mytable a where a.pid not in (select b.pid from upsert b);

postgres=# select * from mytable2 order by 1;
 pid | sales | status
-----+-------+--------
   1 |    12 | CURR
   2 |    37 | CURR
   3 |    15 | OBS
   4 |    42 | NEW

Now, we have Writable CTE which can help us make UPSERT in PostgreSQL.
Enjoy 🙂

New Functions/Improvements in PostgreSQL 9.1

1. SQL function format(text, …):
This function is similar to the C function sprintf; However only the following conversion specifications are recognized:

%s interpolates the corresponding argument as a string
%I escapes its argument as an SQL identifier
%L escapes its argument as an SQL literal
%% outputs a literal %.

A conversion can reference an explicit parameter position by preceding the conversion specifier with n$, where n is the argument position

Some Examples are given below:

postgres=# select format('%1$s %3$s', 1, 2, 3);
 format
--------
 1 3
(1 row)

postgres=# select format('Hello %s', 'World');
   format
-------------
 Hello World
(1 row)
postgres=# select format('Hello %s %1$s %s', 'World', 'Hello again');
            format
-------------------------------
 Hello World World Hello again
(1 row)

2. New string functions concat(), concat_ws(), left(), right(), and reverse()
(i). concat() function:
It Concatenate all arguments. NULL arguments are ignored. Example is given below:

postgres=# select concat('one');
 concat
--------
 one
(1 row)

postgres=# select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
        concat
----------------------
 123hellotf2010-03-09
(1 row)

(ii). concat_ws() function:
It works similar to concat() function, however in this function first argument will be seperators. Example is given below:

postgres=# select concat_ws('#',1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
         concat_ws
----------------------------
 1#2#3#hello#t#f#2010-03-09
(1 row)

postgres=# select concat_ws(',',10,20,null,30);
 concat_ws
-----------
 10,20,30
(1 row)

(iii). left() function:
This function returns first n characters in the string. When n is negative, return all but last |n| characters.

postgres=# select left('Hello World',2);
 left
------
 He
(1 row)
postgres=# select left('Hello World',-2);
   left
-----------
 Hello Wor
(1 row)

(iv). right() function:
This function returns last n characters in the string. When n is negative, return all but first |n| characters. Example is given below:

postgres=# select right('Hello World',2);
 right
-------
 ld
(1 row)

postgres=# select right('Hello World',-2);
   right
-----------
 llo World
(1 row)

(v) reverse() function:
   This function returns reversed string. Example is given below:
postgres=# select reverse('1234567');
 reverse
---------
 7654321
(1 row)

3. pg_read_binary_file() function:
This function is similar to pg_read_file, except it returns return content of Binary file in bytea format. Example is given below:

select pg_read_binary_file('testbin');
                              pg_read_binary_file
--------------------------------------------------------------------------------
 \x303131302020203030313109203031313020202030303031092030313131202020313030300a
(1 row)

4. New version of pg_read_file() for reading entire file:

In Previous version of PostgreSQL, pg_read_file() requires offset and length of data needs to read. Now in PostgreSQL 9.1, there is new version of pg_read_file, using which user would be able to read entire file. Example is given below:

postgres=# select pg_read_file('postgresql.conf');
                                                 pg_read_file
---------------------------------------------------------------------------------------------------------------
 # -----------------------------                                                                              +
 # PostgreSQL configuration file                                                                              +
 # -----------------------------                                                                              +
 #                                                                                                            +
 # This file consists of lines of the form:                                                                   +
 #                                                                                                            +
 #   name = value                                                                                             +
 #                                                                                                            +
 # (The "=" is optional.)  Whitespace may be used.  Comments are introduced with                              +
 # "#" anywhere on a line.  The complete list of parameter names and allowed                                  +
 # values can be found in the PostgreSQL documentation.                                                       +
 #                                                                                                            +
 # The commented-out settings shown in this file represent the default values.                                +
 # Re-commenting a setting is NOT sufficient to revert it to the default value;                               +
 # you need to reload the server.                                                                             +
 #                                                                                                            +
 # This file is read on server startup and when the server receives a SIGHUP                                  +
 # signal.  If you edit the file on a running system, you have to SIGHUP the                                  +
 # server for the changes to take effect, or use "pg_ctl reload".  Some                                       +
 # parameters, which are marked below, require a server shutdown and restart to                               +
 # take effect.                                                                                               +
 #                                                                                                            +
 # Any parameter can also be given as a command-line option to the server, e.g.,                              +
 # "postgres -c log_connections=on".  Some parameters can be changed at run time                              +
 # with the "SET" SQL command.                                                                                +
 #                                                                                                            +
 # Memory units:  kB = kilobytes        Time units:  ms  = milliseconds                                       +
 #                MB = megabytes                     s   = seconds                                            +
 #                GB = gigabytes                     min = minutes                                            +
 #                                                   h   = hours                                              +
 #                                                   d   = days                                               +
                                                                                                              +
:

4. Optional Third argument in array_to_string()/string_to_array() functions for NULL processing.

In PostgreSQL 9.1, array_to_string/string_to_array function has third argument for processing of NULL Values (In previous version of PostgreSQL, this was missing). Some Examples are given below:

postgres=# select array_to_string(array[1,2,3,4,NULL,6], ',', '*');
 array_to_string
-----------------
 1,2,3,4,*,6
(1 row)

Similarly for string_to_array, example is given below:

postgres=# select string_to_array('1,2,3,4,*,6', ',', '*');
 string_to_array
------------------
 {1,2,3,4,NULL,6}
(1 row)

XML Functions:

1. XMLEXISTS and xpath_exists

(i). XMLEXISTS function:

In postgreSQL XMLEXISTS Function has been added. Using this function, user can verify XPath’s first argument returns any nodes or not. This function returns true or false. Example is given below

postgres=# SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol');
 xmlexists
-----------
 f
(1 row)

postgres=# SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol');
 xmlexists
-----------
 t

(ii) xpath_exists function:
PostgreSQL 9.1, now has one more function xpath_exists. This function is a specialized form of the xpath function. Instead of returning the individual XML values that satisfy the XPath, this function returns a Boolean indicating whether the query was satisfied or not. This function is equivalent to the standard XMLEXISTS predicate, except that it also offers support for a namespace mapping argument. Example is given below:

postgres=# SELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml);
 xpath_exists
--------------
 f
(1 row)
postgres=# SELECT xpath_exists('//town[text() = ''Cwmbran'']','Bidford-on-AvonCwmbranBristol'::xml);
 xpath_exists
--------------
 t

2. xml_is_well_formed(), xml_is_well_formed_document(), xml_is_well_formed_content() functions:

(i) xml_is_well_formed() function:
xml_is_well_formed_document checks for a well-formed document example is given below:

 postgres=# SELECT xml_is_well_formed('bar');
 xml_is_well_formed
--------------------
 t
(1 row)

(ii). xml_is_well_formed_content() function:
This function checks for xml well-formed content. Example is given below

SELECT xml_is_well_formed_content('bar');
 xml_is_well_formed_content
----------------------------
 t
(1 row)

SELECT xml_is_well_formed_content('abc');
 xml_is_well_formed_content
----------------------------
 t
(1 row)

(iii). xml_is_well_formed_document function:
This function checks for a well-formed document. Example is given below:

postgres=# SELECT xml_is_well_formed_document('bar');
 xml_is_well_formed_document
-----------------------------
 t
(1 row)

postgres=# SELECT xml_is_well_formed_document('bar');
 xml_is_well_formed_document
-----------------------------
 f
(1 row)

Some Object Information Functions improvements in PostgreSQL 9.1

1. pg_describe_object:

In PostgreSQL 9.1, pg_describe_object function has been added. Using this function, user can get human readable string for understanding the pg_depend content as given below:

postgres=# select classid, objid, objsubid, pg_describe_object(classid, objid, objsubid) from pg_depend where classid &lt;&gt; 0 and pg_describe_object(classi
 classid | objid | objsubid |               pg_describe_object
---------+-------+----------+------------------------------------------------
    1247 | 16426 |        0 | type test_money
    1247 | 16425 |        0 | type test_money[]
    1259 | 16424 |        0 | table test_money
    1259 | 16429 |        0 | index test_money_idx
    2606 | 16431 |        0 | constraint uniq_constraint on table test_money

2. quote_all_identifiers (boolean) parameter:

With this parameter, User can force quoating of all identifiers in EXMPLAIN and in system catalog functions like pg_get_viewdef().
Example is given below:

postgres=# explain select * from emp;
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on emp  (cost=0.00..1.14 rows=14 width=41)
(1 row)

postgres=# set quote_all_identifiers to true;
SET
postgres=# explain select * from emp;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on "emp"  (cost=0.00..1.14 rows=14 width=41)
(1 row)

Similarly for pg_get_viewdef()/any system function, example is given below:

postgres=# select pg_get_Viewdef('salesemp');
                                                  pg_get_viewdef
-------------------------------------------------------------------------------------------------------------------
 SELECT emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm FROM emp WHERE ((emp.job)::text = 'SALESMAN'::text);
(1 row)

postgres=# set quote_all_identifiers to true;
SET
postgres=# select pg_get_Viewdef('salesemp');
                                                                 pg_get_viewdef
-------------------------------------------------------------------------------------------------------------------------------------------------
 SELECT "emp"."empno", "emp"."ename", "emp"."hiredate", "emp"."sal", "emp"."comm" FROM "emp" WHERE (("emp"."job")::"text" = 'SALESMAN'::"text");
(1 row)

Cluster-wise setting of quote_all_identifiers,will force pg_dump/pg_dumpall command to quote identifiers while taking database dump

3. Complete implementation of columns about sequence in information_schema.sequences:

In Previous versions of PostgreSQL Colums about sequence existed in information_schema.sequence view, however sequenece parameter were not implemented.

Now, in 9.1, there is complete implementation of sequence parameter in information_schema.sequeunces. Example is given below:

In 9.0:

postgres=# \x
Expanded display is on.
postgres=# select * from information_schema.sequences where sequence_name='next_empno';
-[ RECORD 1 ]-----------+-----------
sequence_catalog        | postgres
sequence_schema         | public
sequence_name           | next_empno
data_type               | bigint
numeric_precision       | 64
numeric_precision_radix | 2
numeric_scale           | 0
maximum_value           |
minimum_value           |
increment               |
cycle_option            |

In 9.1:

postgres=# select * from information_schema.sequences where sequence_name='next_empno';
-[ RECORD 1 ]-----------+--------------------
sequence_catalog        | postgres
sequence_schema         | public
sequence_name           | next_empno
data_type               | bigint
numeric_precision       | 64
numeric_precision_radix | 2
numeric_scale           | 0
start_value             | 8000
minimum_value           | 1
maximum_value           | 9223372036854775807
increment               | 1
cycle_option            | NO


4. public as a pseudo-role name in has_table_privilege() and related functions:

In pervious verions, public role was not recognized by has_*_previleges. Now in 9.1, public as pseudo-role name has been added, which allows checking for public permissions.
Example is given below:
In PostgreSQL 9.0:

postgres=# select has_table_privilege('public','emp','select');
ERROR:  role "public" does not exist

In postgreSQL 9.1:

postgres=# select has_table_privilege('public','emp','select');
 has_table_privilege
---------------------
 t
(1 row)

Client Applications Improvements in PostgreSQL 9.1

PostgreSQL 9.1 has come with new options and Improvement with Client Application. So, I thought to blog about those improvements.
Deprecated createlang/droplang:
In PostgreSQL 9.1, createlang/dronlang is now deprecated commands. Since in PostgreSQL 9.1, for creating additional language, user has to use CREATE EXTENSION SYNTAX, there these binaries now issue command given below:

CREATE EXTENSION plperl;
CREATE EXTENSION plpyhthon;

Added Features in psql:
In PostgreSQL 9.1, following are the important improvement done in psql command:

1. Know about connection.
Now psql has meta command \conninfo which gives detal about current connections. So, if you are connected to PostgreSQL database, then you can use command “\conninfo” to get detail about your connections, some example is given below:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "5432".

2. Know Function Source:
Now, user can use metacommand \sf [functionname(argument,argument,…)] to get the source of function. This is very useful command if somebody wants to look at the source code of any function. Some Example is given below:

 postgres=# \sf test_func1()
CREATE OR REPLACE FUNCTION public.test_func1()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
   RAISE NOTICE 'TEST Function 1';
END;
$function$

postgres=# \sf test_func1(integer)
CREATE OR REPLACE FUNCTION public.test_func1(a integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
   RAISE NOTICE 'Input variable result is %',a;
   return a;
END;
$function$

If user wants to few function body with line number, then he/she can use \sf+ command, example is given below:

postgres=# \sf+ test_func1(integer)
        CREATE OR REPLACE FUNCTION public.test_func1(a integer)
         RETURNS integer
         LANGUAGE plpgsql
1       AS $function$
2       BEGIN
3          RAISE NOTICE 'Input variable result is %',a;
4          return a;
5       END;
6       $function$

3. Shortcut command for knowing installed language in database.
In psql, now user can us \dL to know about installed language, some example is given below:

postgres=# \dL
      List of languages
  Name   |  Owner   | Trusted
---------+----------+---------
 plperl  | postgres | t
 plperlu | postgres | f
 plpgsql | postgres | t
(3 rows)

postgres=# \dL+
                                                                     List of languages
  Name   |  Owner   | Trusted | Internal Language |      Call Handler      |       Validator        |          Inline Handler          | Access privileges
---------+----------+---------+-------------------+------------------------+------------------------+----------------------------------+-------------------
 plperl  | postgres | t       | f                 | plperl_call_handler()  | plperl_validator(oid)  | plperl_inline_handler(internal)  |
 plperlu | postgres | f       | f                 | plperlu_call_handler() | plperlu_validator(oid) | plperlu_inline_handler(internal) |
 plpgsql | postgres | t       | f                 | plpgsql_call_handler() | plpgsql_validator(oid) | plpgsql_inline_handler(internal) |
(3 rows)

4. Now, user schema and Catalog Schema:

Till 9.0, \dn meta command used to show system schema and user schema both, now in 9.1, \dn meta command will show only User schema and if user wants to view System schema with user schema, then he/she can use S(“system”) option in psql. Snapshot is given below:

postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)

postgres=# \dnS+
                                     List of schemas
        Name        |  Owner   |  Access privileges   |           Description
--------------------+----------+----------------------+----------------------------------
 information_schema | postgres | postgres=UC/postgres+|
                    |          | =U/postgres          |
 pg_catalog         | postgres | postgres=UC/postgres+| system catalog schema
                    |          | =U/postgres          |
 pg_temp_1          | postgres |                      |
 pg_toast           | postgres |                      | reserved schema for TOAST tables
 pg_toast_temp_1    | postgres |                      |
 public             | postgres | postgres=UC/postgres+| standard public schema
                    |          | =UC/postgres         |
(6 rows)

5. Meta command “\e” and “\ef” with line number:
Now, in 9.1, psql allows line number with \e and \ef. Using this option user can place its cursor to any linenumber. Snapshot is given below:

\e 3

CREATE FUNCTION test_func1() returns void
as
 $$
BEGIN
  RAISE NOTICE 'TEST Function 1';
END;
$$ language plpgsql;

6. Set Client encoding from Operating System locale by default:

From 9.1, onwards, if PGCLIENTENCODING environment variable is not set, then psql will take the client encoding from OS.

postgres=# \! echo $PGCLIENTENCODING

postgres=# show client_encoding ;
 client_encoding
-----------------
 SQL_ASCII
(1 row)

edbs-MacBook-Pro:~ postgres$ export PGCLIENTENCODING=UTF8
edbs-MacBook-Pro:~ postgres$ echo $PGCLIENTENCODING
UTF8
edbs-MacBook-Pro:~ postgres$ psql
psql (9.1.0)
Type "help" for help.

postgres=# show client_encoding ;
 client_encoding
-----------------
 UTF8
(1 row)

7. \d meta command distinguish between unique indexes and unique constraint:

There used to be a confusion with \d command till 9.0, now in 9.1, \d command will give proper information on unique constraint and unique indexes, this way user would be able to distinguish in unique constraint and unique index. Example is given below:

postgres=# \d test_money
 Table "public.test_money"
 Column | Type  | Modifiers
--------+-------+-----------
 t      | money |
Indexes:
    "test_money_idx" UNIQUE, btree (t)
    "uniq_constraint" UNIQUE CONSTRAINT, btree (t)

8. \dt+ shows table size using pg_table_size.

Till 9.0, \dt+ meta command used to show relation size, now from 9.1 on wards user will get exact information on table size, since this meta command uses pg_table_size function at the backend. Example is given below:

postgres=# \dt+
                         List of relations
 Schema |    Name    | Type  |  Owner   |    Size    | Description
--------+------------+-------+----------+------------+-------------
 public | test_money | table | postgres | 8192 bytes |
(1 row)

Some more psql meta commands in PostgreSQL 9.1:

 \dE[S+] [PATTERN]     list foreign tables
 \dx[+]  [PATTERN]     list extensions
 \det[+] [PATTERN]     list foreign tables
 \des[+] [PATTERN]     list foreign servers
 \dew[+] [PATTERN]     list foreign-data wrappers

Added Feature in pg_dump:

1. –quote-all-identifiers in pg_dump and pg_dumpall.

Now, user can force quoting of all identifiers using –quote-all-identifiers option using pg_dump and pg_dumpall command.
Example is given below as given below:

pg_dump --quote-all-identifiers postgres
pg_dumpall --quote-all-identifiers

2. directory option in pg_dump command:
In 9.1, pg_dump command has option -F has option d (directory), using this option user can mention the directory name and pg_dump will create that directory to keep the Backup Dump in that directory. For example:

pg_dump -Fd -f testbackup postgres

Above command will create testbackup directory with file for each table and blob being dumped, plus it will create a Table of Contents(TOC), which describe about the dumped objects in machine readable format (pg_restore can read this file), as given below:

edbs-MacBook-Pro:testbackup vibhor$ pwd
edbs-MacBook-Pro:~ vibhor$ pg_dump -Fd -f testbackup postgres
edbs-MacBook-Pro:~ vibhor$ cd testbackup/
edbs-MacBook-Pro:testbackup vibhor$ ls
2125.dat.gz	2126.dat.gz	toc.dat
edbs-MacBook-Pro:testbackup vibhor$