PostgreSQL: how do I query over a column with a custom range?

Question

I'm trying to write a query over a string column called grades_served that can have values such as

k-5
1-5
5-9

The query could be for a specific grade or it could be a range with a start and end value.

I wanted to check what the best approach would be on this. My gut instinct tells me to reformat the column into a range datatype, but the issue is this is a complex range, with string values, such as k and Pre-K.

I thought about creating a custom type but that seems like an overkill and I've never done that before.

http://www.postgresql.org/docs/9.3/static/sql-createtype.html

Would really appreciate any thoughts or recommendations.

• Craig Ringer: Use an enum, sounds like an ideal candidate.

Answer 1

The range type would allow best performance and simple queries.

If you have some string values like k and Pre-K mixed with numeric values, just create a grade table with a rank value (an integer value representing actual sort order):

CREATE TABLE grade(
 grade text PRIMARY KEY
 ,grade_int UNIQUE int NOT NULL
);

Your numeric grades can conveniently be assigned the same grade_value, other grades get integer values according to their rank.

You might write a simple function to convert ranges with your original string values to their integer representations on the fly ...
And vice-versa to display your grades_served column where needed.

Convert grades_served to an int4range type.
Using the overlap operator &&, your query for a range of grades can then simply be:

SELECT * FROM tbl WHERE grades_served && '[1,4]'::int4range;

Or, for a single grade, the "contains element" operator @>

SELECT * FROM tbl WHERE grades_served @> -1;

Index

If your table is not very small I suggest you create a GiST index to support that (and so does the manual).

CREATE INDEX tbl_grades_served_idx ON tbl USING gist (grades_served);