Installing and Creating Database in PostgreSQL

2026-05-26 · Source: Alex The Analyst · Field: Technology & Digital — Software Development & Engineering, Data Science & Analytics · Depth: Novice, long

Summary

This guide details the installation and initial setup of PostgreSQL and its graphical interface, pgAdmin 4, on a local machine, specifically demonstrating the process on Windows. It covers downloading the PostgreSQL installer from EDB, selecting essential components like the PostgreSQL Server and pgAdmin 4, configuring the data directory, setting a server password, and confirming the default port (5432). Following installation, the process outlines launching pgAdmin 4, connecting to the PostgreSQL server (e.g., version 18), and utilizing the query tool. The guide then demonstrates creating database tables (e.g., "ships", "planets", "character_info") within a public schema using a provided SQL script. This script illustrates `DROP TABLE IF EXISTS`, `CREATE TABLE` statements with various data types (e.g., `SERIAL PRIMARY KEY`, `TEXT`, `INTEGER`, `DATE`, `NUMERIC`, `CHARACTER`), and `FOREIGN KEY` references, concluding with verification of data insertion via `SELECT *` queries.

Key takeaway

For data professionals or analytics engineers establishing a local PostgreSQL development environment, you should prioritize installing both the PostgreSQL Server and pgAdmin 4 to efficiently manage your databases. Carefully follow the installation wizard, ensuring you set a memorable password and note the default port (e.g., 5432). Utilize pgAdmin 4's query tool to execute SQL scripts for schema creation, leveraging `DROP TABLE IF EXISTS` for idempotency and defining appropriate data types and keys for robust table structures.

Key insights

Installing PostgreSQL and pgAdmin 4 involves a guided setup process followed by server connection and SQL script execution for database and table creation.

Principles

• Install PostgreSQL Server and pgAdmin 4 together for local database management.
• Use `DROP TABLE IF EXISTS` for idempotent table creation in SQL scripts.
• Define appropriate data types and primary/foreign keys for robust schema integrity.

Method

Install PostgreSQL via EDB installer, selecting server and pgAdmin 4. Set password and default port 5432. Launch pgAdmin 4, connect to the server, then execute SQL scripts to define and populate tables.

In practice

• Download PostgreSQL installer from EDB for your specific operating system.
• Utilize pgAdmin 4's query tool to execute SQL DDL and DML statements.
• Verify table creation and data insertion using `SELECT * FROM table_name` queries.

Topics

• PostgreSQL
• pgAdmin 4
• Database Installation
• SQL Schema Creation
• Data Types
• Foreign Keys
• Local Development Environment

Best for: Data Scientist, Data Engineer, Analytics Engineer

Related on AIssential

• Spring Boot + PostgreSQL — Default Spring Boot and PostgreSQL configurations often fail under high-scale, complex workloads.
• Introduction to Databases (SQL vs NoSQL) — SQL and NoSQL databases are complementary, each suited for different data structures and use cases.
• Netflix Automates RDS PostgreSQL to Aurora PostgreSQL Migration Across 400 Production Clusters — Automating database migrations via an infrastructure-level platform reduces risk and downtime for large-scale operations.
• Creating Synthetic Data In Snowflake Part II — Snowflake functions like `Seq`, `Uniform`, `Randstr`, `Normal`, and `Get` enable robust synthetic data generation.
• Why Your Data Pipeline Is Slow: A Practical Guide to Indexes — Indexes align logical query intent with physical data access, preventing PostgreSQL from doing unnecessary work at scale.
• Postgres vs MySQL vs SQLite: Comparing SQL Performance Across Engines — Database choice significantly impacts application performance, with each SQL engine excelling in different workloads.

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Alex The Analyst.