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
Author Archives: aji nugroho
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
Tags: full join, inner join, join, left join, right join
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
Tags: CTE, windowfunction
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
Tags: exclusivelock, vacuum, vacuumfull
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
Tags: logical, replication