How can I display enum string key instead of integer value in SQL query?

Question

I am pretty new to SQL. I assume this is fairly simple, but I haven't been able to find a straightforward answer online.

I am writing a simple SQL query to group database records by an enum column, and display the count of each value. It works fine, but the output is displaying the enum integer, where I want it to display the string key of that enum value.

Here is an example of the SQL query:

SELECT COUNT(a.sound) as "Sound Count", a.sound
FROM animals a
GROUP BY a.sound

Here is the enum definition:

enum sound: {
  bark: 0,
  meow: 1, 
  moo: 2
}

And here is the output of the query:

Sound Count     Sound

2               0
4               1
3               2

Whereas I really want:

Sound Count     Sound

2               bark
4               meow
3               moo

Beartech · Accepted Answer · 2020-04-21 18:39:19Z

7

You are asking the DB for info using SQL and so it will not have any knowledge of your Rails enums. You need to use Rails to make the query:

Animals.all.group(:sound).count(:sound)

=> {"bark"=>2, "meow"=>4, "moo"=>3}

For a pure sql answer with Postgresql:

SELECT temp.sound_count,
   CASE
    when temp.sound = 0 then 'bark'
    when temp.sound = 1 then 'meow'
    when temp.sound = 2 then 'moo'
   END 
   AS my_sound
FROM (SELECT COUNT(s.sound)  as sound_count, a.sound  from animals a 
   GOUP BY a.sound)
AS temp;

edited Apr 21, 2020 at 18:39

answered Apr 21, 2020 at 17:55

Beartech

6,4812 gold badges20 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Beartech Over a year ago

You could do it in SQL but you'd have to use some sort of logic like "if.. " to convert the numbers to the names.

Matt Over a year ago

Thanks, I am doing this out of a heroku dataclip so unfortunately it needs to be in SQL. I suppose just putting the mapping into the query isn't too bad, I was just hoping if I add enum values to not have to remember to update it here as well.

Beartech Over a year ago

So is your back end Postgres? It would help to know which flavor DB.

engineersmnky Over a year ago

@Matt you could create an animal_sounds table (and related Object in rails) this would make future modification easier and would allow for better relational queries that would not need integral knowledge of non database based logic

Matt Over a year ago

Yeah, backend is Postgres. And thanks for the suggestion @engineersmnky, it is good to consider

|

Siim Liiser · Accepted Answer · 2020-04-21 20:19:37Z

If you're not working on a legacy database and are able to change the schema, then I would suggest not using an integer backed enum. Using a string backed enum will make your database readable without the application code. Then when you add new values to your code, you don't need to document what the integers mean.

Instead of defining the enum as you do, define it as strings:

enum sound: {
  bark: 'bark',
  meow: 'meow', 
  moo: 'moo'
}

And make sure that the column in the database is also a string.

Now you get all the benefits of enum without the hassle of integers in the database. Your query will also work as-is and produce the result you asked for.

As long as the column is indexed, it's basically just as fast to query as an integer. It will just take a few more bytes of space.

If you want to enforce values on the database level, a postgres enum could also be considered.

Collectives™ on Stack Overflow

How can I display enum string key instead of integer value in SQL query?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related