Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- This will result in error:
- SQL Error [XX000]: ERROR: unable to encode table key: *tree.DJSON
- on insert
- */
- CREATE TABLE json_events2 (
- uuid STRING PRIMARY KEY,
- value JSONB,
- event_type STRING,
- scenario_id STRING,
- conversation_id STRING,
- account_id STRING,
- sequence_id STRING,
- unknown JSONB,
- timestamp TIMESTAMP WITH TIME ZONE
- );
- CREATE INDEX IF NOT EXISTS json_debug_index ON json_events2 (scenario_id, timestamp desc, event_type) STORING (uuid, conversation_id, sequence_id, value, unknown);
- ALTER TABLE json_events2 ADD COLUMN IF NOT EXISTS version INT;
- update json_events2
- set version = (unknown->>'scenarioIdVersion')::INT
- where version is null;
- insert into json_events2 (uuid, value, event_type, scenario_id, conversation_id, account_id, sequence_id, version, unknown, timestamp) values
- ('Cg7AK5YxVEJiTv6bYnHp23', '{"a":"value"}', 'eventType', 'scenarioId', 'conversationId', 'accountId', 'sequenceId', '1', '{"random":"shit","scenarioIdVersion":"1","random2":"shit2"}', '2018-09-22 21:38:41.058')
- /*
- This will run ok
- */
- CREATE TABLE json_events3 (
- uuid STRING PRIMARY KEY,
- value JSONB,
- event_type STRING,
- scenario_id STRING,
- conversation_id STRING,
- account_id STRING,
- sequence_id STRING,
- unknown JSONB,
- timestamp TIMESTAMP WITH TIME ZONE
- );
- ALTER TABLE json_events3 ADD COLUMN IF NOT EXISTS version INT;
- update json_events3
- set version = (unknown->>'scenarioIdVersion')::INT
- where version is null;
- CREATE INDEX IF NOT EXISTS json_debug_index ON json_events3 (scenario_id, timestamp desc, event_type) STORING (uuid, conversation_id, sequence_id, value, unknown);
- insert into json_events3 (uuid, value, event_type, scenario_id, conversation_id, account_id, sequence_id, version, unknown, timestamp) values
- ('Cg7AK5YxVEJiTv6bYnHp23', '{"a":"value"}', 'eventType', 'scenarioId', 'conversationId', 'accountId', 'sequenceId', '1', '{"random":"shit","scenarioIdVersion":"1","random2":"shit2"}', '2018-09-22 21:38:41.058')
Add Comment
Please, Sign In to add comment