Postgresql and schemas

rubyonrails-talk
1

There are some bugs somewhere in the stack in which the schemas are not automatically chosen. My database.yml includes the following: schema_search_path: iaa_development

This creates the database in the correct schema, but subsequent queries don't prepend the actual schema name in the query (i.e.)

SELECT * FROM users WHERE (username = E'admin') LIMIT 1

or

SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = 'users'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum

2

There are some bugs somewhere in the stack in which the schemas are not automatically chosen. My database.yml includes the following: schema_search_path: iaa_development

This creates the database in the correct schema,

You mean your migrations install database objects (tables, functions and such) in the correct schema?

but subsequent queries don't prepend the actual schema name in the query (i.e.)

I haven't had any issues with this. If you execute "show search_path" from within your app, what do you get?

this should be qualified somehow: SELECT * FROM scheam_name.users WHERE (username = E'admin') LIMIT 1

Qualification is only necessary if there's another object with the same name that occurs prior in the search path.

Michael Glaesemann grzm seespotcode net

3

You pretty much have to use search path.