[psqldef] Syntax error when using psqldef with Supabase

[shiroemons]

Supabase

Platform

• OS: macOS
• RDBMS: PostgreSQL
• Version: 0.16.9

--export output

$ psqldef --host db.[host].supabase.co --user postgres --password [password] postgres --export
2023/10/10 00:26:41 syntax error at or near "empty"

Input SQL

• No

Current output

$ psqldef --host db.[host].supabase.co --user postgres --password [password] postgres --export
2023/10/10 00:26:41 syntax error at or near "empty"

Expected output

No error

[k0kubun]

Can you share the output of pg_dump with the same database? We need to see the SQL that psqldef is failing to parse.

[shiroemons]

@k0kubun
I have attached the pg_dump results.

supabase.txt

[hokaccha]

The error appears to occur in the following locations.

CREATE TABLE auth.saml_providers (
    id uuid NOT NULL,
    sso_provider_id uuid NOT NULL,
    entity_id text NOT NULL,
    metadata_xml text NOT NULL,
    metadata_url text,
    attribute_mapping jsonb,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    CONSTRAINT "entity_id not empty" CHECK ((char_length(entity_id) > 0)),
    --　　　　　　　　　　　　　　 ^ here                       
    CONSTRAINT "metadata_url not empty" CHECK (((metadata_url = NULL::text) OR (char_length(metadata_url) > 0))),
    CONSTRAINT "metadata_xml not empty" CHECK ((char_length(metadata_xml) > 0))
);

I confirmed that a space in the name of the constraint would cause an error.

$ cat schema.sql 
CREATE TABLE foo (
  id text NOT NULL,
  CONSTRAINT "foo bar" CHECK (char_length(id) > 0)
);

$ psql --host localhost --user postgres sandbox < schema.sql     
CREATE TABLE

$ psql --host localhost --user postgres sandbox -c '\d foo' 
               Table "public.foo"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 id     | text |           | not null | 
Check constraints:
    "foo bar" CHECK (char_length(id) > 0)


$ psqldef --host localhost --user postgres --export sandbox
2023/10/11 09:47:56 syntax error at or near "bar"

If the constraint name is foo_bar, it works fine.

$ psql --host localhost --user postgres sandbox -c '\d foo'                                
               Table "public.foo"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 id     | text |           | not null | 
Check constraints:
    "foo_bar" CHECK (char_length(id) > 0)


$ psqldef --host localhost --user postgres --export sandbox 
CREATE TABLE "public"."foo" (
    "id" text NOT NULL CONSTRAINT foo_bar CHECK (char_length(id) > 0)
);