Useful Date Functions in PostgreSQL

Date Functions:

----------------------

1) Now() in Transaction Block don't change the time,it will show the same as transaction start time:

---------------

prod_db=# BEGIN ;

BEGIN

prod_db=# 

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-15 15:12:24.323916-07

(1 row)

prod_db=#  SELECT clock_timestamp();

        clock_timestamp        

-------------------------------

 2014-09-15 15:43:11.407277-07

(1 row)

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-15 15:12:24.323916-07

(1 row)

prod_db=# END ;

COMMIT

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-15 15:43:28.662925-07

(1 row)

2) now() with interval usage :

---------------

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-15 15:12:24.323916-07

(1 row)

prod_db=# SELECT now()::date + interval '2 day';

      ?column?       

---------------------

 2014-09-17 00:00:00

(1 row)

 3) Increasing interval timings :

--------------

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-15 15:45:17.948343-07

(1 row)

prod_db=# SELECT now() + interval '1day + 1 hour';

           ?column?            

-------------------------------

 2014-09-16 16:45:31.601508-07

(1 row)

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-15 15:45:57.193686-07

(1 row)

prod_db=# SELECT now() + interval '1day + 1 hour + 1 minute';

           ?column?           

------------------------------

 2014-09-16 16:46:59.13767-07

(1 row)

prod_db=# SELECT now() + interval '1day + 1 hour + 1 minute + 1 second';

           ?column?           

------------------------------

 2014-09-16 16:46:58.13767-07

(1 row)

3) Decreasing interval timings :

---------------------

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-15 15:46:26.519146-07

(1 row)

prod_db=# SELECT now() - interval '1day + 1 hour + 1 minute';

           ?column?            

-------------------------------

 2014-09-14 14:45:35.910741-07

(1 row)

prod_db=# SELECT now() - interval '1day + 1 hour';

           ?column?            

-------------------------------

 2014-09-14 14:46:50.983887-07

(1 row)

prod_db=# SELECT now() - interval '1day';

           ?column?            

-------------------------------

 2014-09-14 15:46:57.448071-07

(1 row)

prod_db=# SELECT now() - interval '1day + 1 hour + 1 minute + 1 second';

           ?column?            

-------------------------------

 2014-09-14 14:46:21.792209-07

(1 row)

4) Extract:

    ----------

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-16 04:46:30.926406-07

(1 row)

prod_db=# SELECT extract(DAY from now());

 date_part 

-----------

        16

(1 row)

prod_db=# SELECT extract(DOW from now());

 date_part 

-----------

         2

(1 row)

prod_db=# SELECT extract(DOY from now());

 date_part 

-----------

       259

(1 row)

prod_db=# SELECT extract(MINUTE from now());

 date_part 

-----------

        47

(1 row)

prod_db=# SELECT extract(MONTH from now());

 date_part 

-----------

         9

(1 row)

prod_db=# SELECT extract(YEAR from now());

 date_part 

-----------

      2014

(1 row)

prod_db=# 

5) Time Zone Conversion :

     -----------------------------

prod_db=# SELECT now();

              now              

-------------------------------

 2014-09-16 04:52:15.909354-07

(1 row)

prod_db=# SELECT now() AT TIME ZONE 'GMT';

          timezone          

----------------------------

 2014-09-16 11:52:28.293192

(1 row)

prod_db=# SELECT now() AT TIME ZONE 'IST';

          timezone          

----------------------------

 2014-09-16 13:52:43.646523

(1 row)

prod_db=# SELECT now();

             now              

------------------------------

 2014-09-16 04:53:03.48466-07

(1 row)

prod_db=# SELECT now() AT TIME ZONE 'PST';

          timezone          

----------------------------

 2014-09-16 03:53:17.139931

(1 row)

prod_db=# SELECT now() AT TIME ZONE 'PDT';

          timezone          

----------------------------

 2014-09-16 04:53:22.027907

(1 row)

prod_db=# SELECT now() AT TIME ZONE 'EDT';

          timezone          

----------------------------

 2014-09-16 07:53:27.700054

(1 row)

prod_db=# SELECT  extract(YEAR from created),extract(MONTH from created),extract(day from created),extract(hour from created),extract(minute from created),count(*) from tb_daily_overflow group by 1,2,3,4,5 order by 1,2,3,4,5;

 date_part | date_part | date_part | date_part | date_part | count 

-----------+-----------+-----------+-----------+-----------+-------

      2014 |         9 |        15 |        10 |        20 |     2

      2014 |         9 |        15 |        10 |        22 |     2

      2014 |         9 |        15 |        14 |        55 |     1

      2014 |         9 |        16 |         4 |        56 |    10

      2014 |         9 |        16 |         5 |        56 |    10

      2014 |         9 |        16 |         6 |        56 |    10

      2014 |         9 |        16 |         7 |         2 |    10

      2014 |         9 |        16 |         7 |         7 |    10

      2014 |         9 |        16 |        10 |        15 |     1

      2014 |         9 |        17 |         7 |         7 |    10

      2014 |         9 |        18 |         7 |         7 |    10

      2014 |         9 |        19 |         7 |         7 |    10

(12 rows)

6) Truncateing or Rounding Date functions:

-----------------------------------------------------

prod_db=# SELECT date_trunc('year', now());

       date_trunc       

------------------------

 2014-01-01 00:00:00-08

(1 row)

prod_db=# SELECT date_trunc('month', now());

       date_trunc       

------------------------

 2014-09-01 00:00:00-07

(1 row)

prod_db=# SELECT date_trunc('day', now());

       date_trunc       

------------------------

 2014-09-16 00:00:00-07

(1 row)

prod_db=# SELECT date_trunc('hour', now());

       date_trunc       

------------------------

 2014-09-16 05:00:00-07

(1 row)

prod_db=# SELECT date_trunc('minute', now());

       date_trunc       

------------------------

 2014-09-16 05:08:00-07

(1 row)

prod_db=# SELECT date_trunc('second', now());

       date_trunc       

------------------------

 2014-09-16 05:08:54-07

(1 row)

prod_db=# SELECT date_trunc('millisecond', now());

         date_trunc         

----------------------------

 2014-09-16 05:09:03.884-07

(1 row)

prod_db=# SELECT date_trunc('microsecond', now());

          date_trunc           

-------------------------------

 2014-09-16 05:09:25.020095-07

(1 row)

prod_db=# 

prod_db=# SELECT date_trunc('day',created),date_trunc('hour',created),date_trunc('minute',created),date_trunc('second',created),count(*) from tb_daily_overflow  group by 1,2,3,4 order by 1,2,3,4;

     date_trunc      |     date_trunc      |     date_trunc      |     date_trunc      | count 

---------------------+---------------------+---------------------+---------------------+-------

 2014-09-15 00:00:00 | 2014-09-15 10:00:00 | 2014-09-15 10:20:00 | 2014-09-15 10:20:55 |     1

 2014-09-15 00:00:00 | 2014-09-15 10:00:00 | 2014-09-15 10:20:00 | 2014-09-15 10:20:58 |     1

 2014-09-15 00:00:00 | 2014-09-15 10:00:00 | 2014-09-15 10:22:00 | 2014-09-15 10:22:03 |     1

 2014-09-15 00:00:00 | 2014-09-15 10:00:00 | 2014-09-15 10:22:00 | 2014-09-15 10:22:05 |     1

 2014-09-15 00:00:00 | 2014-09-15 14:00:00 | 2014-09-15 14:55:00 | 2014-09-15 14:55:41 |     1

 2014-09-16 00:00:00 | 2014-09-16 04:00:00 | 2014-09-16 04:56:00 | 2014-09-16 04:56:32 |    10

 2014-09-16 00:00:00 | 2014-09-16 05:00:00 | 2014-09-16 05:56:00 | 2014-09-16 05:56:51 |    10

 2014-09-16 00:00:00 | 2014-09-16 06:00:00 | 2014-09-16 06:56:00 | 2014-09-16 06:56:54 |    10

 2014-09-16 00:00:00 | 2014-09-16 07:00:00 | 2014-09-16 07:02:00 | 2014-09-16 07:02:05 |    10

 2014-09-16 00:00:00 | 2014-09-16 07:00:00 | 2014-09-16 07:07:00 | 2014-09-16 07:07:11 |    10

 2014-09-16 00:00:00 | 2014-09-16 10:00:00 | 2014-09-16 10:15:00 | 2014-09-16 10:15:47 |     1

 2014-09-17 00:00:00 | 2014-09-17 07:00:00 | 2014-09-17 07:07:00 | 2014-09-17 07:07:23 |    10

 2014-09-18 00:00:00 | 2014-09-18 07:00:00 | 2014-09-18 07:07:00 | 2014-09-18 07:07:29 |    10

 2014-09-19 00:00:00 | 2014-09-19 07:00:00 | 2014-09-19 07:07:00 | 2014-09-19 07:07:34 |    10

(14 rows)

prod_db=# 

7) Some miscellaneous functions:

postgres=# SELECT timeofday() as tofd; SELECT statement_timestamp() as st; SELECT now() as now; SELECT localtimestamp as lts; SELECT transaction_timestamp() as tt; SELECT localtime lt;

                tofd                 

-------------------------------------

 Tue Sep 16 05:22:11.492081 2014 PDT

(1 row)

              st               

-------------------------------

 2014-09-16 05:22:11.492407-07

(1 row)

              now              

-------------------------------

 2014-09-16 05:22:09.355997-07

(1 row)

            lts             

----------------------------

 2014-09-16 05:22:09.355997

(1 row)

              tt               

-------------------------------

 2014-09-16 05:22:09.355997-07

(1 row)

       lt        

-----------------

 05:22:09.355997

(1 row)

postgres=# 

postgres=# 

postgres=# SELECT timeofday() as tofd; SELECT statement_timestamp() as st; SELECT now() as now; SELECT localtimestamp as lts; SELECT transaction_timestamp() as tt; SELECT localtime lt;

                tofd                 

-------------------------------------

 Tue Sep 16 05:22:17.556176 2014 PDT

(1 row)

              st               

-------------------------------

 2014-09-16 05:22:17.556356-07

(1 row)

              now              

-------------------------------

 2014-09-16 05:22:09.355997-07

(1 row)

            lts             

----------------------------

 2014-09-16 05:22:09.355997

(1 row)

              tt               

-------------------------------

 2014-09-16 05:22:09.355997-07

(1 row)

       lt        

-----------------

 05:22:09.355997

(1 row)

Deleting Duplicate values in PostgreSQL

dev=# select ctid,* from test;
 ctid  | id | name
-------+----+------
 (0,1) |  1 | RC
 (0,2) |  2 | RC
 (0,3) |  1 | RC
 (0,4) |  2 | RC
(4 rows)

dev=# select ctid,* from test where ctid not in ( select min(ctid) from test group by id,name);
 ctid  | id | name
-------+----+------
 (0,3) |  1 | RC
 (0,4) |  2 | RC
(2 rows)

dev=# delete from test where ctid not in ( select min(ctid) from test group by id,name);
DELETE 2
dev=# select ctid,* from test;
 ctid  | id | name
-------+----+------
 (0,1) |  1 | RC
 (0,2) |  2 | RC
(2 rows)

How to Rebuild Primary key constraints or Indexes in Postgresql 8.4 +

1)  Verified the two tables relations and check the data on those table. 
    If you required take table dump backup of these two tables.

test=# create table table_primary(id int primary key,name text);
NOTICE:  CREATE TABLE / PRIMARY KEY 

test=# create table table_foreign(id int references table_primary(id),location varchar);
CREATE TABLE

test=# \d table_primary
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "table_primary_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# \d table_foreign 
       Table "public.table_foreign"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 id       | integer           | 
 location | character varying | 
Foreign-key constraints:
    "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# insert into table_primary values (1,'RC');
INSERT 0 1
test=# insert into table_primary values (2,'RK');
INSERT 0 1
test=# insert into table_primary values (3,'Balu');
INSERT 0 1
test=# insert into table_primary values (4,'mahi');
INSERT 0 1
test=# insert into table_foreign values (1,'HYD');
INSERT 0 1
test=# insert into table_foreign values (2,'PUNE');
INSERT 0 1
test=# insert into table_foreign values (3,'PUNE');
INSERT 0 1
test=# insert into table_foreign values (4,'PUNE');
INSERT 0 1
test=# select * from table_primary ;
 id | name 
----+------
  1 | RC
  2 | RK
  3 | Balu
  4 | mahi
(4 rows)

test=# select * from table_foreign ;
 id | location 
----+----------
  1 | HYD
  2 | PUNE
  3 | PUNE
  4 | PUNE

2) Add new unique index before deleting existing primary key constraint,once it is done queries will start using new index.

test=# create unique index CONCURRENTLY unique_table_primary_id  on table_primary(id);
CREATE INDEX

test=# \d public.table_primary
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "table_primary_pkey" PRIMARY KEY, btree (id)
    "unique_table_primary_id" UNIQUE, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)


3)  Drop  existing primary key constraint,it will also dependent foreign key constraints also.
And then add the new primary key constraint and foreign key constraints those tables.

test=# begin ;
BEGIN
test=# alter table table_primary  drop constraint table_primary_pkey  CASCADE;  alter table table_foreign add  FOREIGN KEY (id) REFERENCES table_primary(id);
NOTICE:  drop cascades to constraint table_foreign_id_fkey on table table_foreign
ALTER TABLE
ALTER TABLE
test=# commit ;
COMMIT

5) verify and  make sure that you have the table definitions and contents of data in table,what you have before the operation.

test=# \d table_primary 
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "unique_table_primary_id" UNIQUE, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# \d public.table_foreign
       Table "public.table_foreign"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 id       | integer           | 
 location | character varying | 
Foreign-key constraints:
    "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# select * from table_primary ;
 id | name 
----+------
  1 | RC
  2 | RK
  3 | Balu
  4 | mahi
(4 rows)

test=# select * from table_foreign ;
 id | location 
----+----------
  1 | HYD
  2 | PUNE
  3 | PUNE
  4 | PUNE
(4 rows)

6) Now verify the tables primary and foreign key rules are working or not?

test=# insert into table_foreign values (10,'id');

ERROR:  insert or update on table "table_foreign" violates foreign key constraint "table_foreign_id_fkey"

DETAIL:  Key (id)=(10) is not present in table "table_primary".

test=# insert into table_primary values (1,'RC');

ERROR:  duplicate key value violates unique constraint "unique_table_primary_id"

DETAIL:  Key (id)=(1) already exists.

test=# insert into table_primary values (null,'RC');

ERROR:  null value in column "id" violates not-null constraint

7)  How to Add new column to existing composite Primary key : 


i) master table is having  composite PK with two columns.

postgres=# \d+ master1
                           Table "public.master1"
    Column    |  Type   | Modifiers | Storage  | Stats target | Description
--------------+---------+-----------+----------+--------------+-------------
 id           | integer | not null  | plain    |              |
 name         | text    | not null  | extended |              |
 created_date | date    |           | plain    |              |
 loc          | text    |           | extended |              |
Indexes:
    "master1_pkey" PRIMARY KEY, btree (id, name)
Child tables: child100,
              child200
Has OIDs: no

2)  drop existing Pk and  add new index as PK.

postgres=# Begin;

 create unique index CONCURRENTLY on master1 (id,name,created_date);
alter table table_primary  drop constraint table_primary_pkey  CASCADE;

alter table master1  add CONSTRAINT PK_my_table PRIMARY KEY USING INDEX master1_id_name_created_date_idx;

commit;

NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "master1_id_name_created_date_idx" to "pk_my_table"

ALTER TABLE

4) Verify the Table 

postgres=# \d+ master1 

                           Table "public.master1"

    Column    |  Type   | Modifiers | Storage  | Stats target | Description 

--------------+---------+-----------+----------+--------------+-------------

 id           | integer | not null  | plain    |              | 

 name         | text    | not null  | extended |              | 

 created_date | date    | not null  | plain    |              | 

 loc          | text    |           | extended |              | 

Indexes:

    "pk_my_table" PRIMARY KEY, btree (id, name, created_date)

Child tables: child100,

              child200

Has OIDs: no

How to get all databse schemas from Postgresql cluster

1)  Configure the dblink contrib module to the cluster.

 And create the extension on all database.

Postgres=# create extension dblink ;
CREATE EXTENSION

DB1=# create extension dblink ;
CREATE EXTENSION

DB2=# create extension dblink ;
CREATE EXTENSION

2)  Create the table to store ,output of your function.
postgres=# create table all_db_schemas(databasename name,schemaname name);
CREATE TABLE


3) Create the function on Postgres database.

create or replace function get_all_db_schemas() returns text as $$
declare
r record;
r1 record;
dbname1 name;
schemaname1 name;
done text default 'successes';
stmt1 text;
stmt3 text;
begin
for r in select datname from pg_stat_database
loop
dbname1 :=r.datname;
CONTINUE WHEN dbname1='template0';
stmt1 :='select * from dblink('
|| ''''
|| 'dbname='
|| dbname1
|| ''''
|| ','
|| ''''
|| 'select distinct schemaname from pg_statio_user_tables'
|| ''''
|| ') as t1(schemaname name)'
|| ';';
for r1 in execute stmt1
loop
schemaname1 :=r1.schemaname;
stmt3 := 'insert into all_db_schemas(databasename,schemaname) values ('
|| ''''
||dbname1
|| ''''
|| ','
|| ''''
|| schemaname1
|| ''''
|| ')'
|| ';' ;
execute stmt3;
end loop;
end loop;
return done;
end;
$$ language plpgsql;


4) Execute the function to get to collect  all the schemas from all the databases on cluster.

postgres=# select get_all_db_schemas();
 get_all_db_schemas
--------------------
 successes
(1 row)

5) Check the data got inserted into table or not?
postgres=# select * from all_db_schemas;
 databasename | schemaname
--------------+------------
 postgres     | public
 postgres     | s1
 db1            | public
 db2            | public
(4 rows)

How to Change System time and timezone in linux ( IST )

1. check current time .
2.change the date and time using below date command.

[root@pgham postgresql-9.3beta2]# date -s "5 Jul 2013 18:18:14"

Fri Jul  5 18:18:14 PDT 2013

3.change the time zone of the computer.

[root@pgham postgresql-9.3beta2]# cd /etc/

[root@pgham etc]# ln -sf /usr/share/zoneinfo/Asia/Calcutta localtime

4. verify date changed or not.

[root@pgham etc]# date

Fri Jul  5 18:22:29 IST 2013

[root@pgham etc]#

Table Partitioning on PostgreSQL

Table Partitioning on PostgreSQL:
+++++++++++++++++++++++
1)We are using Inheritance consept here to achive partitioning tables.
2)And also we have to use (function and triggers) or Rules to populate the data into partitioned tables.
3)Recomended to dont create any check onstarints or indexes on master table,if those are not comman for all child tables.

All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constr
aints) are not inherited.

Master Table:
---------------
DB1=# create table master1(id int,name text);
CREATE TABLE

Child tables:
---------------
DB1 =# CREATE  TABLE child100(check(id >0 and id<=100))inherits(master1);
CREATE TABLE
DB1 =# CREATE  TABLE child200(check(id >100 and id<=200))inherits(master1);
CREATE TABLE
DB1 =# CREATE  TABLE child300(check(id >200 and id<=300))inherits(master1);
CREATE TABLE

Indexes:
------------
DB1 =# CREATE INDEX child100_id_indx on child100(id);
CREATE INDEX
DB1 =# CREATE INDEX child200_id_indx on child200(id);
CREATE INDEX
DB1 =# CREATE INDEX child300_id_indx on child300(id);

Function & Trigger to achive the data to be populated into partationed tables:
------------------------------------------------------------------------------
CREATE or replace function partirion_check() returns trigger as $$
begin
if (NEW.id >0 and NEW.id <= 100)
then
insert into child100 values(NEW.*);
elsif (NEW.id >100 and NEW.id <= 200) 
then
insert into child200 values(NEW.*);
elsif (NEW.id >200 and NEW.id <= 300)
then
insert into child300 values(NEW.*);
else
RAISE EXCEPTION 'fix the partirion_check() function';
end if;
return null;
end;
$$ language plpgsql;

Triggers:
---------
DB1 =# CREATE  trigger partation_insert before insert on master1  for each row execute procedure partirion_check();
CREATE TRIGGER
DB1 =#

(OR)Instead of trigger and function we can use Rules to achive  partitioned data here:
--------------------------------------------------------------------------------------
DB1 =# CREATE RULE child100_insert as on insert to master1 where (id >0 and id<=100) do instead insert into child100 values(NEW.*);
CREATE RULE

Testing paratitioned tables:
------------------------------------
DB1 =# INSERT INTO master1 VALUES (100,'OPEN');
INSERT 0 0
DB1 =# INSERT INTO master1 VALUES (200,'OPEN');
INSERT 0 0
DB1 =# INSERT INTO master1 VALUES (300,'OPEN');
INSERT 0 0
DB1 =# SELECT * FROM master1;
 id  | name 
-----+------
 100 | OPEN
 200 | OPEN
 300 | OPEN
(3 rows)

DB1 =# SELECT * from child100;
 id  | name 
-----+------
 100 | OPEN
(1 row)

DB1 =# SELECT * from child200;
 id  | name 
-----+------
 200 | OPEN
(1 row)

DB1 =# SELECT * from child300;
 id  | name 
-----+------
 300 | OPEN
(1 row)


Note :

1) Maintenance Operations we needed to perform individually for all child tables tables.

Vacuum,Analyze,Freeze,Reidex .....etc.

2) Make sure that constraint_exclusion parameter should be "on"  or  "partition".


Case 1)  off ,it will give bad performance on queries.

postgres=# show constraint_exclusion;
 constraint_exclusion
----------------------
 off
(1 row)

test=# explain select * from master1 where id=240;
                                 QUERY PLAN                                
-----------------------------------------------------------------------------
 Result  (cost=0.00..31.50 rows=9 width=27)
   ->  Append  (cost=0.00..31.50 rows=9 width=27)
         ->  Seq Scan on master1  (cost=0.00..25.38 rows=6 width=36)
               Filter: (id = 240)
         ->  Seq Scan on child100 master1  (cost=0.00..2.25 rows=1 width=10)
               Filter: (id = 240)
         ->  Seq Scan on child200 master1  (cost=0.00..2.25 rows=1 width=10)
               Filter: (id = 240)
         ->  Seq Scan on child300 master1  (cost=0.00..1.62 rows=1 width=10)
               Filter: (id = 240)
(10 rows)

Case 2)  on or partition ,it will give good performance on queries.

test=# set constraint_exclusion=on;
SET
test=# explain select * from master1 where id=240;
                                 QUERY PLAN                                
-----------------------------------------------------------------------------
 Result  (cost=0.00..27.00 rows=7 width=32)
   ->  Append  (cost=0.00..27.00 rows=7 width=32)
         ->  Seq Scan on master1  (cost=0.00..25.38 rows=6 width=36)
               Filter: (id = 240)
         ->  Seq Scan on child300 master1  (cost=0.00..1.62 rows=1 width=10)
               Filter: (id = 240)
(6 rows)

       (OR)

test=# set constraint_exclusion=partition;
SET
test=# explain select * from master1 where id=240;
                                 QUERY PLAN                                
-----------------------------------------------------------------------------
 Result  (cost=0.00..27.00 rows=7 width=32)
   ->  Append  (cost=0.00..27.00 rows=7 width=32)
         ->  Seq Scan on master1  (cost=0.00..25.38 rows=6 width=36)
               Filter: (id = 240)
         ->  Seq Scan on child300 master1  (cost=0.00..1.62 rows=1 width=10)
               Filter: (id = 240)
(6 rows)

----

How to create Partitions on bigint based column,it is storing date and time :

my_db=# \d my_test

                                        Table "public. my_test"

        Column        |          Type          |                             Modifiers                             

----------------------+------------------------+-------------------------------------------------------------------

 id                   | integer                | not null default nextval('my_test_id_seq'::regclass)

 received_timestamp   | bigint                 | 

 sourceip             | numeric                | 

 destip               | numeric                | 

 my_db=# 

BEGIN;

Create table my_test_20140219 (check( ('1970-01-01 GMT'::timestamp + ((received_timestamp::bigint)::text)::interval)::date='2014-02-19')) inherits(my_test);

CREATE RULE my_test_20140219 as on insert to my_test where ( ('1970-01-01 GMT'::timestamp + ((received_timestamp::bigint)::text)::interval)::date='2014-02-19') do instead insert into my_test_20140219 values(NEW.*);

CREATE unique INDEX my_test_20140219_idx on my_test_20140219(id);

CREATE  index timestamp_sourceip_destip_20140219_idx on  my_test_20140219(sourceip, destip, received_timestamp);

END;

pg_dump --inserts  --data-only  -f /home/postgres/my_test2.dump  my_db

my_db# \i  my_test2.dump

my_db=# \dt+

                                 List of relations

 Schema |             Name              | Type  |  Owner   |  Size   | Description 

--------+-------------------------------+-------+----------+---------+-------------

 public | my_test          | table | postgres | 0 bytes | 

 public | my_test_20140219 | table | postgres | 696 kB  | 

(2 rows)

Prepared Transactions in PostgreSQL 9.2

Prepared Transactions:

PREPARE TRANSACTION prepares the current transaction for two-phase commit. After this command, the transaction is no longer associated with the current session; instead, its state is fully stored on disk, and there is a very high probability that it can be committed successfully, even if a database crash occurs before the commit is requested.

Once prepared, a transaction can later be committed or rolled back with COMMIT PREPARED or ROLLBACK PREPARED, respectively. Those commands can be issued from any session, not only the one that executed the original transaction.

Prepared transactions are session independent,we can create/prepare a prepared transaction on one session and the we can commit/rollback the prepared transaction in different session.

NOTE1: The commit/rollback user should be owner  of the transaction or superuser of he database,otherwise we will get errores.


NOTE2: If we have prepared transaction on the database it will prevent the database maintenance operations like "Reindex/Vacuum Full" .

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

postgres=# BEGIN ;
BEGIN
postgres=# COPY test from '/tmp/test_copy.sql';
COPY 7
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# PREPARE transaction 'trx1';
PREPARE TRANSACTION
postgres=# select * from test;
 id 
----
(0 rows)

postgres=# SELECT * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1716 | trx1 | 2013-05-07 04:04:56.620985-07 | postgres | postgres
(1 row)

postgres=# COMMIT PREPARED 'trx1';
COMMIT PREPARED
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# SELECT * from pg_prepared_xacts ;
 transaction | gid | prepared | owner | database 
-------------+-----+----------+-------+----------
(0 rows)

postgres=# PREPARE transaction 'trx2';
WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
ROLLBACK
postgres=# BEGIN ;
BEGIN
postgres=# DELETE FROM test  where id=10;
DELETE 2
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
(5 rows)

postgres=# 
postgres=# PREPARE transaction 'trx2';
PREPARE TRANSACTION
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# 
postgres=# ROLLBACK PREPARED 'trx2';
ROLLBACK PREPARED
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# 

Errors: we will get if we use prepared transaction in different users.

The below transactions created by  chiru2 and postgres users.i have tried to commit/rollbacked in different CHIRU users and received below errors.

Session 1:



postgres=> select current_user;
 current_user 
--------------
 chiru2
(1 row)

postgres=> select * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1723 | trx3 | 2013-05-07 04:19:45.99662-07  | postgres | postgres
        1730 | trx4 | 2013-05-07 04:30:54.648643-07 | chiru2   | postgres
(2 rows)

Session 2:

postgres=> select current_user;
 current_user 
--------------
 chiru
(1 row)

postgres=> select * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1723 | trx3 | 2013-05-07 04:19:45.99662-07  | postgres | postgres
        1730 | trx4 | 2013-05-07 04:30:54.648643-07 | chiru2   | postgres
(2 rows)

postgres=> 
postgres=> 
postgres=> COMMIT  prepared 'trx3';
ERROR:  permission denied to finish prepared transaction
HINT:  Must be superuser or the user that prepared the transaction.
postgres=> COMMIT  prepared 'trx4';
ERROR:  permission denied to finish prepared transaction
HINT:  Must be superuser or the user that prepared the transaction.
postgres=>