title: org.postgresql.util.PSQLException: ERROR: syntax error at or near "." Spring pageable object with sort query
body: <p>I have a native query with spring pageable </p>
  
  <pre><code> @Query(value = "with RECURSIVE tbsons as(select * from customers where id=:companyId union all select customers.* from customers  join tbsons on customers.parent_id=tbsons.id) SELECT * FROM  tbsons where deleted = false order by ?#{#pageable}",
              countQuery = "with RECURSIVE tbsons as(select * from customers where id=:companyId union all select customers.* from customers  join tbsons on customers.parent_id=tbsons.id) SELECT COUNT (*) FROM  tbsons where deleted = false",
              nativeQuery = true)
          Page&lt;Company&gt; getCompaniesByparent(Pageable pageable, @Param("companyId") Long companyId);
  </code></pre>
  
  <p>And I am getting the exception</p>
  
  <pre><code>org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
        Position: 214
          at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
          at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
          at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
          at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
          at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
          at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
          at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
          at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
          at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
          at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
          at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
          at org.hibernate.loader.Loader.doQuery(Loader.java:919)
          at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
          at org.hibernate.loader.Loader.doList(Loader.java:2617)
          at org.hibernate.loader.Loader.doList(Loader.java:2600)
          at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
          at org.hibernate.loader.Loader.list(Loader.java:2424)
          at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336)
          at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967)
          at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322)
          at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125)
          at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
          at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
          at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:195)
          at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82)
          at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
          at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
          at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482)
          at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
          at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
          at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
          at com.sun.proxy.$Proxy138.getCompaniesByparent(Unknown Source)
          at com.blupa.hb.restapi.company.CompanyController.listAll(CompanyController.java:65)
          at com.blupa.hb.restapi.company.CompanyController$$FastClassBySpringCGLIB$$80ce5f2b.invoke(&lt;generated&gt;)
          at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
          at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
          at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:69)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
          at com.blupa.hb.restapi.company.CompanyController$$EnhancerBySpringCGLIB$$bc1170e7.listAll(&lt;generated&gt;)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:498)
  </code></pre>
  
  <p>The query generated is</p>
  
  <pre><code>with RECURSIVE tbsons as(select * from customers where id=? union all select customers.* from customers  join tbsons on customers.parent_id=tbsons.id) SELECT * FROM  tbsons where deleted = false order by ?, join.name asc limit ?
  </code></pre>
  
  <p>How to form a proper query for which I can pass a sort parameter, ie GET:/rest/company/?page=0&amp;size=10&amp;sort=name.</p>
  
  <p>The query executes fine without sort parameter ie. GET: /rest/company/?page=0&amp;size=10</p>