PostgreSQL中如何存储比较条件并实现前端SE值匹配查询

Absolutely, there's a clean, maintainable pattern for storing these range-based mappings in PostgreSQL that works seamlessly for both database queries and front-end matching. Here's how to implement it:

This is the most flexible and easy-to-maintain approach—instead of hardcoding rules in SQL or front-end logic, you store the SE range rules in a dedicated mapping table.

1. Create the Mapping Table #

First, set up a table to store range boundaries and their corresponding results:

CREATE TABLE se_value_mappings (
    id SERIAL PRIMARY KEY,
    lower_bound NUMERIC, -- Inclusive lower limit, NULL means no lower bound
    upper_bound NUMERIC, -- Exclusive upper limit, NULL means no upper bound
    result_value VARCHAR(50) NOT NULL -- The matching result (foo, bar, etc.)
);

2. Insert Your Rule Data #

Add your four range rules into the table:

INSERT INTO se_value_mappings (lower_bound, upper_bound, result_value)
VALUES
    (NULL, 2, 'foo'),          -- SE < 2
    (2, 3, 'bar'),             -- 2 ≤ SE < 3
    (3, 4, 'foo2'),            -- 3 ≤ SE < 4
    (4, NULL, 'bar2');         -- 4 ≤ SE

3. Query Matches in PostgreSQL #

To get the corresponding result for a given SE value, use this query:

SELECT result_value
FROM se_value_mappings
WHERE
    (lower_bound IS NULL OR SE >= lower_bound)
    AND (upper_bound IS NULL OR SE < upper_bound);

Replace SE with your actual column name or input value—this will reliably match the correct range.

4. Front-End Matching Logic #

After fetching all mapping rules from the database (e.g., as a JSON array), you can use a simple function in the front end to match an SE value:

// Example mapping data fetched from the database
const seMappings = [
    { lowerBound: null, upperBound: 2, resultValue: 'foo' },
    { lowerBound: 2, upperBound: 3, resultValue: 'bar' },
    { lowerBound: 3, upperBound: 4, resultValue: 'foo2' },
    { lowerBound: 4, upperBound: null, resultValue: 'bar2' }
];

function getSeResult(seValue) {
    const match = seMappings.find(mapping => {
        const meetsLower = mapping.lowerBound === null || seValue >= mapping.lowerBound;
        const meetsUpper = mapping.upperBound === null || seValue < mapping.upperBound;
        return meetsLower && meetsUpper;
    });
    return match ? match.resultValue : null; // Return null or a default if no match
}

// Usage examples
console.log(getSeResult(1.5)); // Output: 'foo'
console.log(getSeResult(3.8)); // Output: 'foo2'
console.log(getSeResult(5));   // Output: 'bar2'

Why This Pattern Works Best #

• Maintainability: Update ranges or results by editing the table, no need to modify SQL or front-end code.
• Scalability: Add new ranges by inserting a single row—no changes to existing logic.
• Consistency: Both the database and front end use the same set of rules, eliminating mismatches.

Alternative: Hardcoded CASE Expression (Not Recommended) #

For one-off use cases, you could use a SQL CASE statement, but this is inflexible and requires duplicating logic in the front end:

SELECT
    CASE
        WHEN SE < 2 THEN 'foo'
        WHEN SE >= 2 AND SE < 3 THEN 'bar'
        WHEN SE >= 3 AND SE < 4 THEN 'foo2'
        WHEN SE >= 4 THEN 'bar2'
        ELSE NULL
    END AS result_value
FROM your_table;

This approach is prone to errors if rules change, so the mapping table is far better for long-term use.

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