Strange result from basic SELECT * FROM x query in postgres

Question

Ive noticed some strange behaviour when executing basic SELECT * FROM program query:

Link to screenshot

Im using GenerationType.IDENTITY and inserting whole list of program objects at once.I could add ORDER BY db_id to my query but im interested why this behaviour occured.It was fine till today. Also tried with GenerationType.AUTO but nothing changed...

My model:

@Entity
@Table(name = "program", indexes = { @Index(name = "channelindex", columnList = "channel", unique = false) })
public class OutputProgram {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer db_id;
private String category;
private String title;
....

There are some 8k programs being inserted each time...

Your query does not contain order by. Without order by there is no assurance of sequence of query results. What you have may be unusual but it would not be unexpected. — Belayer, Jul 11 '20 at 03:23
Unrelated, but: why aren't you storing JSON values in a `jsonb` (or at least `json`) column? — , Jul 11 '20 at 07:16
Order is not guaranteed in PosrgreSQL unless it’s specified. https://www.postgresql.org/docs/current/queries-order.html — Thirumal, Jul 11 '20 at 09:34
@a_horse_with_no_name this is db on server with rest controller returning json which i acess with my android app.`category` column is the only one i insert string as json because each row may contain 0-n categories so i figured it may be the easiest way to read that data in android as json array. Would i benefit from storing `category` in `json` instead of String? Being curious from your question i tried to add `compile group: 'com.vladmihalcea', name: 'hibernate-types-52', version: '2.9.12'` to my gradle and adding '@Type(type = "json")' to my category column but it doesnt work... — Nikola Srdoč, Jul 11 '20 at 11:38
you should choose the correct data type for the values you store. You don't store numbers or date values in `varchar` columns either. You benefit from the syntax checking that `jsonb` gives you and you can use the many [JSON operators](https://www.postgresql.org/docs/current/functions-json.html) without the overhead of casting. Btw: the arbitrary limit of 255 does not give you any performance or storage advantages over e.g. 276 or 242 (in case you assumed that. And if your obfuscation layer (aka "ORM") can't properly work with the correct data type, I would question the choice it. — , Jul 11 '20 at 12:15

score 1 · Accepted Answer · answered Jul 11 '20 at 12:18

1

Order is not guaranteed in PosrgreSQL unless it’s specified. https://www.postgresql.org/docs/current/queries-order.html

answered Jul 11 '20 at 12:18

Thirumal

8,280
11
53
103

Strange result from basic SELECT * FROM x query in postgres

1 Answers1