CODEWITHSUNDEEP
postgresqlpostgresql-9.1
TheStoneFox
TheStoneFox

Reputation: 3067

postgres table partition on null date

I'm wondering if it's possible (and how to go about it if it is)

to partition a table in postgres 9.1 based on a date field being null or not null.

So effectively, I have a table and it gets quite large, I have a "delete_on" date in there and when items get deleted they just get tagged as deleted.

This would be ideal to archive them off and keep the active tables small.

I was thinking of a table partition and just having something like

active (delete_on = NULL)

archive (deleted_on != NULL)

Then when I do an update on a record, and set it's deleted on, it would get written to the archive table.

I could do it manually by just creating two separate tables and writing code to copy the data over, but I'd like it if I could just put it on the database directly.

Any ideas?

Edit:

Master table

CREATE TABLE my_table (
    id              int not null,
    deleted         date not null,
    number1         int,
    number2         int
);

Partitions

CREATE TABLE my_table_active (
  CHECK (deleted IS NULL)
) INHERITS (my_table);
CREATE TABLE my_table_archive ( 
  CHECK (deleted IS NOT NULL)
) INHERITS (my_table);

Trigger Function

CREATE OR REPLACE FUNCTION my_table_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.deleted IS NULL ) THEN
        INSERT INTO my_table_active VALUES (NEW.*);
    ELSE 
        INSERT INTO my_table_archive VALUES (NEW.*);

    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Create Trigger

CREATE TRIGGER insert_my_table_trigger
    BEFORE INSERT ON my_table
    FOR EACH ROW EXECUTE PROCEDURE my_table_insert_trigger();

(I've taken the above from the Postgres documentation)

How would this work if you're doing an update on a field that is already in the active table?

So I have a record in the active table (i.e. delete is null) then I set that record to be deleted (by giving it a date stamp). At that point i want it moved to the archive table.

Upvotes: 4

Views: 5829

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324841

You can partition on IS NULL and IS NOT NULL.

= NULL and != NULL both always result in NULL, so they conflict. You never write = NULL in SQL, always a IS NULL or a IS DISTINCT FROM b.

For the details of how to set up PostgreSQL's table-inheritance based partitioning and associated constraint exclusion, see the documentation.

There's no need to "write code to copy the data over" in any case. At worst you'd need to write a simple INSERT INTO newtable SELECT * FROM oldtable WHERE somefield IS NULL;.

Upvotes: 5

Related Questions