Ecto Query Fragment with Postgres substring

Question

Scenario
I need to sort the order from the database alpha then numeric.
So the order should look like ["A","B","C","1","2","3"]

Postgres
This seems to work. I tried it out.

WITH x(t) AS (
    VALUES
     ('a')
    ,('3')
    ,('2')
    ,('1')
    ,('c')
    ,('b')
    )
SELECT t
FROM   x
ORDER  BY (
substring(t, '^[A-Za-z].*'),       -- cast to integer 
substring(t, '[^0-9_].*$'))        -- works as text

I'm using Ecto and from my understanding you need to use Ecto.Query.API.fragment/1 in order to send commands to the database. So I wrote this.

In Ecto

def alphabetical(query) do
 from c in query, order_by: [fragment("substring(?, ?)", c.name, '^[A-Za-z].*'), fragment("substring(?, ?)", c.name, '[^0-9_].*$')]
end

Error
I'm getting this error in the app

ERROR 42883 (undefined_function): function pg_catalog.substring(character varying, integer[]) does not exist

ANSWER
Written in Ecto and using fragments

  def alphabetical(query) do
      from c in query, order_by: [fragment("? < 'a'", c.name)]
  end

Answer 1

Your query does not seem to be correct. Try this simple one:

SELECT t
FROM x
ORDER BY t < 'a', t

Db<>fiddle.