Postgres Pro
Downloads
Home   >   mailing lists

Thread: ALTER TABLE

ALTER TABLE

From
Martins Zarins
Date:
Hello!
Can someone explain me, why last ALTER fails?

work=# \d rdu
                                        Table "rdu"
   Column    |            Type             |                   Modifiers
-------------+-----------------------------+------------------------------------------------
 id          | integer                     | not null default
nextval('"rdu_id_seq"'::text)
 numurs      | integer                     |
 versija     | integer                     |
 klients_id  | integer                     |
 pasutitajs  | text                        |
 agents_id   | integer                     |
 pasut_nr    | integer                     |
 status_id   | character varying(1)        | default 'S'
 start_time  | timestamp without time zone | not null default now()
 end_time    | timestamp without time zone |
 finish_time | timestamp without time zone |
 apraksts    | text                        |
Indexes: rdu_agents_id,
         rdu_klients_id
Unique keys: rdu_id_key
Triggers: RI_ConstraintTrigger_1187649,
          RI_ConstraintTrigger_1187655,
          RI_ConstraintTrigger_1187673,
          RI_ConstraintTrigger_1187675

work=# ALTER TABLE rdu ADD CONSTRAINT rdu_end_time CHECK ((start_time <=
end_time) OR end_time IS NULL);
ALTER
work=# ALTER TABLE rdu ADD CONSTRAINT rdu_finish_time CHECK ((start_time <=
finish_time) OR finish_time IS NULL);
ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint
rdu_finish_time

psql (PostgreSQL) 7.2.3

Mark

Re: ALTER TABLE

From
"A.Bhuvaneswaran"
Date:
> work=# ALTER TABLE rdu ADD CONSTRAINT rdu_finish_time CHECK ((start_time <=
> finish_time) OR finish_time IS NULL);
> ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint
> rdu_finish_time

I can say that the field, finish_time have the values which conflict with
the check constraint you define. ie.. Records exist with start_time
> finish_time, so update the records and try.

regards,
bhuvaneswaran

Re: ALTER TABLE

From
"Victor Yegorov"
Date:
* Martins Zarins <mark@vestnesis.lv> [05.06.2003 10:18]:
> Hello!
> Can someone explain me, why last ALTER fails?
>
> work=# \d rdu
>                                         Table "rdu"
>    Column    |            Type             |                   Modifiers
> -------------+-----------------------------+------------------------------------------------
>  id          | integer                     | not null default
> nextval('"rdu_id_seq"'::text)
>  numurs      | integer                     |
>  versija     | integer                     |
>  klients_id  | integer                     |
>  pasutitajs  | text                        |
>  agents_id   | integer                     |
>  pasut_nr    | integer                     |
>  status_id   | character varying(1)        | default 'S'
>  start_time  | timestamp without time zone | not null default now()
>  end_time    | timestamp without time zone |
>  finish_time | timestamp without time zone |
>  apraksts    | text                        |
> Indexes: rdu_agents_id,
>          rdu_klients_id
> Unique keys: rdu_id_key
> Triggers: RI_ConstraintTrigger_1187649,
>           RI_ConstraintTrigger_1187655,
>           RI_ConstraintTrigger_1187673,
>           RI_ConstraintTrigger_1187675
>
> work=# ALTER TABLE rdu ADD CONSTRAINT rdu_end_time CHECK ((start_time <=
> end_time) OR end_time IS NULL);
> ALTER
> work=# ALTER TABLE rdu ADD CONSTRAINT rdu_finish_time CHECK ((start_time <=
> finish_time) OR finish_time IS NULL);
> ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint
> rdu_finish_time

Perhaps you have some rows, for which check fails,
i.e. (start_time > finish_time AND finish_time IS NOT NULL) is true.

--

Victor Yegorov