What are the ~>=~ and ~<~ operators in PostgreSQL?

Question

My query's filter was school ~ '^nebraska' and this was the resulting query plan. What is ~>=~ and ~<~? These appear to be ungoogleable and I can't find them in the PostgreSQL docs.

-> Nested Loop
 -> Index Scan using schools_school_712ef684_like on schools u2
 Index Cond: (((school)::text ~>=~ 'nebraska'::text) AND ((school)::text ~<~ 'nebraskb'::text))
 Filter: ((school)::text ~ '^nebraska'::text)

The schools_school_712ef684_like index is a btree (school varchar_pattern_ops) created by Django automatically.

• mustaccio: Here is your answer. Next time you search, put special characters in quotes.

Answer 1

select opcname, amopstrategy,amopopr::regoperator
from pg_am am
join pg_opfamily opf on am.oid =opf.opfmethod
join pg_opclass opc on opf.oid = opc.opcfamily
join pg_amop amop on opc.opcfamily = amop.amopfamily
where amname = 'btree'
and opcname in ('text_ops', 'text_pattern_ops')
and amoplefttype = 'text'::regtype
and amoprighttype = 'text'::regtype
order by 1,2;

return

/*
 opcname | amopstrategy | amopopr
------------------+--------------+-----------------
 text_ops | 1 | <(text,text)
 text_ops | 2 | <=(text,text)
 text_ops | 3 | =(text,text)
 text_ops | 4 | >=(text,text)
 text_ops | 5 | >(text,text)
 text_pattern_ops | 1 | ~<~(text,text)
 text_pattern_ops | 2 | ~<=~(text,text)
 text_pattern_ops | 3 | =(text,text)
 text_pattern_ops | 4 | ~>=~(text,text)
 text_pattern_ops | 5 | ~>~(text,text)
 */

Then link:

• https://www.postgresql.org/docs/current/indexes-opclass.html
• https://www.postgresql.org/docs/current/catalog-pg-opclass.html
• https://www.postgresql.org/docs/current/catalog-pg-amop.html

I hope my understand is correct.

---

Faster text pattern match need index. Index is access methods. Then we need index access operator classes.-> pg_opclass For each opclass_name there is detail info -> pg_amop

---

exmaple:
explain (costs off) select * from tickets where passenger_name like 'ELENA%';
query plan:

 QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tickets
 Filter: (passenger_name ~~ 'ELENA%'::text)
 -> Bitmap Index Scan on tickets_passenger_name_pattern_idx
 Index Cond: ((passenger_name ~>=~ 'ELENA'::text) AND (passenger_name ~<~ 'ELENB'::text))
(4 rows)

predicate1 (passenger_name ~>=~ 'ELENA'::text
predicate2 (passenger_name ~<~ 'ELENB'::text)

predicate1 mean that pattern_string larger equal 'ELENA' means either ELENA or ELENA.... or ELENB... [B can any character larger than A]. three dot mean characters.
predicate2 mean pattern_string smaller than ELENB.
match predicate1 and predicate2 is either ELENA or ELENA...

---

update

 select amopopr::regoperator, amopstrategy as st, opr.oprcode, obj_description(opr.oid,'pg_operator') as description
 from pg_am am join pg_opclass opc on am.oid = opc.opcmethod
 join pg_amop amop on amopfamily = opc.opcfamily
 join pg_operator opr on amop.amopopr = opr.oid
where amname = 'btree' and opcname = 'text_pattern_ops'order by 2;

return:

 amopopr | st | oprcode | description
-----------------+----+-----------------+-----------------------
 ~<~(text,text) | 1 | text_pattern_lt | less than
 ~<=~(text,text) | 2 | text_pattern_le | less than or equal
 =(text,text) | 3 | texteq | equal
 ~>=~(text,text) | 4 | text_pattern_ge | greater than or equal
 ~>~(text,text) | 5 | text_pattern_gt | greater than

I think the key part is understand tilde operator.

• Laurenz Albe: That doesn't really explain what those operators are...
• jian: @LaurenzAlbe updated my answer. correct me if i am wrong...
• jian: @LaurenzAlbe I get the ops description from postgresql!.