Re: Using incorrect default-value type

My last question is:

Is it ok to do name overloading in postgreSQL? or is that not recommended at all? Could I find some issues later by doing name overloading?

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".

Thanks,

Arturo

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Arturo Pie Joa <arturotd08(at)yahoo(dot)ca>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Sent: Sunday, October 14, 2012 4:35:07 PM
Subject: Re: [GENERAL] Using incorrect default-value type

Arturo Pie Joa <arturotd08(at)yahoo(dot)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