PostgreSQL - Unable to create table - data type point has no default operator class for access method "btree"

Question

so I'm working on a project to learn SQL, Python, and some server-side stuff and I'm pretty stumped at this point, after much searching. I want to create two identical tables, and I have the following code:

import psycopg2 as db
import json

with open('config.json', 'r') as f:
config = json.load(f)

conn = db.connect(user=config['dbuser'], database=config['dbname'], host=config['dbhost'], password=config['dbpass']);
cur = conn.cursor()

cur.execute("""CREATE TABLE IF NOT EXISTS A 
    (
            city            TEXT,
            location        POINT NOT NULL,
            eta             INTEGER,
            time            TIMESTAMP WITH TIME ZONE NOT NULL,
            holiday         BOOLEAN,
            PRIMARY KEY (location, time)
    );""")

and also table B, with the same format, where A and B are different services. When running this, I get:

Traceback (most recent call last):
  File "dbsetup.py", line 18, in <module>
);""")
psycopg2.ProgrammingError: data type point has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I've been searching for awhile now but nothing I'm coming across is very similar to this case, and I'm confused since surely I'm missing or mistaking something very basic here. Any help/pointing in the right direction would be much appreciated! General advice is very welcome too.

Answer 1

PostgreSQL version doesn't support creating a normal or unique b-tree index on the point data type (true up to and including 9.5 at least). So you can't use point as part of a PRIMARY KEY.

test=> CREATE TABLE test_table( xy point primary key );
ERROR:  data type point has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

You'll need to change your data model so you don't try to use a point as part of the PK.

Or you could write an extension to add b-tree index support for the point type, but that requires a lot more work and understanding of the guts of how PostgreSQL's indexing works.

Anyway, if you're doing geographic stuff you might want to look into using PostGIS and the geometry data type. It has a much richer set of operations for searches to find the nearest point, find by distance, check if a point is within a region, etc, all efficiently indexed.