I have next db.

CREATE TABLE DataLines(
    id        BIGSERIAL NOT NULL,
    TimeStamp TIMESTAMP NOT NULL,
    
    PRIMARY KEY (id, timestamp)
);

CREATE TABLE SpnValues(
    id         BIGSERIAL NOT NULL PRIMARY KEY                              ,
    valueInt   BIGINT    NOT NULL                                          ,
    dataLineId BIGSERIAL                                                   ,
    timestamp  TIMESTAMP NOT NULL                                          ,
    
    FOREIGN KEY (datalineid, timestamp) REFERENCES DataLines(id, timestamp)
);

When I try to insert some value in it, I recieve an error "Key is not present in table", but it is, I've cheked! I've even tried to write to spnvalues directly from datalines, but recieve the same error.

What's the problem?

UPD

I create hypertable from DataLines.

SELECT create_hypertable('datalines', 'timestamp');

CodePudding user response：

datalineid must be also NOT NULL, the same as timestamp.

CREATE TABLE SpnValues(
    id         BIGSERIAL NOT NULL PRIMARY KEY                              ,
    valueInt   BIGINT    NOT NULL                                          ,
    dataLineId BIGSERIAL NOT NULL                                          ,
    timestamp  TIMESTAMP NOT NULL                                          ,

    FOREIGN KEY (datalineid, timestamp) REFERENCES DataLines(id, timestamp)
);

CodePudding user response：

The reason may be that the TimeStamp value that you supply lacks precision. Internally timestamps are precise to the microsecond, i.e. your value literal must look like this '2021-12-15 14:19:20.248137'. So run the query below to extract all TimeStamp digits and retry.

select id, to_char(TimeStamp, 'yyyy-mm-dd hh24:mi:ss.us')
from datalines;

Sample output:

I tried

insert into SpnValues (valueInt, dataLineId, TimeStamp) 
  values (10, 2, '2021-12-14 14:19:20.248137');

and it works. With millisecond precision (3 digits) it fails.