Relation does not exists error, badly quoted table names in PostgreSQL

Question

Migrated issue, originally created by Witold Konior

Hi,

I'm using Alembic for PostgreSQL, noted that when I put all names in models to lowercase Alembic issue "CREATE TABLE" statements without quoting table names and columns. That's good but should be configurable if we want to use quotes or not.

NOTE If any object in PostgreSQL is created with quotes it should be always referenced with quotes and is case sensitive. If object is created without quotes tables UseR is same as user.

Problem occurs when I want to add foreign key constraint,

op.create_foreign_key('some_fkey', 'schema_name.sourcetable', 'schema_name.targettable', ['id'], ['id'] )

Produced alter table have quoted names of "schema.table" which are not recognizable by PostgreSQL, after removing quotes same statement runs smoothly.

Didn't test other commands that could be possibly affected.

Proposals: 1. Create option for enforcing / drooping quotes on object names, 2. Behavior unification when names are lowercase don't put quotes.

该提问来源于开源项目：sqlalchemy/alembic

Answer 1

Michael Bayer () wrote:

first off, that's not how you set the schema for create_foreign_key; per the docs

op.create_foreign_key('some_fkey', 'sourcetable', 'targettable', ['id'], ['id'], source_schema='someschema', referent_schema='someschema' )

secondly, this isn't documented in alembic but for full control of quoting use quoted_name:

op.create_foreign_key('some_fkey', quoted_name('SourceTable', quote=True), 'targettable', ['id'], ['id'], source_schema='someschema', referent_schema='someschema' )

However, note quoted_name is usually not necessary because if the string name is not all lower case, it will be quoted as case sensitive.