Data Types

-- Define array column
CREATE TABLE products (
    id INT,
    tags VARCHAR(20)[] -- String array
);
 
-- Insert data
INSERT INTO products VALUES (1, ARRAY['electronics', 'gadget']);
 
-- Query
SELECT tags[1] FROM products WHERE id = 1; -- Returns 'electronics'

CREATE TABLE user_profiles (
    id INT,
    profile JSONB
);
 
INSERT INTO user_profiles VALUES (1, '{"name": "Alice", "age": 30}');
 
-- Query JSON field
SELECT profile->>'name' FROM user_profiles WHERE id = 1; -- Returns "Alice"

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    amount DECIMAL(10,2)
);

CREATE TYPE status AS ENUM ('pending', 'completed', 'failed');
 
CREATE TABLE tasks (
    id INT,
    task_status status
);
 
INSERT INTO tasks VALUES (1, 'pending');

-- Implicit conversion between INT and BIGINT
SELECT * FROM users WHERE id = 123456789012;
-- id is INT type, but value exceeding range is automatically handled
 
-- Implicit conversion between string and numeric
SELECT * FROM products WHERE price = '99.99';
-- price is DECIMAL type, string is automatically converted

-- Convert string to integer
SELECT CAST('123' AS INT);
 
-- Convert numeric to string
SELECT CAST(123.45 AS VARCHAR(10));
 
-- Convert string to date
SELECT CAST('2023-10-01' AS DATE);

-- Convert string to integer
SELECT '123'::INT;
 
-- Convert numeric to string
SELECT 123.45::VARCHAR(10);
 
-- Convert string to date
SELECT '2023-10-01'::DATE;
 
-- Convert timestamp to date
SELECT '2023-10-01 14:30:00'::TIMESTAMP::DATE;
 
-- Timezone-related conversions
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ; -- Convert to timestamptz
SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai'; -- Add timezone info
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ AT TIME ZONE 'UTC'; -- Convert timezone
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ::TIMESTAMP; -- Remove timezone info

-- Error example: string containing non-numeric characters converted to numeric
SELECT CAST('123abc' AS INT);
-- Error: invalid input syntax for type integer
 
-- Correct handling: use regex pre-check or exception handling
SELECT CASE WHEN '123abc' ~ '^\d+$' THEN CAST('123abc' AS INT) ELSE NULL END;

-- Error example: numeric value exceeding target type range
SELECT CAST(999999999999 AS SMALLINT);
-- Error: smallint out of range
 
-- Correct handling: check numeric range or use larger range type
SELECT CASE WHEN 999999999999 BETWEEN -32768 AND 32767 THEN CAST(999999999999 AS SMALLINT) ELSE NULL END;

-- Error example: date string not conforming to standard format
SELECT CAST('2023/10/01' AS DATE);
-- Error: invalid input syntax for type date
 
-- Correct handling: use appropriate date format or preprocess string
SELECT CAST(to_date('2023/10/01', 'YYYY/MM/DD') AS DATE);

-- Error example: invalid JSON string converted to JSONB
SELECT CAST('{name: "Alice"}' AS JSONB);
-- Error: invalid input syntax for type json
 
-- Correct handling: ensure JSON string format is correct
SELECT CAST('{"name": "Alice"}' AS JSONB);

-- Error example: using non-existent timezone
SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Invalid/Timezone';
-- Error: invalid time zone
 
-- Error example: incorrect timezone format
SELECT CAST('2023-10-01 14:30:00+25' AS TIMESTAMPTZ);
-- Error: time zone displacement out of range
 
-- Correct handling: use valid timezone identifiers
SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai';
SELECT CAST('2023-10-01 14:30:00+08' AS TIMESTAMPTZ);

SELECT TRY_CAST('invalid' AS INT); -- Returns NULL instead of error

-- Validate if it's a valid integer format
SELECT CASE WHEN '123abc' ~ '^-?\d+$' THEN CAST('123abc' AS INT) ELSE NULL END;