0

Suppose I have a table with CREATE,

CREATE TABLE TEST (
    year int,
    month int, 
    date int, 
    hr int, 
    min int, 
    sec int, 
    timestamp timestamp,
    value double
);

CREATE FUNCTION timestamp_insert() RETURNS trigger
LANGUAGE plpgsql
AS $$

    DECLARE datestr TEXT ;
    DECLARE timestr TEXT ;
    begin
        datestr :=  new.year || '-' || new.month || '-' || new.date || ' ' ||  new.hour || ':' || new.min || ':' || new.sec;+ 
        new.timestamp := datestr :: TIMESTAMP                                                                      
        return new;
    end;
$$
CREATE TRIGGER timestamp_insert BEFORE INSERT OR UPDATE ON TEST FOR EACH ROW EXECUTE PROCEDURE timestamp_insert();

Now I want to update a certain row based on the timestamp currently on the table and that from a new set of data. Something like,

UPDATE TEST SET value = ? WHERE timestamp < "generated timestamp from a new set of (year, month, date, ...)"

Is it possible to do something like this in SQL or do I need to programmatically genrate the timestamp and then simply pass that value in SQL?

2
  • Why don't you just have only the timestamp column? Dec 13 '19 at 7:23
  • The data with which I'm populating the rows doesn't have ts field, only the bits and pieces. Dec 14 '19 at 2:29
2

There is a function named make_timestamp() that you can use for this. It also makes your trigger function a lot shorter:

CREATE FUNCTION timestamp_insert() 
  RETURNS trigger
  LANGUAGE plpgsql
AS 
$$
begin
    new.timestamp := make_timestamp(new.year, new.month, new.date, new.hour, new.min, new.sec);
    return new;
end;
$$

The same function can be used in the UPDATE as well

UPDATE TEST 
   SET value = ? 
WHERE timestamp < make_timestamp(year, month, date, hour, min, sec);

If you are using Postgres 12, you don't need the trigger as it now supports generated/calculated columns:

CREATE TABLE TEST 
(
  year int,
  month int, 
  date int, 
  hr int, 
  min int, 
  sec int, 
  ts timestamp generated always as (make_timestamp(year, month, date, hr, min, sec)) stored,
  value double precision
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.