Создаем таблицу:
=> CREATE TABLE observations( date_taken date NOT NULL, temperature numeric CHECK( temperature BETWEEN -60 AND 60 ) );
CREATE TABLE
Добавляем записи и проверяем ограничения:
=> INSERT INTO observations VALUES (current_date, 25), (current_date-1, 22);
INSERT 0 2
=> INSERT INTO observations VALUES (current_date, 70);
ERROR: new row for relation "observations" violates check constraint "observations_temperature_check" DETAIL: Failing row contains (2017-07-22, 70).
=> INSERT INTO observations VALUES (null, 20);
ERROR: null value in column "date_taken" violates not-null constraint DETAIL: Failing row contains (null, 20).
Создаем представление:
=> CREATE VIEW observ_fahrenheit AS SELECT date_taken, temperature AS celsius, round(temperature*9/5+32) AS fahrenheit FROM observations;
CREATE VIEW
=> SELECT * FROM observ_fahrenheit;
date_taken | celsius | fahrenheit ------------+---------+------------ 2017-07-22 | 25 | 77 2017-07-21 | 22 | 72 (2 rows)
Индексируем:
=> CREATE INDEX observations_date ON observations(date_taken);
CREATE INDEX