Question 89989 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL – Updating a column value based on another value

I have been trying to accomplish an automatic update of a table based on a column value. Here is the example. The idea is that the user will enter their phone, but not their country like this:

mobile_phone country
‘51682815’
‘51261728’
‘56682815’
‘56261728’
‘57682815’
‘57261728’

Based on the "mobile_phone" information I want to assign their country automatically with this logic:

  • WHERE left(mobile_phone,2) = ’51’ then ‘PE’
  • WHERE left(mobile_phone,2)=’56’ then ‘CL’
  • WHERE left(mobile_phone,2)=’57’ then ‘CO’

And based on that logic the table should update AUTOMATICALLY with this information:

mobile_phone country
‘51682815’ ‘PE’
‘51261728’ ‘PE’
‘56682815’ ‘CL’
‘56261728’ ‘CL’
‘57682815’ ‘CO’
‘57261728’ ‘CO’

I was thinking to create a trigger like this, but the problem is that I don’t know how to create the function that will update the column country:

CREATE TRIGGER update_country AFTER INSERT ON info
FOR EACH ROW EXECUTE PROCEDURE function_name()

Thanks in advance for your help.

Tags:

2

Answers


  1. 0

    Since your records to be updated are already found within your database, you should first use an UPDATE statement to update your current rows. You can do it using a CASE expression that will assign the "country" value conditionally with respect to the "mobile_phone".

    UPDATE "info"
SET country = CASE WHEN LEFT(mobile_phone,2) = '51' THEN 'PE'
                   WHEN LEFT(mobile_phone,2) = '56' THEN 'CL'
                   WHEN LEFT(mobile_phone,2) = '57' THEN 'CO' END;

    Then create the corresponding trigger, that will update your record before the insertion. It splits into:

    • the function "update_country()", which returns a trigger that uses NEW to access values from the inserting row and update its country value
    • the trigger creation definition (which you included in the post as well), that uses "update_country()" to update records featured in the insertion statement.
    CREATE OR REPLACE FUNCTION update_country()
RETURNS TRIGGER AS
'
BEGIN
    NEW.country = CASE WHEN LEFT(NEW.mobile_phone,2) = ''51'' THEN ''PE''
                       WHEN LEFT(NEW.mobile_phone,2) = ''56'' THEN ''CL''
                       WHEN LEFT(NEW.mobile_phone,2) = ''57'' THEN ''CO'' END;
    RETURN NEW;
END;
'
LANGUAGE plpgsql;

CREATE TRIGGER update_country_trigger
BEFORE INSERT ON "info"
FOR EACH ROW
EXECUTE PROCEDURE update_country();

    Check the demo here.

    Login or Signup to reply.
  2. 0

    You can use Generated Columns.
    With Generated Columns you can not do
    update info set country = 'x'; or
    insert into info(country) value ('x');
    https://www.postgresql.org/docs/current/ddl-generated-columns.html

    BEGIN;
CREATE temp TABLE info (
    mobile_phone text
    , country text GENERATED ALWAYS AS ( CASE WHEN
    LEFT (mobile_phone , 2) = '51' THEN
        'PE'
    WHEN
    LEFT (mobile_phone , 2) = '52' THEN
        'XY'
    WHEN
    LEFT (mobile_phone , 2) = '53' THEN
        'AB'
    WHEN
    LEFT (mobile_phone , 2) = '54' THEN
        'CD'
    WHEN
    LEFT (mobile_phone , 2) = '55' THEN
        'EF'
    WHEN
    LEFT (mobile_phone , 2) = '56' THEN
        'CL'
    WHEN
    LEFT (mobile_phone , 2) = '57' THEN
        'CO'
    ELSE
        'NA'
    END) STORED
) ON COMMIT DROP;
INSERT INTO info (mobile_phone)
    VALUES ('51682815')
    , ('51261728')
    , ('56682815')
    , ('56261728')
    , ('57682815')
    , ('57261728');
TABLE info;
END;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search