Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Postgres is ordering my table against my wil

    Hi all,

    I'm developing a program to put a XML file in tables.

    My table's primary key is a string like bellow:

    1.1
    1.2.1
    1.3.2
    1.10.2

    This granted my XML ordering. I have to do that way. Those have to be my primary keys. Period.

    When I create tables the insert statements are on that order to like bellow:

    INSERT INTO table (primarykey) VALUES (1.1)
    INSERT INTO table (primarykey) VALUES (1.2.1)
    INSERT INTO table (primarykey) VALUES (1.3.2)
    INSERT INTO table (primarykey) VALUES (1.10.2)

    So the output in table could be exactly as I showed it to you above.

    BUT postgres is given me the output

    1.1.1
    1.10.2
    1.2.1
    1.3.1

    The 1.10 should be the last line, but this is not happening!

    I can only imagine that it has some ordering method in postgres that are reordering MY TABLES!

    Can someone please, tell me whats going on and how to solve it?

    This is my CREATE_TABLE statements:

    'CREATE TABLE MainTable(ElementID character varying NOT NULL,Value character varying,PathID characdter varying NOT NULL,CONSTRAINT mainTable_pk PRIMARY KEY (elementID));

    Where elementID is the string that I've just showned...

    Sorry for my bad english... PLEASE HELP!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Data in a relational table is not sorted.
    Think of a table as a bag of apples. Those apples do not have an order as well.

    If you want your data to be returned in a specific way you have to use an ORDER BY.

    As the data you are inserting does not have a "natural" way of sorting you need to either write a function that will sort 1.10 after 1.2 (because the ASCII sort will sort it the other way round) or you need to add a column (e.g. integer) that will define the sort order.

  3. #3
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    In reality the table is in correct order. Your PK is not a number but varying character AKA String or Text and is in correct ASCENDING order as displayed by you.
    Maybe you should create an additional field called ORDINAL or something like that and give each primary key a numeric order to sort in the order you expect?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by caracadon View Post
    In reality the table is in correct order.
    A table does not have an "order"!

  5. #5
    Join Date
    Oct 2010
    Posts
    2
    Tks guys!

    I'll make another column with index.... so I could order later with ORDER BY!

    Tks!

  6. #6
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    Quote Originally Posted by shammat View Post
    A table does not have an "order"!
    Ok, but the results were in ascending order then

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by caracadon View Post
    Ok, but the results were in ascending order then
    Unless you specify an ORDER BY any order you see is purely coincidence.

  8. #8
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    LOL. Yes, I know. But my point was and in context with the op's expected outcome, is that the results were already in ascending order being text { a, b, c, ... }, whatever the process was that produced it. Not the numeric { 1, 2, 3, ... } that he was expecting; and for the reason I gave.

    You are quite correct though: I should be more explicit with my responses, I suppose.

Posting Permissions

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