graphql-engine: On-conflict support for partial unique indexes

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'"
    }
  ]
}

About this issue

Original URL
State: open
Created 4 years ago
Reactions: 17
Comments: 26 (6 by maintainers)

Most upvoted comments

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

+22

MikeBosw on Oct 17, 2020

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?

+10

toddheslin on Jun 11, 2020

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.

remiremi on Jul 3, 2020

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.

0x777 on Jan 9, 2020

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.

cakemountain on Sep 24, 2020

@tirumaraiselvan Bumping this as closed incorrectly - on-conflict support for partial unique indexes still very much desired.

crieggalder on Sep 5, 2022