Recent Posts (postgresql)
Backing Up and Restoring PostgreSQL Databases
- By Ercan S.
- 17/04/2025
- 0 comments
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\dump.sql Restoring a Database To restore a PostgreSQL database from a backup, use the `psql` utility: .\psql.exe -U <username> -d <database_name> < <dump_path> Example: .\psql.exe -U postgres ..
Using OpenStreetMap Data with PostgreSQL
- By Ercan S.
- 13/04/2025
- 0 comments
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 S.
- 13/04/2025
- 0 comments
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 Po..
PostgreSQL Date based Query Examples
- By Ercan S.
- 13/04/2025
- 0 comments
Today select current_date; --2024-01-17 Yesterday select current_date - 1; --2024-01-16 Tomorrow select current_date + 1; --2024-01-18 Format Date select to_char(current_date,'DD/MM/YYYY'); --17/01/2024 This year select extract(year from current_date); --2024 Next year select extract(year from current_date + interval '1 year'); --2025 Last year select extract(year from current_date - interval '1 year'); --2023 First day of current month select cast(date_trunc('month', current_date) as date); --2024-01-01 Last day of current month select cast(date_trunc('month',current_da..