1

I'm trying to ensure that a column in Postgres only holds a single emoji. However, I'm struggling with the length constraint since e.g.

length('') -- 1
length('') -- 2
length('‍❤️‍‍') -- 8

I've found How to count the correct length of a string with emojis in javascript? with some guidance on how to count the length in JS, but it's not that useful for Postgres since the approach uses the Unicode regex flag, which is not available in Postgres.

How would I count string length in Postgres such that all the emojis in my example count as length 1?

pir
  • 5,513
  • 12
  • 63
  • 101
  • If those are really single characters, what is their Unicode code point? They appear as multiple characters on my system. – Laurenz Albe Jun 21 '21 at 07:31

1 Answers1

2

I’d love to stand corrected, but I assume you’ll be out of luck here.

Emojis are a wild beast, character-wise. There are several potential groups, that you’d all need to cover:

  1. single-character emojis, e.g. Wrapped Gift, U+1F381. Those are emojis, that are defined as having the Emoji Unicode property.
  2. single-character emojis, that need the U+FE0F variation selector added, e.g. Red Heart. U+2764 alone (the heart) is what Unicode calls an “unqualified emoji”. It needs U+FE0F added for most platforms to be rendered as emoji.
  3. multi-codepoint emojis. These come in a lot of different shapes. Persons + gender + hair color, glued together with or without U+200D, families, regional flags, ...

And to top it all off, there is both an official ever-growing list of emojis and emojis, that are only supported by some platforms. (Example: Man Zombie: Light Skin Tone ) Unicode calls them non-RGI, not Recommended For General Interchange.

You need to decide, whether you want to support only Unicode-approved emojis or non-RGIs, too.

If you want only Unicode emojis, you could model your requirements with another second table, that you can periodically recreate from the official Unicode data. E.g., take this file:

https://github.com/unicode-org/unicodetools/blob/main/unicodetools/data/emoji/14.0/emoji-test.txt

(Note the “14.0” in the URL that you need to update with new Unicode Emoji versions!)

Take all lines with the text “fully-qualified”, in them everything up until the first colon, convert that from hex code points into a string and feed your helper table with that.

Example:

curl -sS https://raw.githubusercontent.com/unicode-org/unicodetools/main/unicodetools/data/emoji/14.0/emoji-test.txt | \
sed '/^\(#.*\)\?$/d' | \
sed -n '/fully-qualified/p' | \
sed 's/ *;.*//'

This gives you a long list of code points in hex format (e.g. 1F9D1 200D 1F52C). You can feed them in a script, change them from hex to a string and put them in a small helper table:

CREATE TABLE unicode_emojis (
    emoji TEXT PRIMARY KEY
);

Then in your other queries make sure, that values that go into your original table are in the unicode_emojis table, too.

Boldewyn
  • 81,211
  • 44
  • 156
  • 212
  • Thanks for the detailed answer! It's crazy how tricky emojis can be to handle. This approach sounds a bit like what https://github.com/truthly/pg-emoji does, correct? – pir Jun 21 '21 at 12:54
  • 1
    OK, pg-emoji is a strange project for sure. Yes, that looks similar from a glance. They just stop after 1024 emojis, because that’s enough for their purpose. You’d need to continue for your helper table. But https://github.com/truthly/pg-emoji/blob/master/fetch-chars.sh seems to be a good starting point. – Boldewyn Jun 21 '21 at 13:38
  • 1
    (Just note, that their `grep` regex in line 6 is too picky for your needs, since it selects only single-code-point emojis.) – Boldewyn Jun 21 '21 at 13:41
  • Thanks, that makes sense! – pir Jun 22 '21 at 14:29