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

Postgres variables in select and error when using repeat – Postgresql

Suppose the following,

create table person (id serial primary key);
create table person_rating (
  id serial primary key,
  person_id bigint references person,
  rating smallint constraint rating_person_rating_check check (rating > 0 and rating <= 5)
);

insert into person values (1);
insert into person_rating values
  (1, 1, 5),
  (2, 1, 2),
  (3, 1, 5),
  (4, 1, 1);

I’m trying to run the following query:

select
    round(avg(rating)::numeric(10, 2), 2) as rating_averaged,
    ceil(avg(rating)::numeric(10, 2)) as rating_rounded_up,
    concat(
      repeat('★', ceil(avg(rating)::numeric(10, 2))),
      repeat('☆', 5 - ceil(avg(rating)::numeric(10, 2)))
    ) as rating_as_stars
from person_rating;

I’m expecting the following result:

{
    "rating_averaged": 3.25,
    "rating_rounded_up": 4,
    "rating_as_stars": "★★★★☆"
}

I’m running into the following error:

Query Error: error: function repeat(unknown, numeric) does not exist

What’s weird is that basic repeat selects work, such as:

select repeat('hello! ', 2);

I’ve also tried casting as ::text or ::varchar, but the error persists.

Another concern that I have is the repetition. For instance, in this case, the following sequence is repeated 4 times:

avg(rating)::numeric(10, 2)

How can I get around this?

Fiddle

Tags:

3

Answers


  1. 0

    Also cast the second parameter of repeat to integer.

    Login or Signup to reply.
  2. 0

    The second parameter must be integer:

    select
    round(avg(rating)::numeric(10, 2), 2) as rating_averaged,
    ceil(avg(rating)::numeric(10, 2)) as rating_rounded_up,
    concat(
      repeat('★', ceil(avg(rating)::numeric(10, 2))::integer),
      repeat('☆', 5 - ceil(avg(rating)::numeric(10, 2))::integer)
    ) as rating_as_stars
from person_rating;

    Additionally, if you want to keep the code clean, I would suggest create a function that returns a rating based on a "rating" parameter.

    Login or Signup to reply.
  3. 0

    The avg(any integer type) function always returns a numeric therefore the repetition of casting its results (::numeric(10,2)) is unnecessary. Also, as others have indicated the repeat() function requires the second parameter to be an integer, which will need a cast. Beyond that the manipulation of rating can be confined to a CTE, reducing the complexity/wordiness of the repeat() functions. Overall the query reduces to: (see demo)

    with ratings(rating_averaged, rating_rounded_up) as 
     ( select round(avg(rating), 2) 
            , ceil(round(avg(rating),2))::integer  
         from person_rating
     )  --select * from ratings;
select rating_averaged 
     , rating_rounded_up 
     , concat( repeat('★', rating_rounded_up )
             , repeat('☆', 5 - rating_rounded_up)
             ) as rating_as_stars 
from ratings;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search