Re: Connecting to specific schema in PostgreSQL
Re: Connecting to specific schema in PostgreSQL
- Subject: Re: Connecting to specific schema in PostgreSQL
- From: David Avendasora <email@hidden>
- Date: Tue, 08 Apr 2014 09:16:52 -0400
Hi Gennady,
With Postgresql each user is assigned a default Schema. If the user that EOF is connecting to the DB as has a different default schema than the one that your tables are in, you’ll need to add the schema name to the table name in EntityModeler.
If you table name is currently my_entity then simply change it to schema.my_entity
That *should* fix it.
However if it still doesn’t work, keep in mind that Postgresql (and Oracle, not sure about MySQL) is only case-insensitive for SQL identifiers if you **do not** quote them. When you create a schema or a table or add a column or constraint and you use capital letters in the name **and quote the name**, then you will have to *always* match the case and use quotes in your sql.
For example, if you create a table like
create table “SCHEMA”.”MY_ENTITY” …
then none of these will work:
select * from SCHEMA.MY_ENTITY ;
select * from schema.my_entity ;
They will *not* match and you will still get the “relation does not exist” error since Postgresql (and Oracle, etc.) sees the schema name as “SCHEMA" and the table name as “MY_ENTITY” exactly. Postgresql automatically converts all non-quoted identifiers in all sql queries to lowercase, so if you don’t quote in your query it will actually always be looking for “schema” and “my_entity"
They call it being “case insensitive” but the reality is that it is sensitive, it just assumes that if you don’t quote the identifiers (which is really a SQL best practice, IMHO) it should convert your query to all lower case behind your back.
The PostgresqlPlugIn does look for a to a Property to tell it to quote identifiers, but that is not the default, and it is not consistent in terms of always quoting everything.
The best solution is to always use all lowercase for SQL identifiers, then you (and any future developers) can quote or not-quote and they will still work.
Dave
On Apr 8, 2014, at 4:20 AM, Kushnir Gennady <email@hidden> wrote:
> Hello list!
> I am trying to move my application from mysql to postgresql and I am a bit stuck.
> When reading postgres documentation I've found that I can use separate Schemas within single postgresql database.
> But when I am trying to connect my models to the database I get “relation does not exist” errors as a connection does not know in which schema to search for tables.
>
> So how should I mention a schema in a model so that it generates full table names with prefix? Or how to inject a search_path initialization code right after a database connection?
> (I use separate models for different schemas)
>
> Regards,
> Gennady
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
—————————————————————————————
WebObjects - so easy that even Dave Avendasora can do it!™
—————————————————————————————
David Avendasora
Senior Software Abuser
Nekesto, Inc.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden