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

Insert Substrings in Each Row on PostgreSQL

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')

enter image description here

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:

enter image description here

Tags:

2

Answers


  1. 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;
    Login or Signup to reply.
  2. 0

    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:

    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:

    Either way, you do not want to use char(n), which is short for character(n). Use text or varchar(n) instead. See:

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search