Question 332163 in PostgreSQL Question posted in
The official documentation can be found here.

Placeholder in PostgreSQL query with double colon :: cast

I have this SQL query:

select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                    +'6d 18:06:56'::interval as created_at,
       count(*)filter(where status='ACTIVE') as active,
       count(*)filter(where status='BLOCKED') as blocked
from companies
where now()-'12 months'::interval < created_at
group by 1 order by 1;

I tried to use it into Spring Data JPA repository like this:

@Query(value = """
            select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                   +'6d 18:06:56'::interval as created_at,
                                   count(*)filter(where status='ACTIVE') as active,
                                   count(*)filter(where status='BLOCKED') as blocked
                            from companies
                            where now()- :months + ' months'::interval < created_at
                            group by 1 order by 1"""
            , nativeQuery = true)
    List<Result> companiesMonths(@Param("months") Integer months);

But I get error:

SQL Error: 0, SQLState: 42601
ERROR: syntax error at or near ":"

What is the proper way to implement this?

Tags:

2

Answers


  1. 0

    Seems erreur is near ::interval keyword usage try this:

    @Query(value = """
        select date_trunc('month',created_at-'6d 18:06:56'?::interval)
                               +'6d 18:06:56'\:\:interval as created_at,
                               count(*)filter(where status='ACTIVE') as active,
                               count(*)filter(where status='BLOCKED') as blocked
                        from companies
                        where now()- ( ?1 )\:\:interval < created_at
                        group by 1 order by 1"""
        , nativeQuery = true)
List<Result> companiesMonths(String toTime);

    and months definition should be like:

    int months; 
String toTime =months +  " months";

    Inspired by this post: Postgres Interval not working with native spring data JPA query

    Login or Signup to reply.
  2. 0

    If Spring doesn’t like the colons, you can get rid of them entirely: demo

    select date_trunc('month',created_at-interval '6d 18h06m56s')
                                    +interval '6d 18h06m56s' as created_at,
       count(*)filter(where status='Active') as "Active",
       count(*)filter(where status='Blocked') as "Blocked"
from companies
where now() - :months * interval '1 month' < created_at
group by 1 order by 1;

    Even the literal can use h, m and s instead of colon-separated time format. That’s all equivalent cast syntax in PostgreSQL:

    • cast('6d 18h06m56s' as interval)
    • '6d 18h06m56s'::interval
    • interval '6d 18h06m56s'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search