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=>  

Prepared Statements on PostgreSQL 8.4/9.0/9.1/9.2

Prepared Statements: 

PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.



Prepared Statements usage examples on select/Update/Delete/Insert :  


Insert Prepared statement:

postgres=# PREPARE ins(int) as INSERT INTO test  values($1);
PREPARE
postgres=# select * from pg_prepared_statements ;
 name |                     statement                     |         prepare_time         | parameter_types | from_sql 
------+---------------------------------------------------+------------------------------+-----------------+----------
 ins  | PREPARE ins(int) as INSERT INTO test  values($1); | 2013-05-07 09:42:27.64714-07 | {integer}       | t
(1 row)

postgres=# select * from test;
 id 
----
(0 rows)

postgres=# EXECUTE ins(1);
INSERT 0 1
postgres=# EXECUTE ins(10);
INSERT 0 1
postgres=# select * from test;
 id 
----
  1
 10
(2 rows)

Update Prepared statement:

postgres=# PREPARE up(int,int) as UPDATE test set id=$1 where id=$2;
PREPARE
postgres=# EXECUTE up(100,10);
UPDATE 1
postgres=# select * from test;
 id  
-----
   1
 100
(2 rows)

Delete Prepared statement:
postgres=# PREPARE del(int) as DELETE FROM test where id=$1;
PREPARE
postgres=# EXECUTE del(1);
DELETE 1
postgres=# select * from test;
 id  
-----
 100
(1 row)

Select Prepared statement:

postgres=# 
postgres=# PREPARE sel(int) as select * from test where id=$1;
PREPARE
postgres=# EXECUTE sel(100);
 id  
-----
 100
(1 row)

View the existing prepared statements:

postgres=# select * from pg_prepared_statements ;
 name |                         statement                         |         prepare_time          |  parameter_types  | from_sql 
------+-----------------------------------------------------------+-------------------------------+-------------------+----------
 up   | PREPARE up(int,int) as UPDATE test set id=$1 where id=$2; | 2013-05-07 09:44:20.745465-07 | {integer,integer} | t
 del  | PREPARE del(int) as DELETE FROM test where id=$1;         | 2013-05-07 09:45:39.987117-07 | {integer}         | t
 ins  | PREPARE ins(int) as INSERT INTO test  values($1);         | 2013-05-07 09:42:27.64714-07  | {integer}         | t
 sel  | PREPARE sel(int) as select * from test where id=$1;       | 2013-05-07 09:47:08.865568-07 | {integer}         | t
(4 rows)

Remove/Deallocate the existing prepared statements:


postgres=# DEALLOCATE prepare sel;
DEALLOCATE
postgres=# select * from pg_prepared_statements ;
 name |                         statement                         |         prepare_time          |  parameter_types  | from_sql 
------+-----------------------------------------------------------+-------------------------------+-------------------+----------
 up   | PREPARE up(int,int) as UPDATE test set id=$1 where id=$2; | 2013-05-07 09:44:20.745465-07 | {integer,integer} | t
 del  | PREPARE del(int) as DELETE FROM test where id=$1;         | 2013-05-07 09:45:39.987117-07 | {integer}         | t
 ins  | PREPARE ins(int) as INSERT INTO test  values($1);         | 2013-05-07 09:42:27.64714-07  | {integer}         | t
(3 rows)

Remove/Deallocate All the existing prepared statements:

postgres=# DEALLOCATE prepare all;
DEALLOCATE ALL
postgres=# select * from pg_prepared_statements ;
 name | statement | prepare_time | parameter_types | from_sql 
------+-----------+--------------+-----------------+----------
(0 rows)

postgres=# 

======Thank You======

JDBC connectivity with Postgresql9.1 configuration

1. Download JDBC jar file from Postgresql.org  .

http://jdbc.postgresql.org/download.html

2. place this file at PostgreSQL lib location.

cp -r postgresql-9.1-903.jdbc3.jar   /opt/PostgreSQL/9.1/lib/

Execution of Java Sample program.

a. create table and insert sample lines.

create table av_test(id int).

insert into av_test values(1),(2),(3);

b.create sample JavaTest.java program

=================================

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class JavaTest {

 public static void main(String[] args) throws ClassNotFoundException, SQLException{

  Class.forName("org.postgresql.Driver");

  Connection con = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5222/postgres", "postgres", "postgres");

  PreparedStatement ps = con.prepareStatement("SELECT id FROM PUBLIC.AV_TEST");

      System.out.println("Statement output " + ps.toString());

        ResultSet rs = ps.executeQuery();

          while (rs.next()) {

             System.out.println("Done!!!" +rs.getString("id"));

                  }

                   }

                   }

==============================

c.Execution.

[root@localhost java_test]# javac JavaTest.java

[root@localhost java_test]# java -cp :/opt/PostgreSQL/9.1/lib/postgresql-9.1-903.jdbc3.jar  JavaTest

Statement output SELECT id FROM PUBLIC.AV_TEST

Done!!!1

Done!!!2

Done!!!3

Done!!!4

[root@localhost java_test]# 

Sequence in PostgreSQL

Sequence In PostgreSQL:
--------------------------
What Is A Sequence?

A sequence is a special kind of database object designed for generating unique numeric identifiers.
It is typically used to generate artificial primary keys. Sequences are similar, but not identical,
to the AUTO_INCREMENT concept in MySQL.

Type 1:
--------
Here created a sequence with all options.
start with 4   --sequence first value will be 4,because it is starting with 4.
increment by 2
minvalue 2
maxvalue 10
cache 2  --it will keep 2 sequence values in memory.
cycle --when ever it reaches maxvalue,then it repeats with minvalue.
owned by all_sequence_options.id --Assigning sequence to particular table,then when ever we dropped a table then sequence also will get dropped off automatically.

EX:
edb=# create table all_sequence_options(id int,name text);
CREATE TABLE
edb=# create SEQUENCE all_seq increment by 2  minvalue 2 maxvalue 10 start with 4 cache 2 cycle owned by all_sequence_options.id;
CREATE SEQUENCE
edb=# \dS+ all_seq
              Sequence "enterprisedb.all_seq"
    Column     |  Type   |  Value  | Storage | Description
---------------+---------+---------+---------+-------------
 sequence_name | name    | all_seq | plain   |
 last_value    | bigint  | 4       | plain   |
 start_value   | bigint  | 4       | plain   |
 increment_by  | bigint  | 2       | plain   |
 max_value     | bigint  | 10      | plain   |
 min_value     | bigint  | 2       | plain   |
 cache_value   | bigint  | 2       | plain   |
 log_cnt       | bigint  | 0       | plain   |
 is_cycled     | boolean | t       | plain   |
 is_called     | boolean | f       | plain   |

Type 2:
--------
Create table,Serial keyword on column will create implicit sequence.

Create table seq_table(table_id Serial Not null,name varchar);

EX:
edb=# Create table seq_table(table_id Serial Not null,name varchar);
NOTICE:  CREATE TABLE will create implicit sequence "seq_table_table_id_seq" for serial column "seq_table.table_id"
CREATE TABLE

Table:
edb=# \d seq_table
                               Table "enterprisedb.seq_table"
  Column  |       Type        |                          Modifiers
----------+-------------------+--------------------------------------------------------------
 table_id | integer           | not null default nextval('seq_table_table_id_seq'::regclass)
 name     | character varying |

Sequence:
edb=# \dS+ seq_table_table_id_seq
              Sequence "enterprisedb.seq_table_table_id_seq"
    Column     |  Type   |         Value          | Storage | Description
---------------+---------+------------------------+---------+-------------
 sequence_name | name    | seq_table_table_id_seq | plain   |
 last_value    | bigint  | 1                      | plain   |
 start_value   | bigint  | 1                      | plain   |
 increment_by  | bigint  | 1                      | plain   |
 max_value     | bigint  | 9223372036854775807    | plain   |
 min_value     | bigint  | 1                      | plain   |
 cache_value   | bigint  | 1                      | plain   |
 log_cnt       | bigint  | 0                      | plain   |
 is_cycled     | boolean | f                      | plain   |
 is_called     | boolean | f                      | plain   |
edb=#

Restarting sequence:
--------------------
edb=#
edb=# ALTER  SEQUENCE  seq_table_table_id_seq  start with 800 restart;
ALTER SEQUENCE
edb=# \dS+ seq_table_table_id_seq
              Sequence "enterprisedb.seq_table_table_id_seq"
    Column     |  Type   |         Value          | Storage | Description
---------------+---------+------------------------+---------+-------------
 sequence_name | name    | seq_table_table_id_seq | plain   |
 last_value    | bigint  | 800                    | plain   |
 start_value   | bigint  | 800                    | plain   |
 increment_by  | bigint  | 1                      | plain   |
 max_value     | bigint  | 9223372036854775807    | plain   |
 min_value     | bigint  | 1                      | plain   |
 cache_value   | bigint  | 1                      | plain   |
 log_cnt       | bigint  | 0                      | plain   |
 is_cycled     | boolean | f                      | plain   |
 is_called     | boolean | f                      | plain   |

edb=# select nextval('seq_table_table_id_seq');
 nextval
---------
     801
(1 row)

Sequence with Primary key column :
-------------------------------------
If you create sequence on primary key column,it will avoid the duplicate sequence number entries in table.(if sequence restarts then old values may repeat).
edb=# Create table seq_table(table_id Serial primary key,name varchar);
NOTICE:  CREATE TABLE will create implicit sequence "seq_table_table_id_seq" for serial column "seq_table.table_id"
CREATE TABLE
edb=# \d seq_table
                               Table "enterprisedb.seq_table"
  Column  |       Type        |                          Modifiers
----------+-------------------+--------------------------------------------------------------
 table_id | integer           | not null default nextval('seq_table_table_id_seq'::regclass)
 name     | character varying |
Indexes:
    "seq_table_pkey" PRIMARY KEY, btree (table_id)

edb=# insert into seq_table(name) values('chiru');
INSERT 0 1
edb=# \dS+ seq_table_table_id_seq
              Sequence "enterprisedb.seq_table_table_id_seq"
    Column     |  Type   |         Value          | Storage | Description
---------------+---------+------------------------+---------+-------------
 sequence_name | name    | seq_table_table_id_seq | plain   |
 last_value    | bigint  | 1                      | plain   |
 start_value   | bigint  | 1                      | plain   |
 increment_by  | bigint  | 1                      | plain   |
 max_value     | bigint  | 9223372036854775807    | plain   |
 min_value     | bigint  | 1                      | plain   |
 cache_value   | bigint  | 1                      | plain   |
 log_cnt       | bigint  | 32                     | plain   |
 is_cycled     | boolean | f                      | plain   |
 is_called     | boolean | t                      | plain   |

edb=#  select * from seq_table;
 table_id | name
----------+-------
        1 | chiru
        5 | chiru
        6 | chiru
        7 | chiru
        8 | chiru
        9 | chiru
(6 rows)

edb=# select nextval('seq_table_table_id_seq');
 nextval
---------
       10

edb=# ALTER  SEQUENCE  seq_table_table_id_seq start with 5 restart;
ALTER SEQUENCE
edb=# \dS+ seq_table_table_id_seq
              Sequence "enterprisedb.seq_table_table_id_seq"
    Column     |  Type   |         Value          | Storage | Description
---------------+---------+------------------------+---------+-------------
 sequence_name | name    | seq_table_table_id_seq | plain   |
 last_value    | bigint  | 5                      | plain   |
 start_value   | bigint  | 5                      | plain   |
 increment_by  | bigint  | 1                      | plain   |
 max_value     | bigint  | 9223372036854775807    | plain   |
 min_value     | bigint  | 1                      | plain   |
 cache_value   | bigint  | 1                      | plain   |
 log_cnt       | bigint  | 0                      | plain   |
 is_cycled     | boolean | f                      | plain   |
 is_called     | boolean | f                      | plain   |

--One sequence number lost(sequence number increased from 10 to 11),Even row is not inserted into table due duplicate value.
edb=# insert into seq_table(name) values('chiru');
ERROR:  duplicate key value violates unique constraint "seq_table_pkey"

edb=# select nextval('seq_table_table_id_seq');
 nextval
---------
       11
(1 row)
 
Descending sequence:
----------------------
Creating a sequence with minus values.
edb=# create table desc_sequence_table(id int,name text);
CREATE TABLE
edb=# create SEQUENCE desc_seq increment by -1 ;
CREATE SEQUENCE
edb=# \dS+ desc_seq;
                    Sequence "enterprisedb.desc_seq"
    Column     |  Type   |        Value         | Storage | Description
---------------+---------+----------------------+---------+-------------
 sequence_name | name    | desc_seq             | plain   |
 last_value    | bigint  | -1                   | plain   |
 start_value   | bigint  | -1                   | plain   |
 increment_by  | bigint  | -1                   | plain   |
 max_value     | bigint  | -1                   | plain   |
 min_value     | bigint  | -9223372036854775807 | plain   |
 cache_value   | bigint  | 1                    | plain   |
 log_cnt       | bigint  | 0                    | plain   |
 is_cycled     | boolean | f                    | plain   |
 is_called     | boolean | f                    | plain   |

 edb=# insert into desc_sequence_table values(nextval('desc_seq'),'chiru');
 INSERT 0 1
 edb=# select * from desc_sequence_table;
  id | name
 ----+-------
  -3 | chiru
 (1 row)

 edb=# select nextval('desc_seq');
  nextval
 ---------
      -4

How to detach a sequence from a table:
----------------------------------------------------

postgres=# \d

            List of relations

 Schema |   Name    |   Type   |  Owner   

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

 public | t2        | table    | postgres

 public | t2_id_seq | sequence | postgres

(2 rows)

postgres=# alter SEQUENCE t2_id_seq OWNED by none ;
ALTER SEQUENCE

postgres=# drop table t2;

DROP TABLE

postgres=# \d

            List of relations

 Schema |   Name    |   Type   |  Owner   

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

 public | t2_id_seq | sequence | postgres

(1 rows)