3

the doc of Postgres Ltree said that

A label is a sequence of alphanumeric characters and underscores (for example, in C locale the characters A-Za-z0-9_ are allowed). Labels must be less than 256 bytes long.

However, it does not said that if we set the locale to 'en_US.UTF-8', what is the valid character can be used in Postgres Ltree. So, can dash(hyphen) be used in the label of Ltree?

andy
  • 3,951
  • 9
  • 29
  • 40
  • 1
    The fact that it mentions the locale means that it'll be in the current locale's encoding, so yes, if the database is UTF-8 you can use UTF-8 chars. – Craig Ringer Apr 27 '15 at 04:57
  • @CraigRinger, yes i have test that i can even insert a chinese char. – andy Apr 27 '15 at 06:24
  • @andy Any luck on using hypen (-) inside a ltree label? – Charizard_ Nov 27 '15 at 04:37
  • 1
    @Charizard_ Sorry for not updating the answer. Yes, i have finally figured out that it is one of our DBA that changed the source of **ltree** and recompile it with supporting the dash(-) char. We have a single table with more than 6B records – andy Dec 03 '15 at 03:24
  • FYI Postgres 16 will include support for `-` via [this patch](https://github.com/postgres/postgres/commit/b1665bf01e5f4200d37addfc2ddc406ff7df14a5) which was merged recently. – Tim Bunce Mar 22 '23 at 11:52

1 Answers1

4

Sorry for not updating the answer.

Yes, i have finally figured out that it is one of our DBA that changed the source of ltree and recompile it with supporting the dash(-) char. We have a single table with more than 6B records.

andy
  • 3,951
  • 9
  • 29
  • 40
  • Since you have more than 6B records, is there any problem with including the dash? Or is everything good? Just wanted to know why the label restriction was put in the first place. And also do you have the patch hosted somewhere? Thanks. – Charizard_ Dec 03 '15 at 11:19
  • 1
    @Charizard Yes, everything is good, i do not know why the author of **ltree** do not have it in the original source code. The patch is very simple. You can have a look at [the source code](http://doxygen.postgresql.org/ltree_8h.html#a1cb5d5dd0c364d720854e8a250967dd1), i.e., the 83 line of [ltree.h](http://doxygen.postgresql.org/ltree_8h_source.html) – andy Dec 06 '15 at 08:16
  • 1
    I'll try to submit patch request and wish it gets accepted – caub May 19 '17 at 10:28
  • just wonder if someone has re-compiled it as a separate extension or if there's any tutorial about how to re-compile the ltree module, we want to support the dash as well – Deo Leung Sep 07 '21 at 03:40
  • 1
    FYI Postgres 16 will include support for `-` via [this patch](https://github.com/postgres/postgres/commit/b1665bf01e5f4200d37addfc2ddc406ff7df14a5) which was merged recently. – Tim Bunce Mar 22 '23 at 11:51