Re: Delete query takes exorbitant amount of time

On Thu, 2005-03-24 at 20:48 -0500, Tom Lane wrote:
> In that case there's a datatype mismatch between the referencing and
> referenced columns, which prevents the index from being used for the
> FK check.

Can I have more words on this? Here is how I created the tables:

CREATE TABLE int_sensor_meas_type(
    id_int_sensor_meas_type SERIAL PRIMARY KEY,
    id_sensor integer NOT NULL REFERENCES sensor,
    id_meas_type integer NOT NULL REFERENCES meas_type UNIQUE);


CREATE TABLE measurement (
    id_measurement SERIAL PRIMARY KEY,
    id_int_sensor_meas_type integer NOT NULL REFERENCES int_sensor_meas_type,
    datetime timestamp WITH TIME ZONE NOT NULL,
    value numeric(15,5) NOT NULL,
    created timestamp with time zone NOT NULL DEFAULT now(),
    created_by TEXT NOT NULL REFERENCES public.person(id_person));

CREATE INDEX measurement__id_int_sensor_meas_type_idx ON measurement(id_int_sensor_meas_type);

Do I need to cast the id_int_sensor_meas_type column when creating the
index? Both referrer and referenced look like INTEGER to me...

http://www.postgresql.org/docs/8.0/interactive/datatype.html#DATATYPE-SERIAL
says: "The type names serial and serial4 are equivalent: both create
integer columns"

TIA,

--
Karim Nassar
Department of Computer Science
Box 15600, College of Engineering and Natural Sciences
Northern Arizona University,  Flagstaff, Arizona 86011
Office: (928) 523-5868 -=- Mobile: (928) 699-9221