Results 1 to 2 of 2

Thread: Linked Lists?

  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: Linked Lists?

    Is there an easy way to created linked lists on postgresql? Is there some resource someone can point me to because I can't seem to find one on google.

    Basically I want to be able to store posts in a certain order. Users can add new posts in between any other 2 posts so the order will definitely behave like a list. I heard somewhere that mysql couldn't do it but pgsql could.

    Thanks!!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I don't understand why you need a "linked" list to store something with an order.

    Just add an "sort" column to your posts table and you are all set.

    Code:
    CREATE TABLE posts
    (
       id              serial not null primary key,
       thread_id       integer not null,
       posting_date    timestamp not null default current_timestamp,
       title           text not null,
       order_in_thread integer not null,
       constraint fk_post_thread
            foreign key (thread_id) references threads (id)
    )

    If you really insist on doing a linked list, you just need to add a foreign key pointing to the "previous" post.

    Code:
    CREATE TABLE posts
    (
       id                serial not null primary key,
       thread_id         integer not null,
       posting_date      timestamp not null default current_timestamp,
       title             text not null,
       previous_post_id  integer not null, 
       constraint fk_prev_pos 
           foreign key (previous_post_id) references posts (id),
       constraint fk_post_thread
            foreign key (thread_id) references threads (id)
    )
    The second solution is actually a lot easier to deal with in Postgres than in MySQL because PostgreSQL supports recursive queries which make it very easy to retrieve such a structure.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •