0

I have the following database:

create workers (
  id SERIAL PRIMARY KEY,
  name STRING
);

create works_zones (
  id SERIAL PRIMARY KEY,
  area_name String,
  work_from TIME WITHOUT TIMEZONE,
  work_until TIME WITHOUT TIMEZONE
) 

create table shifts (
  id SERIAL PRIMARY KEY,
  worker_id INT,
  day_of_week INT,
  work_zone INT,
  CONSTRAINT fk_worker FOREIGN KEY(worker_id) REFERENCES workers(id) ON DELETE CASCADE,
  CONSTRAINT fk_work_zone FOREIGN KEY(work_zone) REFERENCES works_zones(id) ON DELETE CASCADE
)

CREATE UNIQUE INDEX CONCURRENTLY unique_day_zone_worker ON shifts (worker_id,work_zone,"day_of_week");

And I try to list all worker shifts:


select 
 workers.name,
  works_zones.area_name,
  works_zones.work_from,
  works_zones.work_until,
  shifts.day_of_week
from 
  shifts
join
 workers on shifts.worker_id = workers.id
join
 works_zones on works_zones.id = shifts.work_zone

But how I can translate the day_of_week into human readable form and expecially localized into Greek? The values are ranging from 0 to 6 where ) is Sunday and 6 is Saturday.

Is there a way to do is without the use of Case...when .. end?

1
  • With a lookup table. May 10, 2023 at 16:00

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.