Client Applications Improvements in PostgreSQL 9.1

PostgreSQL 9.1 has come with new options and Improvement for Client Application. So, I thought to blog about those improvements.

 Deprecated createlang/droplang: In PostgreSQL 9.1, createlang/dronlang is now deprecated commands. Since in PostgreSQL 9.1, for creating additional language, user has to use CREATE EXTENSION SYNTAX, there these binaries now issue command given below:

CREATE EXTENSION plperl;
CREATE EXTENSION plpyhthon;

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

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

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

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

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

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

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

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

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

3. Shortcut command for knowing installed language in database.

In psql, now user can us \dL to know about installed language, some example is given below:

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

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

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

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

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

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

\e 3

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

6. Set Client encoding from Operating System locale by default:
 From 9.1, onwards, if PGCLIENTENCODING environment variable is not set, then psql will take the client encoding from OS.

postgres=# \! echo $PGCLIENTENCODING

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

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

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

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

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

8. \dt+ shows table size using pg_table_size.

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

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

Some more psql meta commands in PostgreSQL 9.1:

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

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

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

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

pg_dump -Fd -f testbackup postgres

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

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