Postgresql 9.4 JSONB类型，按日期范围选择

PostgreSQL 9.4 JSONB类型：使用自然语言按日期范围选择

sql
-- 创建表
CREATE TABLE data (
  id SERIAL PRIMARY KEY,
  info JSONB
);
-- 创建索引
CREATE INDEX idx_data_info ON data USING GIN (info);
-- 插入数据
INSERT INTO data (info) VALUES
  ('{"name": "John", "birthday": "1990-01-01"}'),
  ('{"name": "Jane", "birthday": "1995-05-10"}'),
  ('{"name": "Tom", "birthday": "1985-12-25"}');
-- 按日期范围选择数据
SELECT * FROM data
WHERE info->>'birthday'::DATE >= '1990-01-01'::DATE
  AND info->>'birthday'::DATE <= '1995-12-31'::DATE;

sql
-- 创建表
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  info JSONB
);
-- 创建索引
CREATE INDEX idx_users_info ON users USING GIN (info);
-- 插入数据
INSERT INTO users (info) VALUES
  ('{"name": "John", "birthday": "1990-01-01"}'),
  ('{"name": "Jane", "birthday": "1995-05-10"}'),
  ('{"name": "Tom", "birthday": "1985-12-25"}');
-- 按生日范围选择用户
SELECT info->>'name' AS name, info->>'birthday'::DATE AS birthday
FROM users
WHERE info->>'birthday'::DATE >= '1990-01-01'::DATE
  AND info->>'birthday'::DATE <= '1995-12-31'::DATE;