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 […]
PostgreSQL “Point-in-time Recovery” (PITR) also called as incremental database backup , online backup or may be archive backup. The PostgreSQL server records all users’ data modification transaction like insert, update or delete and write it into a file call write-ahead (WAL) log file. This mechanism use the history records stored in WAL file to do […]
PostgreSQL comes with an easy to use export tool to export data from PostgreSQL database. In this tutorial, we show you how to export data from PostgreSQL to a file or csv file. 1. Connect PostgreSQL Use psql command to connect to the PostgreSQL database. $ psql -p 5433 -U dba dbname P.S 5433 is […]
Today, i found out that one of the SQL query is hanging in PostgreSQL, and never release itself. “Cancel query” command won’t help, and the query just hanging there and show an “idle in transaction” status. It left me no choice but go Debian terminal to issue “kill” command to terminate it manually. Query hanging […]
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 […]
“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 […]
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 SELECT scoreid FROM n_score ORDER BY RANDOM() limit 1; 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 […]
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) mkyong@mkyong-desktop:~/Desktop/index$ gcc -o test.o test.c test.c:8:22: error: libpq-fe.h: No […]
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 To list all of the available options of pg_dump , please issue following command. pg_dump -? -p, –port=PORT database server port number -i, –ignore-version proceed […]
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 org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = character varying It’s seem like data […]
I will make a performance test between partition table and non partition table. Here are two tables both contains 200 millions of data. 1) hashvalue_pt (Partiton tables In Month) 2) hashvalue (Non Partiton tables) P.S Please visit Partition Table In PostgreSQL (Create Partition) – Part 1 If you do not know how to do table […]
Here i provide few steps to guide how to install Perl in windows PostgreSQL 1) Install ActivePerl to make your windows support Perl script. 2) Set Perl bin folder to windows environment variable 3) Restart PostgreSQL 4) Issue Createlang command C:\>createlang -U postgres plperl DB_NAME If you hit “The specified module could not be found” […]
If you do not know how to do table partition in PostgreSQL, please study Partition Table In PostgreSQL (Create Partition) – Part 1 Here i will provide a simple function to demonstrate how to insert million of data into partition table. –create sequence for testing CREATE SEQUENCE hashvalue_PT_serial START 1; –Generate Dynamic data for testing […]
Partition table in PostgreSQL is very easy to do, It involve inheritance concept and trigger of PostgreSQL. Here i provide a sample to demonstrate how to partition table in PostgreSQL. Before proceed, please understand some basic concept like,er… better i provide a concept of partition “time” in a table. “Mother” — (Child1, Child2, Child3,Child4, Child5) […]
This is a surprise for me that PostgreSQL do not have any build-in job scheduler. However It did pack into pgAdmin source but i wonder why it didnt intsall it by default? Here i provide some steps to show how to install pgAgent(Job Scheduler) on windows as services 1) Go to your PgAdim file path […]
After we installed and configure PostgreSQL properly, it is no problem to access locally, however when we access through the network , we hit following error message. could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host “10.0.0.6” and accepting TCP/IP connections on port 5432? It is because PostgreSQL disable network […]
It’s been a long time i didnt update my blog, recently i’m busy research on Hyper Estraier , it is a powerful full text search engine written in c, i will write some information about it future. Ok i provide following steps to show how to compile PostgreSQL database source code in linux. 1) Please […]
Here i share how i install postgresql in fedora core. I spent full day to find out a solution. Hope this help 1) First please get a PostgeSQL source code n compile it for installation or using yum command in fedora core. a) Regarding PostgeSQL source code Compilation, please visit here https://mkyong.com/database/how-to-compile-postgresql-database-source-code-in-linux/ b) Regarding Yum […]
Here i show how to uninstall Postgresql from fedora core. change to root user or process owner and issue following command. yum remove postgresql Hope this help.
PostgreSQL support function like create a new table(with data) from an existing table. It can be achieved with following two syntax. 1) CREATE TABLE ‘NEW_TABLE_NAME’ AS SELECT * FROM ‘TABLE_NAME_YOU_WANT_COPY’; 2) SELECT * INTO ‘NEW_TABLE_NAME’ FROM ‘TABLE_NAME_YOU_WANT_COPY’ ; Sometime i also use this method to temporary backup table :), according to PostgresSQL ‘CREATE TABLE AS’ […]