# Postgresql and schemas

**URL:** <https://www.ruby-forum.com/t/postgresql-and-schemas/118199>\
**Category:** Rails\
**Created:** [October 24, 2007, 6:46pm UTC](https://www.ruby-forum.com/t/postgresql-and-schemas/118199 "2007-10-24T18:46:20Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![tresero](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/bb73d2/32.png) [@tresero](https://www.ruby-forum.com/u/tresero)\
**Post date:** [October 24, 2007, 6:46pm UTC](https://www.ruby-forum.com/t/postgresql-and-schemas/118199/1 "2007-10-24T18:46:20Z")

</div>

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

* * *

this should be qualified somehow:  
SELECT \* FROM scheam\_name.users WHERE (username = E’admin’) LIMIT 1

I could be wrong but I know PG very well even if I am new to Rails.

TIA

---

<div class="post-metadata">

**Author:** ![tresero](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/bb73d2/32.png) [@tresero](https://www.ruby-forum.com/u/tresero)\
**Post date:** [October 24, 2007, 9:08pm UTC](https://www.ruby-forum.com/t/postgresql-and-schemas/118199/2 "2007-10-24T21:08:51Z")

</div>

On Oct 24, 2007, at 12:45 , tresero wrote:

> 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 G.  
grzm seespotcode net

---

<div class="post-metadata">

**Author:** ![tresero](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/bb73d2/32.png) [@tresero](https://www.ruby-forum.com/u/tresero)\
**Post date:** [October 24, 2007, 11:00pm UTC](https://www.ruby-forum.com/t/postgresql-and-schemas/118199/3 "2007-10-24T23:00:03Z")

</div>

You pretty much have to use search path.

* * *

## The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database.

Either rails or the driver is not passing the search path under  
certain conditions. The above code was from acts\_as\_authenticated.

I will do some more experimenting, I am new to ruby/rails, so I don’t  
know how much help I can be.