Does not support declaritive partitions.

[Kevin101Zhang]

Describe the bug
Does not support declaritive partitions - PARTITION OF ... FOR VALUES WITH;
declarative parititon

Database Engine
PostgreSQL.

To Reproduce
This Testcase will work as a base:

 {
        title: 'custom data type and partition',
        sql: [
          `CREATE TYPE access_key_permission_kind AS ENUM ('FULL_ACCESS', 'FUNCTION_CALL');

          CREATE TABLE
          access_keys (
          public_key text NOT NULL,
          account_id text NOT NULL,
          created_by_receipt_id text NULL,
          deleted_by_receipt_id text NULL,
          last_update_block_height numeric(20) NOT NULL,
          permission_kind access_key_permission_kind NOT NULL,
          CONSTRAINT access_keys_pk PRIMARY KEY (public_key, account_id)
          ) PARTITION BY HASH (public_key);
          CREATE INDEX access_keys_account_id_idx ON access_keys USING btree (account_id);
          CREATE INDEX access_keys_last_update_block_height_idx ON access_keys USING btree (last_update_block_height);
          CREATE INDEX access_keys_public_key_idx ON access_keys USING btree (public_key);
          `,
          `CREATE TYPE "access_key_permission_kind" AS ENUM ('FULL_ACCESS', 'FUNCTION_CALL') ; CREATE TABLE "access_keys" (public_key TEXT NOT NULL, account_id TEXT NOT NULL, created_by_receipt_id TEXT NULL, deleted_by_receipt_id TEXT NULL, last_update_block_height NUMERIC(20) NOT NULL, permission_kind access_key_permission_kind NOT NULL, CONSTRAINT "access_keys_pk" PRIMARY KEY (public_key, account_id)) PARTITION BY HASH(public_key) ; CREATE INDEX "access_keys_account_id_idx" ON "access_keys" USING BTREE (account_id) ; CREATE INDEX "access_keys_last_update_block_height_idx" ON "access_keys" USING BTREE (last_update_block_height) ; CREATE INDEX "access_keys_public_key_idx" ON "access_keys" USING BTREE (public_key)`
        ]
      },

Then added the declaritive partition on the bottom with break the testcase with error
SyntaxError: Expected "(", ",", "--", "/*", "CROSS", "FULL", "INNER", "JOIN", "LEFT", "LIKE", "RIGHT", or [ \t\n\r] but "O" found.

{
        title: 'custom data type and partition and parititon of',
        sql: [
          `CREATE TYPE access_key_permission_kind AS ENUM ('FULL_ACCESS', 'FUNCTION_CALL');

          CREATE TABLE
          access_keys (
          public_key text NOT NULL,
          account_id text NOT NULL,
          created_by_receipt_id text NULL,
          deleted_by_receipt_id text NULL,
          last_update_block_height numeric(20) NOT NULL,
          permission_kind access_key_permission_kind NOT NULL,
          CONSTRAINT access_keys_pk PRIMARY KEY (public_key, account_id)
          ) PARTITION BY HASH (public_key);
          CREATE INDEX access_keys_account_id_idx ON access_keys USING btree (account_id);
          CREATE INDEX access_keys_last_update_block_height_idx ON access_keys USING btree (last_update_block_height);
          CREATE INDEX access_keys_public_key_idx ON access_keys USING btree (public_key);
          CREATE TABLE access_keys_hash_p0 PARTITION OF access_keys FOR VALUES WITH (modulus 10, remainder 0);
          `,
          `CREATE TYPE "access_key_permission_kind" AS ENUM ('FULL_ACCESS', 'FUNCTION_CALL') ; CREATE TABLE "access_keys" (public_key TEXT NOT NULL, account_id TEXT NOT NULL, created_by_receipt_id TEXT NULL, deleted_by_receipt_id TEXT NULL, last_update_block_height NUMERIC(20) NOT NULL, permission_kind access_key_permission_kind NOT NULL, CONSTRAINT "access_keys_pk" PRIMARY KEY (public_key, account_id)) PARTITION BY HASH(public_key) ; CREATE INDEX "access_keys_account_id_idx" ON "access_keys" USING BTREE (account_id) ; CREATE INDEX "access_keys_last_update_block_height_idx" ON "access_keys" USING BTREE (last_update_block_height) ; CREATE INDEX "access_keys_public_key_idx" ON "access_keys" USING BTREE (public_key) ; CREATE TABLE access_keys_hash_p0 PARTITION OF access_keys FOR VALUES WITH (modulus 10, remainder 0)`
        ]
      },

Expected behavior

Screenshots

Additional context
Tried debugging this myself for a bit but I got stuck. Hopefully I was on the right track here

in create_table_stmt I added

   po:partition_options? {
      if(t) t.forEach(tt => tableList.add(`create::${tt.db}::${tt.table}`));
      return {
        tableList: Array.from(tableList),
        columnList: columnListTableAlias(columnList),
        ast: {
          type: a[0].toLowerCase(),
          keyword: 'table',
          temporary: tp && tp[0].toLowerCase(),
          if_not_exists:ife,
          table: t,
          ignore_replace: ir && ir[0].toLowerCase(),
          as: as && as[0].toLowerCase(),
          query_expr: qe && qe.ast,
          create_definitions: c,
          table_options: to,
          partition_options: po
        }
      }
    }

Bottom of peg file
partition_options
  = "PARTITION OF" table_ref_list "FOR VALUES" "WITH" "(" "modulus" number "," "remainder" number ")"
  

[zwallacedev]

@taozhi8833998 did you already solve this issue? It looks like the test he set up earlier is already passing.

[taozhi8833998]

@taozhi8833998 did you already solve this issue? It looks like the test he set up earlier is already passing.

I am trying to support the table partitioning

[taozhi8833998]

@Kevin101Zhang @zwallacedev fixed it, I will add more test cases and open a new pr.

[zwallacedev]

@taozhi8833998 no worries! I'm trying to identify which open issues I can assist with 😄

[taozhi8833998]

@taozhi8833998 no worries! I'm trying to identify which open issues I can assist with 😄

@zwallacedev Thanks for your help, you can try to fix #1872