Columns

A column is one named field of a table, and every column has a PostgreSQL data type. The type constrains which values the column accepts and gives them meaning: a numeric column rejects arbitrary text and can be used in calculations, a text column accepts almost anything but supports string operations instead, and a PostGIS geometry column turns the table into a map layer. See Types for how each type is sent and returned through the API.

The Column API lets you evolve a table's structure after it has been created: add a column, read its definition, rename it, change its type, nullability or default value, and drop it. Because the SQL API does not accept DDL statements, this API, the CLI and the Web App are the ways to alter columns on a GC2 (Geo Compute Cloud) table.

Get column

You can either get a single column or a collection of columns.

Web App

HTTP

All columns for a table

GET https://api.gc2.io/api/v4/schemas/rockhall/tables/columns HTTP/1.1
Accept: application/json; charset=utf-8
Authorization: Bearer abc123

Specific column

GET https://api.gc2.io/api/v4/schemas/rockhall/tables/inductees/columns/id HTTP/1.1
Accept: application/json; charset=utf-8
Authorization: Bearer abc123

CLI

gc2 column get "rockhall" "inductees" "id"

A column object with nested objects looks like this. All properties prefixed with an _ (underscore) is read only. If submitted in a POST or PATCH they will be ignored.

{
  "name": "id",
  "type": "integer",
  "is_nullable": false,
  "default_value": null,
  "comment": null,
  "identity_generation": 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
}

Properties

• name: Name of the column.
• type: The type of the column, such as varchar, integer, boolean, etc.
• is_nullable: If true, the column can be set to null.
• default_value: The column is set to the default value if no value is provided.
• comment: A comment or description for the column.
• identity_generation: An identity column is a special column generated automatically from an implicit sequence. It can be used to generate key values. Possible values are always or by default.

Add column

Web App

HTTP

Create new column

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

{
    "name": "id",
    "type": "integer"
}

Create multiple columns

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

[
    { "name": "id", "type": "integer"},
    { "name": "name", "type": "varchar(50)"}
]

CLI

gc2 table add "rockhall" "inductees" "id" integer

Rename, set type, set nullable, set default

You can change name, type, default and nullable of a column or collection of columns.

Web App

HTTP

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

{
    "name": "name2",
    "type": "varchar(100)",
    "default_value": "Empty",
    "is_nullable": false
}

CLI

rename

gc2 schema rename "rockhall" "inductees" "id" "id2"

type

gc2 schema type "rockhall" "inductees" "name" varchar(100)

nullable

gc2 schema nullable "rockhall" "inductees" "name" false

default

gc2 schema default "rockhall" "inductees" "name" "Empty"

note

The type of a column can only be changed to a type that is compatible with the existing data.

Delete column

Web App

HTTP

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

CLI

gc2 column drop "rockhall" "inductees" "id"