however when the query is executed, CompanyData is getting converted to companydata.
Hello, here is docs of dynamic table name
To prevent SQL injection, Retool converts SQL queries into prepared statements. The exact behavior of the query depends on your specific database driver. However, most databases do not support prepared statements that have dynamic column names or dynamic table names, because it's difficult to prevent SQL injection in those cases.
That means the following query does not work in PostgreSQL, because the table name is dynamic and PostgreSQL doesn't support dynamic table names in prepared statements.
select * from {{textinput1.value === 'getUsers' ? 'users' : 'payments'}}
Instead, you should write two queries:
select * from users
select * from payments
Then, you can reference the correct SQL statement depending on the value of textinput1:
{
{
textinput1.value === "getUsers" ? users.data : payments.data;
}
}
You can use multi queries or Disable prepared statements
but if you use Retool hosted database, there is no setting to do so.
@rakshit087 , are you using the Retool Database or a different postgres db?
In case of the latter, you might need to include the schema.
I have already disabled the prepared statements, the thing is the list of tables is dynamic in nature so the names are not predefined.
I cannot include schema since the table names are dynamically generated.
If your table is not in the schema public or the proper default schema hasn't been set for the user, then you'll need to include the schema name in your query. That is what the error implicates, it can't find the table (in the schema). Postgres objects tend to be case insensitive by default, so it should not matter if you're trying to query companydata or CompanyData. It's also not best practice to use camelcase in table names, use lowercase with underscores instead.
Maybe this could work?
Select * from schema.{{listbox1.value}}
Or you could try it with double quotes around the table name:
Select * from "{{listbox1.value}}"
Thank you, I was not mentioning the schema.
select * from public."{{listbox1.value}}";
This worked