Graphql-engine: docs: document unique index not showing up in hasura - from existing db

[Mike96Angelo]

I was trying to add Hasura to an existing project and noticed that my Postgresql Unique Indices weren't showing up in the Hasura Console.

These Postgresql Unique Indices are also missing in the on_conflict constraint enum in the GraphQL

NOTE: I am not using Hasura to manage my db migrations.

Docker Image: hasura/graphql-engine:v1.0.0.cli-migrations

GraphQL query:

mutation {
  insert_MyTable(
    on_conflict: {
      constraint: MyTable_column1_column2_key, 
      update_columns: [ column3, column4 ]
    }, 
    objects: {
      column1: "value1",
      column2: "value2",
      column3: "value3",
      column4: "value4"
    }
  ) { 
    returning {
      id
    }
  }
}

Responds with the following error:

{
  "errors": [
    {
      "extensions": {
        "path": "$.selectionSet.insert_MyTable.args.on_conflict.constraint",
        "code": "validation-failed"
      },
      "message": "unexpected value \"MyTable_column1_column2_key\" for enum: 'MyTable_constraint'"
    }
  ]
}

[0x777]

Postgres's ON CONFLICT clause requires you to specify the name of the unique constraint, you cannot use the name of the unique index. You can create a unique constraint as follows:

ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> UNIQUE (<column1>, <column2> );

This will automatically create a unique b-tree index on the specified group of columns.

cc @marionschleifer We'll need to add this to the docs where we talk about upserts.

[Mike96Angelo]

@0x777 Perfect that worked Thanks!

[toddheslin]

OK one case where I've found no solution is where I need to add a partial index.
https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns

In my case I have composite index of three columns:

{
  "routes": {
    "connection_airport_id": "1",
    "connection_airport_id": null,
    "destination_airport_id": "1"
  }
}

The connection_airport_id may or may not be null. A composite unique constraint allows multiple rows as per the above example. If a value is present for connection_airport_id, the constraint works fine. I found the solution to be a partial index as described here: https://www.enterprisedb.com/postgres-tutorials/postgresql-unique-constraint-null-allowing-only-one-null

However, this doesn't allow me to use on conflict.

Is there any workaround here?

[remiremi]

Another use case is creating unique indexes using expressions like

CREATE UNIQUE INDEX example_index ON example_table ((some_json_field->>'field1'), (some_json_field->>'field2'));

Unfortunately not possible with a constraint.

[tirumaraiselvan]

Related to #2219

[cakemountain]

Bumping this - working on a feature where we want to upsert with on_conflict being on a partial unique index on a table; currently this isn't possible. I'm getting an error:

{
  "errors": [
    {
      "extensions": {
        "path": "$.selectionSet.insert_<tablename>.args.on_conflict.constraint",
        "code": "validation-failed"
      },
      "message": "unexpected value \"unique_idx_name\" for enum: '<tablename>_constraint'"
    }
  ]
}

Any chance you'll add support for this? It's possible in Postgres; I've successfully upsert-ed on a unique index before.

[jmoseley]

I've experienced this as well. Even from the GraphQL explorer, the UI suggests the constraint name, but it errors when you execute.

[MikeBosw]

On-conflict support for partial unique indexes (or exclusion constraints) would be great.