Spaces are squeezed from column names when calling .select()

[rickwargo]

I am unable to specify column names that have spaces (an possibly other things) in a fetch statement. I do have a workaround, and that is to literally quote the column name in the args.

In the following example (yes, I know it just returns a bunch of nulls, if it worked), the generated API call fails when column contains a space.

      const {data, error} = await supabase
        .from(table_name)
        .select(column)
        .is(column, null);

For example, assuming the database had a table named "My Table" with a nullable column named "My Column", and the query specified table_name = "My Table" and column = "My Column", the resulting REST query is

https://xxxxxx.supabase.co/rest/v1/My%20Table?select=MyColumn&My+Column=is.null

The table name is correctly interpreted as case-sensitive with spaces, however, for the returned data, the column name is stripped of its space and it returns a 400 Bad Request HTTP error.

The error is:

{
  "code": "42703",
  "details": null,
  "hint": 'Perhaps you meant to reference the column "My Table.My Column".',
  "message": "column My Table.MyColumn does not exist"
}

I did not find in the API documentation where this behavior is specified.

[tengweiherr]

I faced a similar issue. In my case, I named my table "Base Salary", and when fetching data with the Supabase JS client, it removed the space in the table name in the query, resulting in an error.

Here is my code:

const { data, error } = await supabase
  .from('User Career')
  .select(`
      id, 
      jobTitle, 
      Base Salary (
        amount
      )
  `)

The error response when I performed the SELECT query:

{
    "code": "PGRST200",
    "details": "Searched for a foreign key relationship between 'User Career' and 'BaseSalary' in the schema 'public', but no matches were found.",
    "hint": "Perhaps you meant 'Base Salary' instead of 'BaseSalary'.",
    "message": "Could not find a relationship between 'User Career' and 'BaseSalary' in the schema cache"
}

After digging into the source code, I found that in the PostgrestQueryBuilder of postgrest.js, it actually removes whitespaces unless quoted. Hence, to keep the space in the table or the column name, you need to add quotes around it. For example:

const { data, error } = await supabase
  .from('User Career')
  .select(`
      id, 
      jobTitle, 
      "Base Salary" (
        amount
      )
  `)

@rickwargo let me know if it solves your issue.

library versions:

"next": "^14.2.14"
"react": "^18.3.1"
"@supabase/ssr": "^0.5.1"
"@supabase/supabase-js": "^2.45.4"

[tengweiherr]

It should be included in the Javascript API docs as a note or something, since we are allowed to create table / column name with spaces! I can help with a PR

[rickwargo]

Thank you, @tengweiherr. I agree it should be documented.

[tengweiherr]

PR is up!

not sure if @Hallidayo is the right person to tag, but I assume it is open for everyone to fix. Feel free to review my PR

[tengweiherr]

@Hallidayo Hi, can I get your and the team's help to review the PR? Thanks a lot!

[Hallidayo]

Hi everyone, A a pull request has been merged that fixes the issue I'm going to close this one now. Thanks @tengweiherr