Skip site navigation (1) Skip section navigation (2)

BUG #2455: psql failing to restore a table because of a constaint violation.

From: "Jeff Ross" <jross(at)wykids(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2455: psql failing to restore a table because of a constaint violation.
Date: 2006-05-25 21:09:45
Message-ID: (view raw or flat)
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      2455
Logged by:          Jeff Ross
Email address:      jross(at)wykids(dot)org
PostgreSQL version: 8.1.4
Operating system:   OpenBSD 3.9 -current
Description:        psql failing to restore a table because of a constaint

After the upgrade to 8.1.4, this script I use to sync a development database
with our live database began failing.

Here's the script:

#backup script for postgresql databases
DATE=`date +%Y%m%d`
#dump the live wykids database
/usr/local/bin/pg_dumpall -p 5432 -c > \
#drop the development wykids database
/usr/local/bin/dropdb -p 5435 wykids
#recreate the development wykids database from the dump file we just made
/usr/local/bin/psql -p 5435 template1 -f \

Here's the failure:

psql:/home/_postgresql/wykids20060524.sql:84507: ERROR:  new row for
relation "Clearinghouse" violates check
constraint "refnumber_ck"

CONTEXT:  COPY Clearinghouse, line 1: "Video Three R's for Special Education
School Age Uniqueness and
Cultural Awareness 0.5 total 49.9500..."

Here's the record it barfs on:

wykids=# select * from "Clearinghouse" where "Training Material"
ilike('%three r%');
-[ RECORD 1 ]-----+----------------------------------
Type              | Video
Training Material | Three R's for Special Education
Category          | School Age
Section Found In  | Uniqueness and Cultural Awareness
Clock Hours       | 0.5
Notes             | total
Price             | 49.95
# books           | 1
RefNumber         | V207.030

Here's the table structure:

wykids=# \d "Clearinghouse"
             Table "public.Clearinghouse"
      Column       |         Type          | Modifiers
 Type              | character varying(50) |
 Training Material | character varying(75) |
 Category          | character varying(50) |
 Section Found In  | character varying(50) |
 Clock Hours       | real                  |
 Notes             | character varying(50) |
 Price             | double precision      |
 # books           | character varying(10) |
 RefNumber         | character varying(30) | not null
    "clearinghouse_old_pk" PRIMARY KEY, btree ("RefNumber")
Check constraints:
    "refnumber_ck" CHECK ("RefNumber"::text ~ 
similar_escape('[A-Z]|[0-9]|.'::text, NULL::text))
    refnumber_uppercase_ins AS
    ON INSERT TO "Clearinghouse" DO  UPDATE "Clearinghouse" SET 
"RefNumber" = upper(new."RefNumber"::text)
  WHERE "Clearinghouse"."RefNumber"::text = new."RefNumber"::text

The value in the record cited doesn't violate the constraint, and removing
that record from the .sql file
caused the same failure on the very next record.

Using pg_dump -Fc instead also failed.

As a workaround, we dropped the constraint (not critical) to make sure we
still had backup capability.

Jeff Ross


pgsql-bugs by date

Next:From: VivekanandaDate: 2006-05-26 12:42:33
Subject: BUG #2456: How to write user defined functions in Postgress sql
Previous:From: Tom LaneDate: 2006-05-24 21:29:34
Subject: Re: "blah" is not a domain error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group