pgp.helpers.insert ordering

[DJDaveMark]

For my own sanity, I'd like to check that the following code will create a query where the insert values will always appear in the same order as they appear in the data array:

var data = [{
    'depth': 1,
    'value': 123
}, {
    'depth': 2,
    'value': 456
}];
var cs = new pgp.helpers.ColumnSet(['depth', 'value'], {table: 'table_name'});
var query = pgp.helpers.insert(data, cs);
// insert into "table_name"("depth","value") VALUES(1,123),(2,456)

I'm currently inserting data like that for over 20k rows, but every so often, not for every insert, when I query the data, sometimes the depths come back a little bit disordered i.e.:

select depth, value from table_name

depth | value 
------+------
[...]
2498  | 124
2499  | 875
2472  | 363
2473  | 636
2500  | 918
2501  | 927
[...]

Should I be looking into if postgres might disorder the inserts behind the scenes?
Or should I be inserting the data with an extra column (say idx) so I can add an order by clause?

[bdharrington7]

You cannot depend on queries to return ordered results unless you specify an ORDER BY clause.

a query where the insert values will always appear in the same order as they appear in the data array

If you need query order to be the same as insertion order, if you set up a serial data type column (usually done as a primary key), then you can sort on that column to get results back in the same way they were inserted.

You could also use your approach of supplying the index of the element in the data array as part of the row.

[DJDaveMark]

Thanks @bdharrington7,
I also found a simple explanation in the docs:

If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.
So I simply added an order by clause using the id:

select depth, value from table_name order by id