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