Thread: Using incorrect default-value type

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

Arturo Pie Joa <arturotd08@yahoo.ca> writes:
> I have found a strange behavior in postgreSQL when adding columns with
> defaults of a domain type in a schema different from public.

As the other respondent said, you're confusing the issue by using
conflicting names.  If you hadn't done that, you'd see this:

regression=# create domain mytext as text;
CREATE DOMAIN
regression=# create table test (co1 mytext default 'some value');
CREATE TABLE
regression=# \d+ test
                                 Table "public.test"
 Column |  Type  |         Modifiers          | Storage  | Stats target | Description
--------+--------+----------------------------+----------+--------------+-------------
 co1    | mytext | default 'some value'::text | extended |              |
Has OIDs: no

which makes it at least slightly clearer what's going on: pg_get_expr is
just reporting the base type of the constant.  The constant does get
cast to the domain type, but that's an implicit cast that's not shown in
the decompiled output.  You can convince yourself of that by looking
directly at the pg_attrdef entry:

regression=# select * from pg_attrdef where adrelid = 'test'::regclass;
 adrelid | adnum |
                                        adbin
                                                                   |       adsrc         

---------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------
   67889 |     1 | {COERCETODOMAIN :arg {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval
false:constisnull false :location 38 :constvalue 14 [ 0 0 0 14 115 111 109 101 32 118 97 108 117 101 ]} :resulttype
67888:resulttypmod -1 :resultcollid 100 :coercionformat 2 :location -1} | 'some value'::text 
(1 row)

The constant has type 25 (text).  The CoerceToDomain node does have
mytype (with OID 67888 in this test) as result type, but it isn't shown
because it has coercionformat 2 (COERCE_IMPLICIT_CAST).

BTW, the reason it's done like this is that CoerceToDomain has to be
done at runtime: if you were to do an ALTER DOMAIN ADD CONSTRAINT,
it's not clear whether or not the constant would still be a valid value
of the domain.  So we can't just generate a Const node that claims that
'some text' is a value of type mytype.

            regards, tom lane

Arturo Pie Joa <arturotd08@yahoo.ca> writes:
> Is it ok to do name overloading in postgreSQL? or is that�not�recommended at all? Could I find some issues later by
doingname overloading? 

People use it all the time ... and get burnt some of the time.

> We are doing this because we are using different collations on each schema, and our ORM doesn't support custom
domains,so we are just overloading "text". 

Maybe you need a better ORM.  But it's not clear to me why you'd need
domains for this anyway --- can't you just attach a collation property
to each table column?

            regards, tom lane