daily pastebin goal
62%
SHARE
TWEET

Untitled

a guest Sep 24th, 2018 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. jsonb_each_text(jsonb_column) ILIKE '%val%'
  2.    
  3. SELECT
  4.   '{
  5.    "col1": "somevalue",
  6.    "col2": 5.5,
  7.    "col3": 2016-01-01,
  8.    "col4": "othervalue",
  9.    "col5": "yet_another_value"
  10.   }'::JSONB
  11.    
  12. create table foo as SELECT '{"col1": "somevalue", "col2": 5.5, "col3": "2016-01-01", "col4": "othervalue", "col5": "yet_another_value"}'::JSONB as bar;
  13.  
  14. create index pickfoo1 on foo ((bar #>> '{col1}'));
  15. create index pickfoo2 on foo ((bar #>> '{col2}'));
  16.    
  17. -- Create index over lowered value:
  18. create index pickfoo1 on foo (lower(bar #>> '{col1}'));
  19. create index pickfoo2 on foo (lower(bar #>> '{col2}'));
  20.  
  21. -- Check that it matches:
  22. select * from foo where lower(bar #>> '{col1}') = lower('soMEvaLUe');
  23.    
  24. select * from foo where bar #>> '{col1}' ilike '%MEvaL%';
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top