pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hilary Forbes <hforbes@dmr.co.uk>
To: Stef <svb@ucs.co.za>
To: pgsql-admin@postgresql.org
Subject: Re: Bad dumps...
Date: Fri, 09 Jul 2004 09:40:01 +0100
Message-ID: <5.1.0.14.0.20040709093611.05621ea0@mailserver.dmr.co.uk> (raw)
In-Reply-To: <20040709095738.5738334f@svb.ucs.co.za>

This is very similar to my problem with the ascii code 13s when I dumped and couldn't load the dumped data.  What I did was to write a short script replaced the offending characters with an empty string which is different to NULL.  The other alternative is to have

myfield text DEFAULT '' NOT NULL

in your schema. (The '' bit is two single quotes ie the empty string)  This means anyone failing to insert a value for this column will cause the db to insert an empty string rather than leaving it as NULL.)

Hilary

At 09:57 09/07/2004 +0200, Stef wrote:

>Hi all,
>
>I'm stuck with a problem,about which I couldn't find
>much info. I'm sure somebody must have encountered this before.
>
>I've got a "NOT NULL" column of type text.
>It happens that freehand data gets inserted into this table, and
>it typically contains over 1000 rows with the value '\N' , and
>about another 3400 rows that has an occurrence of '\N'
>
>The database dumps fine, but when I import it again, I get
>"cannot insert null value into not null column"-type errors,
>and if I don't pay close attention,  I end up with an empty table
>on the database. There is no way for me to tell how many columns
>could be affected in future, but I need to stabilize the backups.
>
>I'm using postgres version 7.3.4
>
>Any idea how to get around this problem?
>
>Kind Regards
>Stefan

Hilary Forbes
The DMR Information and Technology Group  (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330 
DMR is a UK registered trade mark of DMR Limited
**********************************************************




view thread (10+ messages)  latest in thread

Message-ID: <5.1.0.14.0.20040709093611.05621ea0@mailserver.dmr.co.uk>
Permalink:  ../5.1.0.14.0.20040709093611.05621ea0@mailserver.dmr.co.uk/
Also on:    postgresql.org/message-id/5.1.0.14.0.20040709093611.05621ea0@mailserver.dmr.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: hforbes@dmr.co.uk, svb@ucs.co.za
  Subject: Re: Bad dumps...
  In-Reply-To: <5.1.0.14.0.20040709093611.05621ea0@mailserver.dmr.co.uk>
  References: <20040709095738.5738334f@svb.ucs.co.za> <5.1.0.14.0.20040709093611.05621ea0@mailserver.dmr.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox