Thread: ERROR: could not identify an equality operator for type box
Hi there, create table qq (b box); CREATE TABLE select count(*), b from qq group by b; ERROR: could not identify an equality operator for type box LINE 1: select count(*), b from qq group by b; but following select works fine select ' (43.6038,48.8664536),(1.3620777,1.44327)'::box = ' (43.6038,48.8664536),(1.3620777,1.44327)'::box; ?column? ---------- t (1 row) ^ What does it means ? Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
Ok, casting to ::text solves the problem, but still I think we need to fix it in the right way Oleg On Sat, 4 Dec 2010, Oleg Bartunov wrote: > Hi there, > > create table qq (b box); > CREATE TABLE > select count(*), b from qq group by b; > ERROR: could not identify an equality operator for type box > LINE 1: select count(*), b from qq group by b; > > but following select works fine > > select ' (43.6038,48.8664536),(1.3620777,1.44327)'::box = ' > (43.6038,48.8664536),(1.3620777,1.44327)'::box; > ?column? ---------- > t > (1 row) > ^ > What does it means ? > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
On 03.12.2010 23:53, Oleg Bartunov wrote: > create table qq (b box); > CREATE TABLE > select count(*), b from qq group by b; > ERROR: could not identify an equality operator for type box > LINE 1: select count(*), b from qq group by b; > > but following select works fine > > select ' (43.6038,48.8664536),(1.3620777,1.44327)'::box = ' > (43.6038,48.8664536),(1.3620777,1.44327)'::box; > ?column? ---------- > t > (1 row) > ^ > What does it means ? GROUP BY requires b-tree sort operators. Although there is a '=' operator for box, there is no b-tree opclass. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes: > On 03.12.2010 23:53, Oleg Bartunov wrote: >> create table qq (b box); >> CREATE TABLE >> select count(*), b from qq group by b; >> ERROR: could not identify an equality operator for type box >> What does it means ? > GROUP BY requires b-tree sort operators. Although there is a '=' > operator for box, there is no b-tree opclass. A hash opclass would do too, as of recent releases ... but box hasn't got one of those either. There are some semantic issues involved here, notably that box_eq is actually equality-of-areas, and that both it and box_same use fuzzy equality which does not work well with either hash or sort-based semantics. So you'd need to look at redefining the operator behavior before you could get far. regards, tom lane