Tamil charset in Postgresql

Storing Tamil charset into postgresql database.
I'm assumed your Operating System were installed Tamil font.

1) Create database in Postgresql with Encoding style as UNICODE

CREATE DATABASE tamildb
WITH OWNER = postgres
ENCODING = 'UNICODE';

2) Create table to insert Tamil charset

CREATE TABLE tamitbl (
emp_no int, emp_name varchar(50) );

INSERT INTO tamiltbl VALUES91,'சோலை முருகன்')

since your Operating System were installed Tamil fonts, table would accept records in tamil charset.

likewise you can add any other language charset valuse provided
OS were installed fonts.

Installing Postgresql on BOSS/Debian Image

last week when I was Installing Postgrersql 8.4 on Debian based OS Image, I have got few errors which listed here with solutions.


1) sh: /dev/null: Permission denied
fgets failure: Success
The program "postgres" is needed by initdb but was not found in the
same directory as "/opt/PostgreSQL/8.4/bin/

initdb".
Check your installation.

SOULUTION
full permission to " /dev/null " i.e chmode 777 /dev/null

then start postgresql by

/opt/PostgreSQL/8.4/bin/initdb -D /opt/PostgreSQL/8.4/data/
/opt/PostgreSQL/8.4/bin/postgres -D /opt/PostgreSQL/8.4/data/

2) psql: error while loading shared libraries: libpq.so.5: cannot open shared object file:
No such file or directory

SOLUTION

*) find / -name libpq.so.5
*) export LD_LIBRARY_PATH="/opt/PostgreSQL/8.4/lib/"

3)

A) psql: could not connect to server: No such file or directory
   Is the server running locally and accepting
      connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

SOLUTION
*) possibly change your default port no specified in postgres.conf file,
if you have mentioned other then default port no 5432 while installing. (OR)
*) Change the port no in postgresql.conf file and restart the DB server (OR)
*) instead of psql type full command like psql -p 5432 -h localhost

B)  sudo -u postgres createuser -D -A -P username 

ERROR : 

createuser: could not connect to database postgres: could not connect to server: No such file or directory
    Is the server running locally and accepting
     connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

SOLUTION  :

*) sudo -u postgres createuser -D -A -P username -h localhost

  -h --> server name  

 
4) sh: psql: command not found

SOLUTION

*) add postgresql path to .bashrc
PATH=$PATH:Postgres/8.4/main/bin  to root/.bashrc
*) restart bashrc file
boss:/$ source /root/.bashrc

5) locales not set or any issue related to locales in debian

SOLUTION

*) dpkg-reconfigure locales
     |
     |
      -- it will list the all the locales, select all locales in US_EN* 

6) FATAL: could not create shared memory segment: Cannot allocate memory

DETAIL: Failed system call was shmget(key=1, size=1081344, 03600).

HINT: This error usually means that PostgreSQL's request for a shared
memory segment exceeded available memory or swap space. To reduce the
request size (currently 1081344 bytes), reduce PostgreSQL's
shared_buffers parameter (currently 50) and/or its max_connections
parameter (currently 10).
The PostgreSQL documentation contains more information about 
shared memory configuration.

SOLUTION

aove problem can be sortout by different way,
*) Postgresql installed by deb package, here you go for the solution,

*) postgresql installed from bin file, if you get the above error
while create initdb,just increase the size of the shmall and shmmax value,
value of the variable stored in byte.

the above variable available in sysctl, list and find the variable format

$ sysctl -a
-- in BOSS os(DEBIAN based) the variable   "kernel.shmall and kernel.shmmax"
-- in mac OS X, variable "kern.sysv.shmmax and  kern.sysv.shmall"

increase the size of the varible by

sysctl -w kernel.shmmax=13421772800
sysctl -w kernel.shmall=13421772800

now start postgresql, it ll work.

LIST OUT ALL (FORIEN KEY,PRIMARY KEY, UNIQUE KEY) CONSTRAINTS WITH REFERENCED TABLE & FIELD :

Here is the simple utility query for list out all the constraint( CHECK, UNIQUE, PRIMARY & FOREIGN KEY) their name, table name,column name from where constraint created and references table name with column name.

from this query you can also get hierarchical list of all tables

SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,

rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name

WHERE lower(tc.constraint_type) in ('foreign key')

here you can comment WHERE condition to list out all constraint, else it ll list only FOREIGN KEY constraint.

By running above utility you can get foreign key hierarchy in postgresql.

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

Below query will return the referencing table and field information for the parent table test_master.

Get all child table for the given parent table

SELECT *
FROM information_schema.table_constraints tc
right JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_catalog = ccu.constraint_catalog
AND tc.constraint_schema = ccu.constraint_schema
AND tc.constraint_name = ccu.constraint_name
and ccu.table_name in ('test_master')
WHERE lower(tc.constraint_type) in ('foreign key');

To find all child tables that belong to a parent table you can use above query, change the table (test_master) .

Passwordless postgresql login

We can log in postgres without promoting/enter password through psql.

syntax:

sudo -u user_name psql db_name

i.e
sudo -u postgres psql postgres

postgres -- database name, if you omit this by default it will log in with postgres database only

may forget the password simply enter into psql mode simply by above command and alter user with new password by

alter user user_name with password 'new_password'


note:

use sudo for log in if you face any issue by log in throuogh

su postgres
psql

FATAL: bogus data in lock file in postgresql

some times after postgres server installed in your system you may get error when start up your database server like FATAL : Postgrersql server didn't start see log file for more info "/usr/local/pgsql/pg_log/startup.log", in log file if you could see error like
FATAL: bogus data in lock file "/tmp/.s.PGSQL.5432.lock": ", its simply because of fake lock file,
just remove the lock file from the location, start the server now it will work.

note: give full permission to /tmp folder ( chmod -R 777 /tmp).

postmaster is running but psql not running it shows
command not found

Check if psql starts with a fully quallified path:

find / -name psq
/opt/PostgreSQL/8.4/bin/psql -- ENTER

now it may solve ur issue's  

FATAL: could not create shared memory segment:

ERROR

Starting PostgreSQL 8.3 database server: mainThe PostgreSQL server failed to start. Please check the log output: 2010-09-29 11:49:05 UTC LOG: could not load root certificate file "root.crt": no SSL error reported 2010-09-29 11:49:05 UTC DETAIL: Will not verify client certificates. 2010-09-29 11:49:05 UTC FATAL: could not create shared memory segment: Cannot allocate memory 2010-09-29 11:49:05 UTC DETAIL: Failed system call was shmget(key=5433001, size=29368320, 03600). 2010-09-29 11:49:05 UTC HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 29368320 bytes), reduce PostgreSQL's shared_buffers parameter (currently 3072) and/or its max_connections parameter (currently 103). The PostgreSQL documentation contains more information about shared memory configuration. failed!

SOLUTION

Here in error itself clearly specified the solution what we have to do..
just reduce the shared buffer ( shared memory ) size which value defined in RESOURCE USAGE part of the postgresql.conf file

in unix system path is /etc/postgresql/8.3/main/postgresql.conf

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 24MB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB

--------------------------------------------------------------------------------
once edit this value restart the server..

Analytic function in postgresql - continue.....

list of analytic function available in postgresql is

• row_number()
• rank()
• dense_rank() 
• lag(value any [, offset integer [, DEFAULT any ]])
• lead(value any [, offset integer [, DEFAULT any ]])
• first_value(value any)
• last_value(value any)
• nth_value(value any, nth integer)
• percent_rank()
• cume_dist()
• ntile(num_buckets integer)

you could find analytic function marked over green posted here,

here let look on remaining functions.

FIRST_VALUE and LAST_VALUE function

Syntax

FIRST_VALUE() OVER () or LAST_VALUE() OVER()

  The FIRST_VALUE analytic function picks the first record
from the partition after doing the ORDER BY. The  is computed
on the columns of this first record and results are returned.

  The LAST_VALUE function is used in similar context except
that it acts on the last record of the partition. But it not
picking up the right record instead its pick up same data
what salary column picks.

example

don=#  select person, dept, salary,first_value(salary)
over(partition by dept order by salary desc)-salary “ salary diff ”
from test;

 person   |    dept    | salary | diff by 1st record
-----------+------------+--------+-----------------------
phoebe    | accounting |   3700 |        0
lincoln   | accounting |   2950 |      750
rachel    | accounting |   2950 |      750
benjamin  | accounting |   2100 |     1600
michael   | accounting |   1700 |     2000
paul      | accounting |   1650 |     2050
alexander | it         |   3700 |        0
lj        | it         |   3250 |      450
sara      | it         |   2600 |     1100
ross      | it         |   1250 |     2450
theodore  | management |   3250 |        0
joey      | management |   3250 |        0
monica    | management |   2950 |      300
chandler  | management |   2400 |      850
fernando  | marketing  |   1650 |        0
bradley   | marketing  |   1000 |      650
(16 rows)

in above example first_vlaue() function return first record from
the partition value after order by clause executed.
i.e in accounting partition in would return 3700 ( first after order by desc )
always, from there we could calculate difference in salary from top salary
of the partition.

cluster already running - error while startup postgres postmaster

cluster already running / removed stale pid file, error you may get while start up the postmaster.

postmaster daemons create pid files when start up, remove them when the process exits. some time pid file exist even after postmaster daemons exit because the process crashed before it could remove the pid file or the system crashes and processes do not get a chance to remove their pidfiles even if they want to.

--> in this case just find out & remove postmaster pid file ( you could find out pid file in data directory of postgresql installed path)

or
--> find out the the running process id (PID) using port no & kill PID

lsof | grep 5432 --(lsof | grep PortNo) and kill the pid
kill -9 PID

or
--> find out the the running process id (PID) using process name & kill PID

ps -ef | grep postmaster --(lsof | grep PortNo) and kill the pid
kill -9 PID

Once stop the running postmaster using pid, start the postgres database server.

Function for spell out a number

Oracle having in built functionality to convert number into word format by using Juline(J) and spell out (SP) Pattern Modifiers for Date/Time Formatting.
Convert Number into Word in postgresql
for example

SELECT TO_CHAR(TO_DATE(12345,'J'),'JSP') FROM dual;

output like

TWELVE THOUSAND THREE HUNDRED FORTY-FIVE

in postgresql spell out (SP) pattern modifier not implemented (upto version postgresql 9.0)

here i have posted function to convert given number into word, lets look

create or replace function fn_spellout(p_num varchar) returns varchar as
$$
declare
v_word varchar(50);
v_spell varchar(1000) := '';
v_length integer;
v_pos integer := 1;
begin
select length(p_num) into v_length;
for v_pos in 1 .. v_length loop
--while v_pos <= v_length loop
SELECT case substring(p_num,v_pos,1)
when '1' then 'ONE '
when '2' then 'TWO '
when '3' then 'THREE '
when '4' then 'FOUR '
when '5' then 'FIVE '
when '6' then 'SIX '
when '7' then 'SEVEN '
when '8' then 'EIGHT '
when '9' then 'NINE '
when '0' then 'ZERO '
ELSE 'NULL'
end into v_word;
v_spell := v_spell || v_word;
--v_pos := v_pos+1;
end loop;
return v_spell;
end;
$$ language plpgsql

while execute this function it return output like

select fn_spellout('12345')

"ONE TWO THREE FOUR FIVE "

Analytic function in postgresql

Few function in oracle personally i like much more because of simplicity to achieve what we want, one of those function is analytic/windows functions. In postgresql it works 8.4 and later version only.

Key points:

       Analytic functions give aggregate result they do not group the result set.
They return the group value multiple times with each record. As such any
other non-"group by" column or expression can be present in the select clause.
       Analytic functions are computed after all joins, WHERE clause,
GROUP BY and HAVING are computed on the query. The main ORDER BY clause of the query
operates after the analytic functions, So analytic functions can only appear in the
select list and in the main ORDER BY clause of the query.

The general syntax of analytic function is:

function_name ([expression [, expression ... ]]) OVER ( window_definition )

where window_definition has the syntax

[ existing_window_name ]

[ PARTITION BY expression [, ...] ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

Let see simple example for each function

1 avg(salary) over (partition by dept)

avg() return (average salary of the partition and) group value multiple time for each record with average salary for partition.

SELECT dept,salary, avg(salary) over (partition by dept) FROM test ORDER BY dept,salary DESC;

dept | salary | avg

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

accounting | 3700 | 2508.3333333333333333

accounting | 2950 | 2508.3333333333333333

accounting | 2950 | 2508.3333333333333333

accounting | 2100 | 2508.3333333333333333

accounting | 1700 | 2508.3333333333333333

accounting | 1650 | 2508.3333333333333333

it | 3700 | 2700.0000000000000000

it | 3250 | 2700.0000000000000000

it | 2600 | 2700.0000000000000000

it | 1250 | 2700.0000000000000000

management | 3250 | 2962.5000000000000000

management | 3250 | 2962.5000000000000000

management | 2950 | 2962.5000000000000000

management | 2400 | 2962.5000000000000000

marketing | 1650 | 1325.0000000000000000

marketing | 1000 | 1325.0000000000000000

(16 rows)

2) rank() over(partition by dept order by salary desc)

rank() is kind-of like rownum. It returns which position given row gets when ordering by (whatever we put in “order by” clause inside “over ()”. ) . rank() is not really rownum – it’s not unique.
In case of a tie of 2 records at position N, RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record. While DENSE_RANK declares 2 positions N but does not skip position N+1.

don=# SELECT person,dept,salary, rank() over(partition by dept order by salary desc), dense_rank() over(partition by dept order by salary desc) FROM test ORDER BY dept,salary DESC;

person | dept | salary | rank | dense_rank

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

phoebe | accounting | 3700 | 1 | 1

lincoln | accounting | 2950 | 2 | 2

rachel | accounting | 2950 | 2 | 2

benjamin | accounting | 2100 | 4 | 3

michael | accounting | 1700 | 5 | 4

paul | accounting | 1650 | 6 | 5

alexander | it | 3700 | 1 | 1

lj | it | 3250 | 2 | 2

sara | it | 2600 | 3 | 3

ross | it | 1250 | 4 | 4

theodore | management | 3250 | 1 | 1

joey | management | 3250 | 1 | 1

monica | management | 2950 | 3 | 2

chandler | management | 2400 | 4 | 3

fernando | marketing | 1650 | 1 | 1

bradley | marketing | 1000 | 2 | 2

(16 rows)

3) row_number() over(partition by deptno order by hiredate desc)

ROW_NUMBER( ) gives a running serial number to a partition of records. It is very useful in reporting, especially in places where different partitions have their own serial numbers.

don=# select dept,salary,row_number() over ( partition by dept order by salary desc) from test;

dept | salary | row_number

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

accounting | 3700 | 1

accounting | 2950 | 2

accounting | 2950 | 3

accounting | 2100 | 4

accounting | 1700 | 5

accounting | 1650 | 6

it | 3700 | 1

it | 3250 | 2

it | 2600 | 3

it | 1250 | 4

management | 3250 | 1

management | 3250 | 2

management | 2950 | 3

management | 2400 | 4

marketing | 1650 | 1

marketing | 1000 | 2

(16 rows)4)

4) count(salary) over( partition by dept)

same like avg(), but it return count value for each partition

don=# select dept,salary,count(*) over ( partition by dept order by salary desc) from test;

dept | salary | count

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

accounting | 3700 | 6

accounting | 2950 | 6

accounting | 2950 | 6

accounting | 2100 | 6

accounting | 1700 | 6

accounting | 1650 | 6

it | 3700 | 4

it | 3250 | 4

it | 2600 | 4

it | 1250 | 4

management | 3250 | 4

management | 3250 | 4

management | 2950 | 4

management | 2400 | 4

marketing | 1650 | 2

marketing | 1000 | 2

(16 rows)

5) LEAD and LAG

LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row. The general syntax of LEAD is shown below:

LEAD (, , ) OVER ()

is the expression to compute from the leading row.
is the index of the leading row relative to the current row.
is a positive integer with default 1.
is the value to return if the points to a row outside the partition range.

SELECT deptno, empno, sal,

LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,

LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL

FROM emp WHERE deptno IN (10, 20) ORDER BY deptno, sal DESC;



 DEPTNO  EMPNO   SAL NEXT_LOWER_SAL PREV_HIGHER_SAL

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

     10   7839  5000           2450               0

     10   7782  2450           1300            5000

     10   7934  1300              0            2450

     20   7788  3000           3000               0

     20   7902  3000           2975            3000

     20   7566  2975           1100            3000

     20   7876  1100            800            2975

     20   7369   800              0            1100



8 rows selected.

full query

SELECT person, dept, salary, avg(salary) over (partition by dept), rank() over(partition by dept order by salary desc),row_number() over(partition by dept order by salary desc) , count(salary) over( partition by dept ) FROM test ORDER BY dept,salary DESC ;

Execute procedure in oracle10G EX

writing procedure in oracle 10g EX is same as any other version of oracle database server but while executing procedure we have to call procedure in begin end block rather then EXECUTE or EXEC command

let see simple example.............

creating procedure

CREATE OR REPLACE PROCEDURE sp_addVal (var1IN NUMBER, var2 IN NUMBER, var3 OUT NUMBER)
IS
BEGIN
var3:=var1+var2;
END;

executing procedure


DECLARE
v_sum NUMBER;
BEGIN
sp_addVal(50,50,v_sum);
DBMS_OUTPUT.PUT_LINE('output of test is '||v_sum);
END;

[
other then oracle10g EX simply call the procedure like
VARIABLE v_sum NUMBER;
EXEC sp_addVal(50,50,v_sum)
]
output

output of test is 100

explicit type converstion in postgresql

To convert value from one type to another use explicit type conversion function

1) to_timestamp -- used to convert bigint to timestamp with or with out timezone.
2) epoch -- used to convert timestamp to bigint value.

here see the example

1) to_timestamp

SELECT to_timestamp(1283437580); -- it returns the output like

to_timestamp
timestamp with time zone
----------------------
"2010-09-02 19:56:20+05:30"

want to display the timestamp with out timezone

SELECT to_timestamp(1283437580)::timestamp;

to_timestamp
timestamp with time zone
----------------------
"2010-09-02 19:56:20"

2) epoch

SELECT EXTRACT(epoch FROM current_timestamp(0)) -- it return output like

date_part
double precision
-------------------
1283753630

-- more explicit type conversion will update here...

cross database triggers in postgresql

now we can write Trigger in local database based on trigger event(insert/update/delete) trigger function may execute local/remote database. as everyone knows how to write trigger function execute on local/same database. let us see how can we write trigger function execute on remote database server.

steps
1) create trigger function
2) create trigger
3) execute sql statement for firing trigger event

1) create trigger function

1) Creating trigger function would execute on remote db server table

CREATE OR REPLACE FUNCTION fn_remote_insert() RETURNS TRIGGER
AS
$BODY$
DECLARE
-- declare anyvariable over here
BEGIN
PERFORM
DBLINK_EXEC('dbname=remoteDB host=190.10.1.5','INSERT INTO remoteTbl VALUES('||new.eid||','''||new.ename||''')');
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql'

2) Create trigger on local db server table

CREATE TRIGGER tr_remote_insert
BEFORE INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE fn_remote_insert()

3) Firing trigger event by insert

INSERT INTO table1 VALUES (986,'employeeName');

after above insert statement it will fire the trigger " tr_remote_insert " and trigger execute procedure, the procedure would insert new row in remote table.

by this way we can also  synchronize two PostgreSQL database server....

Querying cross database

for querying cross database should install dblilnk which is available under postgresql-contrib-xxx package.

installation on unix/debain/boss

$ apt-get install postgresql-contrib-8.3

$ su postgres

$ psql dbName < /usr/share/postgresql/8.3/contrib/dblink.sql

now you could be able to querying from different database or server

1) querying from other database on same server

SELECT *

FROM dblink('dbname=test1','select id,name from emp')

AS emp( id int,ename varchar);

here

• host --- specify the remote server ip
  
• dbname -- specify the database name
  
• port -- specify port no other the default (5432)
  
• username -- specify the username (if you did not specify this, dblink will connect
  
• password -- specify the password ( with current username & password)

since we are using querying on same server we omit others'

also we need to specify output structure of the table like AS emp( id int,ename varchar); with out this we may get error like

" ERROR: a column definition list is required for functions returning "record" "

2) Querying other database from remote server

SELECT *

FROM dblink('dbname=test1 host=192.10.1.5','select id from students')

AS stu(id int);

here we ip address of remote host using 'host' parameter.

3) Bulk insert into local table from remote server

SELECT t1.*

INTO test2

FROM dblink('dbname=saas-spms', 'SELECT * FROM test1)

AS t1(emp_no integer, degree varchar);

here table test2 should be new table, if already exist our database it shows error like : relation "test2" already found.

4) querying into remote server
If we want local table insert/update into remote postgres server
we can try like,

for insert :

select

dblink_exec('dbname=test1 host=192.10.1.5','insert into emp(id,ename) values(8,''employeeName'')')

for delete :

select

dblink_exec('dbname=test1 host=192.10.1.5','delete from emp where id=8')

here
dblink --> connect with remote server to execute the query which is return row (i.e Select )
dblink_exec --> connect to remote server and execute query which is not return row.

Display size of all database in postgresql

pg_database_size(dbname) function used to return size of given database in byte format.we can use pg_size_pretty() function to convert byte into human readable format. i.e.

SELECT pg_size_pretty(pg_database_size('dbname'))

now if you would like display the size of all available database in postgres server


create or replace function pg_function_alldb_size() returns void as $$
declare
pg_cursor_dbsize cursor for select datname from pg_database;
dbname pg_database.datname%type;
size varchar(50);
begin
open pg_cursor_dbsize;
loop
fetch next from pg_cursor_dbsize into dbname;
exit when not found;
select * into size from pg_size_pretty(pg_database_size(dbname));
raise notice 'db name is % and size is % ',dbname,size;
end loop;
return ;
end;
$$ language plpgsql

• pg_cursor_dbsize is cursor which stores the all database name from pg_database table
• pg_size_pretty(pg_database_size(dbname)) it returns size of the database to the size variable.
  
• raise notice - used to display the output.
  

how to backup & restore cross databse PostgreSQL server

backup and restore database from two different server
PostgreSql provide excellent feature like cross database restore, i.e backup database from locale DB and restore to local/remote another DB at same time.

syntax:

for backup & restore in local / same server

pg_dump -d SourceDBName -U userName | psql TargetDBName
for backup from local server & restore to remote server
pg_dump -d SourceDBName -U userName | psql -h RemoteServerIP TargetDBName

note :

• pipe ( | ) symbol used here for direct the output of one command to another.
• -h option used for specify the remote server ip

Cross Table reference in PostgreSQL.
backup and restore the specific table

as same like database we can also backup specific table from local database and restore in to another database either remote/local server.
first we would see how to backup and restore specific database table
for backup:
pg_dump -d DBName -t TableName -U UserName -f TableBackupFileName.sql ( or )pg_dump -d DBName -t TableName -U UserName > targetpath/TableBackupFileName.sql

note:

• -t option used to specify the tablename to backup
• -f option used for specify the target file name where backup would store

for restore
psql -f TableBackupFileName.sql DBName

backup & restore specific table from two different server
now we would see how to backup table from locale server and restore into remote server database

pg_dump -d DBName -t TableName -U UserName | psql -h RemoteServerIP TargetDBName

here TargetDBName is the target database to restore the table. It could be

1. You can create TargetDB while restore.
2. you can restore table already existing TargetDB without any schema definition.
3. you can restore to existing DB with schema definition *

suppose you are trying to restore table to database TargetDBName which is already exist and it might have schema( * ) may through constraint violation error because table may have lot of constraint ( foreign key,check, etc..) . To avoid such error use -c option like..
pg_dump -d DBName -t TableName -U UserName -c | psql -h RemoteServerIP TargetDBName

note :
-c option useful for many reason but here what it do before restore to TargetDB is

• Drop the table with constraint.
• Create table without constraint.
• Insert values into table ( from backup file).
• Create constraint and index etc...

If you are using PostgreSQL 9+, then you would get this error while running the above statement.

ERROR :
/usr/lib/postgresql/9.1/bin/pg_dump: invalid option -- 'd'
Try "pg_dump --help" for more information.



SOLUTION : 
remove the -d option from the  statement ans try,
pg_dump DBName -t TableName -U UserName -c | psql -h RemoteServerIP TargetDBName

CREATE VIEW WITH CHECK OPTION in PostgreSql

CREATE VIEW working fine with PostgreSql but CREATE VIEW WITH CHECK OPTION would show error message like WITH CHECK OPTION is not implemented in postgres

In PostgreSql we don't have "create view .. with check option", we could do the same in alternative way by creating rule.

create rule

I) for insert

CREATE OR REPLACE RULE rule_name AS ON INSERT TO view_name
DO INSTEAD
INSERT INTO table_name VALUES(id=new.id,name=new.name)

II) for Update

CREATE OR REPLACE RULE rule_name AS ON UPDATE TO view_name
DO INSTEAD
UPDATE table_name SET name=new.name WHERE id=new.id

III) for Delete

CREATE OR REPLACE RULE rule_name AS ON DELETE TO view_name
DO INSTEAD
DELETE FROM table_name WHERE id=new.id

example

you are having table 'students ' with 'id' and 'name' column

create view vw_students as select * from students

create rule for up-datable view

CREATE OR REPLACE RULE update_vw AS ON UPDATE TO vw_students
DO INSTEAD
UPDATE students SET name=new.name WHERE id=new.id

now you can able update the view by

update vw_students set name='postgres' where id=1

note : parent table also get updated whenever you update on view

ILIKE keyword in PostgreSql

ILIKE keyword in PostgreSQL provides case insensitive  search.

PostgreSql provides case sensitive as well as case insensitive pattern matching, traditional LIKE operator do the same as other rdbms, ILILKE operator used for case insensitive pattern matching.

ex : SELECT name FROM employee WHERE name LIKE 'AN%'

query will display only ANTRO, ANBU, ANTUN etc..

ex : SELECT name FROM employee WHERE name ILIKE 'AN%'

query will display name start with 'an' irrespective case, ANTRO, antTro, ANBU,AnTUN, ANTUN etc..

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

Issue :: DATE data type with pattern matching operator (LIKE) in version 8.3 later.

Date data type with LIKE operator works fine for the prior to 8.3 version.

for ex : SELECT name FROM employee WHERE date_of_birth LIKE '2007-01-19%'; // older version works fine

query will display all the name of date_of_birth falls on 2007-01-19.

but version 8.3 will gives hint like
> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

and
This isn't a bug. 8.3 removes a bunch of implicit casts to text which
led to inappropriate behaviours. Prior to 8.3, your timestamps was
casts to text implicitly.

to get the same query output you may use "date_trunc" function its works like normal "trunc" function.

for ex : SELECT name FROM employee WHERE date_trunc('day',date_of_birth) = '2007-01-19';
query will round with date and display all the name.

note : instead of field ' day ' in date_trunc function you may use

microseconds

milliseconds

second

minute

hour

day

week

month

quarter

year

decade

century

millennium

function would round according to the field value.

TOP n query in PostgreSQL

SQL Server provides TOP clause to return top n row from query,

for ex : SELECT TOP 5 * FROM Tbl_Name order by col_name

in PostgreSql we have LIMIT clause limit the number of rows returned,

for ex : SELECT * FROM Tbl_Name ORDER BY col_name LIMIT 5

PostgreSql and MySql also provides cool feature with LIMIT clause called OFFSET

for ex : SELECT * FROM Tbl_Name ORDER BY col_name LIMIT 5 OFFSET 20

it will skip the row from 1 to 19 and return the record from 20th to 25th record.

Function in PL/PgSQL

before creating function, check your database has PL/PgSQL here

* create function

simple pl/pgsql function, adding row to the test table

CREATE OR REPLACE FUNCTION pg_function_insert(
eno INTEGER) RETURNS VARCHAR AS $$
DECLARE
eno_end INTEGER := eno + 5;
BEGIN
FOR num IN eno.. eno_end LOOP
insert into test values (num,'solai');
END LOOP;
RETURN 'rows success fully inserted' ;
END;
$$ LANGUAGE plpgsql;

* execute function

SELECT pg_function_insert(100) AS function_output

we created function with function name of pg_function_insert,input parameter is Integer and function returns String.
table object test has two column eid with integer and ename with varchar data type.
in for loop initial value is input value given by user while executing function ( in our example 100 ), loop will iterate five times because loop end value is eno+5.

in create function statement we have added $$, This is PostgreSQL dollar-quoting. When used in place of the usual single-quotation mark quoting ('), you don't have to escape single quotation marks within the body of the function. This makes them much easier to read. more about $$

* now you can run a query to view the result

select * from test

eid|ename

100|solai
101|solai
102|solai
103|solai
104|solai
105|solai

| is column separator

Setting up PL/PgSQL

One of the most powerful features of PostgreSQL is its support for user-defined functions written in various programming languages, including pure SQL, C, Perl, Python, and PHP. Perhaps the most common programming language for PostgreSQL functions is PL/pgSQL, because it comes with PostgreSQL and is easy to set up.

PL/pgSQL is a procedural language similar to Oracle's PL/SQL. It's much more powerful than pure SQL in that it supports variables, conditional expressions, looping constructs, exceptions etc.. for more details in this blog we are going to learn how adding PL/PgSQL to your Database

Installing PL/pgSQL to Database

To get started with PL/pgSQL, first make sure it's installed in your PostgreSQL database. If it was a part of the template1 database when your database was created, it will already be installed. To see whether you have it, run the following in the psql client:

SELECT true FROM pg_catalog.pg_language WHERE lanname = 'plpgsql';

If the result row has the value true, PL/pgSQL is already installed in your database. If not, quit psql and execute the command as postgres superuser

$ createlang plpgsql database_name
or
cd /usr/local/pgsql/bin/
$ createlang plpgsql -U postgrers_super_uesrname database_name

To add a language, you must have superuser access to the database. If you've just installed PostgreSQL, then you can likely use the default postgres user by passing -U postgres to createlang. From this point, you should be able write and execute function in your pg client.
NOTE : above we just added PL/PgSQL to particular database only, not for entire server.

To create function in postgres can go here

UI Design Guidelines for Web Application

The primary focus on UI enables

• Ease of development including:
• Coding
• Debugging
• Unit testing
• Cross-browser compatibility
• Consistency and standards
• Internationalization (i18n)
• Accessibility
• Performance
• Scalability
• Extendability
  
• Sustainability
• Help and documentation
  

Define the business objectives of the site (service provider requirements)

• What are the main purposes of the site/application? These could include disseminating information, advertising services, selling products, positioning in the market or demonstrating competency.
• Who do you want to visit the site, is it internet or intranet - what are the important user categories and what are their motivations and goals?
• What type of pages and information will attract users and meet their needs? E.g. hierarchically structured information, a database, downloads of software/files, incentives to explore the site.
• What are the quality and usability goals which can be evaluated? e.g. to demonstrate superiority of the organization to the competition, appropriateness of the web site to user's needs, professionalism of the web site, percentage of users who can find the information they need, ease with which users can locate information, number of accesses to key pages, percentage of users visiting the site who access key pages.
• What is the budget for achieving these goals for different parts of the site?

Identify responsibilities for achieving quality and usability objectives, and estimate the resources and budget for these activities

Site structure

• Structure information so that it is meaningful to the user.
• Categorize information across overlapping audience needs.
• A structure that makes sense to the user will often differ from the structure used internally by the data provider.
• Different user groups may need different interfaces.
  

Site Content

• Make text concise and objective.
• Make the text scan able with bulleted lists, highlighted keywords, meaningful headings and short sections of text.
• Headings and subheadings
• Visual aids

Website Layout - general design rules

A website is in effect a folder or directory containing files (web pages, images, scripts) and other folders. Website layout is about creating a structure for these folders and files and how they are linked together.

• Use page titles which make meaningful bookmarks
• All pages should link back to the home page.
• Any page should be a maximum of two clicks away i.e. 2 links. Or
• Minimize the number of clicks needed to reach the point where users are confident they are on the right track: the more clicks the more users you lose.
• Try not to have more than 20 links per page.
• Highlight important links/text
• Web Page names should be descriptive and contain keywords.
• Links to Internal pages should obey normal linking practice and have descriptive anchor text. I.e. keywords should be used in the link name.
• Every page should have at least one other page pointing to it (i.e. and incoming link).
• A link to a site-map (large sites only) should be included on each page so as to aid user navigation and make indexing by search engines easier.
• A link to a contact page should be included on each page.
• No visitor likes to wait for web pages that take much time to load. To design website that loads quickly and does not take more than 15 seconds.
• Do not expect users to read large amounts of text on-line: provide one large page for printing or a file to download.
  

Web Page Design

• The important information should fit on one screen, as some users will not bother to scroll the home page
• Search was always liked by users, and has now become mandatory for any large site since the amount of content keeps growing.
• Animation is almost always annoying and should be avoided most of the time
• Use the ALT tag to describe graphics, as many users do not wait for graphics to load
• Provide search topics using the META tag in the page heading.

Designing Effective Website Navigation

To be effective Your Website Navigation design should allow visitors to quickly and easily reach the parts of the site that they are interested in

• Make sure your links are well organized according to the order of importance.
• Navigation should be clear and consistent. The important links of your website should be on every page, in the same location, and in the same sequence.
• Design navigation on the top or on the left as these are the first places our eyes go to.
• Meaning of link text is clear and each is unique.
• Avoid putting navigation links at the bottom of the page as visitors will need to scroll right down to see the links.

Web Page Fonts

• The way in which you display the text on your web page will have a great impact for success.
• Webpage text always consistent with your fonts.
• The standard fonts used on the Internet are Arial and Verdana. (Simply because easiest to read on computer screen).
• The standard text size is 2
• Headlines, which require a larger font size, are a bit different. A popular headline font used is Georgia.
  

Selecting style/themes by user

Visitors switch between lists of alternative style sheets to apply to your site. With a change of style sheet the entire look of your site can be transformed.

• Simply define on your page a list of selectable style sheets (using: ).
• Persistent cookies are used to store the user's selection.
• Permanent session information storage.
  

Internalization – W3C I18N

• Encoding
• Use character rather then escape
• Language resource file, containing translated text strings.
• Use style sheets for presentational information. Restrict markup to semantics.
• Use an appropriate encoding on both form and server. Support local formats of names/addresses, times/dates, etc.
• Consider a liquid flow of text to allow for differences in word length.

• German text on average uses 1/3rd more characters per words than English and Asian languages often take up much less character space than English words
• Consider using CSS positioning for text elements or use flexible relative table widths where the cells expand or contract depending on character layout.

• Minimize text contained in graphic where possible.

• Graphics or images containing text will takes longer to localize and are not as easy to translate as HTML text.
• replacing text in a graphic with HTML text where possible
  

Technology

• HTML
• CSS
• AJAX Technology
• JavaScript Framework

Maintenance

Plan and review the site structure as it grows, review the user needs, and make sure the site continues to meet the needs.

• Monitor feedback from users.
• Monitor the words used when searching the site.
• Monitor where people first arrive on the site, and support these pages as entry points.
• Check for broken links.
• Compare your site to other comparable sites as web browsers and web design evolve.

Few top web design mistakes

(Learn from mistakes)

• Our site tries to tell you how wonderful we are as a company, but not how we're going to solve your problems.
• We've designed our site to meet our organization's needs (more sales/contributions) rather than meeting the needs of our visitors.
• We say "Welcome to..." on our home page.
• It takes longer than four seconds for the "Man from Mars" to understand what our site is about.
• Navigation graphics are not the same size and/or color.
• Our site doesn't make us look like credible professionals.
• Our home page — or any page — takes more than four seconds to load.
• We never conduct user testing.
• We don't analyze our log files.
• We don't know which design items are not necessary.
• Quickly scanning the page doesn't tell our visitors much about its purpose.
• We have not eliminated unnecessary design items.
• Our site has different looks on different pages or sections.
• We don't know if our site looks the same in the major browsers.
• Our pages have too much/too little white space.
• Our site mixes text colors on the page.
• We don’t put design elements where our visitors expect them.
• Logo is not on the top of every page and clicking it doesn't lead to the home page.
• Visited links don't change color.
• Our site doesn't make visitors feel they can trust us.
• We don't identify PDF files with an icon.
• Our site uses divider bars.
• Our logo does not look like it was professionally made.
• No one has spent the time figuring out if our color scheme alienates our international users.
• Our site breaks when visited with the JavaScript turned off.
• Our site doesn't have a privacy or legal statement page.
• Our site mixes and matches text sizes on the page.
• We don't know what content is popular.
• Our site's design was "borrowed" from another site.
• The important content does not fit in the first screen.

References

• WCAG
• http://www.w3.org/TR/2006/WD-WCAG20-20060427/appendixB.html
• http://www.w3.org/TR/WCAG10/full-checklist.html
• WebAIM

• http://www.webaim.org/standards/wcag/checklist

• unified WSAG

• http://trace.wisc.edu/archive/html_guidelines/authorcl.htm

• Internationalization

• http://www.w3.org/International/quicktips/#language