PostgreSQL Removing control characters from a string

1

PostgreSQL

We have some bytea columns that contain various control characters including nulls.

How can I remove them?

replace(column,CHR(0)," ") comes back with ERROR: null character not permitted.

regexp_replace(column::test, ‘[^ [:alnum:]]’,’ ',‘g’) doesn’t do anything.

Any ideas?

2

Refering to the postgres reference documentation …

Look at the regular expression functions in postgres:
replace (text, text, text )
translate (text, text, text )

regexp_instr(string, pattern)
regexp_substr(string, pattern)
regexp_replace(string, pattern, replacement)

Example:
SELECT regexp_replace(
E'Hello\x00World\x00!',
E'\x00',
'',
'g'
);

3

Hi Robert,

That give me:
ERROR: invalid byte sequence for encoding “UTF8”: 0x00

Regards

Jeremy

4

Hi Jeremy,

ERROR: invalid byte sequence for encoding “UTF8”: 0x00

Did you check the locale on the column in question?

5

No, how would I do that?

6

Hi Jeremy,

Try something along the following lines and let me know how it goes.

postgres=# SELECT id, label, my_column AS hex_before, octet_length(my_column) AS bytes_before FROM test_bytea;
 id |     label      |        hex_before        | bytes_before
----+----------------+--------------------------+--------------
  1 | Null in middle | \x48656c6c6f00576f726c64 |           11
  2 | Nulls at start | \x00005374617274         |            7
  3 | No nulls       | \x4e6f4e756c6c73         |            7
(3 rows)

postgres=# UPDATE test_bytea
SET my_column = decode(replace(encode(my_column, 'escape'), '\000', ''), 'escape')
WHERE id < 4;
UPDATE 3

postgres=# SELECT id, label, my_column AS hex_after, convert_from(my_column, 'UTF8') AS text_after, octet_length(my_column) AS bytes_after FROM test_bytea;
 id |     label      |       hex_after        | text_after | bytes_after
----+----------------+------------------------+------------+-------------
  1 | Null in middle | \x48656c6c6f576f726c64 | HelloWorld |          10
  2 | Nulls at start | \x5374617274           | Start      |           5
  3 | No nulls       | \x4e6f4e756c6c73       | NoNulls    |           7
(3 rows)
7

Jeremy,

In answer to the question of checking locales, here’s a little example. Basically, the challenge you are facing is that some characters cannot be stored by certain character sets and you might have a column incapable of storing the particular character.

EXAMPLE; REVIEWING LOCALES AND COLLATIONS

-- perform the following in a psql session
create database db01
create database db02 with encoding = 'utf8' lc_collate = 'C' lc_ctype = 'C' template template0;
create database db03 with encoding = 'sql_ascii' lc_collate = 'C' lc_ctype = 'C' template template0;

\l db0?
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
------+----------+-----------+-----------------+-------------+-------------+--------+-----------+-------------------
db01 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
db02 | postgres | UTF8 | libc | C | C | | |
db03 | postgres | SQL_ASCII | libc | C | C | | |

\c db01
create table t1(c1 int,c2 char(10), c3 text, c4 bytea);

\d t1

alter table t1
alter column c2 set data type char(10) collate "C",
alter column c3 set data type text collate "cv_RU";

db01=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
c1 | integer | | |
c2 | character(10) | C | |
c3 | text | cv_RU | |
c4 | bytea | | |

Note: collations are not supported by type bytea

REFERENCE:
select * from pg_collation;

Hope this helps :slight_smile:

8

The UTF8 encoding error’s probably the real clue here — bytea and text handle nulls totally differently, so text functions like regexp_replace might just be fighting the data format. Might be worth checking if you can work with this at the application level instead, or look into whether there’s a bytea-native approach rather than casting to text first.

9

We sorted it in the end by removing the nulls from the source.