Just sharing some PSQL tip I came up with to solve an issue I did not have to worry about in TPS land.
So I am in the process of changing a TPS system to PSQL.
One of our applications cycles through all our databases (folders) collecting data across our Dealers. Each Dealer’s data is in their own folder.
In TPS land
We have a table pointing to each of their TPS file folder and we do a simple IF EXISTS(FolderName) we know there is data and proceed to open and process the files.
In PSQL land
We still have the table which points to all the folders and the folder may exists, but not all have been converted to PSQL or are even active for some reason or another.
The IF EXISTS(FolderName) worked for the 1st part, but when we tried to connect to the database we would get error which we did not want to present to the user. And of course we did not want to continue the process we started. None of our files in the dct have the CREATE option. We do not want clarion to create files like we did in TPS. This would create the table in the database (if create option specified), but we still had the issue of the database existing.
So After checking to see if the folder exists I then do the following SELECT which returns True (1) or False (0) before trying to access any files in the target database.
select exists( SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower(‘dbname’));
Yes, but our TPS logic is
If folder found open the files (Create if needed) and continue
In SQL you have the added need to check the Database existence first
So I needed an easy way to say if folder does not exists or database not exist do not try to open all the files and process. Most of the logic just checks the folder and does a series of Opens (which don’t fail in TPS land because CW will just create them - no harm) A little more complex in SQL land.
But yes, we handle a lot of errors behind the scene - SQL brings a whole new set of issues to take into account. I was just sharing a solution I came up with.
Did you move to a seperate database for each or a seperate schema within one database?
Connecting to the Postgres database (ie the system database)
A list of schemas would be easy to get via a system view. SELECT schema_name FROM information_schema.schemata;
A List of databases can be had by SELECT datname FROM pg_database;
In postgres you can create sql that looks in schemas but you cannot look into other databases.
For what your’re doing sounds like schemas would be better storage for consolidated queries.
Each Customers data is in its own Database since the tables do not have the CustomerID in every record for the customer and we did not want to change the existing code logic.
Also we provide our customers a copy of all their data upon request (They own it, we don’t) and this makes it easier to do that.
You misunderstand a schema. It’s a namespace, a lot like a directory. A seperate set of tables reside in each. But you are able to select across them.
eg Select sum(field) From schema1.table1 union schema2.table1;
You can’t do that across databases in postgres.