On 10/30/15 12:50 PM, Eric Schwarzenbach wrote:
> On 10/30/2015 09:53 AM, Jim Nasby wrote:
>> On 10/29/15 5:29 PM, Eric Schwarzenbach wrote:
>>> I'm just now converting that path to use a custom domain (along with
>>> custom operators) instead of just being a string. (The custom operators
>>> allow the paths to be sorted properly without each segment needing to be
>>> filled with zeros to a fixed length.) (Also FWIW, the latest version of
>>> this regexp is now '^([0-9]+.)*[0-9]+$')
>>
>> Have you looked at using int[]? It wouldn't be hard to go between that
>> and the string representation using string_to_array() and
>> array_to_string(). There's also a chance that eventually you'd be able
>> to do FKs on it.
> Do you mean making the column int[] and converting to string if needed,
> or converting the string column to int[] for the purposes of the
> ordering algorithm?
Changing the storage would probably be best because int[] should be
significantly smaller than a string representation.
> I did consider making the column int[] instead of a string, and it would
> probably be slightly more efficient in a few ways. My main hesitations
> were having to revisit the code that puts together this path, and
You could create a writable view (with the same name as the table) that
would convert the string representation to an array using
string_to_array(blah, '.'). That should be pretty fast.
> compatibility (at the moment we're only using PostgreSQL but we've had
> to run on other databases for certain clients in the past, and in theory
> are open to that in the future). I realize the compatibility concern is
> a little humorous in light of having gone down the
> custom-operator-for-sorting route, but I can always fall back to 0 padding.
BTW, IIRC ORDER BY int[] will do what you want here.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com