how to change priority in ILIKE I have this simple table and user records.
I just want to get users with ilike or something else to get better order in PostgreSQL 10.12.
CREATE TABLE users(
   ID INT PRIMARY KEY      NOT NULL,
   username           CHAR(50) NOT NULL
);

INSERT INTO users (id, username) VALUES(1,'mandela');
INSERT INTO users (id, username) VALUES(2,'delpiero');
INSERT INTO users (id, username) VALUES(3, 'gondela');
INSERT INTO users (id, username) VALUES(4, 'del');
INSERT INTO users (id, username) VALUES(5, 'dell');
INSERT INTO users (id, username) VALUES(6, 'andel');

When I query &quot;del&quot; I want to get &quot;del&quot; user whose id is 4 in first place. it's ok with ordering by length. But still it is not what I want. The order should be like this. Second order must be &quot;dell&quot;, third order must be &quot;delpiero&quot;, fourth &quot;andel&quot;, fifth &quot;gondela&quot; and sixth &quot;mandela&quot;.
select * from users where username ilike '%del%' order by length(users.username) asc;

Here is SQL Fiddle
I would seem to me, that you want leading matches first, then shortest strings first, and finally alphabetic sort order:
SELECT *
FROM   users
WHERE  username ILIKE '%del%'
ORDER  BY username NOT ILIKE 'del%'  -- leading match first
        , length(username)           -- shorter words first
        , username;                  -- finally alphabetical

db&lt;&gt;fiddle here
NOT ILIKE 'del%' because false sorts before true. See:

Sorting null values after all others, except special

Aside: Never use char(n). See:

Any downsides of using data type “text” for storing strings?