[BUGS] Problem with COPY in 8.0.3

#1

#2

On Wed, 2005-10-12 at 09:09 +0100, Oliver Elphick wrote:

Quote:

Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12

I am importing a table using COPY. The data is tab-delimited. COPY
seems to be putting the data for one field into the preceding field,
which should contain the empty string.

To follow up on this, there seems to be a general problem that COPY did
not use the same column order as a default display for this particular
table. The table had just been created and had not been subjected to
alterations.

I got round the problem by specifying the column list to the COPY
command.

--
Oliver Elphick olly (AT) lfix (DOT) co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#3

Oliver Elphick <olly (AT) bray-healthcare (DOT) com> writes:

Quote:

Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12
I am importing a table using COPY. The data is tab-delimited. COPY
seems to be putting the data for one field into the preceding field,
which should contain the empty string.

Can't reproduce that here, under either 8.0 or CVS tip. Are you sure
you aren't making some silly mistake, like copying from the wrong file?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#4

On Wed, 2005-10-12 at 13:52 -0400, Tom Lane wrote:

Quote:

Oliver Elphick <olly (AT) bray-healthcare (DOT) com> writes:
Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12
I am importing a table using COPY. The data is tab-delimited. COPY
seems to be putting the data for one field into the preceding field,
which should contain the empty string.

Can't reproduce that here, under either 8.0 or CVS tip. Are you sure
you aren't making some silly mistake, like copying from the wrong file?

No. This is a regular (nightly) scripted dump, which drops and rebuilds
the entire database, and the right data is going into the file (I only
showed one line out of 300+). But in this particular case, the column
order was wrong. I should add that the table inherits from another one,
but the swapped columns are a long way into the extra columns specific
to this table.

I've changed the script to fix it, so its not a big issue for me, but I
first noticed it several days ago, so it's not a one off occurrence.

Oliver
--
Oliver Elphick olly (AT) lfix (DOT) co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

#5

On Wed, Oct 12, 2005 at 09:09:05AM +0100, Oliver Elphick wrote:

Quote:

Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12

I am importing a table using COPY. The data is tab-delimited. COPY
seems to be putting the data for one field into the preceding field,
which should contain the empty string.

I couldn't duplicate this in 8.0.4 on either FreeBSD or Solaris.
I created a table using your definition and put the data into a
file, converting ^I to tab and removing the trailing $.

perl -lne 'if (/^67829/) {s/\^I/\t/g; s/\$$//; print}' msg.txt > data.txt

I used COPY to load data.txt; SELECT then returned the following:

\x
SELECT * FROM export_invoice;
....
kilos | 570.000
nett | 0.000
dimensions |
terms | CIF-MONTREAL/ACT 30-DAYS
bank |
goods | Chemist's sundries
....

I see a few COPY fixes in pgsql-committers, although I don't know
if any would affect the behavior you're seeing. Have you tried
8.0.4? Have you done any tests with a freshly-created table? Have
you executed any ALTER TABLE statements on export_invoice? I don't
know if COPY could have problems with that; I'm just wondering what
might be different between your environment and mine, aside from
the PostgreSQL version and operating system.

On another note, regarding the following:

Quote:

invdate | date | not null default ('now'::text)::date
taxpoint | date | not null default ('now'::text)::date

Are you sure you want 'now'::text as a default, considering the
warning against it?

http://www.postgresql.org/docs/8.0/i...TETIME-CURRENT

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#6

On Wed, Oct 12, 2005 at 07:08:20PM +0100, Oliver Elphick wrote:

Quote:

I should add that the table inherits from another one, but the
swapped columns are a long way into the extra columns specific to
this table.

Could you post the table definitions?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#7

On Wed, Oct 12, 2005 at 12:19:41PM -0600, Michael Fuhr wrote:

Quote:

On Wed, Oct 12, 2005 at 07:08:20PM +0100, Oliver Elphick wrote:
I should add that the table inherits from another one, but the
swapped columns are a long way into the extra columns specific to
this table.

Could you post the table definitions?

BTW, I meant the CREATE TABLE statements, not the \d output.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#8

On Wed, 2005-10-12 at 12:37 -0600, Michael Fuhr wrote:

Quote:

On Wed, Oct 12, 2005 at 12:19:41PM -0600, Michael Fuhr wrote:
On Wed, Oct 12, 2005 at 07:08:20PM +0100, Oliver Elphick wrote:
I should add that the table inherits from another one, but the
swapped columns are a long way into the extra columns specific to
this table.

Could you post the table definitions?

BTW, I meant the CREATE TABLE statements, not the \d output.

Here it is:

CREATE TABLE invoice
(
invno INTEGER ,
customer VARCHAR(10) NOT NULL,
account VARCHAR(8) NOT NULL,
invdate DATE NOT NULL
DEFAULT CURRENT_DATE,
taxpoint DATE NOT NULL
DEFAULT CURRENT_DATE,
discount DECIMAL(5,3) NOT NULL DEFAULT 0.0
CHECK (discount >= -50.0:

ECIMAL(5,3) AND
discount <= 50.0:

ECIMAL(5,3)),
ordno INTEGER,
custref TEXT NOT NULL DEFAULT 'NONE',
currency CHAR(3) NOT NULL,
carriage DECIMAL(10,2) NOT NULL DEFAULT 0.00,
printed BOOLEAN NOT NULL DEFAULT 'f',
assigned BOOLEAN NOT NULL DEFAULT 'f',
customer_ean VARCHAR(13),
location_ean VARCHAR(13),
exchange_rate DECIMAL(10,4),
grpid VARCHAR(2),
postcode VARCHAR(10),
grn VARCHAR(20),
orderdate DATE CONSTRAINT "sane order date"
CHECK(orderdate <= invdate),
CONSTRAINT "EDI fields" CHECK ((customer_ean IS NULL AND location_ean IS NULL) OR (customer_ean IS
NOT NULL AND customer_ean != '' AND location_ean IS NOT NULL AND location_ean != '')),
CONSTRAINT "foreign exchange" CHECK ((currency = 'GBP' AND exchange_rate is NULL) OR (currency != 'GBP' AND exchange_rate IS NOT NULL AND exchange_rate > 0.))
,
PRIMARY KEY (invno),
CONSTRAINT invoice_list_fkey
FOREIGN KEY (invno) REFERENCES invoice_list (invno)
ON UPDATE CASCADE
ON DELETE RESTRICT
INITIALLY DEFERRED,
CONSTRAINT customer_accounts_fkey
FOREIGN KEY (customer, account)
REFERENCES customer_accounts (customer, account)
ON UPDATE CASCADE
ON DELETE RESTRICT
INITIALLY DEFERRED,
CONSTRAINT customer_fkey
FOREIGN KEY (customer)
REFERENCES customer (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE,
CONSTRAINT authorised_currency_fkey
FOREIGN KEY (currency)
REFERENCES authorised_currency (currency)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE,
CONSTRAINT customer_group_fkey
FOREIGN KEY (grpid)
REFERENCES customer_group (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE

);

CREATE TABLE export_invoice
(
packages INTEGER NOT NULL
CHECK (packages > 0)
DEFAULT 1,
packing NUMERIC(12,2),
other NUMERIC(12,2),
other_desc TEXT,
insurance NUMERIC(12,2),
pack_desc TEXT,
kilos NUMERIC(12,3),
nett NUMERIC(12,3),
dimensions TEXT,
terms TEXT,
bank TEXT,
goods TEXT,
marks TEXT,
port TEXT,
transport TEXT,
destination CHAR(2) CONSTRAINT destination
REFERENCES country (id)
ON UPDATE CASCADE
ON DELETE NO ACTION,
origin CHAR(2) NOT NULL DEFAULT 'GB'
CONSTRAINT origin
REFERENCES country (id)
ON UPDATE CASCADE
ON DELETE NO ACTION,
auth TEXT,
copies INTEGER NOT NULL DEFAULT 1
,
PRIMARY KEY (invno),
CONSTRAINT invoice_list_fkey
FOREIGN KEY (invno) REFERENCES invoice_list (invno)
ON UPDATE CASCADE
ON DELETE RESTRICT
INITIALLY DEFERRED,
CONSTRAINT customer_accounts_fkey
FOREIGN KEY (customer, account)
REFERENCES customer_accounts (customer, account)
ON UPDATE CASCADE
ON DELETE RESTRICT
INITIALLY DEFERRED,
CONSTRAINT customer_fkey
FOREIGN KEY (customer)
REFERENCES customer (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE,
CONSTRAINT authorised_currency_fkey
FOREIGN KEY (currency)
REFERENCES authorised_currency (currency)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE,
CONSTRAINT customer_group_fkey
FOREIGN KEY (grpid)
REFERENCES customer_group (id)
ON UPDATE CASCADE
ON DELETE RESTRICT
DEFERRABLE

)
INHERITS (invoice)
;

ALTER TABLE export_invoice ALTER COLUMN ordno SET NOT NULL;
CREATE INDEX export_inv_customer_ix ON export_invoice(customer);
CREATE INDEX export_inv_account_ix ON export_invoice(account);
CREATE INDEX export_inv_date_ix ON export_invoice(invdate);

--
Oliver Elphick olly (AT) lfix (DOT) co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

#9

On Wed, Oct 12, 2005 at 08:23:15PM +0100, Oliver Elphick wrote:

Quote:

On Wed, 2005-10-12 at 12:13 -0600, Michael Fuhr wrote:
On another note, regarding the following:

invdate | date | not null default ('now'::text)::date
taxpoint | date | not null default ('now'::text)::date

Are you sure you want 'now'::text as a default, considering the
warning against it?

I actually use CURRENT_DATE; that is what the system turns it into.

Ah yes, I see that now. I generally use now(), so I hadn't noticed
that CURRENT_DATE and CURRENT_TIMESTAMP become 'now', whereas only
a literal 'now' is expanded at create time:

CREATE TABLE foo (
d1 date NOT NULL DEFAULT now(),
d2 date NOT NULL DEFAULT CURRENT_DATE,
d3 date NOT NULL DEFAULT 'now'
);

\d foo
Table "public.foo"
Column | Type | Modifiers
--------+------+--------------------------------------
d1 | date | not null default now()
d2 | date | not null default ('now'::text)::date
d3 | date | not null default '2005-10-12'::date

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq