Text primary keys?

[diraneyya]

Hello.

I am a self-hosting consultant and I am trying to figure out if Mathasar, a project completely new to me, can do the following.

I have this Routing table:

... and this Geolocation table:

I want to be able to link the two tables using the ip_address field. This requires that this text field be a primary key on one of the two tables.

How to do this? I only see number and UUID as possible primary key column types.

Alternatively, is there another way to do this? Seemed like a very easy starting point to test Mathaser.

I am curious about what I can use Mathaser for. It seems very well made but like many other open-source projects in the same space (Teable, BaseRow, NocoDB, etc.), it defines a "view" as a concept derived from a single table, which is very different from the modelling of a view in the relational model. This severely impacts our ability as users and consultants to make a great use out of it.

My ultimate dream is a UI or postgresql that makes the same basic functionality in a postgresql database available through the UI. We do not mind writing SQL and stuff. Fearing to expose functionality because it might intimidate is blocking away a lot of the power of the underlying technology.

Kind regards,
Orwa.

[zackkrida]

Hi @diraneyya. Linking these two tables is definitely possible in Mathesar! Let me show you a few ways.

First, a point of clarification. To link two tables, the foreign key does not need to reference a primary key. As long as the ip_address column on the referenced table (Geolocation, in your example) is both UNIQUE and NOT NULL, it can be used to define a foreign key relationship, even if it's a TEXT column.

Mathesar lets you create a relationship in two ways:

1. With the "Create a relationship" button, which will create a new column for you (it looks like you may have done this based on the last "Geolocation" column of your routing table).
2. By adding new foreign key constraints in the Table -> Advanced -> Constraints section.

This second approach sounds like what you're looking for. It will turn the ip_address column of the Routing table into a reference to the Geolocation table, based on the ip_address columns. Here's what configuring this in Mathesar looks like:

As you can see, after creating the new foreign key constraint the ip_address column in Mathesar will display as a link to the Geolocation table:

P.S: You're correct that Mathesar currently only allows users to create integer and UUID primary keys from the UI. Our goal with Mathesar is to give users access to all of the power of SQL without creating new abstractions on top of SQL. We also try to expose good defaults in the UI. We'd eventually like to allow users to create all possible types of primary keys in Mathesar.

Let me know if you have further questions! Thanks. Here's the SQL I used when producing those screenshots:

CREATE TABLE public.geolocation (
  id SERIAL PRIMARY KEY,
  ip_address TEXT NOT NULL UNIQUE,
  country_code TEXT,
  isp TEXT
);

CREATE TABLE public.routing (
  id SERIAL PRIMARY KEY,
  fqdn TEXT,
  ip_address TEXT,
  hops INTEGER,
  latency NUMERIC
);

[diraneyya]

Oh I figured it out.

As I was pasting the data into the table after marking the IP address as NN and UNIQUE, the paste was being halted because of a duplicate value:

mathesar_service  | [-30260] UniqueViolation: duplicate key value violates unique constraint "Geolocation_ip_address_key"
mathesar_service  | DETAIL:  Key (ip_address)=(2a01:2a8:8f07:c000:464e:6dff:fece:74b6) already exists.
mathesar_service  | CONTEXT:  ...

Hence, the remaining data was only the data up to this duplicate value.

[zackkrida]

@diraneyya I'm glad you got your paste working!

What is the best way to dump data n Mathesar?

Right now, the best way to dump data is to either use the pasting functionality, as you did above, or to import data into your tables outside of Mathesar

How to pronounce Mathesar

Good question! Here's a guide that pronounces the name based on it's International Phonetic Alphabet representation

How to run SQL queries against the database when running a postgres Docker Compose container (I plan on using an external database after this initial evaluation phase)

You can connect to a Postgres container running in docker with the following:

docker exec -it mathesar_dev_db psql -U <user-name> -d <database-name>

Here's an example from my local instance:

This is another way to import data, for example if you have a local CSV file you'd like to copy into the Postgres DB (I'll use my local demo dataset about a bike shop as an example):

cat mechanics.csv | docker exec -i mathesar_dev_db psql -U mathesar -d bike_shop -c "\COPY \"Bike Shop\".\"Mechanics\" FROM STDIN WITH (FORMAT csv, HEADER)"

The CSV header names will need to exactly match the column names in your database.

Thanks for trying Mathesar, and for the kind words! Building something that's useful to our users is our top priority so we're always very happy to chat with users, learn about their use cases, and offer support.

[diraneyya]

Any hopes of having a SQL query pane as part of the UI. Sort of like what Supabase does?

And what does the name mean? What is the inspiration behind it?

[zackkrida]

We've considered adding a SQL editor, and might at some point. We'd definitely like to hear from users on how an in-app SQL editor would help their workflows, so if that's something you'd ever want to hop on a call and chat about I'd love to.

As for the name, it's a reference to a character from the cult sci-fi film Galaxy Quest. It was originally just an internal name, but it stuck! The movie is great, I liked it before I started at Mathesar. Definitely recommend it 😆

[seancolsen]

@diraneyya if you want an SQL editor within Mathesar, I'd encourage you to upvote #2277. It would also be interesting to understand how you'd like to use and SQL editor within Mathesar. What problems would it solve for you? What features would you need from it? Any additional insight you can provide (as comments on that discussion) would help us in designing, prioritizing, and thinking through that feature.