All of the scalar types generated by PostGraphile (BigFloat, BigInt, BitString, Boolean, CidrAddress, Date, Datetime, Float, Int, InternetAddress, Interval, JSON, KeyValueHash, MacAddress, MacAddress8, String, Time, UUID) have the following operators:
| SQL | GraphQL | Description |
|---|---|---|
| IS [NOT] NULL | isNull: Boolean |
Is null (if true is specified) or is not null (if false is specified). |
| = | equalTo: T |
Equal to the specified value. |
| <> | notEqualTo: T |
Not equal to the specified value. |
| IS DISTINCT FROM | distinctFrom: T |
Not equal to the specified value, treating null like an ordinary value. |
| IS NOT DISTINCT FROM | notDistinctFrom: T |
Equal to the specified value, treating null like an ordinary value. |
| IN (...) | in: [T] |
Included in the specified list. |
| NOT IN (...) | notIn: [T] |
Not included in the specified list. |
| < | lessThan: T |
Less than the specified value. |
| <= | lessThanOrEqualTo: T |
Less than or equal to the specified value. |
| > | greaterThan: T |
Greater than the specified value. |
| >= | greaterThanOrEqualTo: T |
Greater than or equal to the specified value. |
where T is the type of the field being filtered.
The only exception is KeyValueHash (hstore) fields, for which no sort operators (<, <=, >, >=) are available.
The following types have additional operators:
| SQL | GraphQL | Description |
|---|---|---|
| >> | contains: InternetAddress |
Contains the specified internet address. |
| >>= | containsOrEqualTo: InternetAddress |
Contains or equal to the specified internet address. |
| << | containedBy: InternetAddress |
Contained by the specified internet address. |
| <<= | containedByOrEqualTo: InternetAddress |
Contained by or equal to the specified internet address. |
| && | containsOrContainedBy: InternetAddress |
Contains or contained by the specified internet address. |
| SQL | GraphQL | Description |
|---|---|---|
| @> | contains: JSON |
Contains the specified JSON. |
| ? | containsKey: String |
Contains the specified key. |
| ?& | containsAllKeys [String] |
Contains all of the specified keys. |
| ?| | containsAnyKeys: [String] |
Contains any of the specified keys. |
| <@ | containedBy: JSON |
Contained by the specified JSON. |
| SQL | GraphQL | Description |
|---|---|---|
| @> | contains: KeyValueHash |
Contains the specified KeyValueHash. |
| ? | containsKey: String |
Contains the specified key. |
| ?& | containsAllKeys [String] |
Contains all of the specified keys. |
| ?| | containsAnyKeys: [String] |
Contains any of the specified keys. |
| <@ | containedBy: KeyValueHash |
Contained by the specified KeyValueHash. |
| SQL | GraphQL | Description |
|---|---|---|
| LIKE '%...%' | includes: String |
Contains the specified string (case-sensitive). |
| NOT LIKE '%...%' | notIncludes: String |
Does not contain the specified string (case-sensitive). |
| ILIKE '%...%' | includesInsensitive: String |
Contains the specified string (case-insensitive). |
| NOT ILIKE '%...%' | notIncludesInsensitive: String |
Does not contain the specified string (case-insensitive). |
| LIKE '...%' | startsWith: String |
Starts with the specified string (case-sensitive). |
| NOT LIKE '...%' | notStartsWith: String |
Does not start with the specified string (case-sensitive). |
| ILIKE '...%' | startsWithInsensitive: String |
Starts with the specified string (case-insensitive). |
| NOT ILIKE '...%' | notStartsWithInsensitive: String |
Does not start with the specified string (case-insensitive). |
| LIKE '%...' | endsWith: String |
Ends with the specified string (case-sensitive). |
| NOT LIKE '%...' | notEndsWith: String |
Does not end with the specified string (case-sensitive). |
| ILIKE '%...' | endsWithInsensitive: String |
Ends with the specified string (case-insensitive). |
| NOT ILIKE '%...' | notEndsWithInsensitive: String |
Does not end with the specified string (case-insensitive). |
| LIKE '...' | like: String |
Matches the specified pattern (case-sensitive). An underscore (_) matches any single character; a percent sign (%) matches any sequence of zero or more characters. |
| NOT LIKE '...' | notLike: String |
Does not match the specified pattern (case-sensitive). An underscore (_) matches any single character; a percent sign (%) matches any sequence of zero or more characters. |
| ILIKE '...' | likeInsensitive: String |
Matches the specified pattern (case-insensitive). An underscore (_) matches any single character; a percent sign (%) matches any sequence of zero or more characters. |
| NOT ILIKE '...' | notLikeInsensitive: String |
Does not match the specified pattern (case-insensitive). An underscore (_) matches any single character; a percent sign (%) matches any sequence of zero or more characters. |
The following operators are also available, but the mapping to SQL is more complex:
| GraphQL | Description |
|---|---|
| distinctFromInsensitive | Not equal to the specified value, treating null like an ordinary value (case-insensitive). |
| equalToInsensitive | Equal to the specified value (case-insensitive). |
| greaterThanInsensitive | Greater than the specified value (case-insensitive). |
| greaterThanOrEqualToInsensitive | Greater than or equal to the specified value (case-insensitive). |
| inInsensitive | Included in the specified list (case-insensitive). |
| lessThanInsensitive | Less than the specified value (case-insensitive). |
| lessThanOrEqualToInsensitive | Less than or equal to the specified value (case-insensitive). |
| notDistinctFromInsensitive | Equal to the specified value, treating null like an ordinary value (case-insensitive). |
| notEqualToInsensitive | Not equal to the specified value (case-insensitive). |
| notInInsensitive | Not included in the specified list (case-insensitive). |
The compiled SQL depends on the underlying PostgreSQL column type. Using case-insensitive operators with text/varchar/char columns will result in calling lower() on the operands. Using case-sensitive operators with citext columns will result in casting the operands to text.
For example, here is how the equalTo/equalToInsensitive operators compile to SQL:
| GraphQL operator | PostgreSQL column type | Compiled SQL |
|---|---|---|
| equalTo | text/varchar/char |
<col> = $1 |
| equalTo | citext |
<col>::text = $1::text |
| equalToInsensitive | text/varchar/char |
lower(<col>) = lower($1) |
| equalToInsensitive | citext |
<col> = $1 |
Domain fields have the same operators as the domain's base type. For example, a domain type declared with create domain ... as text check (...); would have all of the String operators.
Enum fields have the same operators as scalar fields.
Range fields have the same operators as scalar fields, plus the following range operators:
| SQL | GraphQL | Description |
|---|---|---|
| @> | contains: T |
Contains the specified range. |
| @> | containsElement: E |
Contains the specified value. |
| <@ | containedBy: T |
Contained by the specified range. |
| && | overlaps T |
Overlaps the specified range. |
| << | strictlyLeftOf: T |
Strictly left of the specified range. |
| >> | strictlyRightOf: T |
Strictly right of the specified range. |
| &< | notExtendsRightOf: T |
Does not extend right of the specified range. |
| &> | notExtendsLeftOf: T |
Does not extend left of the specified range. |
| -|- | adjacentTo: T |
Adjacent to the specified range. |
where T is the type of the range field being filtered and E is the element type of the range.
Array fields have the following operators:
| SQL | GraphQL | Description |
|---|---|---|
| IS [NOT] NULL | isNull: Boolean |
Is null (if true is specified) or is not null (if false is specified). |
| = | equalTo: [T] |
Equal to the specified value. |
| <> | notEqualTo: [T] |
Not equal to the specified value. |
| IS DISTINCT FROM | distinctFrom: [T] |
Not equal to the specified value, treating null like an ordinary value. |
| IS NOT DISTINCT FROM | notDistinctFrom: [T] |
Equal to the specified value, treating null like an ordinary value. |
| < | lessThan: [T] |
Less than the specified value. |
| <= | lessThanOrEqualTo: [T] |
Less than or equal to the specified value. |
| > | greaterThan: [T] |
Greater than the specified value. |
| >= | greaterThanOrEqualTo: [T] |
Greater than or equal to the specified value. |
| @> | contains: [T] |
Contains the specified list of values. |
| <@ | containedBy: [T] |
Contained by the specified list of values. |
| && | overlaps: [T] |
Overlaps the specified list of values. |
| = ANY(...) | anyEqualTo: T |
Any array item is equal to the specified value. |
| <> ANY(...) | anyNotEqualTo: T |
Any array item is not equal to the specified value. |
| > ANY(...) | anyLessThan: T |
Any array item is less than the specified value. |
| >= ANY(...) | anyLessThanOrEqualTo: T |
Any array item is less than or equal to the specified value. |
| < ANY(...) | anyGreaterThan: T |
Any array item is greater than the specified value. |
| <= ANY(...) | anyGreaterThanOrEqualTo: T |
Any array item is greater than or equal to the specified value. |
where T is the item type of the array field being filtered.
Complex logic can be expressed using the following logical operators:
| SQL | GraphQL | Description |
|---|---|---|
| AND | and: [T] |
Checks for all expressions in this list. |
| OR | or: [T] |
Checks for any expressions in this list. |
| NOT | not: T |
Negates the expression. |