Re: Problems upgrading from 7.1.3

On Thursday 06 February 2003 06:31 am, Rajesh Kumar Mallah wrote:
> In my case the message "Invalid command \N" was coming
> during COPY command execution when some other SQL command had
> failed prior to COPY execution. ie the COPY command and data
> in that part were perfectly fine.

Ah, found my problem. The following table was created as:

CREATE TABLE bases (
serialno integer NOT NULL,
ip character varying(15) DEFAULT 'FF.FF.FF.FF.FF',
mac character varying(17) DEFAULT 'FF:FF:FF:FF:FF:FF:FF',
modem_init character varying,
enc_key character varying,
status integer,
"location" integer DEFAULT '-1'
);

You'll notice that the mac column is a VARCHAR(17), but the default value is
20 characters long! I got the following errors:

ERROR: value too long for type character varying(17)
ERROR: Relation "bases" does not exist

So that caused the bases table to not be created. When psql got the "COPY"
command for bases, the table wasn't there, so it didn't really execute the
COPY. Then psql thought all the data for the COPY was really commands, hence
the "Invalid command \N" message. Apparently this really got it confused.

If I hand editted the dump to change the 17 to a 20, everything was happy.

Apparently PostgreSQL 7.1.3 didn't really enforce the length on VARCHAR(#)'s,
which is why this never caused a problem with it.

Now, why did this all work fine if I added "-D" to pg_dump/pg_dumpall?
Shouldn't that have failed for the same reason?

Thanks,
---
Geoffrey Wossum
Software Engineer
Long Range Systems - http://www.pager.net