Postgresql Encoded and decoded

Question

I have a table abc, in which one column name where some data is encoded and some are not encoded. When I run the decode query the exception is found. How to handle it. There is a large number of rows in this table.

create table abc
(
id serial primary key,
name varchar(500)
)

select decode(name,'base64') from abc;

exception occurs: SQL state: 22023

Now how can I convert all non-encoded data to encoded? Or how can I handle this exception.

Answer 1

PostgreSQL uses POSIX regular expressions. If we had a regular expression, we could see if the input matched. If so feed it to decode, otherwise return it as-is. This question's answer provides us one such regex,

^(?:[A-Za-z0-9+/]{4})*(?:[A-Za-z0-9+/]{2}==|[A-Za-z0-9+/]{3}=)?$

Keep in mind, decode returns bytea. We explicitly cast name to bytea here because if it isn't Base64, we need to be sure we return the same type. This would look like this,

SELECT name, CASE
  WHEN name ~ '^(?:[A-Za-z0-9+/]{4})*(?:[A-Za-z0-9+/]{2}==|[A-Za-z0-9+/]{3}=)?$'
  THEN decode(name,'base64')
  ELSE name::bytea
END AS name_maybe_decoded
FROM abc;

This will return

1. name decoded as Base64, if the input looks to be Base64.
2. name as-is.

Try it..

SELECT name, CASE
  WHEN name ~ '^(?:[A-Za-z0-9+/]{4})*(?:[A-Za-z0-9+/]{2}==|[A-Za-z0-9+/]{3}=)?$'
  THEN decode(name,'base64')
  ELSE name::bytea
END AS name_maybe_decoded
FROM ( VALUES
  ('VGhpcyBpcyB0ZXN0IGRhdGE='),
  ('foobar'),
  ('SEFMTE8='),
  ('NOTBase64=')
) AS t(name);

Now remember, that's still the binary representation of the text, and the base64 decoded. You can output that as text using convert_from

WITH t2 AS (
  SELECT name, CASE
    WHEN name ~ '^(?:[A-Za-z0-9+/]{4})*(?:[A-Za-z0-9+/]{2}==|[A-Za-z0-9+/]{3}=)?$'
    THEN decode(name,'base64')
    ELSE name::bytea
  END AS name_maybe_decoded
  FROM ( VALUES
    ('VGhpcyBpcyB0ZXN0IGRhdGE='),
    ('foobar'),
    ('SEFMTE8='),
    ('NOTBase64=')
  ) AS t(name)
)
SELECT convert_from(name_maybe_decoded, 'UTF8')
FROM t2;

Answer 2

I might be way off because the question is extremely vague, but you could try something like this:

DO $$
DECLARE
x character varying[] := SELECT name FROM abc;
z record;
BEGIN
FOR z IN x
LOOP
    SELECT decode(z, 'base64');
EXCEPTION
    WHEN SQLSTATE '22023' THEN encode(z, 'base64');
END LOOP;
END; $$