postgresql,triggers,partitioning , PostgreSQL: update record on master table and move record in child partitions


PostgreSQL: update record on master table and move record in child partitions

Question:

Tag: postgresql,triggers,partitioning

How to define an update trigger function for Updating records in the master table which has partitions defined on it. I've a table which has partitions defined on it and code for the insert trigger function which will insert data in the child tables whenever there is an insert in the master table. The code I've is as follows:

    --Master table
        CREATE TABLE "SMS_RECEIPT"
        (
          "ID" integer NOT NULL,
          "ACCOUNT_INFO" character varying(255),
          "CHARGE" character varying(255),
          "DELIVERY_INFO" character varying(255),
          "DESTINATION" character varying(255),
          "MESSAGE_ID" character varying(255),
          "RECEIPT_TYPE" integer,
          "SMS_CENTRE" character varying(255),
          "SMS_ID" character varying(255),
          "SOURCE" character varying(255),
          "STATUS" character varying(255),
          timedate date,
          "FLAG" character varying(255),
          "STID" character varying(255),
          CONSTRAINT "SMS_RECEIPT_pkey" PRIMARY KEY ("ID")
        );

--child partition tables
        CREATE TABLE SMS_RECEIPT_y2015m01 (
        CHECK ( timedate >=  '2015-01-01' AND timedate <  '2015-01-31' )
         ) INHERITS ("SMS_RECEIPT");
         CREATE TABLE SMS_RECEIPT_y2015m02 (
        CHECK ( timedate >=  '2015-02-01' AND timedate <  '2015-02-28' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m03 (
        CHECK ( timedate >=  '2015-03-01' AND timedate <  '2015-03-31' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m04 (
        CHECK ( timedate >=  '2015-04-01' AND timedate <  '2015-04-30' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m05 ( 
        CHECK ( timedate >=  '2015-05-01' AND timedate <  '2015-05-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m06 ( 
        CHECK ( timedate >=  '2015-06-01' AND timedate <  '2015-06-30' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m07 ( 
        CHECK ( timedate >=  '2015-07-01' AND timedate <  '2015-07-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m08 ( 
        CHECK ( timedate >=  '2015-08-01' AND timedate <  '2015-08-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m09 ( 
        CHECK ( timedate >=  '2015-09-01' AND timedate <  '2015-09-30' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m010 ( 
        CHECK ( timedate >=  '2015-10-01' AND timedate <  '2015-10-31' )
        ) INHERITS ("SMS_RECEIPT");


        ALTER TABLE  SMS_RECEIPT_y2015m01 ADD CONSTRAINT  SMS_RECEIPT_y2015m01_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m02 ADD CONSTRAINT  SMS_RECEIPT_y2015m02_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m03 ADD CONSTRAINT  SMS_RECEIPT_y2015m03_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m04 ADD CONSTRAINT  SMS_RECEIPT_y2015m04_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m05 ADD CONSTRAINT  SMS_RECEIPT_y2015m05_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m06 ADD CONSTRAINT  SMS_RECEIPT_y2015m06_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m07 ADD CONSTRAINT  SMS_RECEIPT_y2015m07_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m08 ADD CONSTRAINT  SMS_RECEIPT_y2015m08_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m09 ADD CONSTRAINT  SMS_RECEIPT_y2015m09_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m010 ADD CONSTRAINT  SMS_RECEIPT_y2015m010_pkey PRIMARY KEY ("ID");


        CREATE INDEX idxSMS_RECEIPT_y2015m01_TIME_DATE ON SMS_RECEIPT_y2015m01 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m02_TIME_DATE ON SMS_RECEIPT_y2015m02 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m03_TIME_DATE ON SMS_RECEIPT_y2015m03 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m04_TIME_DATE ON SMS_RECEIPT_y2015m04 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m05_TIME_DATE ON SMS_RECEIPT_y2015m05 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m06_TIME_DATE ON SMS_RECEIPT_y2015m06 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m07_TIME_DATE ON SMS_RECEIPT_y2015m07 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m08_TIME_DATE ON SMS_RECEIPT_y2015m08 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m09_TIME_DATE ON SMS_RECEIPT_y2015m09 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m010_TIME_DATE ON SMS_RECEIPT_y2015m010 (timedate);


        CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
        RETURNS TRIGGER AS $$
        BEGIN
            IF ( NEW.timedate >=  '2015-01-01' AND NEW.timedate <  '2015-01-31' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-02-01' AND NEW.timedate <  '2015-02-28' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-03-01' AND NEW.timedate <  '2015-03-31' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-04-01' AND NEW.timedate <  '2015-04-30' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-05-01' AND NEW.timedate <  '2015-05-31' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-06-01' AND NEW.timedate <  '2015-06-30' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-07-01' AND NEW.timedate <  '2015-07-31' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-08-01' AND NEW.timedate <  '2015-08-31' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-09-01' AND NEW.timedate <  '2015-09-30' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-10-01' AND NEW.timedate <  '2015-10-31' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
            ELSE
                RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
            END IF;
            RETURN NEW;
        END;
        $$
        LANGUAGE plpgsql;


        CREATE TRIGGER trigger_SMS_RECEIPT_insert
            BEFORE INSERT ON "SMS_RECEIPT"
            FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger(); 

Now I have to write the update trigger function, In a way that whenever there is an update in the master table, the record should move across the partitions accordingly.

currently when I write the update statement on master table it throws me error as:

ERROR:  new row for relation "sms_receipt_y2015m03" violates check constraint "sms_receipt_y2015m03_timedate_check"
DETAIL:  Failing row contains (2, asdf, asdf, asdf, asdf, asdf, 1234, asdfasd, asdfasdf, adsfad, adsfasd, 2015-04-22, adsf, were).
********** Error **********

ERROR: new row for relation "sms_receipt_y2015m03" violates check constraint "sms_receipt_y2015m03_timedate_check"
SQL state: 23514
Detail: Failing row contains (2, asdf, asdf, asdf, asdf, asdf, 1234, asdfasd, asdfasdf, adsfad, adsfasd, 2015-04-22, adsf, were).

so, I've written a trigger function for before update as follows:

Create or replace function sms_receipt_func_update_trigger()
returns trigger as $$
declare total integer;
begin
        IF exists( select 1 from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID";

        ELSIF exists( select 1 from SMS_RECEIPT_y2015m02 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m02 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m03 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m03 where "ID"=OLD."ID";
        ELSIF exists( select 1 from SMS_RECEIPT_y2015m04 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m04 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m05 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m05 where "ID"=OLD."ID";
    ELSIF exists( select 1 from SMS_RECEIPT_y2015m06 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m06 where "ID"=OLD."ID";
        ELSIF exists( select 1 from SMS_RECEIPT_y2015m07 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m07 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m08 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m09 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m09 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m010 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m010 where "ID"=OLD."ID";
            ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;


create trigger trigger_sms_receipt_update 
before update on "SMS_RECEIPT"
for each row execute procedure
sms_receipt_func_update_trigger();

But, still I am not able to solve this issue. Can someone tell me how to modify the update trigger, so as to make this work. Thanks in advance.

Modified this, so to use the view and the instead of trigger. Now, it works for the insertion method. But, how to handle the Update thing?

   CREATE TABLE "SMS_RECEIPT"
(
  "ID" integer NOT NULL,
  "ACCOUNT_INFO" character varying(255),
  "CHARGE" character varying(255),
  "DELIVERY_INFO" character varying(255),
  "DESTINATION" character varying(255),
  "MESSAGE_ID" character varying(255),
  "RECEIPT_TYPE" integer,
  "SMS_CENTRE" character varying(255),
  "SMS_ID" character varying(255),
  "SOURCE" character varying(255),
  "STATUS" character varying(255),
  timedate date,
  "FLAG" character varying(255),
  "STID" character varying(255),
  CONSTRAINT "SMS_RECEIPT_pkey" PRIMARY KEY ("ID")
);

create view "sms_receipt_view" as select * from "SMS_RECEIPT";

CREATE TABLE SMS_RECEIPT_y2015m01 (
CHECK ( timedate >=  '2015-01-01' AND timedate <  '2015-01-31' )
 ) INHERITS ("SMS_RECEIPT");
 CREATE TABLE SMS_RECEIPT_y2015m02 (
CHECK ( timedate >=  '2015-02-01' AND timedate <  '2015-02-28' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m03 (
CHECK ( timedate >=  '2015-03-01' AND timedate <  '2015-03-31' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m04 (
CHECK ( timedate >=  '2015-04-01' AND timedate <  '2015-04-30' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m05 (
CHECK ( timedate >=  '2015-05-01' AND timedate <  '2015-05-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m06 (
CHECK ( timedate >=  '2015-06-01' AND timedate <  '2015-06-30' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m07 (
CHECK ( timedate >=  '2015-07-01' AND timedate <  '2015-07-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m08 (
CHECK ( timedate >=  '2015-08-01' AND timedate <  '2015-08-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m09 (
CHECK ( timedate >=  '2015-09-01' AND timedate <  '2015-09-30' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m010 (
CHECK ( timedate >=  '2015-10-01' AND timedate <  '2015-10-31' )
) INHERITS ("SMS_RECEIPT");


CREATE UNIQUE INDEX SMS_RECEIPT_unique ON "SMS_RECEIPT" USING btree ("ID");

ALTER TABLE  SMS_RECEIPT_y2015m01 ADD CONSTRAINT SMS_RECEIPT_y2015m01_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m02 ADD CONSTRAINT SMS_RECEIPT_y2015m02_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m03 ADD CONSTRAINT SMS_RECEIPT_y2015m03_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m04 ADD CONSTRAINT SMS_RECEIPT_y2015m04_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m05 ADD CONSTRAINT SMS_RECEIPT_y2015m05_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m06 ADD CONSTRAINT SMS_RECEIPT_y2015m06_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m07 ADD CONSTRAINT SMS_RECEIPT_y2015m07_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m08 ADD CONSTRAINT SMS_RECEIPT_y2015m08_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m09 ADD CONSTRAINT SMS_RECEIPT_y2015m09_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m010 ADD CONSTRAINT SMS_RECEIPT_y2015m010_pkey PRIMARY KEY ("ID");


CREATE INDEX idxSMS_RECEIPT_y2015m01_TIME_DATE ON SMS_RECEIPT_y2015m01 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m02_TIME_DATE ON SMS_RECEIPT_y2015m02 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m03_TIME_DATE ON SMS_RECEIPT_y2015m03 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m04_TIME_DATE ON SMS_RECEIPT_y2015m04 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m05_TIME_DATE ON SMS_RECEIPT_y2015m05 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m06_TIME_DATE ON SMS_RECEIPT_y2015m06 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m07_TIME_DATE ON SMS_RECEIPT_y2015m07 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m08_TIME_DATE ON SMS_RECEIPT_y2015m08 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m09_TIME_DATE ON SMS_RECEIPT_y2015m09 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m010_TIME_DATE ON SMS_RECEIPT_y2015m010 (timedate);


create or replace function sms_receipt_func_update_trigger()
returns trigger as $$
begin
    update "sms_receipt_view" set timedate = new.timedate where "ID"=new."ID";
return new;
end;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN

    IF ( NEW.timedate >=  '2015-01-01' AND NEW.timedate < '2015-01-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-02-01' AND NEW.timedate < '2015-02-28' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-03-01' AND NEW.timedate < '2015-03-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-04-01' AND NEW.timedate < '2015-04-30' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-05-01' AND NEW.timedate < '2015-05-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-06-01' AND NEW.timedate < '2015-06-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-07-01' AND NEW.timedate < '2015-07-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-08-01' AND NEW.timedate < '2015-08-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-09-01' AND NEW.timedate < '2015-09-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-10-01' AND NEW.timedate < '2015-10-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;    

CREATE TRIGGER trigger_SMS_RECEIPT_insert
    Instead of INSERT OR UPDATE ON "sms_receipt_view"
    FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

create trigger trigger_sms_receipt_update
instead of update on "sms_receipt_view"
for each row execute procedure sms_receipt_func_update_trigger();

Answer:

The problem is that with the trigger you need to return the old or the new record.

You need an instead of trigger. But it is available only on views. So you need to:

See the table here: http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html

I tested the code and found few thing to fix. Here we go.
First of all, I inserted a record to test:

INSERT INTO sms_receipt_view(
        "ID", "ACCOUNT_INFO", "CHARGE", "DELIVERY_INFO", "DESTINATION", 
        "MESSAGE_ID", "RECEIPT_TYPE", "SMS_CENTRE", "SMS_ID", "SOURCE", 
        "STATUS", timedate, "FLAG", "STID")
VALUES (1, 'acc_info', 'charge', 'delivery info', 'my dest', 
        'ssss id mess', 4, 'vodafone center', 'dff33', '3333 3 33333', 
        'ok', '20150601 15:25', 'myflag', 'stid');

Second, the insert trigger must be fired just on insert, so i run the code:

DROP TRIGGER trigger_sms_receipt_insert ON sms_receipt_view;

CREATE TRIGGER trigger_sms_receipt_insert
  INSTEAD OF INSERT
  ON sms_receipt_view
  FOR EACH ROW
  EXECUTE PROCEDURE sms_receipt_func_insert_trigger();

And then I rewrote the update trigger:

CREATE OR REPLACE FUNCTION sms_receipt_func_update_trigger()
  RETURNS trigger AS
$BODY$
begin
    --update "sms_receipt_view" set timedate = new.timedate where "ID"=new."ID";
    --check if I need to move record
    IF date_part('year', OLD.timedate) <> date_part('year', NEW.timedate) OR date_part('month', OLD.timedate) <> date_part('month', NEW.timedate) THEN
    DELETE FROM "SMS_RECEIPT" WHERE "ID" = NEW."ID";

    INSERT INTO sms_receipt_view ("ID", "ACCOUNT_INFO", "CHARGE", "DELIVERY_INFO", "DESTINATION", "MESSAGE_ID", "RECEIPT_TYPE",
       "SMS_CENTRE", "SMS_ID", "SOURCE", "STATUS", timedate, "FLAG", "STID")
    VALUES (NEW."ID", NEW."ACCOUNT_INFO", NEW."CHARGE", NEW."DELIVERY_INFO", NEW."DESTINATION", NEW."MESSAGE_ID", NEW."RECEIPT_TYPE",
       NEW."SMS_CENTRE", NEW."SMS_ID", NEW."SOURCE", NEW."STATUS", NEW.timedate, NEW."FLAG", NEW."STID"
    );
    ELSE
        UPDATE "SMS_RECEIPT"
        SET "ACCOUNT_INFO" = NEW."ACCOUNT_INFO",
        "CHARGE" = NEW."CHARGE",
        "DELIVERY_INFO" = NEW."DELIVERY_INFO",
        "DESTINATION" = NEW."DESTINATION",
        "MESSAGE_ID" = NEW."MESSAGE_ID",
        "RECEIPT_TYPE" = NEW."RECEIPT_TYPE",
        "SMS_CENTRE" = NEW."SMS_CENTRE",
        "SMS_ID" = NEW."SMS_ID",
        "SOURCE" = NEW."SOURCE",
        "STATUS" = NEW."STATUS",
        timedate = NEW.timedate,
        "FLAG" = NEW."FLAG",
        "STID" = NEW."STID"
        WHERE "ID" = NEW."ID";
    END IF;
return NULL;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sms_receipt_func_update_trigger()
  OWNER TO postgres;

First I check if we really need to move the record. If not, I simply update via master table. If I need to move I delete old record and insert the new record in the view (so I trigger the insert view).
Warning: in my simulation i supposed key never change. If you need to update the id you will need to adapt some code.

Then I tested the 2 case. In my scenario id was: 1

UPDATE sms_receipt_view
SET "SMS_CENTRE" = 'tim center'
--timedate = '20150501'
WHERE "ID" = 1;

UPDATE sms_receipt_view
SET "SMS_CENTRE" = 'tim center',
timedate = '20150501'
WHERE "ID" = 1;

Make some test, but should work.


Related:


Spring Boot - How to set the default schema for PostgreSQL?


java,spring,hibernate,postgresql
What I am currently doing in my application.properties file is: spring.datasource.url=jdbc:postgresql://localhost:5432/myDB?currentSchema=mySchema Isn't there another property for this? Since it looks hacky and according to a post (still finding the link sorry, will update later), it is only applicable to PostgreSQL 9.4....

Postgres SQL constraint a character type


postgresql,constraints
I have a table definition in Postgres. I would like to add a constraint to a column that is of Character datatype to have only 3 allowed values: CREATE TABLE my_table ( id character varying(255) NOT NULL, uid character varying(255) NOT NULL, my_text text NOT NULL, is_enabled boolean NOT NULL...

Capping values after a trigger level in a different variable _after GroupBy


pandas,triggers,group-by
There was an elegant answer to a question almost like this provided by EdChum. The difference between that question and this is that now the capping needs to be applied to data that had had "GroupBy" performed. Original Data: Symbol DTE Spot Strike Vol AAPL 30.00 100.00 80.00 14.58 AAPL...

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders


postgresql
In SQL systems other than Postgres, such as MySQL for instance, prepared statements can use question marks ? as a placeholder for data in prepared statements. INSERT INTO foo (id, name) VALUES (?, ?), (?, ?); However, in Postgres the only available placeholders seem to be the numbered placeholders, so...

Cancel DELETE on MySQL REPLICATE


mysql,triggers,replication
I'm looking for a way to prevent DELETE statement on a MySQL replication Master/Slave. In my case, the Master is a live database, with fresh entries (not older than a week), and the Slave is an archive database which must contains all entries. I have several problems with my test:...

postgresql mathematical formula error


postgresql
Hi I am trying to use a mathematical function on each row in postgresql. But It gives me a error. My Query: Select stock_inventory_line.product_code AS Sku, COUNT(sale_order_line.name) AS Qty_Sold, stock_inventory_line.product_qty AS Current_Qty, (stock_inventory_line.product_qty / Qty_Sold) AS NOM From sale_order_line, product_product, product_template, product_category, stock_inventory_line WHERE sale_order_line.product_id = product_product.id AND product_product.product_tmpl_id =...

Subtract two columns of different tables


sql,postgresql,sum,aggregate-functions,subtract
I have two unrelated tables: contribution(id,amount, create_at, user_id) solicitude(id, amount, create_at, status_id, type_id, user_id) I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative. How can I do this? Function or query? I tried...

Fire trigger from DocumentDB stored procedure


stored-procedures,triggers,azure-documentdb
We have a stored procedure that is modeled after the bulkImport sproc in the DocumentDB sample code. This sproc takes an array of documents, does some stuff, and eventually calls createDocument, which the documentation says is asynchronous. Now we have written a pre-trigger for Create that checks some fields and...

How to get second row in PostgreSQL?


sql,postgresql
I have next table: COLUMN_NAME DATA_TYPE PK NULLABLE inc_id bigint YES NO dt_cr timestamp NO NO email varchar(255) NO YES email column is not unique, so I should use GROUP BY email. Question: How can I get from this table minimal date, next row after minimal date and email related...

How to order SQL query result on condition?


sql,postgresql,order,condition
i have the this SQL query: SELECT DISTINCT category FROM merchant ORDER BY category ASC that gives this output: accommodation education food general health money shopping sport transport How to put the row that contains "general" at the start (or the end) of the result?...

MySQL trigger help, capturing a variable from php insert into


php,mysql,triggers
I am writing a MySQL trigger to encrypt a column of sensitive data using RSA plugin. There is an insert statement from the website which is PHP based $sql = "insert into redcap_data (project_id, event_id, record, field_name, value) values ($project_id, " . getEventId() . ", '" . prep($_GET['id']) . "',...

What happens with duplicates when inserting multiple rows?


sql,postgresql,exception,duplicates,upsert
I am running a python script that inserts a large amount of data into a Postgres database, I use a single query to perform multiple row inserts: INSERT INTO table (col1,col2) VALUES ('v1','v2'),('v3','v4') ... etc I was wondering what would happen if it hits a duplicate key for the insert....

postgres: using previous row value when current row value is null


postgresql
In my app I need to produce a data output (to be then converted to JSON) for the data in a postgres database (which is actually temperature data gathered by sensors). The data is stored in device_history_log as follows (for data between 11am and noon): row_id;deviceid;sensor_value_raw;last_update 104401;20865735;21.56;"2015-06-10 11:00:14" 104432;493417852;23.9;"2015-06-10 11:00:58"...

Connecting C++ and Postgresql


c++,postgresql,linker-error,libpqxx
I'm trying to connect C++ to Postgres. I recently installed libpqxx with Homebrew as follows: brew install libpqxx Then I made it with: make DatabaseTest on the file DatabaseTest.cpp. I ran the following simple program and got a strange error that I'm not sure what to do with... #include <iostream>...

Postgres Index-only-scan: can we ignore the visibility map or avoid heap fetches?


postgresql,indexing
Sorry, lots of context before the actual question as we've throughly researched this and I wanted to give you full context. Some context: postgres index-only-scans rely on the visibility map (VM). If a page is not marked as not-fully-visible in the visibility map, postgres fetches that page to ensure the...

How to create a SELECT query FROM “TABLE1 AND TABLE2”


sql,postgresql,select,join
I have a PostgreSQL database, with only SELECT permissions. In this DB there are two tables with the same structure (the same columns). I need to write several query in each table and join the results. There is a way for writing a query like this one? SELECT field1, field2,...

postgresql complex group by in query


sql,postgresql
I have a query that gives: itemid deadlineneeded delievrydate quantity 200 15/07/15 14/07/15 5 200 15/07/15 14/07/15 10 200 15/07/15 13/07/15 25 201 15/07/15 14/07/15 30 200 14/07/15 10/07/15 3 201 15/07/15 15/07/15 100 It gives the information from multiple tables. Basically it means When items arrive to warehouse (delievrydate)...

How to delete replication slot in postgres 9.4


postgresql,replication,postgresql-9.4
I have replication slot which I want to delete but when I do delete I got an error that I can't delete from view. Any ideas? postgres=# SELECT * FROM pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn --------------+--------------+-----------+--------+----------+--------+------+--------------+-------------...

Redirect if ActiveRecord::RecordNotUnique error exists


ruby-on-rails,postgresql,activerecord,error-handling
I have the next code to save: Transaction.create(:status => params[:st], :transaction_id => params[:tx], :purchased_at => Time.now).save! But how can I redirect to main root page if this ActiveRecord::RecordNotUnique error appears? Can I catch this error?...

group by is not working in postgreSQL


sql,postgresql,group-by
I want group my data by createdAt but it is not working I don't know why... Only group by id which is useless for me. This is working: SELECT "id", "createdAt", "updatedAt" FROM "tables" AS "Table" WHERE "Table"."createdAt" BETWEEN '2014-04-21 20:46:25.938-04' AND '2015-04-21 20:46:25.938-04' GROUP BY "id"; This is what...

Speed up Min/Max operation on postgres with index for IN operator query


postgresql,postgresql-9.3
I would like to optimize the following query in postgres SELECT(MIN("products"."shipping") AS minimal FROM "products" WHERE "products"."tag_id" IN (?) with an index like CREATE INDEX my_index ON products (tag_id, shipping DESC); Unfortunately this one is only used when it's just one tag. Almost alwayst it is queried for a handful...

How to customize the configuration file of the official PostgreSQL docker image?


postgresql,docker
I'm using the the postgres official image https://registry.hub.docker.com/_/postgres/. And now I'm trying to customize its configuration. For this purpose the command sed is used, e.g. to change the max_connections: sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" /var/lib/postgresql/data/postgresql.conf I tried two methods to apply this configuration. The first is by adding...

need help specifying potentially reserved words as strings in postgres query


postgresql
I've been using mysql for close to 10 years, but have recently jumped into a project that's exposed me to postgres for the first time. Most of the differences aren't a bit deal, but I have been running into some small issues along the way that are throwing me off....

Save a hex-string to PostgreSQL column character varying


postgresql,hex
I want to save hex-string ('A2-5A-47-00-10-00-00-00') into a PostgeSQL database. My column is character varying(30). How can I do this?...

Return integer value of age(date) function in Postgres


postgresql
I would like to determine the number of days that an account has been open. Ideally, I would like to compare the return value to an integer(days). i.e I would like to see if age(open_date) > 14 If anyone has any better ideas... Thanks...

Error while trying to insert data using plpgsql


postgresql,timestamp,plpgsql
I have the following plpgsql function: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) RETURNS bigint AS $BODY$ DECLARE current_time timestamp with time zone = now(); BEGIN INSERT INTO "TEST"( created) VALUES (current_time) RETURNING id INTO pid; END $BODY$ LANGUAGE plpgsql; select * from test_func(); The above gives an error: column...

In PostgreSQL is it possible to join between table and function?


sql,postgresql
I have a table named Cars and a function named PartsPerCar there is no direct link between carid and partid. the link is though 3 tables in the middle. I do have a function called PartsPerCar which gets carid (it goves via the tables in the middle) and give back...

Is there a better way to write this query involving a self select?


sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

Is there a better solution to join the same dataset to get the distinct count of a dimension used for aggregation?


sql,postgresql
I have to do an aggregation on certain measures, but i also have to say how much distinct values i have for a dimension, which i use for aggregation. I can solve the problem using two sub-selects, but performance is not the best. Is there any way to solve this...

How to check what constraint has been violated?


java,sql,postgresql,exception
I'm used to check if the user try to store a valid data with cathcing the ConsratinViolationException, like that: try { //persisitng to a db } catch (ConstraintViolationException e){ //Print message } I'm using PostgreSQL and now I'm under the isssue that the persisitng can violate more than one different...

what is the SQL prepared stament for lo_import in postgreSQL


c++,sql,database,postgresql,odbc
In postgreSQL we have option to import the file into database using lo_import method, which returns a OID using that we can export the file from database to the filesystem. eg: describe test table name text, file_id oid insert into test values('arul_test',lo_import('/home/arul/test.txt')); prepared statement would be insert into test values(?,?);...

Syntax error while creating table in PostgreSQL 8.1


postgresql
I have a syntax error in this query: CREATE TABLE test (LIKE original_table INCLUDING INDEXES); ERROR : syntax error at or near "INDEXES" I am using PostgreSQL 8.1 version. Where is the issue in my query?...

Translation of interval


postgresql,datetime,translation,intervals,postgresql-8.4
There is maybe a way to translate automatically interval as it's shown by postgresql (e.g. "1330 days 10:54:54.266684") to other languages? For instance using locales, or other settings or I have to use replace, e.g. regexp_replace((now()-t.another_date)::text, 'day','dzień')?

JSONB: more than one row returned by a subquery used as an expression


sql,postgresql,postgresql-9.4,jsonb,set-returning-functions
I am (still) new to postgresql and jsonb. I am trying to select some records from a subquery and am stuck. My data column looks like this (jsonb): {"people": [{"age": "50", "name": "Bob"}], "another_key": "no"} {"people": [{"age": "73", "name": "Bob"}], "another_key": "yes"} And here is my query. I want to...

Avoid calling COUNT twice in CASE expression (PostgreSQL)


sql,postgresql
Inside a larger query, I have to COUNT a variable, then if it is larger than 1, have the count as a string otherwise an empty string: CASE COUNT(measurement.id) > 1 THEN to_char(COUNT(measurement.id),' 999') ELSE '' I'm afraid this is slow because I use COUNT() twice. Is there a better...

How to install / use orafce package in postgresql 9.4?


postgresql,debian,orafce
I'm running the "official" docker container of postgresql in version 9.4. I went inside the running container and installed orafce docker exec -i -t my_postgres bash apt-get install postgresql-9.4-orafce afterwards I've tried to reload and restart the postgresql service, as well as just restarting the whole container, but when I...

How to use Rails #update_attribute with array field?


ruby-on-rails,ruby,postgresql,ruby-on-rails-4,activerecord
Each question has an array of tags. schema.rb: create_table "questions", force: true do |t| t.text "tags", default: [], array: true How to atomically append to tags? How to prevent dups within the array? I tried question.update_attribute tags: tags << :ruby, but this doesn't work. Rails 4.17 and Postgres. EDIT: This...

Update enum column in Laravel migration using PostgreSQL


postgresql,laravel,laravel-5,laravel-migrations
According to this answer, I have to run a raw query if I want to update an enum in MySQL. But with PostgreSQL, I can't use this query, and enum type for PostgreSQL in Laravel seems strange. Is there any way to update enum in a migration for postgreSQL ?...

Sqoop Export with Missing Data


sql,postgresql,shell,hadoop,sqoop
I am trying to use Sqoop to export data from HDFS into Postgresql. However, I receive an error partially through the export that it can't parse the input. I manually went into the file I was exporting and saw that this row had two columns missing. I have tried a...

Trigger to find next available inventory location


oracle,triggers,inventory
I am trying to implement inventory tracking and am running into problems. As this is my first foray into database triggers (& PL/SQL in general) I think I need an adjustment to my thinking/understanding of how to solve this issue. My situation is as follows: Each time a new item...

PostgreSQL conditional statement


sql,postgresql,if-statement
I need to implement a conditional SELECT statement based on the result of another statement. I don't want to create a function for this purpose, but simply using conditional SELECT. My unsuccessful approach looks as below: DO $do$ BEGIN IF SELECT count(*) FROM table1 < 1 THEN SELECT * FROM...

Oracle 11g Insert Statement into Multiple Tables


sql,oracle,oracle11g,triggers,sql-insert
I am currently having trouble trying to execute multiple statements at the same time. I keep getting this error when trying to run the following INSERT statements: INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE)); INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id,...

Postgresql Update JDBC


java,postgresql,jdbc
I want to update table: id integer NOT NULL, "first" character varying(255), "last" character varying(255), age integer, CONSTRAINT registration_pkey PRIMARY KEY (id) using method: void updateTable(String tableName, String columnName, String value, String columnName2, String value2) { try { String sql = "UPDATE " + tableName + " SET " +...

name of value returned from PostgreSQL function


postgresql,plpgsql
I'm working on some code that talks to a PostgreSQL server for the first time (my experience is with SQL Server) and I need help figuring something out. I've always tried to avoid "SELECT * ..." from code, but I can't figure out what the alternative is when calling a...

PostgreSQL: trigger to call function with parameters


postgresql
In PostgreSQL, I'm trying to create a trigger that passes an argument to a function, but it appears that this operation looks for a function whose signature has zero arguments: ERROR: function create_rec_if_needed() does not exist: CREATE TRIGGER after_update_winks AFTER UPDATE ON winks FOR EACH ROW WHEN (NEW.counter > 3)...

Retrieve updated rows in AFTER UPDATE trigger Postgresql


postgresql,triggers,plpgsql
I'm writing a AFTER UPDATE trigger in postgresql. Actualy I need to get at least one row after update in STATEMENT LEVEL trigger, but there is no OLD or NEW variable there. In FOR EACH ROW trigger I didn't manage to find a sort of batch_last param. But to do...

JPA NamedNativeQuery syntax error with Hibernate, PostgreSQL 9


java,hibernate,postgresql,jpa
I am having a rather odd problem. I have native query which runs perfectly when executed on sql server: SELECT date_time, GREATEST(sum(count_up - count_down) OVER (PARTITION BY date_trunc('day', result.date_time) ORDER BY date_time),0) AS cum_amt FROM peoplecounting.result order BY date_time; However, using this query as native query in JPA results in...

PostgreSQL can't find column


java,postgresql
I create a database that contains 4 column (ID_PRODUIT,NOM_PRODUIT,QUANTITE, PRIX).I tried to make a methods that allow me to serach Product(my class)using a string key,however, the query failed to identify the "NOM_PRODUIT" column and it turn "NOM_PRODUIT" to "nom_produit" in the messsage error. Connection conn = DriverManager.getConnection(url, user, passwd); PreparedStatement...

How to group following rows by not unique value


sql,postgresql,greatest-n-per-group,window-functions,gaps-and-islands
I have data like this: table1 _____________ id way time 1 1 00:01 2 1 00:02 3 2 00:03 4 2 00:04 5 2 00:05 6 3 00:06 7 3 00:07 8 1 00:08 9 1 00:09 I would like to know in which time interval I was on which...