Skip site navigation (1) Skip section navigation (2)

Fwd: schemas not appearing in pgAdmin

From: "Alejandro Gasca" <galejadror(at)gmail(dot)com>
To: pgadmin-support(at)postgresql(dot)org
Subject: Fwd: schemas not appearing in pgAdmin
Date: 2007-10-25 16:14:41
Message-ID: 20d60a6c0710250914s4b9bc327x8f28adb4189e4e5b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgadmin-support
---------- Forwarded message ----------
From: Alejandro Gasca <galejadror(at)gmail(dot)com>
Date: 25-oct-2007 11:13
Subject: Re: [pgadmin-support] schemas not appearing in pgAdmin
To: Derek Fonda <Derek(dot)Fonda(at)level3(dot)com>



2007/10/24, Derek Fonda <Derek(dot)Fonda(at)level3(dot)com>:
>
> I'm running postgres 8.0.3 (Solaris) and PgAdmin III 1.8.0
>
> I created a schema in my prod DB that is named pgmacdon, however it
> appears that pgAdmin III is not displaying this schema as existing in the
> prod DB.
>
> It would appear that you are using the wrong query to exclude the pg_*
> internal schemas
>
> PgAdmin III appears to be doing a query similar to the following to
> retrieve the list of schemas to display:
>
> SELECT
>   nspname,
>   CASE WHEN nspname LIKE 'pg\_tmp\_%%' THEN 1
>        WHEN (nspname LIKE 'pg\_%') THEN 0
>        ELSE 3 END AS nsptyp
> FROM
>   pg_namespace
>
>
>        nspname       | nsptyp
> --------------------+--------
>   pg_toast           |      0
>   pheller            |      3
>   pg_temp_1          |      0
>   pg_catalog         |      0
>   public             |      3
>   mdpeters           |      3
>   information_schema |      3
>   dfonda             |      3
>   msessums           |      3
>   adbrown            |      3
>   philmacdonald      |      3
>   pgmacdon           |      0  <===== FALSELY being set to 0 (don't
> display)
>
>
> It would appear that in order to match pg_*, postgres requires:
>
> LIKE 'pg\\_%'
> _rather than_
> LIKE 'pg\_%'
>
> Not sure if this was a bug in 8.0.3 that was later fixed or the syntax for
>
> a literal underbar MUST BE '\\_'
>
>
> Thank you for your time and attention
>    Derek Fonda
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


Same problem here, with postgres 8.2.5, pgAdmin 1.8.0  if i do:

create schema pgpg;

the schema doesn't appear.

by the way, why don't put:
    CASE
        WHEN nspname ~ '^pg_temp_' THEN 1
        WHEN (nspname ~ '^pg_') THEN 0
        ELSE 3
    END AS nsptyp,

instead of
    CASE
        WHEN nspname LIKE E'pg_temp_%%' THEN 1
                WHEN (nspname LIKE E'pg\_%') THEN 0
                ELSE 3
        END AS nsptyp,

thanks.

Alejandro

In response to

pgadmin-support by date

Next:From: Alexander KirpaDate: 2007-10-25 22:14:13
Subject: Refresh version string/number bug
Previous:From: Vishal AroraDate: 2007-10-25 07:37:12
Subject: Postgres SQL Client for Suse Linux

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group