Compare three column values with three inputs by size in postgres

Question

I am writing software where I have a box size (width=10, height=20, length=10) and a Postgres database with shelves (also defined by width, height, length). Now I want to find all shelves where I can put a specific box (the shelf dimensions need to be greater or equal to the box). I was originally thinking that I could do this:

SELECT shelves.* WHERE shelves.width = 10 AND shelves.height = 20 AND shelves.length = 10

However, my box can fit in any of these shelves, because it can just be rotated to fit:

|------|---------|----------|----------|
|  id  |  width  |  height  |  length  | 
|------|---------|----------|----------|
|  1   |   20    |    10    |    10    |
|------|---------|----------|----------|
|  2   |   10    |    20    |    10    |
|------|---------|----------|----------|
|  2   |   10    |    10    |    20    |
|------|---------|----------|----------|

So, in JavaScript, this code would look like this:

const boxdim = [box.width, box.height, box.length].sort();
const shelfdim = [shelf.width, shelf.height, shelf.length].sort();
const canFit = boxdim[0] <= shelfdim[0] && boxdim[1] <= shelfdim[1] && boxdim[2] <= shelfdim[2];

Is is possible to do this in Postgres? I would need something that looks like this (which is obviously pseudo code and doesn't work:

SELECT shelves.* WHERE SORT(shelves.width, shelves.height, shelves.length)[0] = 10 AND SORT(shelves.width, shelves.height, shelves.length)[1] = 10 AND SORT(shelves.width, shelves.height, shelves.length)[2] = 20

Any tips appreciated.

(Your JS code [does not work](https://stackoverflow.com/q/1063007/1048572)) — Bergi, Mar 19 '20 at 17:40

score 1 · Answer 1 · answered Mar 19 '20 at 17:45

I am not saying that you should do this, but in PostgreSQL you can do some pretty cool (or bad, depends on your point of view) things with arrays:

SELECT id, array_agg(d) FROM (
    SELECT id, unnest(ARRAY[width, height, length]) AS d 
      FROM shelves 
     ORDER BY id, d) X 
 GRUOP BY id;

And you have your dimensions sorted into a nice array, à là Javascript. I suggest using this query to generate a view.

Now, depending on your code you can do this again with the table of boxes or just pass the three parameters in the right order to the query that will work on the view.

I really hope someone will provide an answer that uses standard SQL, without arrays. In that case, please, choose their answer. :)

Bergi · Accepted Answer · 2020-03-20T09:58:08.950

1

The intarray extension provides a sort function for arrays, but you can also write one yourself if you don't want to use that or have a different element type.

~~Now since in Postgres "The array ordering operators (<, >=, etc) compare the array contents element-by-element,"~~ To check whether each value is smaller than the respective shelf dimension, you can use UNNEST:

SELECT *
FROM shelves
WHERE (SELECT bool_and(box_dim <= shelf_dim)
       FROM UNNEST(
         sort(ARRAY[width, height, length]),
         ARRAY[10, 10, 20]
       ) AS dims(box_dim, shelf_dim));

edited Mar 20 '20 at 09:58

answered Mar 19 '20 at 17:45

Bergi

630,263
148
957
1,375

Incredible! I can't believe it was this simple. Thanks a bunch! – Ronze Mar 19 '20 at 17:51
It seems that the postgres array comparison is not working like I expected. Why would `SELECT ARRAY[9,10,10] >= ARRAY[9,9,12]` return true? it seems that it's simply comparing the first value, not all values in sequence? – Ronze Mar 19 '20 at 20:27
I think it compares them like a string. `10 >= 9`, so it's `TRUE` – Bergi Mar 19 '20 at 21:22
Hmmm, I'm not quite sure I understand. And the code seems to not work the way I need because of this. In the last item of the array `10` is lower than `12`, so why is the result true? – Ronze Mar 19 '20 at 21:35
Oops, you're totally right, string comparison does not work for this case. Makes it a bit more complicated. – Bergi Mar 20 '20 at 09:55

Compare three column values with three inputs by size in postgres

2 Answers2