Problem adding foreign key when table in personal schema

["Sune B. Woeller" <sune(at)syntetisk(dot)dk>]

Hi list,

I can reproduce this error - could it be added as an
official bug, please?

I wish to create a foreign key on a table, referencing a
table in a schema that has the same name as my user id (my
personal schema). In the list of tables when adding the
foreign key ('References'), tables in my personal schema are
listed by table name, without the schema part. When choosing
such a table, the column list is empty.

The tables in my personal schema are in the search path, but
the column list apparently does not take that in account ?

regards, Sune B. Woeller


Miha Radej wrote:
> hi!
>
> the foreign key problem seems to come from using an incorrect schema 
> name. ie. in a database i am using i have a few schemas. i am working on 
> one of them and when selecting a referencing table all table names are 
> displayed as 'schema.tablename' except the tables from the current 
> schema i'm working on, where just the form 'tablename' is used. and 
> pgadmin3 appears to use the public schema if no schema seems to be 
> specified, which then returns no columns.
>
> ie: i am working on a schema called 'myschema' and i select a table 
> 'tablename' as a referenced table. pgadmin generates the following query 
> to retrieve the column names from a newly selected table:
> SELECT attname
>           FROM pg_attribute att, pg_class cl, pg_namespace nsp
>          WHERE attrelid=cl.oid AND relnamespace=nsp.oid
>            AND nspname='public'
>            AND relname='tablename'
>            AND attnum > 0
>
>          ORDER BY attnum
>
> instead of
> SELECT attname
>           FROM pg_attribute att, pg_class cl, pg_namespace nsp
>          WHERE attrelid=cl.oid AND relnamespace=nsp.oid
>            AND nspname='myschema'
>            AND relname='tablename'
>            AND attnum > 0
>
>          ORDER BY attnum
>
> regards,
> Miha
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend