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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
