Cognite Postgres Gateway Tables API

openapi: 3.2.0
info:
  title: Cognite Postgres Gateway Tables API
  description: '# Introduction

    This is the reference documentation for the Cognite API with

    an overview of all the available methods.'
  version: v1
  contact:
    name: Cognite Support
    url: https://support.cognite.com
    email: support@cognite.com
servers:
- url: https://{cluster}.cognitedata.com/api/v1/projects/{project}
  description: The URL for the CDF cluster to connect to
  variables:
    cluster:
      enum:
      - api
      - az-tyo-gp-001
      - az-eastus-1
      - az-power-no-northeurope
      - westeurope-1
      - asia-northeast1-1
      - gc-dsm-gp-001
      default: api
      description: The CDF cluster to connect to
    project:
      default: publicdata
      description: The CDF project name.
security:
- oidc-token:
  - https://{cluster}.cognitedata.com/.default
- oauth2-client-credentials:
  - https://{cluster}.cognitedata.com/.default
- oauth2-open-industrial-data:
  - https://api.cognitedata.com/.default
- oauth2-auth-code:
  - https://{cluster}.cognitedata.com/.default
tags:
- name: Postgres Gateway Tables
  description: View and create foreign **tables** for a given **user**
paths:
  /postgresgateway/tables/{username}:
    get:
      tags:
      - Postgres Gateway Tables
      operationId: list_tables
      x-capability:
      - postgresGateway:READ
      parameters:
      - $ref: '#/components/parameters/Username'
      - $ref: '#/components/parameters/100Limit_'
      - $ref: '#/components/parameters/Cursor'
      - in: query
        name: includeBuiltIns
        description: Determines if API should return built-in tables or not
        required: false
        schema:
          type: boolean
          default: false
      description: '> **Required capabilities:** `postgresGateway:READ`


        List all tables in a given project. If more than `limit` tables exist, a cursor for pagination will be returned with the response.'
      responses:
        '200':
          description: List of tables paginated
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ItemsWithCursor_Table_'
        '400':
          $ref: '#/components/responses/400ErrorResponse'
        '422':
          $ref: '#/components/responses/ValidationError_'
      summary: List tables
      x-code-samples:
      - lang: Python
        label: Python SDK
        source: "custom_table_list = client.postgres_gateway.tables.list(\"myUserName\", limit=5)\n\nfor table in client.postgres_gateway.tables:\n    table # do something with the custom table\n\nfor table_list in client.postgres_gateway.tables(chunk_size=25):\n    table_list # do something with the custom tables\n"
    post:
      tags:
      - Postgres Gateway Tables
      operationId: create_tables
      x-capability:
      - postgresGateway:WRITE
      parameters:
      - $ref: '#/components/parameters/Username'
      description: '> **Required capabilities:** `postgresGateway:WRITE`


        Create up to 10 tables.'
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Items_CreateTable_'
        description: List of tables to create
      responses:
        '200':
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ItemsWithoutCursor_TableCreated_'
          description: List of created tables
        '400':
          description: Response of a failed request
          $ref: '#/components/responses/400ErrorResponse'
        '422':
          $ref: '#/components/responses/ValidationError_'
      summary: Create tables
      x-code-samples:
      - lang: Python
        label: Python SDK
        source: 'from cognite.client.data_classes.data_modeling import ViewId

          from cognite.client.data_classes.postgres_gateway import ViewTableWrite

          table = ViewTableWrite(tablename="myCustom", options=ViewId(space="mySpace", external_id="myExternalId", version="v1"))

          res = client.postgres_gateway.tables.create("myUserName",table)

          '
  /postgresgateway/tables/{username}/byids:
    post:
      tags:
      - Postgres Gateway Tables
      operationId: retrieve_tables
      parameters:
      - $ref: '#/components/parameters/Username'
      x-capability: postgresGateway:READ
      description: '> **Required capabilities:** `postgresGateway:READ`


        Retreive a list of postgres tables for a user by their table names, optionally ignoring unknown table names'
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/ItemsWithIgnoreUnknown_Tablename_'
      responses:
        '200':
          description: List postgres tables by table names
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ItemsWithoutCursor_Table_'
        '400':
          $ref: '#/components/responses/400ErrorResponse'
          description: Response of a failed request
        '422':
          $ref: '#/components/responses/ValidationError_'
      summary: Retrieve tables
      x-code-samples:
      - lang: Python
        label: Python SDK
        source: 'res = client.postgres_gateway.tables.retrieve("myUserName", ''myCustom'')


          res = client.postgres_gateway.tables.retrieve("myUserName", ["myCustom", "myCustom2"])

          '
  /postgresgateway/tables/{username}/delete:
    post:
      tags:
      - Postgres Gateway Tables
      operationId: delete_tables
      parameters:
      - $ref: '#/components/parameters/Username'
      x-capability:
      - postgresGateway: WRITE
      description: '> **Required capabilities:** `[object Object]`


        Delete up to 10 tables'
      requestBody:
        required: true
        description: Tables to delete
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/ItemsWithIgnoreUnknown_Tablename_'
      responses:
        '200':
          $ref: '#/components/responses/EmptyResponse'
          description: Empty response
        '400':
          $ref: '#/components/responses/400ErrorResponse'
        '422':
          $ref: '#/components/responses/ValidationError_'
      summary: Delete tables
      x-code-samples:
      - lang: Python
        label: Python SDK
        source: 'client.postgres_gateway.tables.delete("myUserName", ["myCustom", "myCustom2"])

          '
components:
  schemas:
    TableType:
      enum:
      - raw_rows
      - built_in
      - nodes
      - edges
      type: string
      title: TableType
      description: The type of table (usually built_in) but could be any of raw_rows, nodes or edges when custom.
    CreateFdmTable:
      type: object
      title: Create Fdm table
      additionalProperties: false
      required:
      - type
      - tablename
      - options
      allOf:
      - $ref: '#/components/schemas/CreateTable_'
      - type: object
        properties:
          type:
            enum:
            - view
            title: Type
            type: string
          options:
            $ref: '#/components/schemas/FdmTableOptions'
      discriminator:
        propertyName: type
    ItemsWithoutCursor_Table_:
      type: object
      additionalProperties: false
      properties:
        items:
          items:
            $ref: '#/components/schemas/Table'
          title: Items
          type: array
      required:
      - items
    Tablename:
      type: string
      title: Tablename
      maxLength: 60
      minLength: 1
    FdmTableOptions:
      type: object
      additionalProperties: false
      description: Fdm table options
      required:
      - space
      - externalId
      properties:
        space:
          type: string
        externalId:
          type: string
        version:
          type: string
    ItemsWithoutCursor_TableCreated_:
      type: object
      additionalProperties: false
      properties:
        items:
          items:
            $ref: '#/components/schemas/TableCreated'
          title: Items
          type: array
      required:
      - items
    CreateTable:
      type: object
      title: Create Table
      additionalProperties: false
      discriminator:
        mapping:
          raw_rows: '#/components/schemas/CreateRawTable'
          view: '#/components/schemas/CreateFdmTable'
        propertyName: type
      oneOf:
      - $ref: '#/components/schemas/CreateRawTable'
      - $ref: '#/components/schemas/CreateFdmTable'
    ItemsWithIgnoreUnknown_Tablename_:
      type: object
      additionalProperties: false
      title: List of postgres table names
      required:
      - items
      properties:
        items:
          items:
            $ref: '#/components/schemas/TablenameWrapper'
          title: Items
          type: array
          maxItems: 10
          minItems: 1
        ignoreUnknownIds:
          type: boolean
          title: IgnoreUnknown
          description: Ignore table names not found
    Column_:
      type: object
      description: Foreign table columns.
      additionalProperties:
        type: object
        required:
        - type
        properties:
          type:
            type: string
            enum:
            - TEXT
            - VARCHAR
            - BOOL
            - BIGINT
            - DOUBLE PRECISION
            - REAL
            - TIMESTAMPTZ
            - JSON
            - TEXT[]
            - VARCHAR[]
            - BOOL[]
            - BIGINT[]
            - DOUBLE PRECISION[]
            - REAL[]
            - JSON[]
            - BYTEA
    Error:
      type: object
      required:
      - code
      - message
      description: Cognite API error.
      properties:
        code:
          type: integer
          description: HTTP status code.
          format: int32
          example: 401
        message:
          type: string
          description: Error message.
          example: Could not authenticate.
        missing:
          type: array
          description: List of lookup objects that do not match any results.
          items:
            type: object
            additionalProperties: true
        duplicated:
          type: array
          description: List of objects that are not unique.
          items:
            type: object
            additionalProperties: true
    CreateTable_:
      type: object
      additionalProperties: false
      required:
      - type
      - tablename
      properties:
        type:
          enum:
          - raw_rows
          - view
          title: Type
          type: string
        tablename:
          type: string
          title: Tablename
          maxLength: 60
          minLength: 1
          description: 'Name of the foreign table.\

            __CANNOT__ be any of the the built-in table names found [here](https://docs.cognite.com/cdf/integration/guides/interfaces/ingest_into_raw#create-a-table)

            '
    ValidationErrorContent_:
      type: object
      required:
      - code
      - message
      description: Cognite API error.
      properties:
        code:
          type: integer
          description: HTTP status code.
          format: int32
          example: 422
        message:
          type: string
          description: Error message.
          example: 'Extra inputs are not permitted: type'
    Username:
      type: string
      title: Username
      description: Username to authenticate the user on the DB.
    RawTableOptions:
      type: object
      additionalProperties: false
      description: Raw rows options
      required:
      - database
      - table
      properties:
        database:
          type: string
        table:
          type: string
        primaryKey:
          type: string
    ItemsWithCursor_Table_:
      type: object
      additionalProperties: false
      properties:
        items:
          items:
            $ref: '#/components/schemas/Table'
          title: Items
          type: array
          maxItems: 100
          minItems: 0
        nextCursor:
          type: string
          title: Next cursor
          description: Cursor for pagination
      required:
      - items
    TableOptions:
      description: Table options
      title: Table Options
      oneOf:
      - $ref: '#/components/schemas/FdmTableOptions'
      - $ref: '#/components/schemas/RawTableOptions'
    Items_CreateTable_:
      additionalProperties: false
      required:
      - items
      properties:
        items:
          items:
            $ref: '#/components/schemas/CreateTable'
          title: Items
          type: array
          minItems: 1
          maxItems: 10
    EpochTimestamp:
      description: The number of milliseconds since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC), minus leap seconds.
      type: integer
      minimum: 0
      format: int64
      example: 1730204346000
    Table:
      type: object
      additionalProperties: false
      title: (Custom) Table
      description: Foreign tables
      required:
      - type
      - columns
      - tablename
      properties:
        columns:
          $ref: '#/components/schemas/Column_'
        options:
          $ref: '#/components/schemas/TableOptions'
        type:
          $ref: '#/components/schemas/TableType'
        tablename:
          $ref: '#/components/schemas/Tablename'
    TableCreated:
      type: object
      additionalProperties: false
      title: Table Created Response
      description: Foreign tables
      required:
      - type
      - columns
      - tablename
      - createdTime
      properties:
        columns:
          $ref: '#/components/schemas/Column_'
        options:
          $ref: '#/components/schemas/TableOptions'
        createdTime:
          $ref: '#/components/schemas/EpochTimestamp'
        type:
          type: string
          title: Table Type
          enum:
          - raw_rows
          - view
        tablename:
          $ref: '#/components/schemas/Tablename'
    TablenameWrapper:
      type: object
      additionalProperties: false
      required:
      - tablename
      properties:
        tablename:
          $ref: '#/components/schemas/Tablename'
    CreateRawTable:
      type: object
      title: Create Raw Table
      additionalProperties: false
      required:
      - type
      - options
      - columns
      allOf:
      - $ref: '#/components/schemas/CreateTable_'
      - type: object
        properties:
          type:
            enum:
            - raw_rows
            title: Type
            type: string
          options:
            $ref: '#/components/schemas/RawTableOptions'
          columns:
            $ref: '#/components/schemas/Column_'
      discriminator:
        propertyName: type
  responses:
    EmptyResponse:
      description: Empty response.
      content:
        application/json:
          schema:
            type: object
    ValidationError_:
      description: Validation Error
      content:
        application/json:
          schema:
            type: object
            required:
            - error
            properties:
              error:
                $ref: '#/components/schemas/ValidationErrorContent_'
    400ErrorResponse:
      description: The response for a bad request.
      content:
        application/json:
          schema:
            type: object
            required:
            - error
            properties:
              error:
                $ref: '#/components/schemas/Error'
          example:
            error:
              code: 400
              message: '`null` values aren''t allowed.'
  parameters:
    Username:
      name: username
      description: The name of the username (a.k.a. database) to be managed from the API
      in: path
      required: true
      schema:
        $ref: '#/components/schemas/Username'
    Cursor:
      name: cursor
      description: 'Cursor for paging through results. In general, if a response contains a `nextCursor`

        property, it means that there may be more results, and you should pass that value as the

        `cursor` parameter in the next request.


        Note that the cursor may or may not be encrypted, but either way, it is not intended to be

        decoded. Its internal structure is not a part of the public API, and may change without

        notice. You should treat it as an opaque string and not attempt to craft your own cursors.

        '
      in: query
      schema:
        type: string
        example: 4zj0Vy2fo0NtNMb229mI9r1V3YG5NBL752kQz1cKtwo
    100Limit_:
      name: limit
      description: Limits the number of results to be returned. The maximum results returned by the server is 100 even if you specify a higher limit.
      in: query
      schema:
        type: integer
        default: 100
        minimum: 1
        maximum: 100
  securitySchemes:
    oidc-token:
      type: http
      scheme: bearer
      bearerFormat: OpenID Connect or OAuth2 token
      description: Access token issued by the CDF project's configured identity provider. Access token must be an OpenID Connect token, and the project must be configured to accept OpenID Connect tokens. Use a header key of 'Authorization' with a value of 'Bearer $accesstoken'. The token can be obtained through any flow supported by the identity provider.
    oauth2-client-credentials:
      type: oauth2
      description: Access token issued by the CDF project's configured identity provider. Access token must be an OpenID Connect token, and the project must be configured to accept OpenID Connect tokens. Use a header key of 'Authorization' with a value of 'Bearer $accesstoken'. The token can be obtained through any flow supported by the identity provider.
      flows:
        clientCredentials:
          tokenUrl: https://your-idps.token.url/
          scopes:
            default: https://{cluster}.cognitedata.com/.default
    oauth2-auth-code:
      type: oauth2
      description: Access token issued by the CDF project's configured identity provider. Access token must be an OpenID Connect token, and the project must be configured to accept OpenID Connect tokens. Use a header key of 'Authorization' with a value of 'Bearer $accesstoken'. The token can be obtained through any flow supported by the identity provider.
      flows:
        authorizationCode:
          authorizationUrl: https://your-idps.authorization.url/
          tokenUrl: https://your-idps.token.url/
          scopes:
            default: https://{cluster}.cognitedata.com/.default
    oauth2-open-industrial-data:
      type: oauth2
      description: Auth flow for Open Industrial Data. Get your client secret from https://hub.cognite.com/open-industrial-data-211.
      flows:
        clientCredentials:
          tokenUrl: https://login.microsoftonline.com/48d5043c-cf70-4c49-881c-c638f5796997/oauth2/v2.0/token
          scopes:
            default: https://api.cognitedata.com/.default
    org-oidc-token:
      type: openIdConnect
      openIdConnectUrl: https://auth.cognite.com/.well-known/openid-configuration
      description: 'Access token issued by the Cognite authorization server, and valid for the target organization. The token must

        be an OpenID Connect token, and it can be obtained by performing an OIDC login flow toward `auth.cognite.com`.

        This is a single URL for all CDF organizations.'
x-tagGroups:
- name: Changelog
  tags:
  - Changelog
- name: Organizations and projects
  tags:
  - Organizations
  - Projects
- name: Identity and access management
  tags:
  - Principals
  - Groups
  - Security categories
  - Sessions
  - Token
  - User profiles
  - Project Deletion Reporting
- name: Data modeling
  tags:
  - Data Modeling
  - Data models
  - Spaces
  - Views
  - Containers
  - Nodes
  - Instances
  - Statistics
  - Streams
  - Records
- name: Asset-centric data model
  tags:
  - Assets
  - Time series
  - Synthetic Time Series
  - Data point subscriptions
  - Events
  - Files
  - Sequences
  - Geospatial
  - Seismic
- name: 3D
  tags:
  - 3D Models
  - 3D Model Revisions
  - 3D Files
  - 3D Asset Mapping
  - 3D Contextualization
  - 3D Jobs
  - 3D Migration
  - 3D Scenes
- name: Contextualization
  tags:
  - Entity matching
  - Entity matching pipelines
  - Engineering diagrams
  - Vision
  - Advanced joins
- name: Cognite AI
  tags:
  - Agents
  - Skills
  - Chat Completions
  - Document AI
  - Models
- name: Documents
  tags:
  - Documents
  - Document preview
- name: Data ingestion
  tags:
  - Raw
  - Extraction Pipelines
  - Extraction Pipelines Runs
  - Extraction Pipelines Config
  - Extractors
- name: Data organization
  tags:
  - Data sets
  - Data domains
  - Data products
  - Rule sets
  - Labels
  - Relationships
  - Annotations
- name: Transformations
  tags:
  - Transformations
  - Transformation Jobs
  - Transformation Schedules
  - Transformation Notifications
  - Query
  - Schema
- name: Functions
  tags:
  - Functions
  - Function calls
  - Function schedules
- name: Hosted Extractors
  tags:
  - Sources
  - Jobs
  - Destinations
  - Mappings
- name: PostgreSQL Gateway
  tags:
  - Postgres Gateway Users
  - Postgres Gateway Tables
- name: SAP Writeback
  tags:
  - SAP Instances
  - SAP Endpoints
  - Schema Mappings
  - Writeback Requests
- name: Data workflows
  tags:
  - Workflows
  - Workflow versions
  - Workflow executions
  - Workflow triggers
  - Tasks
  - Workers
- name: Simulators
  tags:
  - Simulators
  - Simulator Integrations
  - Simulator Models
  - Simulator Routines
  - Simulation Runs
  - Simulator Logs
- name: Units
  tags:
  - Units
  - Unit Systems
- name: ''
  tags:
  - ''