You are mixing up the transaction log (WAL) and the regular text log file. The latter does contain statements (if the configuration is set like that), while the transaction log doesn’t contain statements at all, just binary information about what has changed in which block. You won’t be able to recommend an index just from … Read more
There are two methods you can use. Both require creating a user and a database. By default psql connects to the database with the same name as the user. So there is a convention to make that the “user’s database”. And there is no reason to break that convention if your user only needs one … Read more
A PostgreSQL “schema” is roughly the same as a MySQL “database”. Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.
If all the candidate keys of a relation contain multivalued attributes: Introduce a surrogate attribute for at least one multivalued attribute. For each attribute you deem “composite” (having heterogeneous components, like a tuple): For each attribute component that can be missing: Add a relation with attributes of some multivalue-free candidate key and an attribute for … Read more
Database Normalization is all about constructing relations (tables) that maintain certain functional dependencies among the facts (columns) within the relation (table) and among the various relations (tables) making up the schema (database). Bit of a mouth-full, but that is what it is all about. A Simple Guide to Five Normal Forms in Relational Database Theory … Read more
The date format model elements are listed in the Datetime Format Models documentation: SELECT to_timestamp_tz (‘2014-09-12T11:53:06+00:00’, ‘YYYY-MM-DD”T”HH24:MI:SSTZH:TZM’) FROM DUAL TO_TIMESTAMP_TZ(‘2014-09-12T11:53:06+00:00′,’YYYY-MM-DD”T”HH24:MI:SSTZH:TZM’) ————————————————————————— 12-SEP-14 11.53.06.000000000 +00:00 The fixed T can be included as a character literal: You can include these characters in a date format model: Punctuation such as hyphens, slashes, commas, periods, and colons Character literals, … Read more
There is a difference between EAV done faithfully or badly; 5NF done by skilled people or by those who are clueless. Sixth Normal Form is the Irreducible Normal Form (no further Normalisation is possible). It eliminates many of the problems that are common, such as The Null Problem, and provides the ultimate method identifying missing … Read more
Use “Insert Only Databases” The basic idea is that you never update or delete data. Each table has 2 datetime columns from and to. They start with the value null in each (beginning of time to end of time) When you need to “change” the row you add a new row, at the same time … Read more
Maintainability of your code is always a big concern when determining where business logic should go. Integrated debugging tools and more powerful IDEs generally make maintaining middle tier code easier than the same code in a stored procedure. Unless there is a real reason otherwise, you should start with business logic in your middle tier/application … Read more