Linked Lists?

mikesmith1287 · #1 (**permalink**) 09-02-11, 16:10

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!!

shammat · #2 (**permalink**) 09-02-11, 16:35

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech