[SQL] techniques for manual ordering of data ?
I tried googling, but the keywords here are so general, that I couldn't find much useful. I am looking for reommendations on how to build an ordered list of media (in this case photographs) in a particular "context". What I have - which I have somewhat simplified maually - is: CREATE TABLE contexts ( photo_idint8 REFERENCES photos ..., catalogue_idint8 REFERENCES catalogues ..., collection_id int8 REFERENCES collections ..., orderingint ); Here, a photograph may appear in multiple catalogue/collection places. What I want to do is to allow the admin to reorder the display by using + / - style buttons. My initial thought was to use an arbitrary "ordering" column, like old BASIC line numbers, which would be initially allocated in 10s or 100s, and then updating the value via the UI and occassionally applying a "renum" style function when the list gets uneven. To avoid performance hits through too many updates, I am trying to avoid the admin UI from UPDATEing all the rows in a specific catalogue/collection every time a button is pressed - both the SQL and the subsequent fragmented table. Hmm. Anyone got any "prior art" or suggestions on how they acheive this ? Note that I am NOT including ordering based on an photograph specific metadata like time or location. That's another story and another interface. rgds, -- Peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Storing a range of numbers
CoL wrote: or you can have one column, with array type. than the first element is the min value the second is the max:) Question is: is it good for your applications or not, how woud you use it, etc. I thought about using an array but it seems overly complicated for what I'm doing. Two rows will work fine ... a numeric range type seemed obvious at the time so I figured I just missed it in the documentation or something. Thanks for the reply. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Storing a range of numbers
hi, Chris Fisher wrote: Hi, Is it possible to store a numeric range in a row and query for it, or would I have to use two rows containing the minimum and maximum limits? or you can have one column, with array type. than the first element is the min value the second is the max:) Question is: is it good for your applications or not, how woud you use it, etc. C. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Storing a range of numbers
Chris Fisher <[EMAIL PROTECTED]> writes: > ... a numeric range type seemed > obvious at the time so I figured I just missed it in the documentation > or something. The "seg" datatype implemented by contrib/seg might be what you want. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
