CHECK constraint fails when it's not supposed to

From: Richard Yen <richyen3(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: CHECK constraint fails when it's not supposed to
Date: 2009-08-04 18:12:39
Message-ID: 7e9353d7-0d38-4062-a43a-2f448f3f0b8d@u38g2000pro.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All,

I encountered an odd issue regarding check constraints complaining
when they're not really violated.

For this particular machine, I am running 8.3.7, but on a machine
running 8.3.5, it seems to have succeeded. I also upgraded a third
machine from 8.3.5 to 8.3.7, and the query succeeded (so I'm
thinking
it's not related to different postgres versions)

I have a table called "m_class" and the definition is something like
this:

> tii=# \d m_class
> Table "public.m_class"
> Column | Type
> | Modifiers
> -------------------------+--------------------------
> +--------------------------------------------------------------
> id | integer | not null
> default nextval(('m_class_id_seq'::text)::regclass)
> class_type | smallint | not null
> title | character varying(100) | not null
> ...snip...
> date_setup | timestamp with time zone | not null
> default ('now'::text)::date
> date_start | timestamp with time zone | not null
> date_end | timestamp with time zone | not null
> term_length | interval | not null
> default '5 years'::interval
> ...snip...
> max_portfolio_file_size | integer |
> Indexes:
> "m_class_pkey" PRIMARY KEY, btree (id)
> "m_class_account_idx" btree (account)
> "m_class_instructor_idx" btree (instructor)
> Check constraints:
> "end_after_start_check" CHECK (date_end >= date_start)
> "end_within_term_length" CHECK (date_end <= (date_start +
> term_length))
> "min_password_length_check" CHECK
> (length(enrollment_password::text) >= 4)
> "positive_term_length" CHECK (term_length > '00:00:00'::interval)
> "start_after_setup_check" CHECK (date_start >= date_setup)
> ...snip...

When I run my update, it fails:
> tii=# begin; update only "public"."m_class" set date_end='2009-09-03
> 05:38:24.030331-07',term_length='177 days 17:59:09.868431' where
> id='2652020';
> BEGIN
> ERROR: new row for relation "m_class" violates check constraint
> "end_within_term_length"
> tii=# rollback;
> ROLLBACK

The data reads:
> tii=# select date_start, date_end, term_length, '2009-09-03
> 05:38:24.030331-07'::timestamptz - date_start AS new_term_length
> from m_class where id = 2652020;
> date_start | date_end |
> term_length | new_term_length
> -----------------------------+-----------------------------
> +-------------+--------------------------
> 2009-03-09 11:39:14.1619-07 | 2009-04-08 11:39:14.1619-07 | 30
> days | 177 days 17:59:09.868431

Based on new_term_length, the update should succeed. However, it
doesn't. Would anyone have an explanation?

Thanks for your help!
--Richard

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guy Rouillier 2009-08-04 18:28:22 Re: Does derby have an embedded Mode like Derby ?
Previous Message Edward Amsden 2009-08-04 17:54:39 ECPG: insert array?