Schemas in postgresql

Archived Topics
1

Greeting,

we use multiple schemas in a single postgresql database. (ign 7.7.x)

  1. Database Query Browser should delimit all tablenames on the quick double click "select * from " feature…if you have a capital in the name you have to add the quotes manually …eg select * from fred works, but not select * from Fred

There is an option to delimit columns in the translator but not for tables.

  1. Database Query always returns tables from all schemas - if you have lots of tables schemas are useful to segregate various tables and still maintain cross schema query capability. Using separate databases is non workable as you cannot then run queries across multiple connections.

Using your option in the database connection to include schema in table names improves things by using schema, but it displays all tables from all schemas eg similar to post https://www.inductiveautomation.com/forum/viewtopic.php?f=70&t=7149&start=0&st=0&sk=t&sd=a&hilit=translator

To get around this I tried to use the filter option to solve my issue by creating a custom translator per schema to filter out the extra table names. The filter is however applied to the table name only not the combined schema.table so it does not work as one wished.

I realise this is an edge case, but either filtering on the combined schema.tablename or filter by schema would provide more functionality.

2
  1. Is it not feasible for you to use psql to test queries? I find features like EXPLAIN ANALYZE to be a huge help for more complex queries. Plus, psql doesn’t treat identifiers as case-sensitive, so
select * from fred

and

select * from FRED

both work fine.
I’m not really sure what you mean about the delimiters for tables?

  1. I believe you can use foreign data wrappers with 9.3+ to access other databases. See postgresql.org/docs/9.3/stat … s-fdw.html for info.

Or I’ve also heard that you can use the contrib module dblink for older postgres versions. See postgresql.org/docs/9.3/stat … ction.html for info.

3

Not really

select * from schema.fred

and

select * from schema.FRED

are the same in Postgres, but is completely different to

select * from schema.“FRED”

Postgres rolls all identifiers to lower case unless explicitly delimited (double quote char in this case).

So the first 2 queries both actually refer to an object schema.fred despite the case differences, and the last query points to an object schema.FRED where the object actually includes case characters.

As you can see the quote symbol is required to be explicit.

The query browser assumes the implicit model and assumes schema.FRED=Schema.“FRED” when they are not the same things.

When using postgres with arbitrary identifiers it always best to assume case sensitivity on identifiers and delimit the identifier…then it works properly for cased objects. The query browser for example returns the correctly cased object from the database, but due to failure to delimit cannot retrieve a cased object from the database without manual intervention.

  1. I agree that you can do foreign wrappers etc in recent psql, but if you do so you also need to create foreign user mappings, foreign tables etc creating exactly the clutter and complication that can easily be avoided by using schemas in the first place.

dblink has performance and other issues.

In any case our production server is 9.1 so options are limited to dblink…and that is not the solution.

Schemas in our usage are the proper solution.

As indicated filter to be able to include schemas would be nice but in no way essential.