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)

Remote Copy:
---------------

Connect  remote system using psql -h option and perform below operation, the input/out files will reside local system only.

In normal copy command      COPY    end with semicolon  ';'

Where as Remote copy command    \COPY  end without semicolon ';'


Copying data from remote system to local system:
---------------------------------------------------
enterprisedb@adminedb-ThinkPad-SL510:/tmp$ /opt/PostgresPlus/9.1AS/bin/psql -h 172.24.35.131  -p 5444 edb
psql (9.1.2.2)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
edb=# \copy dept  to '/tmp/chiru_dept.copy'
edb=# \q

Copying data from local  system to remote system :

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

enterprisedb@adminedb-ThinkPad-SL510:/tmp$ /opt/PostgresPlus/9.1AS/bin/psql -h 172.24.35.131  -p 5444 edb

edb=#  \copy test1  from  '/tmp/chiru_test1.copy'

edb=# select * from test1;

 deptno |   dname    |   loc

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

     10 | ACCOUNTING | NEW YORK

     20 | RESEARCH   | DALLAS

     30 | SALES      | CHICAGO

(3 rows)

Excel function for concatenate

 =CONCATENATE("Insert into test values('",B1,"','",C1,"');")

streaming replication parameters

http://www.network-theory.co.uk/docs/postgresql9/vol3/StreamingReplication.html

psql Segmentation fault

bash-3.2$ /opt/PostgreSQL/9.1/bin/psql -p 5433
psql.bin (9.1.1)
Type "help" for help.
/opt/PostgreSQL/9.1/bin/psql: line 30: 25199 Segmentation fault      LD_LIBRARY_PATH=$PG_BIN_PATH/../lib:$LD_LIBRARY_PATH "$PG_BIN_PATH/psql.bin" "$@"

So remove some  contents in the .psql_history file and then restart instance.