Re: Primary Key Increment Doesn't Seem Correct Under Table Partition - Mailing list pgsql-general

Hello all,

I solve my problem using the following. It seems that when inherit from parent table, the parent table's constraint is
notbeing carried over to child table. 

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS
$BODY$DECLARE
    measurement_table_index bigint;
    measurement_table_name text;
BEGIN
    -- 20 is just an example here right now. The true value will be 100,000,000
    measurement_table_index = NEW.measurement_id % 20;
    measurement_table_name = 'measurement_' || measurement_table_index;

    -- Since measurement_id for parent table is already a bigserial
    -- Do I still need to create index for child's measurement_id?

    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = measurement_table_name) THEN
        EXECUTE 'CREATE TABLE ' || quote_ident(measurement_table_name) || '
        (
          CONSTRAINT pk_measurement_id_' || measurement_table_index || ' PRIMARY KEY (measurement_id),
          CONSTRAINT fk_unit_id_' || measurement_table_index || ' FOREIGN KEY (fk_unit_id)
              REFERENCES unit (unit_id) MATCH SIMPLE
              ON UPDATE NO ACTION ON DELETE CASCADE
        ) INHERITS (measurement);';
        EXECUTE 'CREATE INDEX ' || quote_ident(measurement_table_name) || '_measurement_id ON ' ||
quote_ident(measurement_table_name)|| '(measurement_id);';       
    END IF;

    EXECUTE 'INSERT INTO ' || quote_ident(measurement_table_name) || '(measurement_id, fk_unit_id, v) VALUES (' ||
NEW.measurement_id|| ',' || NEW.fk_unit_id || ',' || quote_literal(NEW.v) || ')'; 
    RETURN NULL;
END;$BODY$
LANGUAGE plpgsql;