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)

Bergi
– Bergi

2020-03-19 17:40:28 +00:00
Commented Mar 19, 2020 at 17:40 — Bergi
– Bergi, Commented Mar 19, 2020 at 17:40

Bergi · Accepted Answer · 2020-03-20 09:58:08Z

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, 2020 at 9:58

answered Mar 19, 2020 at 17:45

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ronze Over a year ago

Incredible! I can't believe it was this simple. Thanks a bunch!

Ronze Over a year ago

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?

Bergi Over a year ago

I think it compares them like a string. 10 >= 9, so it's TRUE

Ronze Over a year ago

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?

Bergi Over a year ago

Oops, you're totally right, string comparison does not work for this case. Makes it a bit more complicated.

fog · Accepted Answer · 2020-03-19 17:45:36Z

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. :)

Collectives™ on Stack Overflow

Compare three column values with three inputs by size in postgres

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related