0

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.

  • 2
    Use an enum, sounds like an ideal candidate. – Craig Ringer Jul 6 '14 at 3:13
4

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.