My boss ask me export some data from database for him to do statistic report. PostgreSQL provided easy and useful export feature to do it. Here i will demonstrate how to export data or query result from PostgreSQL into a file or csv file.

1) Connect to PostgreSQL with psql command

yongmo@abcdb:~$ psql -p 5433 -U dba dbname

P.S i installed my PostgreSQL in port 5433, so i need to specific port number.
P.S Type \? to view all available command

2) Type \o /home/yongmo/data25000.csv, it will export query result to /home/yongmo/data25000.csv file.

dbname=> \o /home/yongmo/data25000.csv

3) Type query that you want to export

dbname=> select url from urltable where scoreid=1 limit 25000;

4) Done, all query result exported to /home/yongmo/data25000.csv

Here is the full command

yongmo@abcdb:~$ psql -p 5433 -U dba dbname
Password for user dba: 
Welcome to psql 8.2.4 (server 8.3.3), the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
 
WARNING:  You are connected to a server with major version 8.3,
but your psql client is major version 8.2.  Some backslash commands,
such as \d, might not work properly.
 
dbname=> \o /home/yongmo/data25000.csv
dbname=> select url from urltable where scoreid=1 limit 25000;
dbname=> \q

Today i find out one of the query is hanging in PostgreSQL, it can’t release itself, it just seem like stuck in PostgreSQL forever.

I issue a command to cancel it in PostgreSQL, but it seem not working. Query just hanging there and show an “idle in transaction” status. I have no way to terminal it, it left me no choice but go Debian terminal to issue kill command to terminate it manually.

Query hanging or not responding in PostgreSQL is because i didn’t handle transaction manager properly in my web application. When System accidently shut down, running query will hanging in PostgreSQL and my transaction manager (DataSourceTransactionManager) is not manage to rollback whole transaction. I have no idea why it’s not working. After i changed my transaction manager to JtaTransactionManager, it work correctly and no transaction hanging in middle way when system accidentally shut down.

However here i show how to terminal the hanging query in PostgreSQL. The hanging query will display as “idle in transaction” in PostgreSQL. I have to list out all PostgreSQL processes and issue a kill terminate command to terminate the query manually.

1) Issue ps -ef | grep postgres to list out all processes belong to postgres user.

mkyong:~# ps -ef | grep postgres
postgres 13648     1  0 11:04 ?        00:00:00 /var/lib/postgresql/PostgresPlus8.3xxxxxxx
postgres 13651 13648  0 11:04 ?        00:00:00 postgres: logger process                                                                        
postgres 13653 13648  0 11:04 ?        00:00:00 postgres: writer process                                                                        
postgres 13654 13648  0 11:04 ?        00:00:00 postgres: wal writer process                                                                    
postgres 13655 13648  0 11:04 ?        00:00:00 postgres: autovacuum launcher process                                                           
postgres 13656 13648  0 11:04 ?        00:00:00 postgres: stats collector process                                                               
postgres 13668 13648  0 11:04 ?        00:00:00 postgres: postgres postgres 10.70.1.27(3734) idle                                               
postgres 13689 13648  0 11:05 ?        00:00:00 postgres: usrdba db_test 10.70.1.67(4164) idle                                               
postgres 13714 13648  0 11:06 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57586) idle in transaction                               
postgres 13721 13648  0 11:06 ?        00:00:16 postgres: usrdba db_test 10.70.1.67(4165) idle                                               
postgres 13832 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57592) idle                                              
postgres 13833 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57593) idle                                              
postgres 13834 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57594) idle                                              
postgres 13835 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57595) idle                                              
postgres 13836 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57596) idle                                              
postgres 14201 13648  0 11:23 ?        00:00:00 postgres: nrsdba postgres 10.70.1.8(4419) idle                                                  
postgres 14202 13648  0 11:23 ?        00:00:00 postgres: usrdba db_test 10.70.1.8(4420) idle                                                
postgres 14207 13648  0 11:24 ?        00:00:00 postgres: usrdba db_test 10.70.1.8(4421) idle                                                
root     18030 17992  0 13:46 pts/0    00:00:00 grep postgres
mkyong:~#

2) Notice process id 13714, idle in transaction, this is the hanging query in PostgreSQL. Issue following command to terminate the PostgreSQL process manually.

mkyong:~# kill 13714

or

mkyong:~# kill -TERM 13714

or

mkyong:~# kill -15 13714

3) Done, hanging query is gone

mkyong:~# ps -ef | grep postgres
postgres 13648     1  0 11:04 ?        00:00:00 /var/lib/postgresql/PostgresPlus8.3xxxxxxx
postgres 13651 13648  0 11:04 ?        00:00:00 postgres: logger process                                                                        
postgres 13653 13648  0 11:04 ?        00:00:00 postgres: writer process                                                                        
postgres 13654 13648  0 11:04 ?        00:00:00 postgres: wal writer process                                                                    
postgres 13655 13648  0 11:04 ?        00:00:00 postgres: autovacuum launcher process                                                           
postgres 13656 13648  0 11:04 ?        00:00:00 postgres: stats collector process                                                               
postgres 13668 13648  0 11:04 ?        00:00:00 postgres: postgres postgres 10.70.1.27(3734) idle                                               
postgres 13689 13648  0 11:05 ?        00:00:00 postgres: usrdba db_test 10.70.1.67(4164) idle                                                                         
postgres 13721 13648  0 11:06 ?        00:00:16 postgres: usrdba db_test 10.70.1.67(4165) idle                                               
postgres 13832 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57592) idle                                              
postgres 13833 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57593) idle                                              
postgres 13834 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57594) idle                                              
postgres 13835 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57595) idle                                              
postgres 13836 13648  0 11:10 ?        00:00:00 postgres: usrdba db_test 10.70.0.61(57596) idle                                              
postgres 14201 13648  0 11:23 ?        00:00:00 postgres: nrsdba postgres 10.70.1.8(4419) idle                                                  
postgres 14202 13648  0 11:23 ?        00:00:00 postgres: usrdba db_test 10.70.1.8(4420) idle                                                
postgres 14207 13648  0 11:24 ?        00:00:00 postgres: usrdba db_test 10.70.1.8(4421) idle                                                
root     18030 17992  0 13:46 pts/0    00:00:00 grep postgres
mkyong:~#

As we all know PostgreSQL 8.3 make very strong checking on data type, it make a lot application hit many data type error after migration from PostgreSQL8.x to PostgreSQL8.3. Mostly is cause by data type checking.

One of the common error is to_date() function. It accept two text as parameters.

to_date(text,text)

Ok now i create a table as following. A simple table call n_url_test contain a createddate as timestamp data type.

CREATE TABLE n_url_test
(
  urltestid bigserial NOT NULL,
  createddate timestamp without time zone DEFAULT now(),
  CONSTRAINT n_url_to_test_pkey PRIMARY KEY (urltestid)
)

I run following sql in PostgreSQL8.2, it return my expected result as ‘YYYY-MM-DD’ format.

--PostgreSQL 8.2
---------------
SELECT  to_date(createddate,'YYYY-MM-DD') FROM n_url_test;

However after i migrated to PostgreSQL8.3, i hit following error

--PostgreSQL 8.3
---------------
SELECT  to_date(createddate,'YYYY-MM-DD') FROM n_url_test;
 
--------------------------------------------------------------------------------
ERROR:  FUNCTION to_date(timestamp without time zone, unknown) does NOT exist
LINE 1: SELECT  to_date(createddate,'YYYY-MM-DD') FROM n_url_test
                ^
HINT:  No FUNCTION matches the given name AND argument types. 
You might need TO ADD explicit type casts.
 
********** Error **********

PostgreSQL 8.3 will not automatically convert from timestamp to text for us like what it does in previous version. We need to explicit type casts createddate like following

--PostgreSQL 8.3
---------------
SELECT  to_date(createddate::text,'YYYY-MM-DD') FROM n_url_test;

Append ::text to createddate will explicit convert datatype as text, It’s work. PostgreSQL8.3 strictly checking on data type is good function , but please do not forget there still have a lot people using previous version. It cause a lot problem if application is design base on ignore data type checking like what i mention above.

“public” is PostgreSQL default scheme, i have to change it because i had migrated a new database data into another new schema call “new_public”.
Before start to change, i have to check what is current PostgreSQL default schema?

1) Command

SHOW search_path

2) Check postgresql.conf

#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
 
# - Statement Behavior -
 
#search_path = '"$user",public'		# schema names
#default_tablespace = ''		# a tablespace name, '' uses
					# the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off

Here i show how to change Postgresql default schema.

SET search_path = new_schema

However above command is apply to current session only, next time schema will change back to public. If we want to make effect permanently, we have to change in postgresql.conf file like following.

#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
 
# - Statement Behavior -
 
#search_path = '"$user",public'		# schema names
search_path = '"$user",new_schema'	# NEW SCHEMA HERE
#default_tablespace = ''		# a tablespace name, '' uses
					# the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off

After that just restart PostgreSQL service. Done.

How can i get a random row from a query result in PostgreSQL?
The answer is easy, just using RANDOM() built-in function in PostgreSQL

Above example will get a random score id from table n_score. Everytime execute it will get a random score id. Done.

I want my subquery results (multi rows) return as a singale row in my query. For example.

SELECT u.url, (SELECT c.categoryid FROM category c 
WHERE c.categoryid = u.categoryid) FROM url u

If url contains multiple categories, subquery will return multiple rows as following
P.S Subquery return multiple rows is not supported in most of the database but PostgreSQL

url | categoryid
---------------------
1   |  1
1   |  2
2   |  1

Result i wanted is as following

url | categoryid
---------------------
1   |  1,2
2   |  1

I’m not sure how easy it can achieve in others database like oracle or mysql, however PostgreSQL provided Array data type, using array it’s very easy to get what i want. I have to changed my query to

SELECT u.url, (SELECT array(SELECT c.categoryid FROM category c 
WHERE c.categoryid = u.categoryid))AS categoryid FROM url u

Result ~

url | categoryid
---------------------
1   |  {1,2}
2   |  1

Done ~ PostgreSQL Array Rock ~

Create and compile a program with PostgreSQL libpq is not so straightforward. I created a sample program like “testlibpq.c” in PostgreSQL documentation to test it.

When i compile it, i hit following error

libpq-fe.h: No such file or directory
PGconn’ undeclared (first use in this function)

1
2
3
4
5
6
7
8
9
10
11
12
13

mkyong@mkyong-desktop:~/Desktop/index$ gcc -o test.o test.c
test.c:8:22: error: libpq-fe.h: No such file or directory
test.c:11: error: expected ‘)’ before ‘*’ token
test.c: In function ‘main’:
test.c:21: error: ‘PGconn’ undeclared (first use in this function)
test.c:21: error: (Each undeclared identifier is reported only once
test.c:21: error: for each function it appears in.)
test.c:21: error: ‘conn’ undeclared (first use in this function)
test.c:22: error: ‘PGresult’ undeclared (first use in this function)
test.c:22: error: ‘res’ undeclared (first use in this function)
test.c:41: error: ‘CONNECTION_OK’ undeclared (first use in this function)
test.c:57: error: ‘PGRES_COMMAND_OK’ undeclared (first use in this function)
test.c:83: error: ‘PGRES_TUPLES_OK’ undeclared (first use in this function)

It do need to include -I/usr/include/postgresql/ in order to compile it, please check your database or Linux administrator, or just type “pg_config –includedir” to find out where is PostgreSQL include file located.

However I still hit error like follwing

undefined reference to `PQfinish’
undefined reference to `PQconnectdb’

1
2
3
4
5
6
7
8
9
10
11
12
13

mkyong@mkyong-desktop:~/Desktop/index$ gcc -I/usr/include/postgresql/  -o test.o test.c
/tmp/ccoOJzAT.o: In function `exit_nicely':
test.c:(.text+0xd): undefined reference to `PQfinish'
/tmp/ccoOJzAT.o: In function `main':
test.c:(.text+0x57): undefined reference to `PQconnectdb'
test.c:(.text+0x65): undefined reference to `PQstatus'
test.c:(.text+0x74): undefined reference to `PQerrorMessage'
test.c:(.text+0xac): undefined reference to `PQexec'
test.c:(.text+0xba): undefined reference to `PQresultStatus'
test.c:(.text+0xca): undefined reference to `PQerrorMessage'
test.c:(.text+0xef): undefined reference to `PQclear'
test.c:(.text+0x105): undefined reference to `PQclear'
test.c:(.text+0x118): undefined reference to `PQexec'

Library is missing. Include PostgreSQL library pathwith -L/usr/lib/postgresql/8.3/lib/, this all PostgreSQL configuration information can get it from “pg_config”.

But I still hit the same error again

undefined reference to `PQfinish’
undefined reference to `PQconnectdb”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mkyong@mkyong-desktop:~/Desktop/index$ gcc -I/usr/include/postgresql/ -L/usr/lib/postgresql/8.3/lib/ -o test.o test.c
/tmp/ccgWnRJg.o: In function `exit_nicely':
test.c:(.text+0xd): undefined reference to `PQfinish'
/tmp/ccgWnRJg.o: In function `main':
test.c:(.text+0x57): undefined reference to `PQconnectdb'
test.c:(.text+0x65): undefined reference to `PQstatus'
test.c:(.text+0x74): undefined reference to `PQerrorMessage'
test.c:(.text+0xac): undefined reference to `PQexec'
test.c:(.text+0xba): undefined reference to `PQresultStatus'
test.c:(.text+0xca): undefined reference to `PQerrorMessage'
test.c:(.text+0xef): undefined reference to `PQclear'
test.c:(.text+0x105): undefined reference to `PQclear'
test.c:(.text+0x118): undefined reference to `PQexec'
test.c:(.text+0x126): undefined reference to `PQresultStatus'
test.c:(.text+0x136): undefined reference to `PQerrorMessage'
test.c:(.text+0x15b): undefined reference to `PQclear'
test.c:(.text+0x171): undefined reference to `PQclear'

It do need to specified exactly library with -lpq to include libpg library, correct command is like following

gcc -I/usr/include/postgresql/ -L/usr/lib/postgresql/8.3/lib/ -lpq -o test.o test.c

Done, compile without error.

Here i demostrate how to backup and restore dabatase in PostgreSQL

1)Backup data with pg_dump
pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f “/usr/local/backup/10.70.0.61.backup” old_db

1

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f "/usr/local/backup/10.70.0.61.backup" old_db

To list all of the available options of pg_dump , please issue following command.

1

pg_dump -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name

2) Restore data with pg_restore
pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v “/usr/local/backup/10.70.0.61.backup”

1

pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v "/usr/local/backup/10.70.0.61.backup"

To list all of the available options of pg_restore , please issue following command.

1

pg_restore -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode

Today i hit a weird error, actually my company is plan to migrate existing j2ee web application from PostgreSQL 8.2 to latest PostgreSQL 8.3. But after i set up PostgreSQL 8.3 properly, and start web application, i keep hiting following error

It’s seem like data type mismatch error, funny is it working fine at PostgreSQL 8.2 but PostgreSQL 8.3? I googling few hours and find out, it’s probably has to do with the changes to implicit casting done in 8.3. It’s seem like PostgreSQL 8.3 put a lot effort to enchance and check data type.

This will delibrate a lot side effect, because we set string in valuelist to compare any coloums, and let PostgreSQL to do the casting automatically.
This is no longer support in PostgreSQL 8.3 (i guess). Here i provide two solution to solve above error.

Solution
—————
1) use setInt or setLong to cast to the appropriate data type before send statement to PostgreSQL

2) use explicit casting like ::smallint

for example

If you are using valuelist like me, you need to wrap statementBuilder to override StandardStatamentBuilder to your appropriate data type.

1
2
3
4
5
6
7
8
9
10
11
12

 <property name="statementBuilder">
     <bean id="xx" class="net.mlw.vlh.adapter.jdbc.util.StandardStatementBuilder">
	<property name="setters">
	 	<map>
			<entry key="sourceId">
				<ref local="intSetter"/>
			</entry>
 
		</map>
	</property>
     </bean>
  </property>

Hope work to you.

Regular Expression is a very powerful tools for programming language like java, .NET, PHP , Perl…or even PostgreSQL.
Here i write some basic examples to show how to use regular expression in PostgreSQL.

1) isdigit function - This function is missing in PostgreSQL, as this is a built-in function in others database.
This is a very useful function to validate only number allow. However we can create isdigit function ourself in PostgreSQL.

1
2
3
4
5
6
7

CREATE OR REPLACE FUNCTION isdigit(text) returns BOOLEAN AS '
select $1 ~ ''^(-)?[0-9]+$'' as result
' LANGUAGE sql;
 
--test function
SELECT isdigit('1') --return true
SELECT isdigit('A') --return false

2) isString function - This function is missing in PostgreSQL as well, as this is a built-in function in others database.
This is a very useful function to validate only string allow. However we can create isString function ourself in PostgreSQL.

1
2
3
4
5
6
7

CREATE OR REPLACE FUNCTION isString(text) returns BOOLEAN AS '
select $1 ~ ''^(-)?[a-zA-Z]+$'' as result
' LANGUAGE sql;
 
--test function
SELECT isString('1') --return false
SELECT isString('A') --return true

3) isIPAddress function - I do not think this is a built-in function in other database. This functions is use to validate IP Address allow.

Example A

1
2
3
4
5
6
7
8
9

--check ipaddress
CREATE OR REPLACE FUNCTION isIPAddress(text) returns BOOLEAN AS '
select $1 ~ ''^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$'' as result
' LANGUAGE sql;
 
--test function
SELECT isIPAddress('202.111.0.1') --return true
SELECT isIPAddress('202.ZZZ.0.A') --return false
SELECT isIPAddress('202.11199999999.1.100') --return true

Example B

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION isIPAddressStrict(text) returns BOOLEAN AS '
select $1 ~ ''^[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?$'' as result
' LANGUAGE sql;
 
--test function
SELECT isIPAddressStrict('202.111.0.1') --return true
SELECT isIPAddressStrict('202.ZZZ.0.A') --return false
SELECT isIPAddressStrict('202.11199999999.1.100') --return false

Wow, impressive right? Actually Regular Expression is more powerful than above simple function. It is worth to invest time to study on it. Please go here if you want to know more about it
http://en.wikipedia.org/wiki/Regular_expression

Please share your example to me also, if you do not mind. Thanks