Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();


version


PostgreSQL 10.22 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE member 
(
  member_id INTEGER PRIMARY KEY,
  member_fname VARCHAR(15) NOT NULL,
  member_lname VARCHAR(15) NOT NULL,
  member_status VARCHAR(15) NOT NULL,
  member_address VARCHAR(10) NOT NULL,
  member_email VARCHAR(30) NOT NULL
);

CREATE TABLE


CREATE TABLE bicycle (
  bicycle_id INTEGER PRIMARY KEY,
  bicycle_brand VARCHAR(25) NOT NULL,
  bicycle_model VARCHAR(25) NOT NULL,
  bicycle_colour VARCHAR(15) NOT NULL,
  bicycle_type VARCHAR(20) NOT NULL,
  bicycle_size VARCHAR(10) NOT NULL,
  bicycle_availability VARCHAR(20) NOT NULL
);

CREATE TABLE


CREATE TABLE rental (
  rental_no INTEGER PRIMARY KEY,
  rental_period DATE NOT NULL,
  bicycle_pickup_date DATE NOT NULL
);

CREATE TABLE


CREATE TABLE company (
  company_id INTEGER PRIMARY KEY,
  company_name VARCHAR(20) NOT NULL,
  company_contact VARCHAR(50) NOT NULL,
  company_address VARCHAR(50) NOT NULL
);

CREATE TABLE


CREATE TABLE sponsor (
  sponsor_id INTEGER PRIMARY KEY,
  sponsor_name VARCHAR(15) NOT NULL,
  sponsor_contact VARCHAR(30) NOT NULL,
  sponsor_period DATE NOT NULL,
  sponsor_address VARCHAR(50) NOT NULL,
  sponsor_fee DECIMAL (6, 2) NOT NULL
);

CREATE TABLE


CREATE TABLE terminal (
  terminal_id INTEGER PRIMARY KEY,
  terminal_address VARCHAR(50) NOT NULL,
  terminal_minstorage VARCHAR(50) NOT NULL,
  terminal_maxstorage VARCHAR(50) NOT NULL
);

CREATE TABLE


CREATE TABLE bill (
  bill_id INTEGER PRIMARY KEY,
  bill_date DATE NOT NULL,
  bill_total_amount DECIMAL(6, 2) NOT NULL,
  payment_no INTEGER NOT NULL,
  payment_date DATE NOT NULL,
  payment_method VARCHAR(15) NOT NULL
);

CREATE TABLE


CREATE TABLE maintenance (
  maintenance_id INTEGER PRIMARY KEY,
  bicycle_id INTEGER NOT NULL,
  maintenance_contact_person VARCHAR(15) NOT NULL,
  maintenance_phone_number INTEGER NOT NULL,
  maintenance_fee DECIMAL(6, 2) NOT NULL
);

CREATE TABLE


--
-- Foreign key defined without error
--
ALTER TABLE maintenance ADD CONSTRAINT maint_bike_fk FOREIGN KEY (bicycle_id)
  REFERENCES bicycle (bicycle_id);

ALTER TABLE


DROP TABLE maintenance;

DROP TABLE


CREATE TABLE maintenance (
  maintenance_id INTEGER PRIMARY KEY,
  bicycle_id INTEGER NOT NULL,
  maintenance_contact_person VARCHAR(15) NOT NULL,
  maintenance_phone_number INTEGER NOT NULL,
  maintenance_fee DECIMAL(6, 2) NOT NULL,
  CONSTRAINT maint_bike_fk FOREIGN KEY (bicycle_id)
    REFERENCES bicycle (bicycle_id)
);

CREATE TABLE


--
-- First Error - first of your foreign keys defined
--
ALTER TABLE bicycle ADD CONSTRAINT fk_bicycle_pickup_date FOREIGN KEY (bicycle_pickup_date) REFERENCES rental(bicycle_pickup_date);

ERROR:  column "bicycle_pickup_date" referenced in foreign key constraint does not exist


ALTER TABLE bicycle ADD CONSTRAINT fk_maintenance_contact_person FOREIGN KEY (maintenance_contact_person) REFERENCES maintenance(maintenance_contact_person);

ERROR:  column "maintenance_contact_person" referenced in foreign key constraint does not exist


ALTER TABLE bicycle ADD CONSTRAINT fk_terminal_id FOREIGN KEY (terminal_id) REFERENCES terminal(terminal_id);

ERROR:  column "terminal_id" referenced in foreign key constraint does not exist


ALTER TABLE bicycle ADD CONSTRAINT fk_rental_period FOREIGN KEY (rental_period) REFERENCES rental(rental_period);

ERROR:  column "rental_period" referenced in foreign key constraint does not exist