PostgreSQL中校验多插入对象单日累计工时不超24小时的实现

Alright, let's figure out how to enforce that the total working hours for a single day don't exceed 24 when inserting new records. Your existing CHECK constraint only validates individual hours values, so we need extra layers to handle the cumulative check. Here are the most reliable approaches:

1. Application-Level Validation (First Line of Defense) #

This is the most straightforward way to catch issues early and give immediate feedback to users. Before saving the new Hour object, query the database for the total hours already logged on the target date, then check if adding the new hours would push it over 24.

Example Code (Java/Spring Boot) #

@Service
public class HourService {
    private final HourRepository hourRepository;

    public HourService(HourRepository hourRepository) {
        this.hourRepository = hourRepository;
    }

    @Transactional
    public Hour saveHour(Hour hour) {
        LocalDate targetDate = hour.getDate();
        // Get current total hours for the day, default to 0 if no records exist
        Integer currentTotal = hourRepository.findTotalHoursByDate(targetDate);
        currentTotal = currentTotal == null ? 0 : currentTotal;

        if (currentTotal + hour.getHours() > 24) {
            throw new IllegalArgumentException("Total daily hours cannot exceed 24. Current total: " + currentTotal);
        }

        return hourRepository.save(hour);
    }
}

Handling Concurrency #

To avoid race conditions (e.g., two requests inserting at the same time both seeing a valid total), add a lock to your query. Use SELECT ... FOR UPDATE to lock existing records for the target date during the transaction:

-- Repository method query
SELECT COALESCE(SUM(hours), 0) FROM hours_worked WHERE date = ? FOR UPDATE;

This ensures that only one transaction can modify the hours for a specific date at a time.

2. Database-Level Enforcement (Last Line of Defense) #

Even if you validate in the application, it's wise to add a database-level check to prevent invalid data from being inserted directly (e.g., via SQL clients or buggy code). A trigger is perfect for this.

Example Trigger (PostgreSQL) #

First, create a function that checks the daily total before insert/update:

CREATE OR REPLACE FUNCTION check_daily_hour_limit()
RETURNS TRIGGER AS $$
DECLARE
    daily_total INTEGER;
BEGIN
    -- Calculate total hours for the new record's date
    SELECT COALESCE(SUM(hours), 0) INTO daily_total
    FROM hours_worked
    WHERE date = NEW.date;

    -- Throw exception if adding new hours exceeds 24
    IF daily_total + NEW.hours > 24 THEN
        RAISE EXCEPTION 'Daily total hours cannot exceed 24. Current total: %, New hours: %', daily_total, NEW.hours;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then attach the trigger to your table:

CREATE TRIGGER validate_daily_hours
BEFORE INSERT OR UPDATE ON hours_worked
FOR EACH ROW EXECUTE FUNCTION check_daily_hour_limit();

This trigger runs every time a record is inserted or updated, ensuring the daily total never exceeds 24.

3. Best Practice: Combine Both Approaches #

For maximum reliability, use both application-level validation and database triggers:

• The application layer provides fast, user-friendly error messages.
• The database trigger acts as a safety net to prevent invalid data from slipping through.

Don't forget to handle update scenarios too: if someone modifies an existing record's date or hours, you'll need to recheck the daily total for both the old and new dates.

内容的提问来源于stack exchange，提问作者degath