DT
Drizzle Team•14mo ago
Shashank

Is this a bug or I am just an idiot

So basically I have 3 tables named as chapter, volume and novel. I was trying to get all the novels (which is 8 right now), and latest 5 chapters of these novels. To achieve this I was trying to use subquery of volume and chapter combined and then giving them a row number. Because volume and chapter both have a column name as number somehow it is causing issue 😕 . For reference: I'm using supabase with @vercel/postgres My error: 
error: column reference "number" is ambiguous
    at F:\projects\quaslation\node_modules\@neondatabase\serverless\index.js:1345:74
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at VercelPgPreparedQuery.execute (F:\projects\quaslation\node_modules\src\vercel-postgres\session.ts:60:20)
    at main (f:\projects\quaslation\temp.ts:35:18) {
  length: 111,
  severity: 'ERROR',
  code: '42702',
  detail: undefined,
  hint: undefined,
  position: '62',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '816',
  routine: 'scanRTEForColumn'
}
error: column reference "number" is ambiguous
    at F:\projects\quaslation\node_modules\@neondatabase\serverless\index.js:1345:74
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at VercelPgPreparedQuery.execute (F:\projects\quaslation\node_modules\src\vercel-postgres\session.ts:60:20)
    at main (f:\projects\quaslation\temp.ts:35:18) {
  length: 111,
  severity: 'ERROR',
  code: '42702',
  detail: undefined,
  hint: undefined,
  position: '62',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '816',
  routine: 'scanRTEForColumn'
}
No description
Solution:
@Shashank Hi, I'm pretty sure the issue is to do with the chapter number and volume number, and the fix should be as simple as this: 
chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')
chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')
...
Jump to solution
5 Replies
Solution
Mario564
Mario564•14mo ago
@Shashank Hi, I'm pretty sure the issue is to do with the chapter number and volume number, and the fix should be as simple as this: 
chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')
chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')
Mario564
Mario564•14mo ago
This should resolve the ambiguity the error is describing
Shashank
ShashankOP•14mo ago
Yupp it does but the problem is it also takes away the power of typescript for chapterNumber and volumeNumber I guess this is the workaround for now
Mario564
Mario564•14mo ago
Oh yeah, that can be an issue. Adding to the already kinda verbose solution, you can also add .mapWith(Number) to TS knows it's a number (add it before .as()) We'll look into aliasing columns without this clunky behavior sometime soon
Shashank
ShashankOP•14mo ago
yeah that will be gr8 for now I'm planning to use relational queries. It fits better with my use case.

Did you find this page helpful?