YSQL Data Types

CREATE TABLE integers_example (
  id SERIAL PRIMARY KEY,
  small_num SMALLINT,
  regular_num INTEGER,
  big_num BIGINT
);

INSERT INTO integers_example (small_num, regular_num, big_num)
VALUES (100, 1000000, 9223372036854775807);

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL
);

INSERT INTO users (username) VALUES ('alice'), ('bob');
-- user_id is automatically generated

CREATE TABLE measurements (
  sensor_id INT PRIMARY KEY,
  temperature REAL,
  precise_value DOUBLE PRECISION
);

CREATE TABLE financial (
  transaction_id INT PRIMARY KEY,
  amount NUMERIC(10, 2),  -- 10 digits total, 2 after decimal
  price DECIMAL(8, 3)
);

INSERT INTO financial VALUES (1, 12345.67, 999.999);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  price MONEY
);

INSERT INTO products VALUES (1, '$19.99');

CREATE TABLE strings_example (
  id INT PRIMARY KEY,
  code CHAR(5),           -- Fixed 5 characters
  name VARCHAR(100),      -- Up to 100 characters
  description TEXT        -- Unlimited length
);

INSERT INTO strings_example VALUES 
  (1, 'ABC01', 'Sample Product', 'This is a long description...');

CREATE TABLE files (
  file_id INT PRIMARY KEY,
  file_name TEXT,
  file_data BYTEA
);

-- Insert binary data
INSERT INTO files VALUES (1, 'image.png', '\\xDEADBEEF'::bytea);

CREATE TABLE events (
  event_id INT PRIMARY KEY,
  event_time TIMESTAMP,
  event_time_tz TIMESTAMPTZ,
  event_date DATE,
  duration INTERVAL
);

INSERT INTO events VALUES (
  1,
  '2024-03-15 14:30:00',
  '2024-03-15 14:30:00-08',
  '2024-03-15',
  INTERVAL '2 hours 30 minutes'
);

SELECT 
  event_time,
  event_time + duration AS end_time,
  EXTRACT(YEAR FROM event_date) AS year
FROM events;

CREATE TABLE flags (
  id INT PRIMARY KEY,
  is_active BOOLEAN DEFAULT TRUE,
  is_deleted BOOL DEFAULT FALSE
);

INSERT INTO flags VALUES 
  (1, TRUE, FALSE),
  (2, 't', 'f'),      -- Alternative syntax
  (3, 'yes', 'no'),   -- Alternative syntax
  (4, '1', '0');      -- Alternative syntax

SELECT * FROM flags WHERE is_active AND NOT is_deleted;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
  session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id INT,
  created_at TIMESTAMP DEFAULT now()
);

INSERT INTO sessions (user_id) VALUES (1001);
SELECT * FROM sessions;

CREATE TABLE api_logs (
  log_id SERIAL PRIMARY KEY,
  request_data JSONB,
  response_data JSON,
  created_at TIMESTAMP DEFAULT now()
);

INSERT INTO api_logs (request_data, response_data) VALUES (
  '{"method": "GET", "path": "/api/users", "params": {"limit": 10}}',
  '{"status": 200, "data": [{"id": 1, "name": "Alice"}]}'
);

-- Query JSON data
SELECT 
  request_data->>'method' AS http_method,
  request_data->'params'->>'limit' AS limit,
  response_data->>'status' AS status
FROM api_logs;

-- Create index on JSONB
CREATE INDEX idx_request_method ON api_logs ((request_data->>'method'));

CREATE TABLE array_example (
  id INT PRIMARY KEY,
  tags TEXT[],
  scores INT[],
  matrix INT[][]
);

INSERT INTO array_example VALUES (
  1,
  ARRAY['postgresql', 'yugabytedb', 'sql'],
  ARRAY[95, 87, 92],
  ARRAY[[1,2],[3,4]]
);

-- Access array elements (1-indexed)
SELECT 
  tags[1] AS first_tag,
  scores[2] AS second_score,
  array_length(tags, 1) AS tag_count
FROM array_example;

-- Query with ANY
SELECT * FROM array_example 
WHERE 'sql' = ANY(tags);

CREATE TABLE reservations (
  reservation_id INT PRIMARY KEY,
  room_number INT,
  stay_period DATERANGE,
  price_range NUMRANGE
);

INSERT INTO reservations VALUES (
  1,
  101,
  '[2024-03-15, 2024-03-20)',
  '[100.00, 200.00]'
);

-- Check if date is in range
SELECT * FROM reservations 
WHERE stay_period @> '2024-03-17'::date;

-- Check for overlapping ranges
SELECT * FROM reservations 
WHERE stay_period && '[2024-03-18, 2024-03-22)'::daterange;

CREATE TABLE locations (
  location_id INT PRIMARY KEY,
  position POINT,
  area CIRCLE,
  boundary BOX
);

INSERT INTO locations VALUES (
  1,
  '(10.5, 20.3)',
  '<(0,0),5>',
  '(0,0),(10,10)'
);

CREATE TABLE network_devices (
  device_id INT PRIMARY KEY,
  ip_address INET,
  network CIDR,
  mac_address MACADDR
);

INSERT INTO network_devices VALUES (
  1,
  '192.168.1.100',
  '192.168.1.0/24',
  '08:00:2b:01:02:03'
);

SELECT * FROM network_devices 
WHERE ip_address << '192.168.1.0/24'::inet;

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE persons (
  person_id INT PRIMARY KEY,
  name TEXT,
  current_mood mood
);

INSERT INTO persons VALUES (1, 'Alice', 'happy');

SELECT * FROM persons WHERE current_mood = 'happy';

CREATE TYPE address AS (
  street TEXT,
  city TEXT,
  state CHAR(2),
  zip_code VARCHAR(10)
);

CREATE TABLE companies (
  company_id INT PRIMARY KEY,
  name TEXT,
  headquarters address
);

INSERT INTO companies VALUES (
  1,
  'YugabyteDB Inc',
  ROW('123 Main St', 'San Francisco', 'CA', '94105')
);

SELECT 
  name,
  (headquarters).city,
  (headquarters).state
FROM companies;

CREATE TABLE documents (
  doc_id INT PRIMARY KEY,
  title TEXT,
  body TEXT,
  tsv TSVECTOR
);

INSERT INTO documents VALUES (
  1,
  'PostgreSQL Guide',
  'PostgreSQL is a powerful database',
  to_tsvector('english', 'PostgreSQL is a powerful database')
);

CREATE INDEX idx_tsv ON documents USING gin(tsv);

SELECT * FROM documents 
WHERE tsv @@ to_tsquery('english', 'database');

CREATE TABLE xml_data (
  id INT PRIMARY KEY,
  data XML
);

INSERT INTO xml_data VALUES (
  1,
  '<book><title>Database Systems</title></book>'
);

-- Explicit casting
SELECT 
  '123'::INTEGER AS int_value,
  123::TEXT AS text_value,
  CAST('2024-03-15' AS DATE) AS date_value;

-- Type coercion
SELECT 
  1 + 2.5 AS mixed_numeric,  -- Returns 3.5 (numeric)
  'Value: ' || 42 AS concat;  -- Returns 'Value: 42'

-- Supported
CREATE INDEX idx_timestamp ON events(event_time);
CREATE INDEX idx_jsonb ON api_logs USING gin(request_data);

-- Creates GIN index for arrays
CREATE INDEX idx_tags ON array_example USING gin(tags);