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!

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.

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?

A table does not have an "order"!

Tks guys!

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

Tks!

Ok, but the results were in ascending order then

Unless you specify an ORDER BY any order you see is purely coincidence.

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.