Re: Using incorrect default-value type

[David Johnston <polobo@xxxxxxxxx>]

On Oct 14, 2012, at 15:49, Arturo Pie Joa <arturotd08@xxxxxxxx> wrote:

Hello,

I have found a strange behavior in postgreSQL when adding columns with defaults of a domain type in a schema different from public. This is the example to reproduce it:

CREATE SCHEMA schema_1;

CREATE DOMAIN schema_1.text AS text;

SET search_path TO schema_1, pg_catalog;

CREATE TABLE test

(

  col1 text DEFAULT 'some value'

);

SELECT a.attname,

       pg_get_expr(d.adbin, d.adrelid) AS default

FROM pg_attribute a LEFT JOIN pg_attrdef d

           ON a.attrelid = d.adrelid AND a.attnum = d.adnum

WHERE a.attrelid = 'schema_1."test"'::regclass

                AND a.attnum > 0 

ORDER BY a.attnum;

Last query will return: 

col1     |     "'some value'::pg_catalog.text"

I don't understand why it is using "pg_catalog.text", when it should be using "schema_1.text", or in this case the query should return just "text" since the search path is using "schema_1". 

Furthermore, if I open pgAdmin and select "col1" in "test" table, SQL pane will show:

ALTER TABLE schema_1.test ALTER COLUMN col1 SET DEFAULT 'some value'::text;

but I believe, it should show:

ALTER TABLE schema_1.test ALTER COLUMN col1 SET DEFAULT 'some value'::schema_1.text;

Is this a bug or am I missing something?

Thanks,

Arturo

What you are doing (name overloading) seems inadvisable regardless of whether it should work are described.  My guess is that the system searches for an actual type first and only if it fails to find a matching type then looks for a matching domain.

David J.