27 lines
896 B
SQL
27 lines
896 B
SQL
CREATE TYPE fluid_t AS ENUM ('E', 'G', 'W');
|
|
|
|
CREATE TYPE unit_t AS ENUM ('m³', 'KWh');
|
|
|
|
CREATE DOMAIN reading_d AS DECIMAL(8,3)
|
|
CONSTRAINT reading_nonnegative CHECK (VALUE >= 0.0);
|
|
|
|
CREATE TYPE quantity_t AS (fluid fluid_t, size reading_d, unit unit_t);
|
|
|
|
CREATE DOMAIN quantity_d AS quantity_t
|
|
CONSTRAINT quantity_unit
|
|
CHECK (((VALUE).fluid = 'E' AND (VALUE).unit = 'KWh')
|
|
OR ((VALUE).fluid != 'E' AND (VALUE).unit = 'm³'));
|
|
|
|
CREATE TABLE meter_reading
|
|
( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
when_taken DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
when_entered TIMESTAMPTZ NOT NULL DEFAULT CLOCK_TIMESTAMP(),
|
|
fluid fluid_t NOT NULL,
|
|
reading reading_d NOT NULL,
|
|
when_given DATE,
|
|
CONSTRAINT mr_uniq UNIQUE (when_taken, fluid),
|
|
CONSTRAINT mr_gt_order CHECK (when_given >= when_taken)
|
|
);
|
|
|
|
GRANT SELECT ON meter_reading, meter_reading_id_seq TO mr_user;
|