By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE cmdb (
hostname text,
mgmt_ip text,
os_type text);
CREATE TABLE cmdb_attic (
hostname text,
mgmt_ip text,
os_type text);
CREATE FUNCTION redirect_to_attic() RETURNS TRIGGER
AS $$
BEGIN
IF NEW.mgmt_ip = '' THEN
INSERT INTO cmdb_attic VALUES (NEW.*);
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER redirect
BEFORE INSERT
ON cmdb
FOR EACH ROW
EXECUTE PROCEDURE redirect_to_attic();
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT INTO cmdb(hostname, mgmt_ip, os_type) VALUES
('testdevice a', '', 'ios'),
('testdevice b', '127.0.0.1', 'android');
TABLE cmdb;
TABLE cmdb_attic;
INSERT 0 1
| hostname | mgmt_ip | os_type |
|---|---|---|
| testdevice b | 127.0.0.1 | android |
SELECT 1
| hostname | mgmt_ip | os_type |
|---|---|---|
| testdevice a | ios |
SELECT 1