Better support for `bytea` type

[jakob]

Postico currently has only limited support for bytea columns. Postico can display images stored in bytea columns in the sidebar, but apart from that there is no way to look at the data.

I've just discussed some ideas with @apeiros, who uses bytea columns to store textual data that may contain invalid code points. He suggested that the data should be displayed directly in the table view, in one of the following formats:

1. ascii with escape sequences, eg. hello\x00w\xc3\xb6rld
2. hex, eg. 0x68656c6c6f0077c3b6726c64
3. utf-8, eg. hello\x00wörld

It would be nice if Postico did some auto-detection to display data in a sensible way (eg. don't try to display images as text), and also allow the user to view data in a specific way.

I'd love to hear feedback on this. Do people actually use the bytea type? And what kind of data do you store inside?

[jakob]

I've just received a suggestion from a Postico user via email:

It would be very nice if there were

1. an option to view bytea field contents encoded as hex or base64
2. the export and import function would work for those fields (especially copy-paste), because right now, exporting will just render those fields as empty values

[mtwilliams]

Any movement on this? I'm looking to use a bytea column to store Erlang terms in ETF. Something along the lines of ASCII w/ escape sequences would be a huge boon. Ideally, in an over-engineered world, the ability to script column rendering would allow in-place editing 😈

[jakob]

@mtwilliams no updates yet -- as you can see there isn't a lot of interest on this issue.

It looks like ETF is a complex binary format -- would displaying that in Postico even make sense? What form would be most useful, I assume just the raw bytes in hex format?

[mtwilliams]

The raw bytes in hex would work well. Especially if it came with a pop-out window a la HxD.

[mcculley]

I have a table with a column that uses the bytea type. The data is images or short strings encoded as UTF-8. I would really like for Postico to be able to show me the strings.

[tsutsu]

People who need deterministic primary keys tend to use binary-encoded UUIDs in a bytea-typed primary-key column. Frameworks such as Elixir's ORM Ecto have first-class support for this approach, so you see a lot of people who use Ecto with Postgres taking advantage of it.

Personally, I'm using bytea for a number of things related to cryptocurrency, namely columns (which are often primary- or foreign-keys) that hold binary-encodings of SHA256 or RIPEMD160 hashes (those being the ID formats of most crypto-ledgers' blocks and addresses, respectively.)

Essentially, in both of these cases, bytea is only being used as a lower-overhead equivalent to storing the hex representation of the value directly in a text column.

So, in both of the above cases, being able to see/edit the hex representation of the bytea would be perfect.

[jakob]

@tsutsu I agree that displaying bytea as HEX would be useful for hashes, but as far as I understand the blog post you linked to talks about the PostgreSQL UUID type, which Postico does support properly.

[banteg]

I'm using bytea to store hashes, an option to display them as hex would be very useful.

[dr-bonez]

I am using bytea to store bitcoin locking scripts and being able to copy/paste them as hex would be very useful.

[ralphcallaway]

i use the bytea for storing several hashes and would love to be able to get the value in postico table explorer

[Fyb3roptik]

+1 for me. I store hashed items in my database and being able to verify the hash with what I show in the log would be great!

[voikya]

One more vote here for hash storage. I'd love to see the data displayed in hex format.

[supreethdevm]

one more vote for displaying bytea as HEX

[tomdcc]

Another vote. For my purposes hex viewing doesn't matter, but being able to export the binary data somehow would be very useful. Just having Copy Cell work so that I can then use pbpaste to put the data somewhere would be nice.

[chrisdone-artificial]

On Wireshark whose raison d'etre is displaying binary as structured info, you have the context menu to "Copy as" binary, hex, printable text, etc.

I think a dropdown on the column name would be handy. E.g. "Display as..." I think hex and text (and maybe base64) are enough to get most people where they want to go.

(Our licensed team is storing HTTP request responses, which are mostly JSON text but sometimes not.)

[pscott31]

another vote on this; our database is chock full of 32 byte bytea hashes that I would like to view, copy, paste etc.. as hex strings. I'll buy a license if you add this as I love the program otherwise!

[jakob]

After only 7 years of deliberation, I've started working on this feature. I've decided on supporting at least the following display modes:

1. HEX (by far the most popular demand)
2. Base 64 (useful for reducing size of exports)
3. Crockfords Base 32 (useful for ULIDs, recently suggested by a customer via email)

Possibly I'll also add these modes later (slightly harder because they are variable length, so I can't compute them on the fly and will need some kind of caching to avoid performance problems)

4. ASCII with octal escapes (this is how PostgreSQL traditionally displayed bytea, could be useful for data that is partially textual)
5. UTF-8 mode (this one is tricky, because I don't know how to best handle data that is not valid UTF-8)

Development of this feature is in the bytea-display branch which is available here: https://releases.eggerapps.at/postico2/changelog?branch_id=764

Here's my plan:

• Settings
  • [x] Implement global setting for bytea display mode
  • [ ] Implement per-column setting
• Table View
  • [x] Format bytea values in the Table View
  • [x] Allow editing formatted bytea values
  • [ ] Format bytea values in the sidebar
  • [ ] Add filters for bytea columns:
    • [ ] is equal / is not equal
    • [ ] contains / does not contain (maybe only for HEX? would be confusing for Base32/Base64 since the search string would not be visible in the encoded text if it is not aligned to 5/6 byte groups)
    • [ ] maybe length < / = / > filters?
  • [x] Add support for copy/pasting bytea values
  • [x] Add support for exporting bytea values
• SQL Query Editor
  • [x] Format bytea values in the SQL Query results
  • [ ] Sorting SQL Query results
• QA
  • [ ] Write test cases for encoding / decoding the various formats

If you have any more suggestions with respect to bytea columns, let me know!

[mmaa]

Would uuid columns also support the option to display in Crockford's Base 32? I use uuid columns to store ULIDs as primary keys, and it would be nice not to have to convert between the two display formats. I.e., my app displays 01GTGATJT0JXE9CFBXVQ0ZFWG6, but Postico displays 0186a0ad-4b40-975c-963d-7dddc1f7f206.

[jakob]

I merged the bytea feature into the main branch. So it is now available in the latest development build, which you can download from the changelist: https://releases.eggerapps.at/postico2/changelog?update_channel=1

It's still a work in progress (see todo list above), but I think it's already useful in its current state.

Please let me know if you run into an issues!

[chrisdone-artificial]

Ohh, thanks for the update! I've been following this thread for a year!