Skip to main content

Tables

A table stores your data as rows under a fixed set of named columns, each with a PostgreSQL data type. Tables live inside a schema and are addressed as schema.table throughout GC2 (Geo Compute Cloud).

The Table API is the provisioning interface for tables: it creates a table, optionally with nested columns, indices and constraints in one request, reads its definition, renames it or moves it to another schema, and drops it. Once a table exists you read and write its rows with SQL, GraphQL or the Feature API, and a table with a geometry column is automatically available as a map layer through the OGC services. Use this API from migrations, CLI scripts or the Web App when you need to change the structure of your data rather than its content.

Add table​

A new table object can be empty (name is the only property) or contain nested columns, indices and constraints.

Create new empty table
POST https://api.gc2.io/api/v4/schemas/rockhall HTTP/1.1
Content-Type: application/json
Authorization: Bearer abc123

{
    "name": "inductees"
}

Get table​

You can either get a single table or a collection of tables. The schema object contains all nested objects like columns, indices and constraints. Use the namesOnly parameter to omit nested objects.

All tables, names only
GET https://api.gc2.io/api/v4/schemas/rockhall/tables?namesOnly HTTP/1.1
Accept: application/json; charset=utf-8
Authorization: Bearer abc123
Specific tables
GET https://api.gc2.io/api/v4/schemas/rockhall/tables/inductees,anothertable HTTP/1.1
Accept: application/json; charset=utf-8
Authorization: Bearer abc123

A table object with nested objects looks like this:

{
  "name": "inductees",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "is_nullable": false,
      "default_value": null,
      "comment": null,
      "_num": 1,
      "_full_type": "integer",
      "_typname": "int4",
      "_is_array": false,
      "_character_maximum_length": null,
      "_numeric_precision": 32,
      "_numeric_scale": 0,
      "_max_bytes": 4,
      "_reference": null,
      "_restriction": null,
      "_is_primary": true,
      "_is_unique": true,
      "_index_method": [
        "btree"
      ],
      "_checks": null
    },
    {
      "name": "name",
      "type": "character varying",
      "is_nullable": true,
      "default_value": null,
      "comment": null,
      "_num": 2,
      "_full_type": "character varying",
      "_typname": "varchar",
      "_is_array": false,
      "_character_maximum_length": -5,
      "_numeric_precision": null,
      "_numeric_scale": null,
      "_max_bytes": 1073741824,
      "_reference": null,
      "_restriction": null,
      "_is_primary": false,
      "_is_unique": false,
      "_index_method": null,
      "_checks": null
    }
  ],
  "indices": [
    {
      "name": "inductees-id-btree",
      "method": "btree",
      "unique": true,
      "columns": [
        "id"
      ]
    }
  ],
  "constraints": [
    {
      "name": "inductees-id-primary",
      "constraint": "primary",
      "columns": [
        "id"
      ]
    }
  ],
  "comment": null
}

Rename and move table​

You can move a table to another schema by setting the schema property and renaming the table by setting the name property.

PATCH https://api.gc2.io/api/v4/schemas/rockhall/tables/inductees HTTP/1.1
Content-Type: application/json
Authorization: Bearer abc123

{
    "name": "members",
    "schema": "rock_roll_of_hall"
}

Delete table​

DELETE https://api.gc2.io/api/v4/schemas/rockhall/tables/inductees HTTP/1.1
Authorization: Bearer abc123