PostgreSQL: how to query over a column with a custom range

postgresql

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.

Best Answer

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

Related Solutions

PostgreSQL custom domain with trigger

You cannot do that.

As the documentation says:

CREATE TRIGGER creates a new trigger. The trigger will be associated with the specified table or view

This clearly excludes types and domains. (Very logically: triggers are normally fired upon changing something - here you want to change a column, not the type.)

You should instead use a stored procedure to take care of your password column(s). And personally I think the less places password are stored at, the better.

Postgresql – find all JSON scalars in PostgreSQL 9.3

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

Best Answer

Index

Related Solutions

PostgreSQL custom domain with trigger

Postgresql – find all JSON scalars in PostgreSQL 9.3

Related Question