BUG #2983: Nonsense error: "Table has type interval, but query expects interval" - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2983
Logged by:          David Flater
Email address:      dave@flaterco.com
PostgreSQL version: 8.2.2
Operating system:   Linux
Description:        Nonsense error:  "Table has type interval, but query
expects interval"
Details:

-- Demo for interval defaulting to null bug in PostgreSql 8.2.2.
-- Simpler example with only 2 columns did not reproduce bug.
-- DWF, 20070209

create table data_sets (
  index              serial primary key,
  name               text not null,
  original_name      text,
  station_id_context text,
  station_id         text,
  lat                double precision
    check (lat >= -90  and lat <= 90),
  lng                double precision
    check (lng >= -180 and lng <= 180),
  timezone           text not null,
  country            text,
  units              text not null
    check (units = 'feet' or units = 'meters' or units = 'knots'
    or units = 'knots^2'),
  min_dir            double precision
    check (min_dir >= 0 and min_dir < 360),
  max_dir            double precision
    check (max_dir >= 0 and max_dir < 360),
  legalese           text,
  notes              text,
  comments           text,
  source             text,
  pedigree           text,
  restriction text not null,
  date_imported      date default current_date,
  xfields            text,
  meridian             interval hour to minute,
  datumkind            text,
  datum                double precision,
  months_on_station    smallint,
  last_date_on_station date,
  ref_index integer    references data_sets,
  min_time_add         interval day to minute,
  min_level_add        double precision,
  min_level_multiply   double precision
    check (min_level_multiply > 0),
  max_time_add         interval day to minute,
  max_level_add        double precision,
  max_level_multiply   double precision
    check (max_level_multiply > 0),
  flood_begins         interval day to minute,
  ebb_begins           interval day to minute,
  check ((lat is null and lng is null) or
         (lat is not null and lng is not null)),
  check ((datum is null and meridian is null) or
         (datum is not null and meridian is not null)),
  check (datum is not null or ref_index is not null)
) with oids;

-- This insert succeeds.
insert into data_sets (index, name, timezone, units, restriction, meridian,
                       datumkind, datum)
 values (1,
         'Generic Tide Reference Station',
         'UTC0',
         'meters',
         'For testing only',
         '0:00',
         'Zulu',
         0);

-- The following statement produces:
-- ERROR:  attribute 21 has wrong type
-- DETAIL:  Table has type interval, but query expects interval.
insert into data_sets (name, lat, lng, timezone, units, restriction,
ref_index)
  values ('North Pole',  90,    0, 'UTC0', 'meters', 'For testing only',
1);

-- This insert succeeds (only difference is explicit null meridian).
insert into data_sets (name, lat, lng, timezone, units, restriction,
ref_index,
                       meridian)
  values ('North Pole',  90,    0, 'UTC0', 'meters', 'For testing only', 1,
                       NULL);