Workarounds for Ordering Results Based on the IN List in PostgreSQL

However, there are some workarounds to achieve a similar effect in PostgreSQL:

-- Sample table
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data
INSERT INTO products (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Select products with category in ('Electronics', 'Clothing') ordered by the order in the list
SELECT *
FROM products
WHERE category IN ('Electronics', 'Clothing')
ORDER BY CASE category
         WHEN 'Electronics' THEN 1
         WHEN 'Clothing' THEN 2
       END;

UNNEST with ORDINALITY (PostgreSQL 9.4+):

-- Sample table (same as above)
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data (same as above)
INSERT INTO products (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Desired order list
SELECT unnest(ARRAY['Electronics', 'Clothing']) AS category_order;

-- Select products with category matching the order list
WITH ordered_categories AS (
  SELECT unnest(ARRAY['Electronics', 'Clothing']) AS category_order,
         ROW_NUMBER() OVER (ORDER BY category_order) AS order_id
)
SELECT p.*
FROM products p
JOIN ordered_categories oc ON p.category = oc.category_order
ORDER BY oc.order_id;

Temporary Table:

-- Sample table (same as above)
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data (same as above)
INSERT INTO PRODUCTS (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Temporary table with desired order
CREATE TEMP TABLE category_order (
  order_id INT PRIMARY KEY,
  category VARCHAR(50)
);

INSERT INTO category_order (order_id, category) VALUES
  (1, 'Electronics'),
  (2, 'Clothing');

-- Select products with category matching the order
SELECT p.*
FROM products p
JOIN category_order co ON p.category = co.category
ORDER BY co.order_id;

-- Drop temporary table after use
DROP TABLE category_order;

This approach utilizes the GENERATE_SERIES function to create a sequence of numbers and a CASE statement to assign sorting order based on the value in your field.

-- Sample table (same as previous examples)
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data (same as previous examples)
INSERT INTO products (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Desired order list
SELECT value AS category_order
FROM GENERATE_SERIES(1, ARRAY_LENGTH(ARRAY['Electronics', 'Clothing'])) AS gs(value);

-- Select products with category matching the order list
SELECT p.*
FROM products p
JOIN (
  SELECT value AS category_order,
         CASE WHEN category = 'Electronics' THEN 1
              WHEN category = 'Clothing' THEN 2
              ELSE 3 -- Add a default order for unmatched categories (optional)
         END AS order_id
  FROM GENERATE_SERIES(1, ARRAY_LENGTH(ARRAY['Electronics', 'Clothing'])) AS gs(value)
) AS oc ON p.category = oc.category_order
ORDER BY oc.order_id;

CTE with ROW_NUMBER (all PostgreSQL versions):

This method uses a Common Table Expression (CTE) with the ROW_NUMBER function to assign a position to each value based on the desired order.

-- Sample table (same as previous examples)
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data (same as previous examples)
INSERT INTO products (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Desired order
WITH category_order AS (
  SELECT category,
         ROW_NUMBER() OVER (ORDER BY CASE category
                                       WHEN 'Electronics' THEN 1
                                       WHEN 'Clothing' THEN 2
                                       END) AS order_id
  FROM (SELECT DISTINCT category FROM products) AS all_categories
)
-- Select products with category matching the order
SELECT p.*
FROM products p
JOIN category_order co ON p.category = co.category
ORDER BY co.order_id;