postgresql - 添加列但保留特定的 ID

Question

我有一个“列表”表，如下所示:

| listing_id |                   amenities                      |
|------------|--------------------------------------------------|
|  5629709   | {"Air conditioning",Heating, Essentials,Shampoo} |
|  4156372   |   {"Wireless Internet",Kitchen,"Pets allowed"}   |

还有另一个像这样的“Amenity”表:

| amenity_id |                     amenities                    |
|------------|--------------------------------------------------|
|      1     |                 Air conditioning                 |
|      2     |                      Kitchen                     |
|      3     |                      Heating                     |

有没有办法像这样将两个表连接到一个新的“Listing_Amenity”中:

| listing_id | amenities |
|------------|-----------|
|  5629709   |     1     |
|  5629709   |     3     |
|  4156372   |     2     |

Answer 1

你可以使用 unnest:

CREATE TABLE Listing_Amenity
AS
SELECT l.listing_id, a.amenity_id
FROM Listing l
, unnest(l.ammenities) sub(elem)
JOIN Amenity a
  ON a.ammenities = sub.elem;

db<>fiddle demo