[NOVICE] Argh! What's a 'bpchar'? (copy/serial issues...I think)

[Ken Corey]

I'm trying to copy some data (several hundred rows) from the file
/tmp/player.txt containing:

9,test16610,EN,NULL,a...@b.com
10,test24930,EN,NULL,a...@b.com
11,test19829,EN,NULL,a...@b.com
12,test6285,EN,Y,a...@b.com
13,test20885,EN,NULL,a...@b.com
...

into this table:

CREATE TABLE PLAYER
(
PLAYER_ID SERIAL,
PLAYER_NAME varchar(255) NOT NULL,
PLAYING_FOR varchar(255) NOT NULL,
CHEAT_FLAG char(1) NULL,
EMAIL varchar(255) NULL,
--UNIQUE (PLAYER_NAME,PLAYING_FOR,EMAIL),
CONSTRAINT PK_PLAYER PRIMARY KEY (PLAYER_ID)
)
;

with this command:

GRE=# copy player from '/tmp/player.txt' using delimiters ',';
ERROR: Unable to identify an operator '=' for types 'bpchar' and
'varchar'
You will have to retype this query using an explicit cast
ERROR: Unable to identify an operator '=' for types 'bpchar' and
'varchar'
You will have to retype this query using an explicit cast

(I've tried adding a "with null as 'null'", with no success.)

Anyone got a clue for the newbie?

-Ken

[Tom Lane]

Ken Corey <k...@kencorey.com> writes:
> CREATE TABLE PLAYER
> (
> PLAYER_ID SERIAL,
> PLAYER_NAME varchar(255) NOT NULL,
> PLAYING_FOR varchar(255) NOT NULL,
> CHEAT_FLAG char(1) NULL,
> EMAIL varchar(255) NULL,
> --UNIQUE (PLAYER_NAME,PLAYING_FOR,EMAIL),
> CONSTRAINT PK_PLAYER PRIMARY KEY (PLAYER_ID)
> )
> ;

> with this command:

> GRE=# copy player from '/tmp/player.txt' using delimiters ',';
> ERROR: Unable to identify an operator '=' for types 'bpchar' and
> 'varchar'

"bpchar" is the internal type name for CHAR(n) (think "blank-padded
char"). It's unhappy because something is trying to compare a
char(n) field to a varchar(n) field --- we don't let you do that
because the semantics aren't well defined. (One type thinks trailing
blanks are significant in a comparison, the other doesn't.)

As to what that something is, my guess is a foreign key constraint
that you didn't show us. IIRC, 7.0 fails to check for comparable
datatypes when you define a foreign key, so you get the error at
runtime instead :-(. Do you have another table that references this
one?

regards, tom lane