supabase: Valid query containing `UNIQUE NULLS NOT DISTINCT` is not validated

Bug report

I confirm this is a bug with Supabase, not with my own application.
I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

In Postgres 15, the NULLS NOT DISTINCT syntax was introduced. However, queries including this syntax fail in Studio - both on CLI and app.supabase.com

To Reproduce

Create a new database, either via app.supabase.com or with supabase CLI
Go to the SQL editor

Run a command that includes the NULLS NOT DISTINCT syntax

CREATE TABLE new_style (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  val1 TEXT NOT NULL,
  val2 TEXT NULL,
  CONSTRAINT uq_val1_val2_new UNIQUE NULLS NOT DISTINCT (val1, val2)
);

It will fail with Failed to validate sql query: syntax error at or near "NULLS"

Or create a table and try to add a constraint

CREATE TABLE new_style (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  val1 TEXT NOT NULL,
  val2 TEXT NULL
);
ALTER TABLE new_style ADD CONSTRAINT uq_val1_val2_new UNIQUE NULLS NOT DISTINCT (val1, val2);

Connect to the database with a client like Postico, and the command will work ok.

Expected behavior

It is expected that the SQL Editor validates the NULLS NOT DISTINCT syntax correctly.

Additional context

There are 2 reports in discord but no bug reports here.

About this issue

Original URL
State: closed
Created a year ago
Comments: 16

Most upvoted comments

Welp, it looks like libpg-query is not able to handle UNIQUE NULLS NOT DISTINCT yet either. I tried to parse the following table within pgsql-parser:

CREATE TABLE new_style (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  val1 TEXT NOT NULL,
  val2 TEXT NULL,
  CONSTRAINT uq_val1_val2_new UNIQUE NULLS NOT DISTINCT (val1, val2)
);

And I got the following error:

syntax error at or near "NULLS"

      19 | export const parse = (sql) => {
      20 |   if (!sql) throw new Error('no SQL provided to parser');
    > 21 |   const result = parseQuerySync(sql);
         |                                ^
      22 |   return result.stmts.map(mapStmt);
      23 | };
      24 |

      at parseQuerySync (../../node_modules/libpg-query/index.js:21:31)
      at parse (src/index.js:21:32)
      at check (__tests__/kitchen-sink.test.js:13:21)
      at Object.<anonymous> (__tests__/kitchen-sink.test.js:42:5)

I’ll throw it in their issue board, but according to their repo, it seems parsing Postgres 15 is still in active development, but I’ll try to verify that just in case. But if this is just some feature that libpg-query needs to develop before anything can work, then I’m afraid this is outside of my expertise until that’s done.

nith2001 on Apr 6, 2023

No problem… happy I could help shed some light on it.

data-envoy on Apr 3, 2023

Woooo, that’s great! I actually don’t have my dev environment fully set up yet so I’ve been playing it by ear (or I guess eye) to trace the issue down.

I’ll see if I can work on this.

nith2001 on Apr 2, 2023

You’re right, it’s the postgres-meta repo.

Here’s a trace of the error.

{
  error: Error: syntax error at or near "NULLS"
      at parseQuerySync (/usr/src/app/node_modules/libpg-query/index.js:21:31)
      at parse (/usr/src/app/node_modules/pgsql-parser/main/index.js:52:47)
      at Module.Parse (file:///usr/src/app/dist/lib/Parser.js:19:22)
      at Object.<anonymous> (file:///usr/src/app/dist/server/routes/query.js:36:40)
      at preHandlerCallback (/usr/src/app/node_modules/fastify/lib/handleRequest.js:128:37)
      at preValidationCallback (/usr/src/app/node_modules/fastify/lib/handleRequest.js:112:5)
      at handler (/usr/src/app/node_modules/fastify/lib/handleRequest.js:76:7)
      at /usr/src/app/node_modules/fastify/lib/contentTypeParser.js:192:9
      at AsyncResource.runInAsyncScope (node:async_hooks:203:9)
      at done (/usr/src/app/node_modules/fastify/lib/contentTypeParser.js:186:14) {
    fileName: 'scan.l',
    functionName: 'scanner_yyerror',
    lineNumber: 1236,
    cursorPosition: 161,
    context: null
  }

The function is here.

Dependency of pgsql-parser is here.

In turn pgsql-parser has a dep of "libpg-query": "13.3.1" here.

The newest version of libpg-query is 15.02.

So I think that is the cause of this issue. Outdated sub dependency.

Edit, the pgsql-parser readme also confirms the compatibility up to postgres 13.

data-envoy on Apr 2, 2023