Author Archives: aji nugroho

sqlstate and sqlerrm in PostgreSQL

what is sqlstate and sqlerrm ? All messages emitted by the PostgreSQL server are assigned five-character error codes that follow the SQL standard’s conventions for “SQLSTATE” codes. Applications that need to know which error condition has occurred should usually test the error code, rather … Read More

How to use Liquibase with PostgreSQL

What is Liquibase ? Liquibase is a database schema change management solution that enables you to revise and release database changes faster and safer from development to production. How liquibase works ? Liquibase uses SQL, XML, JSON, and YAML changelog files to list database … Read More

How to ‘Join’ in PostgreSQL

How we can accessed multiple table at a time? that were join queries came in handy. Queries that access multiple tables (or multiple instances of the same table) at one time are called join queries. They combine rows from one table with … Read More

Introduction to PostgreSQL common table expressions or CTEs

A common table expression is a temporary result set which you can reference within another SQL statement including SELECT, INSERT, UPDATE or DELETE. Common Table Expressions are temporary in the sense that they only exist during the execution of the query. The following shows the … Read More

PostgreSQL object hierarchy

Understanding the hierarchy of objects within PostgreSQL can help you avoid confusion as you get to know the system and read up on documentation. PostgreSQL’s main “global” object is a database cluster, which is just the name given to the collection … Read More

Vacuum and Vacuum Full PostgreSQL

VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables. Plain VACUUM (without FULL) … Read More

Exception Handling in PostgreSQL

By default, any error occurring in an SPL program aborts execution of the program. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION section. The syntax is an extension of the normal syntax for a BEGIN block: How it … Read More

PostGIS exercise : part 1

here we will have several question : suppose we have 5 points (a, b, c, d, e), each having it’s own latitude and longitude, consider we have another point (x) on another latitude and longitude, find the nearest and the … Read More

Import CSV file to PostgreSQL

In this session, We will give an example how to import csv file into postgresql table, first, create a new table called nyc_census_blocks_test Second, prepare the csv file, my path of csv file is : /var/lib/pgsql/nyc_census_data.csv To import CSV file … Read More

PostgreSQL Logical Replication

Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication. Logical … Read More