Skip to end of metadata
Go to start of metadata
This page contains interesting SQL statements and expressions for working with Postgres

Select statements

OOINet resources selects

Standard resource queries

Temporal/vertical range selects:

OOI geospatial selects

See also http://postgis.net/docs/manual-1.3/ch06.html

Association traversal

Traversal with association id path:

Traversal with association id path and cycle detection:

Resource find based on graph search:

See http://www.postgresql.org/docs/9.3/static/queries-with.html

JSON

JSON selects

Inserting a number of test rows into a table

Working with JSON functions and indexes

See

Links

JSON functions and indexes:

Working with indexes

Index functions

Note: the json data type is not indexable in Postgres 9.3 use operators that covert the result to text instead (e.g. ->>, #>>, json_extract_path_text

Notes:

  • A GIN index is an index for keys within collections (e.g. keys contained in arrays). It's efficient when the same keys occur in multiple collections
  • Functional indexes can be created on IMMUTABLE functions. You can declare own functions calling built in functions as IMMUTABLE
  • Postgres 9.3 JSON query functions are IMMUTABLE

Analyzing index use

See:

Working with PSQL

Describe a table schema:

Working with Databases

To export a database:

To restore the database to this state:

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.