PostgreSQL Data Types

-- Integer types — for whole numbers with no decimal part
CREATE TABLE numeric_demo (
  small_num   SMALLINT,    -- -32,768 to 32,767
  normal_num  INTEGER,     -- -2.1 billion to 2.1 billion (most common)
  big_num     BIGINT,      -- very large whole numbers
  auto_id     SERIAL,      -- auto-incrementing integer (great for primary keys)
  big_auto_id BIGSERIAL    -- auto-incrementing bigint
);

-- Decimal types — for numbers with a fractional part
CREATE TABLE decimal_demo (
  price       NUMERIC(10, 2),  -- exact: up to 10 digits, 2 after decimal — use for money
  rating      DECIMAL(3, 1),   -- exact: e.g. 4.5 — DECIMAL and NUMERIC are identical
  temperature REAL,            -- approximate 6 decimal digits — use for scientific data
  measurement DOUBLE PRECISION -- approximate 15 decimal digits — more precise than REAL
);

CREATE TABLE text_demo (
  product_code  CHAR(8),         -- fixed length — always exactly 8 characters, padded with spaces
  first_name    VARCHAR(50),     -- variable length — up to 50 characters, no padding
  bio           TEXT,            -- unlimited length — no maximum, stores any size text
  status        VARCHAR(20)      -- common for short controlled values like 'active', 'pending'
);

-- Inserting text values
INSERT INTO text_demo (product_code, first_name, bio, status)
VALUES ('PROD0001', 'Alice', 'Alice is a senior engineer at Dataplexa.', 'active');

SELECT * FROM text_demo;

CREATE TABLE boolean_demo (
  product_name TEXT,
  is_active    BOOLEAN,
  is_featured  BOOLEAN
);

-- PostgreSQL accepts many formats for true and false
INSERT INTO boolean_demo VALUES ('Laptop',  TRUE,  TRUE);
INSERT INTO boolean_demo VALUES ('Monitor', FALSE, TRUE);
INSERT INTO boolean_demo VALUES ('Webcam',  't',   'f');   -- t and f are also valid
INSERT INTO boolean_demo VALUES ('Keyboard','yes',  'no');  -- yes and no work too

SELECT product_name, is_active, is_featured
FROM boolean_demo
WHERE is_active = TRUE;

CREATE TABLE datetime_demo (
  event_name        TEXT,
  event_date        DATE,                     -- date only: 2024-03-15
  event_time        TIME,                     -- time only: 14:30:00
  created_at        TIMESTAMP,                -- date + time, no timezone
  updated_at        TIMESTAMPTZ,              -- date + time WITH timezone (recommended)
  duration          INTERVAL                  -- a span of time: '2 hours', '30 days'
);

INSERT INTO datetime_demo VALUES (
  'Product Launch',
  '2024-06-01',
  '09:00:00',
  '2024-06-01 09:00:00',
  '2024-06-01 09:00:00-05',   -- -05 = US Central time
  '2 hours 30 minutes'
);

-- Get the current date and time
SELECT CURRENT_DATE, CURRENT_TIME, NOW();

-- Enable the uuid extension (needed to generate UUIDs)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE sessions (
  session_id  UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id     INTEGER,
  started_at  TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO sessions (user_id) VALUES (1), (2), (3);

SELECT * FROM sessions;

CREATE TABLE products_json (
  id          SERIAL PRIMARY KEY,
  name        TEXT,
  attributes  JSONB    -- stores flexible product attributes
);

INSERT INTO products_json (name, attributes) VALUES
('Laptop',  '{"brand": "Dell", "ram_gb": 16, "storage_gb": 512, "color": "silver"}'),
('Monitor', '{"brand": "LG",   "size_in": 27, "resolution": "4K", "color": "black"}');

-- Query a specific field inside the JSON using the -> operator
SELECT name, attributes -> 'brand' AS brand FROM products_json;

-- Get the value as plain text using ->> operator
SELECT name, attributes ->> 'color' AS color FROM products_json;

-- Filter by a JSON field value
SELECT name FROM products_json WHERE attributes ->> 'brand' = 'Dell';

CREATE TABLE employees_arr (
  id      SERIAL PRIMARY KEY,
  name    TEXT,
  skills  TEXT[],     -- array of text values
  scores  INTEGER[]   -- array of integers
);

INSERT INTO employees_arr (name, skills, scores) VALUES
('Alice', ARRAY['SQL', 'Python', 'PostgreSQL'], ARRAY[95, 88, 92]),
('Bob',   ARRAY['Excel', 'SQL'],                ARRAY[78, 85]);

-- Query: find employees who know SQL
SELECT name FROM employees_arr WHERE 'SQL' = ANY(skills);

-- Access the first element of the array (arrays are 1-indexed in PostgreSQL)
SELECT name, skills[1] AS primary_skill FROM employees_arr;