what does the @> operator in postgres do?

Question

I came across a query in postgres here which uses the @> operator on earth objects.

I've searched everywhere, but have come up empty on the meaning of this operator (and likely others like it, eg: @<, etc...).

Is this documented somewhere in the postgres docs? I'm even more curious to understand what the operator does on earth objects.

Null

postgresql.org/docs/current/… – Brutal

Answer 1

In general @> is the "contains" operator.

It is defined for several data types.

• arrays: http://www.postgresql.org/docs/current/static/functions-array.html
• range types: http://www.postgresql.org/docs/current/static/functions-range.html
• geometric types: http://www.postgresql.org/docs/current/static/functions-geometry.html
• JSON (and JSONB): http://www.postgresql.org/docs/current/static/functions-json.html

Murmurous

I dug. But obviously not deep enough. :-/ This is perfect. Thank you! – Null

Do you know why select (ARRAY[1464816152000]::bigint[] <@ ARRAY[1464816152000,1466906028002]::bigint[])::boolean; return false, but select (ARRAY[1464816152000]::bigint[] <@ ARRAY[1464816152000,1]::bigint[])::boolean; returns true? – Huntsville

@CristianCiobotea The first statement returns true for me. – Coriolanus

Note that when searching GIN indexed array fields, using @> is much faster than ANY operator because the former uses the index while latter does not. More: coderwall.com/p/ey7mbq/… – Consueloconsuetude

Answer 2

According to the PostgreSQL Official Documentation

interval values can be written using the following verbose syntax:

[@] quantity unit [quantity unit...] [direction] where quantity is a number (possibly signed); unit is microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, or abbreviations or plurals of these units; direction can be ago or empty. The at sign (@) is optional noise. The amounts of the different units are implicitly added with appropriate sign accounting. ago negates all the fields. This syntax is also used for interval output, if IntervalStyle is set to postgres_verbose.

Jellybean

The @> operator has nothing to do with interval literals – Murmurous

Thanks for not deleting this answer. Google and postgresql's internal search engine ignore @ in queries, so it's nearly impossible to search for the meaning of the "at sign" or "at symbol" (@), and I was having a very hard time locating documentation that explained what it meant within postgresql. Your wrong answer helped me here more than the correct one! 😂 – Kong

Yes about searching "at". To help, I quote wikipedia's "AT sign" article: "The absence of a single English word for the symbol has prompted some writers to use the French arobase or Spanish and Portuguese arroba, or to coin new words such as ampersat and asperand, or the (visual) onomatopoeia strudel, but none of these have achieved wide use." Myself I searched for "at greater than" "arobase greater than", "arobase supérieur à"... – Uncap