Re: Primary Key Increment Doesn't Seem Correct Under Table Partition

[Yan Cheng Cheok <yccheok@xxxxxxxxx>]

Hello all,

I solve my problem using the following. It seems that when inherit from parent table, the parent table's constraint is not being 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;


      


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general