Postgres issue with getting tables for a schema which has space in its name

[pavansandeep2910]

code:

const inspector = schemaInspector(knex);
inspector.withSchema('test schema')
await inspector.tables();

error:

SELECT
        rel.relname AS name
      FROM
        pg_class rel
      WHERE
        rel.relnamespace IN ('test schema'::regnamespace)
        AND rel.relkind = 'r'
      ORDER BY rel.relname
     - invalid name syntax
    at Parser.parseErrorMessage (/Users/pavan/Documents/node-project/node_modules/.pnpm/pg-protocol@1.5.0/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/Users/pavan/Documents/node-project/node_modules/.pnpm/pg-protocol@1.5.0/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/pavan/Documents/node-project/node_modules/.pnpm/pg-protocol@1.5.0/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/pavan/Documents/node-project/node_modules/.pnpm/pg-protocol@1.5.0/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 96,
  severity: 'ERROR',
  code: '42602',
  detail: undefined,
  hint: undefined,
  position: '116',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'regproc.c',
  line: '1890',
  routine: 'stringToQualifiedNameList'
}

[rijkvanzanten]

What version of Postgres are you using?

[pavansandeep2910]

What version of Postgres are you using?

14.3

[rijkvanzanten]

Looks like

SELECT
        rel.relname AS name
      FROM
        pg_class rel
      WHERE
        rel.relnamespace IN ('test schema'::regnamespace)
        AND rel.relkind = 'r'
      ORDER BY rel.relname

is throwing an error, whereas

SELECT
        rel.relname AS name
      FROM
        pg_class rel
      WHERE
        rel.relnamespace IN ('"test schema"'::regnamespace)
        AND rel.relkind = 'r'
      ORDER BY rel.relname

works as expected.

This should be able to be resolved by adding an additional set of quotes in these pieces:

knex-schema-inspector/lib/dialects/postgres.ts

Lines 89 to 91 in 20d6ad0

	const schemaIn = this.explodedSchema.map(
	(schemaName) => `${this.knex.raw('?', [schemaName])}::regnamespace`
	);

Wanna open a PR for that @pavansandeep2910?

[pavansandeep2910]

I don't see any difference between the two code snippets you provided. Correct me If I missed anything

[rijkvanzanten]

My bad, miscopied 👍🏻 Updated now

[pavansandeep2910]

Looks like

SELECT
        rel.relname AS name
      FROM
        pg_class rel
      WHERE
        rel.relnamespace IN ('test schema'::regnamespace)
        AND rel.relkind = 'r'
      ORDER BY rel.relname

is throwing an error, whereas

SELECT
        rel.relname AS name
      FROM
        pg_class rel
      WHERE
        rel.relnamespace IN ('"test schema"'::regnamespace)
        AND rel.relkind = 'r'
      ORDER BY rel.relname

works as expected.

This should be able to be resolved by adding an additional set of quotes in these pieces:

knex-schema-inspector/lib/dialects/postgres.ts

Lines 89 to 91 in 20d6ad0

	const schemaIn = this.explodedSchema.map(
	(schemaName) => `${this.knex.raw('?', [schemaName])}::regnamespace`
	);

Wanna open a PR for that @pavansandeep2910?

I am happy to raise the PR

[Fasunle]

Hello @pavansandeep2910 @rijkvanzanten

May I work on this bug?

[pavansandeep2910]

@Fasunle Yes, please. I am sorry for not continuing to work on this issue. I was and am currently busy with some other work. Thanks