Recent Posts (postgresql)
Installing PostgreSQL on MacOS
- By Ercan
- 26/06/2025
If you're working on personal projects and prefer running PostgreSQL directly on your Mac instead of using Docker, you're not alone. While containerization is great for many use cases, sometimes you just want your database to live natively on your machine — especially when you care about persistent data and minimal overhead. In this guide, I’ll walk you through installing PostgreSQL 18 using Homebrew on macOS, with separate instructions for Apple Silicon and Intel-based Macs. Step 1: Install PostgreSQL brew install postgresql@18 By default, Homebrew initializes a database clust..
PostgreSQL Advanced Query Examples
- By Ercan
- 15/05/2025
PostgreSQL is known for its standards compliance and performance — but what truly sets it apart is its depth. Beyond the typical SELECT, JOIN, and WHERE statements lies a powerful set of query features that can make your SQL far more expressive and efficient. In this article, we’ll explore advanced PostgreSQL queries that go beyond the basics — including recursive CTEs, partitioned tables, JSONB filtering, window functions, and more. Each example includes simple schema setup statements, so you can try them out directly in your environment. 1. Recursive CTE: Handling Parent–Child Relati..
Backing Up and Restoring PostgreSQL Databases
- By Ercan
- 17/04/2025
This guide outlines the steps to back up and restore PostgreSQL databases using command-line tools on Windows. Backing Up a Database To back up a PostgreSQL database, use the `pg_dump` utility: .\pg_dump.exe -U <username> -d <database_name> > <dump_path> Example: .\pg_dump.exe -U postgres -d testdb > C:\Users\Ercan\Desktop\db_dump.sql Backing Up a Table To back up a PostgreSQL table, use the `pg_dump` utility: .\pg_dump.exe -U <username> -d <database_name> -t <table_name> > <dump_path> Example: .\pg_dump.e..
Using OpenStreetMap Data with PostgreSQL
- By Ercan
- 13/04/2025
This guide outlines the steps to import OpenStreetMap (OSM) data into a PostgreSQL database with PostGIS support on a Windows environment. 1. Install PostGIS Extension Add spatial support to PostgreSQL by installing the PostGIS extension: https://postgis.net/install/ 2. Create a PostgreSQL Database Create a new database in PostgreSQL to store the OSM data. Then, enable the necessary extensions by executing: CREATE EXTENSION postgis; CREATE EXTENSION hstore; 3. Download OSM Data Obtain the desired .osm.pbf file from Geofabrik: https://download.geofabrik.de/ 4. Downloa..
Installing PostgreSQL on Windows
- By Ercan
- 13/04/2025
This guide outlines the steps to install and initialize PostgreSQL on a Windows environment using the ZIP archive. 1. Download PostgreSQL Visit the official PostgreSQL website and download the ZIP archive: https://www.enterprisedb.com/download-postgresql-binaries 2. Extract the ZIP File Extract the downloaded ZIP file to your desired location, for example: D:\Dev\pgsql-14.1 3. Create Necessary Directories Within the extracted folder, create the following directories: mkdir <<postgresql_path>>\data mkdir <<postgresql_path>>\log 4. Initialize PostgreSQL Run the fo..
PostgreSQL Date-Based Query Examples: Today, Month, and Year
- By Ercan
- 13/04/2025
Working with dates is essential in PostgreSQL for reporting, analytics, and application logic. This guide provides practical examples of common date-based queries in PostgreSQL, from today’s date to month and year calculations. 1. Day-Based Queries PostgreSQL makes it easy to retrieve today’s, yesterday’s, or tomorrow’s date: -- Today SELECT current_date; -- Output: 2024-01-17 -- Yesterday SELECT current_date - 1; -- Output: 2024-01-16 -- Tomorrow SELECT current_date + 1; -- Output: 2024-01-18 2. Formatting Dates You can format dates using the to_char function: ..