Spring Boot Supabase CRUD Example
Connect Spring Boot 4.1.0 to a free Supabase PostgreSQL database with Spring Data JPA. Full CRUD REST API, unit tests, and real output.
How to run an init script for Docker Postgres
The official Docker postgres image will run all *.sh and *.sql scripts in its /docker-entrypoint-initdb.d directory automatically when it starts. Table of contents: 1. Create an init.sql 2. Copy init.sql to container 3. Start the Postgres container 4. Access the Postgres container 5. References Technologies used: Docker 24.0.5 Official Docker postgres image (latest) 1. Create […]
Run PostgreSQL Using Docker with Docker Compose
Learn how to run PostgreSQL as a Docker container using Docker Compose. This guide is perfect for developers who want a clean, isolated database environment for local development and testing.
Spring Boot Testcontainers Example
This article shows how to test the Spring Boot REST endpoints using TestRestTemplate and Testcontainers (PostgreSQL container). Technologies used : Spring Boot 3.1.2 (Spring Web MVC, Spring Data JPA and Spring Test) Testcontainers 1.19.0 PostgreSQL 15, Alpine Linux base image postgres:15-alpine Java 17 JUnt 5 Tables of contents: 1. Project Structure 2. Project Dependencies 3. […]
How to Uninstall Oracle Database 19c on Windows
To uninstall Oracle database on Windows, we have to delete the all the related Ora* registry keys, files and folders manually. P.S Tested with Oracle database 19c on Windows 10 Note I can’t find any uninstaller to uninstall the Oracle database automatically? 1. Stop Oracle* services Press Windows key, type services, stop all Oracle related […]
JDBC CallableStatement – PostgreSQL Stored Function
A JDBC CallableStatement example to show you how to call a stored function from PostgreSQL database. P.S Tested with PostgreSQL 11 and Java 8 pom.xml <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.5</version> </dependency> 1. Call Function 1.1 Create a stored function and calling it via JDBC. FunctionReturnString.java package com.mkyong.jdbc.callablestatement; import java.sql.*; public class FunctionReturnString { public static void […]
JDBC – How to print all table names from a database?
A JDBC example to connect to a PostgreSQL, and print out all the tables from the default database postgres pom.xml <dependencies> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.5</version> </dependency> </dependencies> P.S Tested with Java 8 and postgresql jdbc driver 42.2.5 PrintAllTables.java package com.mkyong.jdbc; import java.sql.*; public class PrintAllTables { public static void main(String[] argv) { System.out.println("PostgreSQL JDBC Connection […]
Spring Boot + Spring Data JPA + PostgreSQL example
This article shows how to use Spring Web MVC to create REST endpoints for CRUD database operations using the Spring Data JPA and PostgreSQL. At the end of the tutorial, we will use Docker to start a PostgreSQL container to test the Spring Boot REST endpoints using curl commands. We will use Spring Test and […]
JDBC Transaction example
JDBC transaction make sure a set of SQL statements is executed as a unit, either all of the statements are executed successfully, or NONE of the statements are executed (rolled back all changes). 1. Without JDBC Transaction 1.1 A JDBC example to insert two rows and update one row. TransactionExample.java package com.mkyong.jdbc; import java.math.BigDecimal; import […]
Regular Expression in PostgreSQL
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 […]
Connect to PostgreSQL with JDBC driver
A JDBC example to show you how to connect to a PostgreSQL database with a JDBC driver. Tested with: Java 8 PostgreSQL 11 PostgreSQL JDBC driver 42.2.5 1. Download PostgreSQL JDBC Driver Visit http://jdbc.postgresql.org/download.html to download the latest PostgreSQL JDBC Driver. 2. JDBC Connection 2.1 Make a connection to the PostgreSQL database. JDBCExample.java import java.sql.Connection; […]
PostgreSQL Point-in-time Recovery (Incremental Backup)
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 […]
How to export table data to file / csv – PostgreSQL
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 […]
How to kill / terminate PostgreSQL hang query
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 […]
TO_DATE function between PostgreSQL 8.2 and 8.3
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 […]
PostgreSQL – How to change default schema
“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 to get random row from sql query – PostgreSQL
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.
Convert Subquery Result to Array
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 […]
How to building PostgreSQL libpq Programs
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 […]
Backup & Restore Database in PostgreSQL (pg_dump,pg_restore)
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 […]
PostgreSQL – ERROR: operator does not exist: smallint = character varying (Solution)
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 […]
Performance Testing on Partition Table In PostgreSQL – Part 3
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 […]
Install Perl in PostgreSQL, The specified module could not be found.
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” […]
Partition Table In PostgreSQL (Simulate Millions Data) – Part 2
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 (Create Partition) – Part 1
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) […]
How to install pgAgent on windows (PostgreSQL Job Scheduler)
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 […]
PostgreSQL Network Access – (Solution)
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 […]
How to compile PostgreSQL database source code in linux
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 […]
How to install postgresql in fedora core
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 […]
How to uninstall postgresql from fedora core
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 – Create table from existing table
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’ […]