Using latest versions of Postgres, Node and node pg.
Trying to figure out why ORDER BY col DESC LIMIT 100 always returns the first rows regardless of order.

Here's the table:

CREATE TABLE transactions (
    id BIGSERIAL NOT NULL,
    timestamp timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    user_id INT NOT NULL,
    to_id INT NOT NULL,
    amount NUMERIC(10, 2) DEFAULT 0 NOT NULL,
    comment VARCHAR(100) DEFAULT '' NOT NULL,
    rate INT DEFAULT 0 NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX index_transactions_id ON transactions(id);
CREATE INDEX index_transactions_user_id ON transactions(user_id);
CREATE INDEX index_transactions_timestamp ON transactions(timestamp);

Here's the query:

SELECT * FROM transactions ORDER BY $1 DESC LIMIT 100;

Where $1 is id and passed via params.
Rows in transactions are sometimes deleted, so there are empty ranges. The returned rows from this query are always the first 100. Tried NULLS FIRST/LAST and the result is the same.

What's happening?

CodePudding user response：

Where $1 is id and passed via params.

SQL does not allow to parametrize object names like column names. In most cases you get a syntax error right away if you try this kind of nonsense. Unfortunately, the ORDER BY clause does not necessarily expect a column name, but allows expressions including a constant (typed) value (which can be parameterized).

If you pass 'id' to this prepared query, this is what happens:

SELECT * FROM transactions ORDER BY 'id'::text DESC LIMIT 100;

ORDER BY <constant value> is just noise and has no effect on the result.

Turn on the server logs to check what the database actually receives. To parameterize column names like you seem to have in mind, you need dynamic SQL, i.e. first concatenate the query string and then execute it.