Handling colums names containing spaces

[ddarno]

Below is an example of the SQL I'm trying to get via the API

psql -d -c "select "cHostname","Server Model" from "Server Today" where "cHostname" ilike '%nim%';"

cHostname | Server Model
------------+-------------------------
argnim1 | IBM,9113-550 (P5-550)
argnim2 | IBM,9113-550 (P5-550)
daaa2nim71 | IBM,9131-52A (P5-52A)
daah3nim71 | IBM,8406-71Y (P7-PS701)
hbnim1 | IBM,9133-55A (P5-55A)
(5 rows)

Some examples of what I get when I've tried using curl, as per the chat room the table name syntax seems to work fine with %20, but not for columns.

1. Example of the table and cHostname column, which works fine.

curl 'http://:3000/Server%20Today?select=cHostname&cHostname=ilike.nim'; echo ""
[{"cHostname":"argnim1"},{"cHostname":"argnim2"},{"cHostname":"daaa2nim71"},{"cHostname":"daah3nim71"},{"cHostname":"hbnim1"}]

2. Example of adding the column with the space and using %20, as per the last part of the error seems to ignore the %20 and truncate the column to Server

curl 'http://:3000/Server%20Today?select=cHostname,Server%20Model&cHostname=ilike.nim'; echo ""
{"hint":"Perhaps you meant to reference the column "Server Today.MQ Server".","details":null,"code":"42703","message":"column Server Today.Server does not exist"}

3. Example when trying to quote it for SQL.

curl 'http://:3000/Server%20Today?select=cHostname,"Server Model"&cHostname=ilike.nim'; echo ""
Bad Request

[vx3r]

Same problem with "." in column name

http://127.0.0.1:3000/vulns_details?pluginattributes.vuln_information.cpe=like.*microsoft*

pluginattributes.vuln_information.cpe is the column name, condition is never triggered..

[ddarno]

This may not be suitable for others, but in my use case I've got a workaround for now.

1. Create a separate schema for Postgrest use. (obviously need to update the config file to use it)
2. Using CREATE TABLE newschema.newtable (LIKE public."old table" INCLUDING ALL); To newtable has the space removed to fix that bit.
3. Simple use INSERT to take a copy of the source table into the new schema with new table.
4. That run a query which looks at the system tables to create suitable ALTER TABLE commands to rename the columns in the new schema.

The query I'm using is as follows:-
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' RENAME column ' || quote_ident(a.attname) || ' TO ' || REGEXP_REPLACE(REGEXP_REPLACE(quote_ident(a.attname), ' ', '', 'g'), '.', '', 'g')
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE a.attnum >= 1 -- 10/01/2018 added = as was not processing first column in the table.
AND reltype > 0
AND a.attname <> 'geom'
AND NOT a.attisdropped
AND n.nspname !~~ 'pg_%' -- exclude catalog & temp tables, to be sure
AND n.nspname = 'api' -- specific new API schema name
AND REGEXP_REPLACE(REGEXP_REPLACE(quote_ident(a.attname), ' ', '', 'g'), '.', '', 'g') <> quote_ident(a.attname) -- Only include ALTER TABLE lines if value changes, can not rename columns to the same name.

[begriffs]

✔️ reproduced:

create table "fun times" as select generate_series(1,10) as "fun column";

# this one works
curl "localhost:3000/fun%20times"

# this one does not
curl "localhost:3000/fun%20times?select=fun%20column"

{
  "hint":null,
  "details":null,
  "code":"42703",
  "message":"column fun times.fun does not exist"
}