Error when I query my connected Postgres schema

How to work around Postgres' identifier "folding"

Suggest Edits

Use case

"I definitely have a Project schema connected, but I'm seeing an error. Can you help me fix this?"

TLDR

  • You may be encountering Postgres' identifier "folding"
  • Postgres requires you to use double quotes around mixed case identifiers
  • Velvet will attempt to self heal, even when you don't follow Postgres best practices

Postgres context

If you see an error in Velvet for a table you think is available in your schema, you may be encountering Postgres' identifier "folding". Postgres automatically lowercases all identifiers unless they are double quoted. For example, you may have a table or column name with mixed case characters, such as Project or createdAt. See a docs article from Postgres for additional context.

We are bound by Postgres' features and nuances when you query a direct database connection in the editor (e.g. "Postgres" instead of "Unified Sources".)

When your identifiers are not double quoted, even a simple query like SELECT * FROM public.Project ORDER BY createdAt will fail, because Postgres converts this to SELECT * FROM public.project ORDER BY createdat. Postgres won't be able to find the project table, resulting in an error. A good hint this is happening is that the error message has the wrong character casing in the text (e.g. can't find relation 'project' vs can't find relation 'Project')

How to solve this issue

You should use double quotes around mixed case identifiers. You likely already use this in your app (or your ORM handles it for you). To make the ambiguous query above work, you'd need to write SELECT * from "public"."Project" ORDER BY "createdAt" to preserve the casing.

Velvet adds additional checks when generating the LLM context. We inject specific details when we detect mixed casing. This should eliminate this issue so you can write queries as normal. We are always tweaking and refining our context generation, so let us know if cases come up where the generated SQL doesn't seem right.

Get help from our team

If you're still having issues, email us at [email protected] or schedule a call. We'll help you get it working quickly.

Further reading on this topic

Updated 21 days ago