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

Postgresql – How to transfer string from one column to another if it doesn’t contains substring

In my Postgres table I’ve got two columns, one of them sometimes have strings, the other one is empty, but some strings from first column sometimes has substrings, I want to move data from first column to another if it doesn’t contain substring.

What I have:

col1 col2
some string null
some string null
some string with substring null
some string null
null null
some string null

What I want it to be:

col1 col2
null some string
null some string
some string with substring null
null some string
null null
null some string

I don’t want it to be selected, I want to update all table, because back then this column was used by TWO INPUTS on web-interface, and users puts there completely different information, but there is a pattern of substring that I found, that can help to separate data from one column to another. I’m completely new for PostgreSQL, so I need your advices.

I was trying to do it like this:

UPDATE table
SET col2 = col1, col1=NULL
WHERE LOWER(col1) NOT LIKE ‘%substring%’

But for some reason, it just moves all data from col1 to col2

Tags:

2

Answers


  1. 0

    You can use a CASE expression to update the table.

    Only update when col1 is LIKE '%substring%'

    Fiddle

    UPDATE test
SET col2 = CASE 
              WHEN lower(col1) NOT LIKE '%substring%' THEN col1 
              ELSE col2 
           END,
    col1 = CASE 
              WHEN lower(col1) NOT LIKE '%substring%' THEN col2
              ELSE col1 
           END;

    Output

    col1 col2
    null some string
    null some string
    some string with substring null
    null some string
    null null
    null some string
    Login or Signup to reply.
  2. 0

    An often overlooked capability of the update statement is to simply switch values between columns. So all you need is something like: (see demo)

    update tab 
   set col2 = col1
     , col1 = null 
 where position('substring' in lower(col1)) = 0;

    This works because SQL keeps a copy of the old row values throught the update. The position() function determines if the substring exists within original column. Its returns initial place where the substring starts.

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

Back To Top
Search