Как создать условие проверки, которое проверяет, является ли дата определенной датой, тогда доход должен быть> x?

Я пытаюсь выяснить, как написать и добавить общее ограничение за пределами CREATE TABLE, где, когда payDate - Новый год 2010 (01-01-2010), тогда доход разных сотрудников должен превышать 100 000 долларов. Некоторые данные о доходах на эту дату имеют значение NULL, но я пытаюсь найти только те доходы, которые превышают 100 000 долларов.

Таблица, с которой я работаю:

Employees | Date     | Income
John      |12-01-2009| 50000
Jake      |12-01-2009| 70000  
Jill      |12-21-2009| 85000.75
Jonn      |12-27-2009| 120000.5
June      |01-01-2010| 100000.25
Joey      |01-01-2010| \N
Jack      |01-01-2010| 120000
Jane      |01-01-2010| 110000.75
Jean      |01-01-2010| \N
Joon      |01-01-2010| \N  

Я пытался:

ALTER TABLE Employees
ADD CONSTRAINT nyIncome
CHECK(payDate = DATE '2010-01-01' AND income > 100000 AND income IS NOT NULL);

Это дает мне:

"ERROR: check constraint nyIncome is violated by some row"

Как мне исправить свой чек, чтобы он удовлетворял условиям?

Обновлено: причина, по которой мое первоначальное ограничение не сработало, заключалась в том, что в таблице есть другие даты, которые не были указаны в условии.

Решение, которое предоставил Джонас:

ALTER TABLE Employees ADD CONSTRAINT nyIncome CHECK (payDate != '2010-01-01' OR (payDate = '2010-01-01' AND (income > 100000 OR income IS NULL)));

Это решение будет проходить через все payDates и создавать конкретное условие, при котором доход должен быть выше 100000 на дату «2010-01-01», поэтому, если, например, кортеж с датой «2010-01-01» и доходом 80 000 не будут соответствовать этому ограничению и, следовательно, не могут быть вставлены, или доход с даты «2010-01-01» не может быть обновлен с доходом меньше 100000.

Это означает, что данные в таблице не удовлетворяют вашему условию

Sergey Kozharinov 17.05.2022 10:08

Моя таблица содержит только доходы больше 100000 и нулевые доходы на эту конкретную дату (01-01-2010), и я не могу найти, почему мое условие не было выполнено. Может ли быть другая причина, по которой он нарушается, например, неправильно написанное мое условие? @СергейКожаринов

drewster 17.05.2022 10:12

В вашем условии запрещены нулевые значения дохода. Замените AND income IS NOT NULL на OR income IS NOT NULL.

Sergey Kozharinov 17.05.2022 10:33

Пожалуйста, уточните, какова ваша цель здесь. Вы хотите просто фильтр свои данные на основе этих требований к дате и доходу? Если это так, то вы, вероятно, захотите использовать Посмотреть или запрос, а не проверочное ограничение.

Tim Biegeleisen 17.05.2022 10:55

Привет, Тим, я хотел бы отфильтровать сотрудников, которые заработали более 100 тысяч долларов на 01.01.2010, поэтому мое условие должно разрешать только строки «Июнь», «Джек» и «Джейн», где доход> 100000 и дата 01.01.2010. Я обнаружил, что могут быть основные проблемы с моим проверочным ограничением, потому что эквивалентный оператор SELECT работает правильно.

drewster 17.05.2022 11:10
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
0
5
46
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Точное сообщение об ошибке, которое вы видите, будет показано, если в вашей таблице уже есть одна или несколько записей, которые не соответствуют проверочному ограничению. Чтобы найти такие записи, вы можете использовать следующий запрос:

SELECT *
FROM Employees
WHERE payDate <> '2010-01-01' OR income > 100000 OR income IS NOT NULL;

На самом деле, ваше контрольное ограничение очень специфично, и мне кажется, что большинство данных не соответствуют ему. Возможно, вы имели в виду отрицательный этого ограничения проверки:

ALTER TABLE Employees
ADD CONSTRAINT nyIncome
CHECK (payDate <> '2010-01-01' OR income > 100000 OR income IS NOT NULL);

Как мне создать условие проверки, удовлетворяющее только тех сотрудников, которые заработали 100 долларов или более (не нуль) 01.01.2010 и исключить все остальные строки в таблице?

drewster 17.05.2022 10:36

Я предлагаю нет использовать для этого проверочное ограничение. Вместо этого просто используйте представление или запрос, который фильтрует ваши данные в соответствии с этой логикой.

Tim Biegeleisen 17.05.2022 10:38

Привет, Тим, я пытаюсь изучить ограничения проверки прямо сейчас. Нельзя ли решить эту задачу, используя проверочные ограничения?

drewster 17.05.2022 10:54
Ответ принят как подходящий

Вы не можете создать ограничение для существующих таблиц, если некоторые записи не удовлетворяют условиям ограничения. Если вам действительно нужно проверить все эти условия, вам нужно обновить уже существующие записи перед созданием ограничения, чтобы убедиться, что все строки удовлетворяют условиям. Или, если это не предназначено, вам нужно создать триггер перед вставкой вместо ограничения, которое будет проверять, разрешены ли новые записи или нет. Если вы создаете такой триггер, вы также должны проверить, достаточно ли до вставки или до того, как должно быть выполнено обновление. В вашей ситуации кажется, что условия вашего ограничения неверны. Вероятно, правильный синтаксис (согласно вашему описанию) будет:

ALTER TABLE Employees
ADD CONSTRAINT nyIncome
CHECK (payDate = '2010-01-01' AND (income > 100000 OR income IS NULL));

Если вы не уверены в правильных условиях для вашего ограничения, сначала выполните выборку с точными условиями, такими как предложение where, которое вы хотите проверить своим ограничением, и посмотрите, является ли результат всей вашей таблицей. Или верните предложение where и проверьте, пуст ли результат.

Обновите, потому что вы отредактировали свой вопрос: вы не можете создать такое ограничение, потому что в вашей таблице есть записи с другой датой. Скорее всего, вам понадобится триггер, который запрещает вставлять/обновлять записи с доходом <= 100000 и датой '2010-01-01'. Или просто создайте точное предложение where в своих запросах вместо триггера, если эти ограничения должны применяться только в запросах.

Привет, Джонас, я выполнил 'SELECT * FROM Employees WHERE (payDate = '2010-01-01' AND (income> 100000 ИЛИ WinPrize IS NULL));' и это дало мне правильные результаты. Однако, когда я запускаю «ИЗМЕНИТЬ ТАБЛИЦУ Сотрудников, ДОБАВИТЬ ОГРАНИЧЕНИЕ nyIncome CHECK (payDate = '2010-01-01' И (доход> 100000 ИЛИ доход IS NULL));' Я получаю ошибку нарушения строки. Любая идея о том, почему ошибка происходит?

drewster 17.05.2022 11:08

Да, я уже говорил вам об этом: «Вы не можете создать такое ограничение, потому что в вашей таблице также есть записи с другой датой». В этом случае вам понадобится триггер.

Jonas Metzler 17.05.2022 11:15

Привет Джонас, спасибо за разъяснение ошибки для меня. Есть ли способ поместить что-то вроде предложения WHERE в условие проверки? Бывший. 'code'ALTER TABLE Сотрудники ДОБАВИТЬ ОГРАНИЧЕНИЕ nyДоход от сотрудников Где payDate = '2010-01-01' CHECK (доход> 100000); "код"?

drewster 17.05.2022 11:40

Вы можете сделать что-то вроде ALTER TABLE Employees ADD CONSTRAINT nyIncome CHECK (payDate! = '2010-01-01' OR (payDate = '2010-01-01' AND (income> 100000 OR yield IS NULL))); Я не знаю, почему вы хотите делать такие сумасшедшие вещи, и я не верю, что это хорошая идея, но в целом это возможно.

Jonas Metzler 17.05.2022 11:45

Это проверочное ограничение, которое вы написали, было тем, что я искал. Спасибо Йонас!

drewster 17.05.2022 12:00

Ок, отлично. пожалуйста. Пожалуйста, отметьте ответ как принятый. И имейте в виду, что есть лучшие решения вместо такого странного ограничения проверки. Лучше всего проверять такие вещи в соответствующем приложении еще до того, как пытаться выполнить вставку. Но это уже другая тема ;)

Jonas Metzler 17.05.2022 12:04

when the payDate is New Years 2010 (01-01-2010), then the income of different employees must be more than $100,000. Some of the income data on that date is NULL,

Другие люди уже указали, что вы не можете использовать контрольное ограничение для (paydate = 2010-01-01, тогда доход не равен нулю), потому что некоторые строки нарушают условие.
Таким образом, следующий ответ для ограничения установки для: если дата выплаты = 01.01.2010, то доход = ноль или доход > 100000.

через контрольное ограничение. Значение проверки текущих строк и входящих строк. если не соответствуют критериям, будет вызвана ошибка.

ALTER TABLE emp
    ADD CONSTRAINT nyIncome CHECK (
        (payDate = DATE '2010-01-01' AND income > 100000)
    OR (payDate = DATE '2010-01-01' AND income IS NULL)
    OR (payDate <> DATE '2010-01-01'));
drop trigger trg_special_date on emp;

через триггер. Очевидно, что триггер не проверяет существующую строку.

CREATE OR REPLACE FUNCTION trg_special_date ()
    RETURNS TRIGGER
    AS $$
BEGIN
    IF NEW.paydate = '2010-01-01' THEN
        IF NEW.income IS NULL THEN
            RETURN new;
        elsif NEW.income > 100000 THEN
            RETURN new;
        ELSE
            RETURN NULL;
        END IF;
    ELSE
        RETURN new;
    END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trg_special_date
    BEFORE INSERT OR UPDATE ON emp FOR EACH ROW
    EXECUTE PROCEDURE trg_special_date ();

примечание: этот триггер будет просто игнорировать строку, которая не соответствует критериям. Вы можете настроить его, поэтому, если входящая строка не соответствует критериям, вызовите ошибку.

другой способ - безопасность на уровне строк, довольно сложная. другой способ — просмотреть с помощью security_barrier или security_invoker или с помощью check_option. (сделаю в будущем).

Другие вопросы по теме