Translate query from MSSQL to PostgreSQL

user_13929820 0
Viewed 46

This query update one column if UserName has only numbers.

UPDATE [dbo].[MyTable]
SET [MyColumn] = CAST(CASE 
            WHEN UserName LIKE '%[^0-9]%'
                THEN 0
            ELSE 1
            END AS BIT)

I need to retranslate it to postgreSql. It must be something like this

UPDATE "MySheme"."MyTable"
SET "MyColumn" = CAST(CASE 
            WHEN "UserName" LIKE '%[^0-9]%'
                THEN 0
            ELSE 1
            END AS BIT)
case sql-update postgresql sql
1 Answers

Consider:

update mytable
set mycolumn = (username !~ '\d')::int::bit

This produces a bit value of 1 if username does not contain any digit, else 0.

Note that Postgres supports the boolean datatype, that would probably make more sense here. This would also simply the query:

update mytable
set mycolumn = (username !~ '\d')

Note that I did not quote the identifiers. Quoted identifiers usually add no value, and make queries unnecessarily lengthy - I would recommend avoiding them in your set up.

user_106767160
Related