Odd delete behavior in 7.0beta1

From: Andrew Rawnsley <ronz(at)ravensfield(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Odd delete behavior in 7.0beta1
Date: 2000-02-23 17:03:57
Message-ID: 00022312270400.04412@coho.ravensfield.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Can anyone tell me if this is (a) a bug in 7.0beta1, (b) a feature, or (c) me
being an idiot.

Fresh build of 7.0beta1 on a Redhat 6.0 box. I've been using pgsql for enough
time (6 months) that I'm reasonably familiar with how everything works.

To sum up: selects and updates work, using an index scan, but deletes won't.
Casting the field as text doesn't throw an error, but it doesn't work. Casting
it as anything busts a nut also.

In detail:

sm=> \d parameters
Table "parameters"
Attribute | Type | Modifier
-------------+---------------+-----------
casno | char(15) | not null
parameter | text | not null
status | integer | default 0
mf | varchar(50) |
mw | numeric(10,3) |
tsca | integer | default 0
epa_inv | char(2) |
ohvap | integer | default 0
bustr | integer | default 0
saraiii | integer | default 0
description | text |
uses | text |
Indices: pkprm, (NOTE: pkprm is primary key on casno)
xparm (NOTE: xparm is a btree index on parameter)

sm=> insert into parameters (casno,parameter) values ('0000-00-0','METHYL ETHYL DOGPOOP');
INSERT 199287 1
sm=> explain update parameters set parameter = 'METHYL ETHYL PUPPYPOOP' where casno = '0000-00-0';
NOTICE: QUERY PLAN:

Index scan using pkprm on parameters (cost=0.00..7.09 rows=10 width=98)

sm=> update parameters set parameter = 'METHYL ETHYL PUPPYPOOP' where casno = '0000-00-0';
UPDATE 1
sm=> explain delete from parameters where casno = '0000-00-0';
NOTICE: QUERY PLAN:

Index scan using pkprm on parameters (cost=0.00..7.09 rows=10 width=98)

sm=> delete from parameters where casno = '0000-00-0';
ERROR: Unable to identify operator '=' for types 'int4' and 'bpchar'
You will have to retype this query using an explicit cast
sm=> delete from parameters where casno = '0000-00-0'::text;
DELETE 0
sm=> delete from parameters where casno = '0000-00-0'::char;
ERROR: Unable to identify operator '=' for types 'int4' and 'bpchar'
You will have to retype this query using an explicit cast
sm=> delete from parameters where casno = '0000-00-0'::bpchar;
ERROR: Unable to identify operator '=' for types 'int4' and 'bpchar'
You will have to retype this query using an explicit cast
sm=> delete from parameters where parameter = 'METHYL ETHYL PUPPYPOOP';
ERROR: Unable to identify operator '=' for types 'int4' and 'bpchar'
You will have to retype this query using an explicit cast

Other than this, 7.0 seems quite nifty so far...nifty enough to move
everything from Sybase...

--
Regards,

Andrew Rawnsley
Ravensfield Geographic Resources, Ltd.
(740) 587-0114
www.ravensfield.com

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Rawnsley 2000-02-23 17:27:57 Odd delete behavior in 7.0beta1
Previous Message Hiroshi Inoue 2000-02-23 17:03:29 RE: AW: [HACKERS] TRANSACTIONS