Обсуждение: explicit cast error

Caveat ... I'm a software engineer with some previous
experience in MySQL, very, very new to PostgreSQL, and
not at all deeply knowledgeable about SQL in general
other than the basics of setting up tables, relations,
doing queries, et al -- the point being that if you
take pity on me and choose to spend time helping me
with the problem described, *please* don't assume I
know too much, and please don't send me pseudocode with
a lot of ellipses and foo's and "and so forth"s in it,
or I'm liable not to know what you're talking about and
how to apply it.

Having said that ... I'm charged with putting together
what seems to be a fairly straightforward database of
so far five tables. Most things are working all right,
but I have a hump to get over. I have a table
definition that is structurally idential to the
following (with actual field, table names, and comments
changed/removed because the project is company
confidential):

create table abc
(
  c1 serial not null primary key,
  c2 smallint not null,
  lab text not null,
  dlab text not null,
  bdigits bigint null,
  foreign key (bdigits) references def
);

Never mind the table def for right now.

I try to load a trial piece of data into this table
using a line like this in an external file, which I
read using \i add_abc in interactive psql:

insert into abc
  (c2, lab, dlab, bdigits) values
  (5, 'somestring', 'abc123', 3410999762);

psql returns to me this message:

psql: ... ERROR: Unable to identify an operator '=' for types 'int4' and 'text'
      You will have to retype this query using an explicit cast

I'm unable to figure this one out. Various trips to
Google have produced explanations and solutions that
have proven so far too dense for me to fathom.
Apparently, however, this is not exactly an unknown
problem. BTW, I have the same identical problem in a
second table.

Can someone please tell me what I might be able to do
to fix this?

Many thanks for your consideration.

--
Lynn David Newton
Phoenix, AZ

> insert into abc
>   (c2, lab, dlab, bdigits) values
>   (5, 'somestring', 'abc123', 3410999762);
> psql returns to me this message:
> psql: ... ERROR: Unable to identify an operator '=' for types 'int4' and 'text'
>       You will have to retype this query using an explicit cast

Try surrounding the bigint integer with single quotes.

                   - Thomas

Lynn David Newton <lynn.newton@cox.net> writes:
> Never mind the table def for right now.

That's not a good idea, because I'll bet that inconsistency between abc
and def is exactly your problem.

> insert into abc
>   (c2, lab, dlab, bdigits) values
>   (5, 'somestring', 'abc123', 3410999762);

> psql returns to me this message:

> psql: ... ERROR: Unable to identify an operator '=' for types 'int4' and 'text'
>       You will have to retype this query using an explicit cast

I am guessing that (a) you are trying to make a foreign key reference
from an integer column to a text column, and (b) you are not using a
recent Postgres release.  Recent releases would have complained when
you tried to define the foreign-key reference, not on first use.

            regards, tom lane

Lynn David Newton wrote:
> insert into abc
>   (c2, lab, dlab, bdigits) values
>   (5, 'somestring', 'abc123', 3410999762);
>
> psql returns to me this message:
>
> psql: ... ERROR: Unable to identify an operator '=' for types 'int4' and 'text'
>       You will have to retype this query using an explicit cast
>

Try:

insert into abc
   (c2, lab, dlab, bdigits) values
   (5, 'somestring', 'abc123', 3410999762::bigint);

or even:

insert into abc
   (c2, lab, dlab, bdigits) values
   (5, 'somestring', 'abc123', '3410999762');

HTH,

Joe

On Thu, 4 Jul 2002, Lynn David Newton wrote:

> Having said that ... I'm charged with putting together
> what seems to be a fairly straightforward database of
> so far five tables. Most things are working all right,
> but I have a hump to get over. I have a table
> definition that is structurally idential to the
> following (with actual field, table names, and comments
> changed/removed because the project is company
> confidential):
>
> create table abc
> (
>   c1 serial not null primary key,
>   c2 smallint not null,
>   lab text not null,
>   dlab text not null,
>   bdigits bigint null,
>   foreign key (bdigits) references def
> );
>
> Never mind the table def for right now.

Well, actually, it'd be useful to know how the primary
key for def is defined since you're doing a references
constraint to it (since you have not specified a column
set it'll point to the primary key of the other table).

I'd suggest as a first step turning on query printing
in the logs so you can see if it's your query or the
query being run for the foreign keys or some other query
that is causing the problem.
I think you can do this by setting debug_print_query
to true in the postgresql.conf in your data directory.

On Fri, 5 Jul 2002, Lynn David Newton wrote:

> --------------------- the tables ----------------------
> create table abc
> (
>   c1 serial not null primary key,
>   c2 smallint not null,
>   lab text not null,
>   dlab text not null,
>   bdigits bigint null,
>
>   foreign key (dlab) references def
> );
>
> create table def
> (
>   defseq serial not null primary key,
>   dlab text unique not null,
>   dset text null,
>   choices text null check (logos in ('','case1','case2', 'case3','case4')),
>   l1 text null,
>   l2 text null,
>   l3 text null,
>
>   foreign key (dset) references datasets
> );
> -------------------------------------------------------
>
> One person asked about the release. It's 7.1.3.


Yep, IIRC we didn't start doing at constraint definition
checks for type equivalence until 7.2, so...
The types for a foreign key must be comparable on both sides

In abc, you've got a text field and in def it's refrencing
the primary key which is an int.  That's not allowed.  If
you were running 7.2 it would have errored when you tried
to make the table.  Perhaps you mean for abc(dlab) to
reference def(dlab) in which case the constraint should
read:
 foreign key(dlab) refrences def(dlab)

To the several people who responded to my "explicit
cast" problem, trying to cover all responses in one
message.

The model input is:

insert into abc
   (c2, lab, dlab, bdigits) values
   (5, 'somestring', 'abc123', 3410999762);

Two or three suggested single quoting the big integer.
Didn't work. Two suggested doing 3410999762::bigint.
That didn't work either.

Another one or two thought the table with the foreign
key should be looked at. Okay, here are both the tables
abc and def. Note: I *may* have made a booboo in typing
out the definition of abc the first time. (I have to
use generic field names to protect the confidentiality
of the project.) If so, I apologize for wasting your
time. I think it's right this time.

You'll note that table def itself has a foreign key
reference. I trust that the table it refers to is
irrelevant for the sake of this problem.

--------------------- the tables ----------------------
create table abc
(
  c1 serial not null primary key,
  c2 smallint not null,
  lab text not null,
  dlab text not null,
  bdigits bigint null,

  foreign key (dlab) references def
);

create table def
(
  defseq serial not null primary key,
  dlab text unique not null,
  dset text null,
  choices text null check (logos in ('','case1','case2', 'case3','case4')),
  l1 text null,
  l2 text null,
  l3 text null,

  foreign key (dset) references datasets
);
-------------------------------------------------------

One person asked about the release. It's 7.1.3.

BTW, this is a very small and very short project.
Probably no more than a couple dozen records in a total
of five tables. But it's an important one.

Unfortunately, the clock is about to run out on this
problem, and I may have to go with another klugier
version to get it in operation. I hate it when that
happens. I had a total of two days to go from scratch
to becoming an expert in PostgreSQL. Didn't exactly get
that far.

Thanks again for your help. Sorry if I overlooked
anyone who responded.

--
Lynn David Newton
Phoenix, AZ

  > ...>
  >> foreign key (dset) references datasets

  Stephan> In abc, you've got a text field and in def
  Stephan> it's refrencing the primary key which is an
  Stephan> int. That's not allowed. If you were running
  Stephan> 7.2 it would have errored when you tried to
  Stephan> make the table. Perhaps you mean for
  Stephan> abc(dlab) to reference def(dlab) in which
  Stephan> case the constraint should read:

  Stephan>  foreign key(dlab) refrences def(dlab)

Ack! Of course, of course, of course ...

My problem is now solved. Having close to zero
experience, and having extrapolated syntax for the
foreign key statement from an example full of foos and
ellipses, I assumed that the argument to 'references'
was simplay a table name and that it would look for a
column by the same *name* as the one referred to in the
calling table. I now see that doesn't make a lot of
sense, and that the actual field name in the other
table needs to be specified, even if it's the same
name, unless the default primary key is intended.

I would have eventually discovered that, if I'd read
only another 5-600 pages of manual.

Thank you very much.

--
Lynn David Newton
Phoenix, AZ