Thread: array issue....

Hi All,

 I know it was bad design on my part but I've got a table that has one
field that is an array (it's not even used), and I need to delete a row
from the table. Each time I try and remove it I get this error:

 ERROR:  Unable to identify an operator '=' for types 'integer[]' and 'integer'
       You will have to retype this query using an explicit cast

I can not find any documentation on how to cast an array so it can be
properly deleted and the archives are also returning pretty much nothing..

could someone point me in the right direction please? :>


 Chris Bowlby,
 -----------------------------------------------------
 Manager of Information and Technology.
 excalibur@hub.org
 www.hub.org
 1-902-542-3657
 -----------------------------------------------------

On Thu, 19 Sep 2002, Len Morgan wrote:

Woops,

 I forgot to paste the actual query... :(..

 delete from account_info where contact_id = 242;

 is the query, and I'm not even using the column that is of type array[]....

> Why do you need to reference an array (i.e., column) when what you are
> trying to do is remove a row?  Is it because the array contains some special
> value (like NULL) and that is the criteria for deleting?  If not, just
> DELETE FROM <tablename> WHERE <where clause>;
>
> Len Morgan
>
> ----- Original Message -----
> From: "Chris Bowlby" <excalibur@hub.org>
> To: <pgsql-general@postgresql.org>
> Sent: Thursday, September 19, 2002 7:37 AM
> Subject: [GENERAL] array issue....
>
>
> > Hi All,
> >
> >  I know it was bad design on my part but I've got a table that has one
> > field that is an array (it's not even used), and I need to delete a row
> > from the table. Each time I try and remove it I get this error:
> >
> >  ERROR:  Unable to identify an operator '=' for types 'integer[]' and
> 'integer'
> >   You will have to retype this query using an explicit cast
> >
> > I can not find any documentation on how to cast an array so it can be
> > properly deleted and the archives are also returning pretty much nothing..
> >
> > could someone point me in the right direction please? :>
> >
> >
> >  Chris Bowlby,
> >  -----------------------------------------------------
> >  Manager of Information and Technology.
> >  excalibur@hub.org
> >  www.hub.org
> >  1-902-542-3657
> >  -----------------------------------------------------
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> >
>
>
>

 Chris Bowlby,
 -----------------------------------------------------
 Manager of Information and Technology.
 excalibur@hub.org
 www.hub.org
 1-902-542-3657
 -----------------------------------------------------

Chris Bowlby <excalibur@hub.org> writes:
>  I forgot to paste the actual query... :(..
>  delete from account_info where contact_id = 242;
>  is the query, and I'm not even using the column that is of type array[]....

I'll bet you set up a misconceived foreign-key constraint on the column
that is of type array[] ... and that you are running an old version,
else it would have rejected the foreign key constraint.  Before about
7.1 (or was it 7.2?) the system didn't notice a type mismatch between
referenced and referencing columns until you actually did something that
would fire a foreign-key constraint check.

You'll need to drop the foreign key constraint, which in those older
versions means finding and dropping the three triggers that implement
it.  I think there's a recipe for this on the techdocs website.

            regards, tom lane