0

Given a table cc_playlist with 4 row/records where id_key is the primary key that auto increments, and track_id, track_position and playlist_id:

+--------+----------+----------------+-------------+
| id_key | track_id | track_position | playlist_id |
+--------+----------+----------------+-------------+
|     23 |        3 |              0 |           1 |
|    223 |       23 |              1 |           1 |
|    343 |       45 |              2 |           1 |
|    344 |       32 |              3 |           1 |
+--------+----------+----------------+-------------+

Problem:
When I wish to add a new record, e.g. track_id = 777 ,track_position=2, playlist_id =1, I want it to place the new record and push the rest of the records down, as long as it is in the same playlist_id.

    +--------+----------+----------------+-------------+
    | id_key | track_id | track_position | playlist_id |
    +--------+----------+----------------+-------------+
    |     23 |        3 |              0 |           1 |
    |    223 |       23 |              1 |           1 |
 -->|    345 |      777 |              2 |           1 |
    |    343 |       45 |              3 |           1 |
    |    344 |       32 |              4 |           1 |
    +--------+----------+----------------+-------------+

NOTE: I am doing this in php, If it can't be done in Postgres.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Charm_quark
  • 376
  • 2
  • 6
  • 30
  • Could you clarify what you mean by 'push the rest of the records down'? Nothing about the other records seems to have changed. – william.berg Mar 31 '13 at 15:21
  • 1
    But what is the point of this? Can't you just `ORDER BY track_position` in your query? I'm asking because AFAIK with auto-increment it'd either very hard or impossible to do. – Lemur Mar 31 '13 at 15:21
  • Aha! see my *award winning* answer here: http://stackoverflow.com/a/14092775/905902 – wildplasser Mar 31 '13 at 16:50
  • Interesting solution however I cannot add a new field to the database. – Charm_quark Mar 31 '13 at 17:57
  • my gut feeling is that the problem is not solvable without an extra state-variable per row. please prove me wrong. – wildplasser Apr 01 '13 at 00:09
  • :), i'm not a pro at programming. I am learning sql/php/shell by trying to solve my current problem. The bigger picture is that the database (application) does not belong to me, just trying to add functionality. – Charm_quark Apr 01 '13 at 20:48

2 Answers2

0

I think what you want is a query like this before you insert the new record:

update cc_playlist
    set track_position = track_position + 1
    where playlist_id = YOURPLAYLISTID and track_position > YOURTRACKPOSITION;

Note you'll need to do something similar for delete, when you remove a track.

Also, you can set this up as an update trigger in the database, so the database does this automatically. Or, wrap the insert in a stored procedure, and do both the insert and the update there.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • that is exactly what i have ended up doing, bacically after the incrementation i do the insert, and the same thing in reverse to remove track. BTW thanks – Charm_quark Apr 01 '13 at 20:37
0

In my opinion, you should just insert the new row with the appropriate track_position and order by track_position when querying the table (bear with me).

The reason is because PostgreSQL stores tables as unordered heaps. This means that every time you insert a row on a table it gets appended (ie, inserted at the end) to the file holding the table's contents.

There's a way to reorganize a table according to an index (postgresql cluster). I'm including this in case you want to user it for testing purposed but I totally advice against doing this. That being said, you would create an index on the table using playlist_id, track_id as the index key and then use the cluster command.

I also think is a bad idea doing this in php, as this behavior clearle belongs in PostgreSQL.

I know this is not an actual answer to your question, but maybe it helps

To summarize:

  • Just insert the row and order when querying
  • Do this in PostgreSQL, not in php
  • Don't use cluster
Carlos
  • 208
  • 1
  • 6