SEO

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.

Here is the data, with tabs indicated by ^I and end of line by $:
67829^I22058^IX378^I10 FEB 2004^I10 FEB 2004^I0.000^I5411^I15498-00^IGBP^I0.00^It^It^I\N^I\N^I\N^I\N^I\N^I\N^I10 FEB 2004^I60^I0.00^I0.00^I0^I0.00^ICarton^I570.000^I0. 000^I^ICIF-MONTREAL/ACT 30-DAYS^I^IChemist\'s sundries^IAMG MEDICAL;5411;MONTREAL;1-60;^ITHAMESPORT^ISEA/ALLIANCE^ICA^IGB^IMAIREAD BOYCE, DIRECTOR^I1$

the command used to fill the table is:
COPY export_invoice FROM '/usr1/avdumps/dbdump.exportinvoice';

and here is how COPY has rendered it:
bray=# select * from export_invoice where invno = 67829;
invno | customer | account | invdate | taxpoint | discount | ordno | custref | currency | carriage | printed | assigned | customer_ean | location_ean | exchange_rate | grpid | postcode | grn | orderdate | packages | packing | other | other_desc | insurance | pack_desc | kilos | nett | dimensions | terms | bank | goods | marks | port | transport | destination | origin | auth | copies
-------+----------+---------+------------+------------+----------+-------+----------+----------+----------+---------+----------+--------------+--------------+---------------+-------+----------+-----+------------+----------+---------+-------+------------+-----------+-----------+---------+-------+--------------------------+-------+------+--------------------+---------------------------------+------------+--------------+-------------+--------+-------------------------+--------
67829 | 22058 | X378 | 2004-02-10 | 2004-02-10 | 0.000 | 5411 | 15498-00 | GBP | 0.00 | t | t | | | | | | | 2004-02-10 | 60 | 0.00 | 0.00 | 0 | 0.00 | Carton | 570.000 | 0.000 | CIF-MONTREAL/ACT 30-DAYS | | | Chemist's sundries | AMG MEDICAL;5411;MONTREAL;1-60; | THAMESPORT | SEA/ALLIANCE | CA | GB | MAIREAD BOYCE, DIRECTOR | 1
(1 row)

The problem is seen in columns 28 and 29 (dimensions and terms). The
data for terms is in dimensions, which ought to contain the empty
string.

Table definitions:
Table "prod.export_invoice"
Column | Type | Modifiers
---------------+-----------------------+--------------------------------------
invno | integer | not null
customer | character varying(10) | not null
account | character varying(8) | not null
invdate | date | not null default ('now'::text)::date
taxpoint | date | not null default ('now'::text)::date
discount | numeric(5,3) | not null default 0.0
ordno | integer | not null
custref | text | not null default 'NONE'::text
currency | character(3) | not null
carriage | numeric(10,2) | not null default 0.00
printed | boolean | not null default false
assigned | boolean | not null default false
customer_ean | character varying(13) |
location_ean | character varying(13) |
exchange_rate | numeric(10,4) |
grpid | character varying(2) |
postcode | character varying(10) |
grn | character varying(20) |
orderdate | date |
packages | integer | not null 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 | character(2) |
origin | character(2) | not null default 'GB'::bpchar
auth | text |
copies | integer | not null default 1
....indexes and constraints omitted...





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

On Wed, 2005-10-12 at 09:09 +0100, Oliver Elphick wrote:
> 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@lfix.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

Oliver Elphick <olly@bray-healthcare.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?

regards, tom lane

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

On Wed, 2005-10-12 at 13:52 -0400, Tom Lane wrote:
> Oliver Elphick <olly@bray-healthcare.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@lfix.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

On Wed, Oct 12, 2005 at 09:09:05AM +0100, Oliver Elphick wrote:
> 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:

> 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

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?

--
Michael Fuhr

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

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.

--
Michael Fuhr

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

On Wed, 2005-10-12 at 12:37 -0600, Michael Fuhr wrote:
> 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@lfix.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

On Wed, Oct 12, 2005 at 08:23:15PM +0100, Oliver Elphick wrote:
> 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

On Wed, Oct 12, 2005 at 08:17:23PM +0100, Oliver Elphick wrote:
> On Wed, Oct 12, 2005 at 12:19:41PM -0600, Michael Fuhr wrote:
> > Could you post the table definitions?
>
> Here it is:

I created the tables you posted (sans foreign key constraints because
you didn't include the referenced tables) and loaded the data from
your previous post into an 8.0.4 server on FreeBSD 4.11-STABLE.
Here's what I got; it looks correct around the columns you report
having trouble with:

\x
SELECT * FROM export_invoice;
-[ RECORD 1 ]-+--------------------------------
invno | 67829
customer | 22058
account | X378
invdate | 2004-02-10
taxpoint | 2004-02-10
discount | 0.000
ordno | 5411
custref | 15498-00
currency | GBP
carriage | 0.00
printed | t
assigned | t
customer_ean |
location_ean |
exchange_rate |
grpid |
postcode |
grn |
orderdate | 2004-02-10
packages | 60
packing | 0.00
other | 0.00
other_desc | 0
insurance | 0.00
pack_desc | Carton
kilos | 570.000
nett | 0.000
dimensions |
terms | CIF-MONTREAL/ACT 30-DAYS
bank |
goods | Chemist's sundries
marks | AMG MEDICAL;5411;MONTREAL;1-60;
port | THAMESPORT
transport | SEA/ALLIANCE
destination | CA
origin | GB
auth | MAIREAD BOYCE, DIRECTOR
copies | 1

--
Michael Fuhr

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