Is it possible to sort by the sequence of input IDs?

Sort order

Seems like PostgreSQL does not fetch rows by the sequence of values in the IN list?

The order of values passed to IN is insignificant. Postgres is at liberty to return rows in arbitrary order - in absence of ORDER BY.

You could pass a set and add an "ordering" column.
But it's much simpler to pass an array and work with the given order of array elements. Use unnest() and append WITH ORDINALITY:

-- 5122 first
SELECT s.*
FROM   unnest('{5122,3087}'::int[]) WITH ORDINALITY AS i(id, ord)
JOIN   song s USING (id)
ORDER  BY ord;

db<>fiddle here

See:

• PostgreSQL unnest() with element number

Another important difference: while duplicates in an IN list (or set) are removed, they are kept as given when joining to a set - like an unnested array.

Better solution

While both tables (favorite and songs) reside in the same DB, it's much better to run a single query:

SELECT s.*
FROM   favorite f
JOIN   song s ON s.id = f.song_id
WHERE  f.user_id = 123
ORDER  BY f.added_at DESC;

Then you can order by favorite.added_at directly. And it's a single round-trip to the DB server.

There is a fundamental flaw with your approach, in that you first select the favorites and then fetch the song details in a second query. Not only are you lacking a good ORDER BY clause for the second query, but splitting a task like that in two queries causes unnecessary work.

You can improve performance and solve your problem in one step by solving the complete problem in a single query that joins both tables. Then the ORDER BY clause would come natural.

If I've understood you correctly, you want to do something like this (a fiddle with all of the code below is available here):

CREATE TABLE song
(
  song_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  song_name TEXT NOT NULL,
  song_artist TEXT NOT NULL
  --
  -- more fields
  --
);

populate it:

INSERT INTO song (song_name, song_artist) VALUES 
('x song  1', 'artist  1'),
('y song  2', 'artist  2'),  -- the reason for the letters is that songs 
('k song  3', 'artist  3'),  -- in a collection are unlikely to be in 
('c song  4', 'artist  4'),  -- alphabetical order
('b song  5', 'artist  5'),
('l song  6', 'artist  6'),
('m song  7', 'artist  7'),
('a song  8', 'artist  8'),
('a song  9', 'artist  9'),
('b song 10', 'artist 10');

So now, if you run a query like this:

SELECT
  s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (3, 5)
UNION
SELECT
  s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (2, 8);

You will obtain a result like this⁽¹⁾:

song_name   song_artist
y song  2   artist  2
b song  5   artist  5
a song  8   artist  8
k song  3   artist  3

where (3, 5) and (2, 8) are "mixed up". NOTE: you are not guaranteed to obtain this exact result on any run of the fiddle or on your own system - the SQL Standard says that without an ORDER BY clause in a query, the results can be returned in any order!

On a table with thousands of records (with UPDATEs and DELETEs thrown into the mix), predicting the sort order of any resultset (without an ORDER BY) is going to be impossible.

⁽¹⁾ I was surprised by this result - I expected the query to return results in PRIMARY KEY order, but it doesn't - at least not today on this particular fiddle - proving that ORDER BY always needs to be specified. Just because a non-specified order is returned today (just good luck? whatever?), doesn't mean that your results will be returned in that order tomorrow!

So, you can do something like this:

SELECT
  'query 1' AS the_query, s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (3, 1, 5)
UNION
SELECT
  'query 2', s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (2, 8)
ORDER BY the_query, song_name;

Result:

the_query   song_name   song_artist
query 1     b song  5   artist  5
query 1     k song  3   artist  3
query 1     x song  1   artist  1
query 2     a song  8   artist  8
query 2     y song  2   artist  2

Which is correct (note the sequences (b, k, x) and (a, y).

I introduced an "artificial" sorting key into this query - the the_query string which can be used to separate the results of the different queries in the ORDER BY. This can be useful in cases such as this!

Or, if you simply want to sort by song_id within the different queries:

SELECT
  'query 1' AS the_query, s.song_id, s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (3, 1, 5)
UNION
SELECT
  'query 2', s.song_id, s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (2, 8)
ORDER BY the_query, song_id;

Result:

the_query   song_id     song_name   song_artist
query 1           1     x song  1   artist  1
query 1           3     k song  3   artist  3
query 1           5     b song  5   artist  5
query 2           2     y song  2   artist  2
query 2           8     a song  8   artist  8

Again, because we've specified the ORDER BY, we get what we asked for (it does what it says on the tin!).

EDIT:

On rereading the question, it appears that the OP wants the order of records to reflect the order of the song_ids in the IN(...) clause (fiddle here).

This can be done as follows:

Imagine we want the songs with ids 5, 8 and 2 to be presented in that order in the resultset.

So, we might try:

SELECT
  s.song_name, s.song_artist
FROM 
  song s
WHERE s.song_id IN (5, 8, 3);
-- ORDER BY s.song_id DESC;

But, the result (on my fiddle today) is:

song_name   song_artist
k song  3   artist  3
b song  5   artist  5
a song  8   artist  8

So, it's in order of song_id - but there is no guarantee that this will happen next week, tomorrow or even in the next second. I can guarantee or reverse this adding the ORDER BY s.song_id ASC/DESC.

PostgreSQL will resuse space in pages when records are deleted so records may well not be in PRIMARY KEY order on disk. Unlike some other systems, PostgreSQL does not have clustered indexes.

If you want a cast-iron guarantee that the records will appear as you put them into the list in the IN (...) clause, then you have to do the following:

SELECT 
  s.song_id, 
  s.song_name, 
  s.song_artist, 
  t.id, 
  t.id_sort_order
FROM
  song s
JOIN
(
  VALUES(1, 5), (2, 8), (3, 2)
) AS t (id_sort_order, id) 
ON s.song_id = t.id
ORDER BY t.id_sort_order;

You use the VALUES clause to construct an "artificial" table with two fields - one is your id_sort_order (1,2,3) and the other is your id which is your desired sort order (5,8,3) and then JOIN on the desired sort order to retrieve the details you want about songs (5,8,3), but you sort by (1,2,3), thereby obtaining your desired order in your final result set.

Result:

song_id     song_name   song_artist     id  id_sort_order
      5     b song  5   artist  5        5              1
      8     a song  8   artist  8        8              2
      2     y song  2   artist  2        2              3

The methods outlined above are the only ones which will guarantee your sort order.

You can use ORDER BY clause with a CASE:

SELECT * FROM `songs`
ORDER BY 
  CASE
    WHEN `id` = 3087 THEN 0
    WHEN `id` = 5122 THEN 1
    ELSE `id` NOT IN (3087, 5122)
  END
ASC

In this example, The song with ID 3087 will be the first record. And the song with ID 5122 will be the second. Then other records