[BUG] PostgresIntrospector.#introspectPartitions throws an error

[bakasmarius]

After updating kysely-codegen to 0.16.0, I get this error: ✗ syntax error at or near "schema"

After digging into it, I found out that it's coming from a query in PostgresIntrospector.#introspectPartitions:

      select pg_namespace.nspname schema, pg_class.relname name
      from pg_inherits
      join pg_class on pg_inherits.inhrelid = pg_class.oid
      join pg_namespace on pg_namespace.oid = pg_class.relnamespace;

Running it straight on the database (Postgres 13.5) produces a bit more informative error:

SQL Error [42703]: ERROR: column "schema" does not exist
  Position: 36

Adding as to aliases makes the error go away:

      select pg_namespace.nspname as schema, pg_class.relname as name
      from pg_inherits
      join pg_class on pg_inherits.inhrelid = pg_class.oid
      join pg_namespace on pg_namespace.oid = pg_class.relnamespace;

I will submit a PR soon 😉

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

[RobinBlomberg]

What! Is this some old PostgreSQL version? It looks like AS is mandatory in PostgreSQL 7.2. In any case, I will add AS to make sure this works everywhere. Thanks for reporting.

[RobinBlomberg]

Fixed in kysely-codegen@0.16.2!

[bakasmarius]

I think it's because schema and name are reserved words, so you have to either use as or use a non-reserved alias, for example, this works just fine:

      select pg_namespace.nspname schemaa, pg_class.relname namee
      from pg_inherits
      join pg_class on pg_inherits.inhrelid = pg_class.oid
      join pg_namespace on pg_namespace.oid = pg_class.relnamespace;

[RobinBlomberg]

I think it's because schema and name are reserved words, so you have to either use as or use a non-reserved alias, for example, this works just fine:

      select pg_namespace.nspname schemaa, pg_class.relname namee
      from pg_inherits
      join pg_class on pg_inherits.inhrelid = pg_class.oid
      join pg_namespace on pg_namespace.oid = pg_class.relnamespace;

It works for me even without changing anything, so it could possibly be that an earlier PostgreSQL version treats reserved words differently. But that's good to know.

[bakasmarius]

After installing 0.16.2, postgres-introspector.js still contains the old query - maybe build step had to be executed before the release?

[bakasmarius]

I think it's because schema and name are reserved words, so you have to either use as or use a non-reserved alias, for example, this works just fine:

      select pg_namespace.nspname schemaa, pg_class.relname namee
      from pg_inherits
      join pg_class on pg_inherits.inhrelid = pg_class.oid
      join pg_namespace on pg_namespace.oid = pg_class.relnamespace;

It works for me even without changing anything, so it could possibly be that an earlier PostgreSQL version treats reserved words differently. But that's good to know.

I checked on a different DB where version is 15.4 and the old query works fine without the aliases, so yeah, looks like older versions treat reserved words differently.

[RobinBlomberg]

After installing 0.16.2, postgres-introspector.js still contains the old query - maybe build step had to be executed before the release?

I will cry. I had some code that I had forgotten to deduplicate, so I changed the wrong file. I just installed knip and removed a bunch of unused files and variables – fixed in 0.16.3. 😁