the Postgresql words "time" and "references" are unrecognized by the sql parser

[echouvet]

I tried to get feed my database's schema into pg-mem from a file using the following

  db.public.none(fs.readFileSync(fileName, "utf8"));

my file has the following table:

create table ws (
    id         uuid primary key default gen_random_uuid(),
    created_at timestamp without time zone not null default (now() at time zone 'utc'),
    updated_at timestamp without time zone not null default (now() at time zone 'utc'),
    name text not null
);

I got the following error message:

     Error: 💔 Your query failed to parse.
This is most likely due to a SQL syntax error. However, you might also have hit a bug, or an unimplemented feature of pg-mem.
If this is the case, please file an issue at https://github.com/oguimbal/pg-mem along with a query that reproduces this syntax error.

👉 Failed query:

    create table ws
(
    id         uuid primary key default gen_random_uuid(),
    created_at timestamp without time zone not null default (now() at time zone 'utc'),
    updated_at timestamp without time zone not null default (now() at time zone 'utc'),

    name text not null
);
;

💀 Syntax error at line 4 col 71:

      created_at timestamp without time zone not null default (now() at TIME
                                                                        ^
Unexpected word token: "time". I did not expect any more input. Here is the state of my parse table:

    kw_between → %word ● 
    kw_operator → %word ●

      at Object.parseSql (node_modules/pg-mem/src/parse-cache.ts:44:15)
      at DbSchema.parse (node_modules/pg-mem/src/schema.ts:80:16)
      at DbSchema.queries (node_modules/pg-mem/src/schema.ts:88:31)
      at queries.next (<anonymous>)
      at DbSchema.query (node_modules/pg-mem/src/schema.ts:67:20)
      at DbSchema.none (node_modules/pg-mem/src/schema.ts:52:14)
      at runSqlFile (test/setup/mongo_setup.test.ts:9:21)
      at Context.<anonymous> (test/setup/mongo_setup.test.ts:27:3)
      at processImmediate (node:internal/timers:463:21) 

PS: maybe also look into adding the pg function gen_random_uuid()

UPDATE:

After getting gen_random_uuid to work and removing the at time some 'utc', I came across an issue with references:

create table workspace
(
    id            uuid primary key                     default gen_random_uuid(),
    created_at    timestamp without time zone not null default now(),
    updated_at    timestamp without time zone not null default now(),

    name          text                        not null,

    parent_id     uuid references workspace (id),
);

Here's the error msg:

💀 Syntax error at line 46 col 24:

      parent_id     uuid references
                         ^
Unexpected kw_references token: "references". Instead, I was expecting to see one of the following:

    - A "dot" token
    - A "lparen" token
    - A "kw_array" token
    - A "lbracket" token
    - A "kw_collate" token
    - A "kw_constraint" token
    - A "kw_unique" token
    - A "kw_null" token
    - A "kw_default" token
    - A "kw_check" token
    - A "comma" token
    - A "rparen" token
    - A "kw_primary" token
    - A "kw_not" token
    - A "word" token

[oguimbal]

Hi !

Thanks for the report.

I dont have much time these days, but i'll try to look into this asap !

[steve-taylor]

Inline foreign key definitions don't seem to work, but you can add them with alter table, which is an acceptable workaround.

So, instead of

parent_id     uuid references workspace (id)

use

parent_id     uuid

and after creating the table, add

alter table workspace
add constraint fk_workspace_1
foreign key (parent_id)
references workspace (id);