1

I dumped a database and imported it into a different server. One of the tables has a bytea column and has a single row of data. On the original server, if I SELECT * FROM users;, it shows the correct value as @. - however, when I do that same select statement on the second server, I get \x402e for that same field. I have tried to wrap my head around this column type but it is over my head. Why would it appear as an escaped string on one server but not the other? Both servers are running Pg11 and I am accessing both via psql.

Original Server:

=# \d+ users
                                                         Table "public.users"
  Column   |          Type          | Collation | Nullable |              Default              | Storage  | Stats target | Description 
-----------+------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id        | integer                |           | not null | nextval('users_id_seq'::regclass) | plain    |              | 
 priority  | integer                |           | not null | 7                                 | plain    |              | 
 policy_id | integer                |           | not null | 1                                 | plain    |              | 
 email     | bytea                  |           | not null |                                   | extended |              | 
 fullname  | character varying(255) |           |          | NULL::character varying           | extended |              | 

=# SELECT * FROM users;
 id | priority | policy_id | email | fullname 
----+----------+-----------+-------+----------
  1 |        0 |         1 | @.    | 
(1 row)

Secondary Server:

=> \d+ users
                                                         Table "public.users"
  Column   |          Type          | Collation | Nullable |              Default              | Storage  | Stats target | Description 
-----------+------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id        | integer                |           | not null | nextval('users_id_seq'::regclass) | plain    |              | 
 priority  | integer                |           | not null | 7                                 | plain    |              | 
 policy_id | integer                |           | not null | 1                                 | plain    |              | 
 email     | bytea                  |           | not null |                                   | extended |              | 
 fullname  | character varying(255) |           |          | NULL::character varying           | extended |              | 

=> SELECT * FROM users;
 id | priority | policy_id | email  | fullname 
----+----------+-----------+--------+----------
  4 |        0 |         1 | \x402e | 
(1 row)
Arcane Feenix
  • 120
  • 1
  • 8
  • 2
    Why would you store an email address in a `bytea` column? That makes no sense to begin with. An email is not a "binary value" - it's a `text` value. I am a bit surprised that your "original" server would show a `bytea` column as text to begin with. But probably the way you exported and imported the data is the reason for this - but you didn't even tell us how you did it. –  Dec 28 '20 at 09:02
  • @a_horse_with_no_name - this was the schema provided by Amavis - according to their documentation, it is to be compliant with RFC 2821. See the 4th paragraph here: https://www.ijs.si/software/amavisd/README.sql-pg.txt – Arcane Feenix Dec 28 '20 at 09:05
  • I exported the database with a basic pg_dump command with no args. I "imported" by pasting into a psql shell. – Arcane Feenix Dec 28 '20 at 09:06
  • 2
    Well, I wouldn't follow the recommendations in that link. Apparently whoever wrote that doesn't really have experience with Postgres. "*Also, fields mail_id and secret_id should be treated case-sensitively, so data types char or varchar should be avoided*" - that is plain wrong. `varchar` or `text` **is** case sensitive. And suggesting to use `char(x)` instead of proper `boolean` columns is also rather questionable. Seriously: use `text` for the email column (and proper `boolean` columns for yes/no flags) –  Dec 28 '20 at 10:11
  • I can change the email columns but the Y/N columns cannot be changed as they are expected values in the Amavis software. I had the exact same thought when I first saw that schema. – Arcane Feenix Dec 28 '20 at 16:59

1 Answers1

1
set bytea_output to hex; 
select '@.'::bytea;

┌────────┐
│ bytea  │
├────────┤
│ \x402e │
└────────┘

set bytea_output to escape; 
select '@.'::bytea;

┌───────┐
│ bytea │
├───────┤
│ @.    │
└───────┘

It seems that you have a different settings at your servers.

Documentation

Abelisto
  • 14,826
  • 2
  • 33
  • 41