PostgreSQL中如何在JSONB列的增改请求中添加创建/更新时间

Got it, let's tackle this problem! In PostgreSQL, triggers are the ideal way to replicate that automatic timestamp behavior you'd get with standard timestamp columns, but for your jsonb field. Here's a step-by-step implementation that fits your needs:

Step 1: Create a Trigger Function #

First, we'll write a PL/pgSQL function that handles both INSERT and UPDATE operations, modifying the json_data column to add the required timestamps without messing with existing data you might pass in.

CREATE OR REPLACE FUNCTION set_json_timestamps()
RETURNS TRIGGER AS $$
BEGIN
  -- Handle INSERT: Add created_at only if the input doesn't already include it
  IF TG_OP = 'INSERT' THEN
    IF NOT NEW.json_data ? 'created_at' THEN
      NEW.json_data = NEW.json_data || jsonb_build_object('created_at', CURRENT_TIMESTAMP);
    END IF;
  END IF;

  -- Handle UPDATE: Add last_update_time, and preserve existing created_at
  IF TG_OP = 'UPDATE' THEN
    -- Add last_update_time unless the user provided their own
    IF NOT NEW.json_data ? 'last_update_time' THEN
      NEW.json_data = NEW.json_data || jsonb_build_object('last_update_time', CURRENT_TIMESTAMP);
    END IF;
    -- Keep the original created_at if it exists and isn't overwritten in the update
    IF OLD.json_data ? 'created_at' AND NOT NEW.json_data ? 'created_at' THEN
      NEW.json_data = NEW.json_data || jsonb_build_object('created_at', OLD.json_data->'created_at');
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

What this function does: #

• Uses TG_OP to detect if we're dealing with an INSERT or UPDATE operation
• For inserts: Adds a created_at field with the current timestamp only if you didn't already include it in your input JSON
• For updates: Adds a last_update_time timestamp (again, only if you didn't provide one) and ensures the original created_at value stays intact unless you explicitly overwrite it
• Uses jsonb_build_object to create timestamp entries and the || operator to merge them into your existing JSONB data

Step 2: Attach Triggers to Your Table #

Next, we'll create two triggers (one for INSERT, one for UPDATE) that run this function before each row is modified:

-- Trigger for INSERT operations
CREATE TRIGGER trigger_json_insert_timestamps
BEFORE INSERT ON data
FOR EACH ROW
EXECUTE FUNCTION set_json_timestamps();

-- Trigger for UPDATE operations
CREATE TRIGGER trigger_json_update_timestamps
BEFORE UPDATE ON data
FOR EACH ROW
EXECUTE FUNCTION set_json_timestamps();

Step 3: Test It Out #

Let's verify this works as expected:

1. Insert a row:

INSERT INTO data (json_data) VALUES ('{"item": "laptop", "price": 999}');

Query the row, and you'll see the created_at field automatically added:

SELECT json_data FROM data;
-- Output: {"item": "laptop", "price": 999, "created_at": "2024-05-20T14:30:00.123456"}

2. Update the row:

UPDATE data SET json_data = json_data || '{"price": 899}' WHERE id = 1;

Now the row will have both created_at and the new last_update_time:

SELECT json_data FROM data;
-- Output: {"item": "laptop", "price": 899, "created_at": "2024-05-20T14:30:00.123456", "last_update_time": "2024-05-20T14:35:00.789012"}

Optional Adjustments #

• If you want the triggers to always overwrite any user-provided created_at or last_update_time, just remove the IF NOT NEW.json_data ? 'key' checks from the function
• To use UTC timestamps instead of your server's local time, replace CURRENT_TIMESTAMP with CURRENT_TIMESTAMP AT TIME ZONE 'UTC'

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