Insert Substrings in Each Row on PostgreSQL

Question

create table phone_log(phone_number CHAR(20) NOT NULL,
                      area_code CHAR(10));

INSERT INTO phone_log(phone_number) 
     VALUES ('310-430-9201');
    
INSERT INTO phone_log(phone_number)
    VALUES ('917-829-2876');
    
INSERT INTO phone_log(phone_number)
    VALUES ('424-917-1055')

How do I insert the area code of each phone number using the SUBSTRING FUNCTION for each row?

I want the result to be like below:

So do you have your answer? – Erwin Brandstetter Jun 24 '23 at 03:58 — Erwin Brandstetter, Jun 24 '23 at 03:58

score 0 · Answer 1 · answered Jun 17 '23 at 03:24

0

Using the substring function update in existing row is done. try the below query: -

UPDATE phone_log
SET area_code = SUBSTRING(phone_number, 1, 3)
WHERE area_code IS NULL;

answered Jun 17 '23 at 03:24

Venkat

549
6

Erwin Brandstetter · Answer 2 · 2023-06-17T03:41:03.223

If the area_code can vary in length (which is usually the case), I suggest split_part():

INSERT INTO phone_log(phone_number, area_code)
SELECT nr, split_part(nr, '-', 1)
FROM  (
   VALUES
     ('310-430-9201')
   , ('917-829-2876')
   , ('424-917-1055')
   ) input(nr);

See:

Split comma separated column data into additional columns

If you must store the functionally dependent value for area_code redundantly (which I would try to avoid), consider a generated column:

CREATE TABLE phone_log2 (
  phone_log_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, phone_number text NOT NULL
, area_code    text GENERATED ALWAYS AS (split_part(phone_number, '-', 1)) STORED
);

Then your original simple INSERT commands just work.

See: