1

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.

7
  • 2
    Give some example data.The encoded data needs to be in the correct format
    – Philᵀᴹ
    Oct 22, 2016 at 14:42
  • some data like this YWJj - encoded data and some are like this test - non encoded data in same columns name. How can i handles it. Oct 24, 2016 at 4:35
  • Why did you change the chosen answer? Did my method not work for you? It should be a lot faster. Feb 3, 2017 at 19:24
  • ERROR: invalid byte sequence for encoding "UTF8": 0x00 SQL state: 22021 @EvanCarroll Feb 4, 2017 at 5:11
  • @SaddamKhan that has nothing to do with any answer, it's impossible to store NUL in a varlena (varchar/text) field in PostgreSQL because they're NUL terminated. Can you create a test case... How are you inserting that 0x00? Try this with the abc table in your question INSERT INTO abc (name) VALUES (chr(0)::varchar(500)); You will get ERROR: null character not permitted Feb 6, 2017 at 0:43

2 Answers 2

1
+50

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;
0
1

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; $$
1
  • Slower than my method below. I can't see any advantage to this. Feb 6, 2017 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.