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)