Postgresql :- Error – “relation … is not a btree index”

Background Reviewing PostgreSQL metadata and ran into a tiny error. Error The error reads ... Text Image Premise PostgreSQL supports a variety of index types. Among supported index types are B-tree and fulltext ( Gin and Gist ). Remediation Outline Because the set of operations supported by an Index Type may vary, it is best … Continue reading Postgresql :- Error – “relation … is not a btree index”

PostgreSQL – Repair Application Binaries

Background Working from the console and not careful, it is not so hard to make stupid mistakes. Scenario Yesterday while trying to export and import files, I made a freshman mistake. To export data, I issued this command pg_dumpall > datafile Conversely, to import data, issued this command pg_dumpall < datafile Significance Directing datafile to … Continue reading PostgreSQL – Repair Application Binaries

PostgreSQL – v12 – Importing data from previous version

Background Let us import data from PostgreSQL Version 11 into Version 12. Lineage PostgreSQL – v12 – Installation/Upgrade on MS Windows Link   Outline Previous Instance Start previous Instance Export Data Options pg_dumpall Using pg_dumpall, export data Stop previous Instance New Instance Start new Instance Import Data Options psql Using psql, import data Review imported … Continue reading PostgreSQL – v12 – Importing data from previous version

PostgreSQL – v12 – Installation/Upgrade on MS Windows

Background PostgreSQL Version 12 was released On October 3rd, 2019. That is about two weeks ago. Since then I have wanted to upgrade our Development instance from Version 11 to Version 12. Release Notes A brief summary of the Release Notes are available here.   Installation Outline Here are the steps we will take :- … Continue reading PostgreSQL – v12 – Installation/Upgrade on MS Windows

PostgreSQL – Table Value Constructor

Background Part of a DBA's workload is to engage the Developers Community and make sure that SQL Changes are streamline and easy to review and deploy. Adding Data Recently we needed to add about one hundred records to a single table. Table Value Constructor Thankfully SQL 92 thought quite a bit ahead and recommends support … Continue reading PostgreSQL – Table Value Constructor

PostgreSQL – v12 – Release Notes

Background PostgreSQL version 12 was released yesterday, Thursday 2019-Oct-3rd. Features and Enhancements Here are some of the enhancements. Performance Index B-tree Index Indexes utilize up to 40% less storage compared to Version 11 Statistics More reliable statistics on non-uniformly distributed datasets Table Partitioning Better partition elimination when querying limited subset of the data More performant … Continue reading PostgreSQL – v12 – Release Notes

PostgreSQL – Full Text Search – Day 1

Background Let us play with Full Text Search against our PostgreSQL table. Table Query Traditional Query Find Verses :- Jesus and Peter SQL Output Full Text Query Find Verses :- Jesus and Peter Outline to_tsvector([column-name]) to_tsvector(verse) to_tsquery Syntax :- to_tsquery([operand]) Sample :- to_tsquery('(Jesus & Peter)')) SQL Output ' Query Plan Indexes Functional indexes can be … Continue reading PostgreSQL – Full Text Search – Day 1