0

I have a table in my database where I store dates using the following standard: YYYY-MM-DD. As you can see, I'm not saving the hour. The first time I exported the same table to a csv file I got the following records in this order:

AAA@outlook.com
BBB@gmail.com
CCC@gmail.com
DDD@hotmail.com

But when I ran the same query to export the data to the same CSV, I received the records in this following order:

AAA@outlook.com
111@hotmail.com
222@gmail.com
333@gmail.com

So, the conclusion that I reached is when sorting data as this can be non deterministic. Isn't? This is my query to export the data from my database:

SELECT id AS "_id", cpf, email, name, agency, account, "createdDate" FROM "NextUsers", "Users" WHERE "userId"="Users".id ORDER BY "createdDate" OFFSET $1 LIMIT 500;

Where $1 starts at 0 and it is incremented with 500 per iteration. OBS: The returned data from both queries are correctly sorted but not following in the same order as you can see.

  • 1
    Please start any such question by declaring your version of Postgres and the defining CREATE TABLE statement. That shows exact data types and constraints and can avoid a lot of confusion. And show an unambiguous query. It's not even clear from which table the column "createdDate" originates. – Erwin Brandstetter Jun 20 at 19:40
  • @ErwinBrandstetter thanks for the recommendations – olegario Jun 24 at 17:09
2

If the date you are sorting by has repeat values in it and are not sorting by any other columns the order you get them in will be arbitrary.

Result set 1

email|date
aaa@gmail.com|2019-01-01
bbb@gmail.com|2019-01-01
ccc@gmail.com|2019-01-01

Result set 2

email|date
ccc@gmail.com|2019-01-01
bbb@gmail.com|2019-01-01
aaa@gmail.com|2019-01-01

If you just have an order by on date the database can return either result set as is just ordering by the date field and pulling in records as it gets them regardless of order.

If you need a consistent order in the results you are going to need to add more fields to the order until you can get a more unique set of fields to order by.

For your query I would update it to also order by _id so you will have a consistent result set

SELECT id AS "_id", 
       cpf, 
       email, 
       name, 
       agency, 
       account, 
       "createdDate" 
FROM "NextUsers", 
     "Users" 
WHERE "userId"="Users".id 
ORDER BY "createdDate",
         id 
OFFSET $1 
LIMIT 500;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.