Re: Odd behavior with domains

Joshua D. Drake wrote:

> Yes but what makes it weird is this:
> 
> postgres=# create domain text char(3);
> CREATE DOMAIN
> 
> -- cool, no problem
> 
> postgres=# create domain text char(2);
> ERROR:  type "text" already exists
> 
> -- as expected
> 
> postgres=# \dD
>              List of domains
>  Schema | Name | Type | Modifier | Check
> --------+------+------+----------+-------
> (0 rows)
> 
> -- wait what? I just created this.

The unadorned name "text" doesn't refer to the domain at this point,
since it's masked by the system type pg_catalog.text.

If you do "\dD public.*" you will see your "text" domain listed as well.

> postgres=# create domain textd char(2);
> CREATE DOMAIN
> postgres=# \dD
>                  List of domains
>  Schema | Name  |     Type     | Modifier | Check
> --------+-------+--------------+----------+-------
>  public | textd | character(2) |          |
> (1 row)
> 
> -- why would this show up without changing the search path if the
> -- previous one didn't?

Because there is no system object named textd.

> postgres=# drop domain text;
> ERROR:  "text" is not a domain

Right -- "text" is not a domain, it is pg_catalog.text.

> postgres=# set search_path to 'public';
> SET
> postgres=# drop domain text;
> ERROR:  "text" is not a domain
> postgres=#

Here you're still referring to pg_catalog.text, since as I said above
pg_catalog is put in front of the search path if you don't specify it
anywhere.  You need to add pg_catalog to search_path *after* public.
So you can do either
set search_path to 'public', 'pg_catalog'
drop domain text;

or
drop domain public.text;


> Note: If this is literally just the way it is, cool. It was just as I was
> exploring this all seemed odd.

Yes, this is the way it is, and yes it is odd -- but as I said it's not
specific to domains:

alvherre=# create table pg_class (a int, b text);
CREATE TABLE
alvherre=# \d
No se encontraron relaciones.


-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services