Why PostgreSQL over other databases - See It in Action

Why PostgreSQL Over Other Databases

📖 Scenario: You are working as a junior database analyst for a company deciding which database system to use for their new application. Your task is to explore why PostgreSQL might be a better choice compared to other popular databases.

🎯 Goal: Build a simple PostgreSQL database schema and write queries that demonstrate PostgreSQL's key features that make it stand out from other databases.

📋 What You'll Learn

Create a table with various data types including JSON and arrays

Use a configuration setting to enable a PostgreSQL-specific feature

Write a query that uses PostgreSQL's JSON functions

Add a final constraint or index that shows PostgreSQL's advanced capabilities

💡 Why This Matters

🌍 Real World

PostgreSQL is widely used in real-world applications that require complex data types, JSON support, and strong data integrity features.

💼 Career

Knowing PostgreSQL's unique features helps database professionals design efficient, scalable, and maintainable databases favored by many companies.

1

DATA SETUP: Create a table with diverse data types

Create a table called products with columns: id as serial primary key, name as text, tags as text array, and details as JSON.

PostgreSQL

-- Your code here to create the products table with the specified columns

Need a hint?

Use SERIAL for auto-incrementing id. PostgreSQL supports arrays and JSON as column types.

2

CONFIGURATION: Enable a PostgreSQL-specific setting

Set a configuration variable search_path to public to specify the schema search path.

PostgreSQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[],
  details JSON
);

-- Your code here to set search_path to public

Need a hint?

Use the SET command to change configuration for the current session.

3

CORE LOGIC: Query JSON data using PostgreSQL functions

Write a query to select name and the color field from the details JSON column using the ->> operator from the products table.

PostgreSQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[],
  details JSON
);

SET search_path TO public;

-- Your code here to select name and color from details JSON

Need a hint?

Use the ->> operator to extract text from a JSON column.

4

COMPLETION: Add a unique index on the name column

Create a unique index called idx_unique_name on the name column of the products table.

PostgreSQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[],
  details JSON
);

SET search_path TO public;

SELECT name, details->>'color' AS color FROM products;

-- Your code here to create a unique index on name

Need a hint?

Use CREATE UNIQUE INDEX to enforce uniqueness on a column.