Is there a way to get/index unique column values?
Hey all! I'm building a real estate saas that manages and creates a website for properties.
Basically I have a "property_addresses" table that has columns like "city", "state", "suburb" and so on.
I need to be able to get all unique values for the column "suburb" (for example) that are "linked" to an organization.
Is there a way to do this supabase/postgres alone?
14 Replies
@Kaue It sounds like what you want is
SELECT DISTINCT - https://www.w3schools.com/postgresql/postgresql_select_distinct.phpidk if Supabase javascript sdk has a way to do a select distinct query, but you can always make a view
So it would be something like
Oh that sounds exactly like what I need!
Thanks a lot @Oli__
I'll give that a try, really hoping supabase supports it on the JS api.
I'll just need to add an organization column that I don't currently have
That was just an example, I don't really know your data schema so there might be some other way to link addresses to organizations
But the
SELECT DISTINCT suburb is the key bitYup, I think I can figure it out with
select distinct
My schema is super simple right now (I"m a frontend engineer so not even sure if it's the perfect database architecture).
I have those tables: organizations, properties and property_addresses
properties has an address foreign key that points to property_addresses and also an organization foreign key that points to organizations.
I was thinking of doing the same on the property_addresses table.
Not sure if its the most efficient way to do thisAh. So then you actually already have a link between addresses and organizations
You can write SQL to understand trasitive links like that
Something like that
https://www.w3schools.com/sql/sql_join.asp - this is a pretty good primer on the different types of joins and why you would use them, but in general, joins are the thing that lets you query one table based on its relationship to another table
Oooh I thought I'd just be able to do
Postgres doesn't inherently understand the relationships, you have to tell it how you want those two tables to be related by specifying joins
For more complex schemas, there may be multiple ways for those two tables to be related, and you would be relying on postgres to choose one, and it might choose wrong
So postgres makes you tell it explicitly how to follow the links
Got it!
I'll study a bit about
join
I guess it was a bit naive to try to build the backend on my own without at least a bit of a foundation on SQL. (even though Supabase does make it much easier).
I'm used to NoSQL (like Mongo and Firebase) so I don't know much about sqlSQL intimidates people sometimes, but it's really an incredibly powerful tool and there's nothing else quite like it in the world of databases. It's sort of like networking. Once you really understand it, it's like a superpower.
Totally worth a bit of studying IMO
NoSQL databases may be a bit easier to work with, but they can't do a lot of things that relational SQL databases can do, around enforcing data integrity and doing more advanced queries, especially in a single atomic transaction.
Yeap, totally agree with everything you said.
I think SQL (along with my love for UI) is one of the things that pushed me to focus on Frontend.
But yeah, I did start to feel limited by NoSQL. It's easier at the beginning, but it feels limiting as your application go.
I had built a lot of my app on Firebase, but I started to feel the limitations of NoSQL and decided to rewrite everything using Supabase.
It's being a bit tricky, but I do feel like I can do way more things with SQL
At my day job, we built a whole company on Firebase. It ended up being a 4-year nightmare trying to get off it and onto postgres. I have previously described Firebase as a glue trap for startups.
IMO your data integrity is one of the most important things in your app, and a big advantage to using SQL databases is that they can enforce your data integrity for you, so you always know it's correct. NoSQL databases rely on you to do it yourself, and it's very easy to make a mistake, and those mistakes compound and make your data very hard to work with.
SQL databases make you jump through a whole bunch of hoops defining schema before you can even start loading data, but all that is for very good reason - you should know what type of data you're actually trying to store! And if you can tell the database, then it will check your work for you.
Anyway I'm done rambling 😄
That's exactly what I was afraid of hahaha
I knew I could somehow make things work with firebase, but it felt like it wouldn't scale and it would be a nightmare to maintain down the road.
No worries, didn't feel like rambling. I'm definitely onboard with data integrity and typing . It'll take me a while but I'll get used to SQL. TBH the
join part was the only confusing thing for me so far. But it'll sink in eventually lol
Anyways, thanks for all the help @Oli__ !
It was super helpful, really!