Обсуждение: Wrong column default values

Hi,

I have installed Postgres 8.3.8 on Fedora Linux and I access it per
JDBC (PostgreSQL 8.1 JDBC3 with SSL (build 407)). If I create a table,
the default values will be set by the database to

NULL::timestamp without time zone

for a timezone column and to

NULL::character varying

for a varchar column. How I can prevent such a behaviour? Both column
defaults should be NULL. Any ideas?

Thanks
Andre

Dave Cramer <pg@fastcrypt.com> writes:
> On Fri, Sep 18, 2009 at 4:24 AM, Andre Rothe <phosco@gmx.de> wrote:
>> I have installed Postgres 8.3.8 on Fedora Linux and I access it per
>> JDBC (PostgreSQL 8.1 JDBC3 with SSL (build 407)). If I create a table,
>> the default values will be set by the database to
>> NULL::timestamp without time zone
>> for a timezone column and to
>> NULL::character varying
>> for a varchar column. How I can prevent such a behaviour? Both column

> How do you create this table ?

Indeed.  In 8.3 it seems that even if you try to set the default that
way, the system will throw away the cast:

regression=# create table t1 (f1 varchar default null::varchar);
CREATE TABLE
regression=# \d t1
           Table "public.t1"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 f1     | character varying |

regression=# create table t2 (f1 varchar);
CREATE TABLE
regression=# alter table t2 alter column f1 set default null::varchar;
ALTER TABLE
regression=# \d t2
           Table "public.t2"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 f1     | character varying |

I'm thinking that this has less to do with what the server thinks
and more to do with whatever tool the OP is using to examine the
table ...

            regards, tom lane

Hi,

I have exlicitly set the default value for every column, but if I ask
the
driver for the current value, it doesn't give me a simple NULL, but
the
described expression. For the moment I have translated the result
with a

    private String dropAnnotations(String defValue) {
    String res = defValue;
    if ((res != null) && (res.contains("::"))) {
        res = res.substring(0, res.lastIndexOf("::"));
    }
    return res;
    }

But this is an ugly way to get a clean default value from the driver.

Andre

On Sep 18, 10:07 pm, p...@fastcrypt.com (Dave Cramer) wrote:
> How do you create this table ? To answer your question, just add default
> null to the definition of the column.

That's my own tool, I simply ask the DatabaseMetaData.getColumns()
for the structure of the table and I read the value from COLUMN_DEF
column of the result set.
So it seems, that this is a problem of the JDBC driver. I would never
await an annotation as part of the default value...

Andre

On Sep 18, 10:15 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> I'm thinking that this has less to do with what the server thinks
> and more to do with whatever tool the OP is using to examine the
> table ...

On Fri, 18 Sep 2009, Tom Lane wrote:

> Indeed.  In 8.3 it seems that even if you try to set the default that
> way, the system will throw away the cast:

That's only true without a typmod.  Consider:

CREATE TEMP TABLE tt (a varchar default null, b varchar(10) default null,
c timestamptz default null, d timestamptz(4) default null);

\d tt


  Column |            Type             |               Modifiers
--------+-----------------------------+----------------------------------------
  a      | character varying           |
  b      | character varying(10)       | default NULL::character varying
  c      | timestamp with time zone    |
  d      | timestamp(4) with time zone | default NULL::timestamp with time
zone


Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> On Fri, 18 Sep 2009, Tom Lane wrote:
>> Indeed.  In 8.3 it seems that even if you try to set the default that
>> way, the system will throw away the cast:

> That's only true without a typmod.

Hmm, maybe that could use improvement.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Kris Jurka <books@ejurka.com> writes:
>> On Fri, 18 Sep 2009, Tom Lane wrote:
>>> Indeed.  In 8.3 it seems that even if you try to set the default that
>>> way, the system will throw away the cast:

>> That's only true without a typmod.

> Hmm, maybe that could use improvement.

I looked at this a bit and found that AddRelationNewConstraints
will throw away a plain null Const default, but what it's actually
seeing in these cases is an invocation of the type's length coercion
function on a null Const.  We could possibly add more code to throw
that away too (probably conditionally on the length coercion function
being strict, else it might have processing to do for a null).  It
doesn't really seem worth it though.  I think the OP's problem is
most elegantly solved by not writing "default null" in the first place.
It's certainly the only solution that he's likely to see payback from
in less than a year's time ...

            regards, tom lane

On Sat, 19 Sep 2009, Andre Rothe wrote:

> I have exlicitly set the default value for every column, but if I ask
> the driver for the current value, it doesn't give me a simple NULL, but
> the described expression. For the moment I have translated the result
> with a
>
>    private String dropAnnotations(String defValue) {
>     String res = defValue;
>     if ((res != null) && (res.contains("::"))) {
>         res = res.substring(0, res.lastIndexOf("::"));
>     }
>     return res;
>    }
>
> But this is an ugly way to get a clean default value from the driver.
>

This isn't a really safe thing to do if you consider all the possible
default values, so I'm not sure there's much that the JDBC driver can do
to clean this up.  I think it just has to report what the server tells it.

Consider:

CREATE TEMP TABLE deftest (
     a text default 'a::b',
     b timestamptz default now() + 3 * '5 minutes'::interval
);

jurka=# \d deftest
                                       Table "pg_temp_1.deftest"
  Column |           Type           |                            Modifiers
--------+--------------------------+------------------------------------------------------------------
  a      | text                     | default 'a::b'::text
  b      | timestamp with time zone | default (now() + ((3)::double
precision * '00:05:00'::interval))


Kris Jurka