Let’s see how to change a user’s password or your own password in PostgreSQL. We’ll also discuss how to avoid sending the password in clear over a network.
Category: PostgreSQL
PostgreSQL: Tables without columns
PostgreSQL allows to create, read and write tables without columns. Let’s see how to work with them, and whether they can have practical uses.
PostgreSQL: Check which privileges a user has on a table
Let’s see how to check what a user can do with a certain PostgreSQL table, or which users have a certain permission on a table.
PostgreSQL: Making a comparison case-insensitive
In PostgreSQL, string comparisons are case-sensitive by default. Making them case-insensitive requires the ILIKE operator and may require an additional index for performance reasons.
Find dates in a given year
Sometimes we want to find all events that happened in a given year. For example, a count of the sales that happened in the year 2000. It’s frequent to run a correct query that turns out to be slow. Let’s see how to do it properly.
PostgreSQL: Deleting or re-ordering ENUM elements with no disruption
PostgreSQL manual says that it is not possible to delete an element from an ENUM type, nor change their order. However, there is a way to do it in a single transaction.
PostgreSQL: Working with ENUM types
Sometimes a text column should not contain free texts, but only values from a predefined list. PostgreSQL allows to achieve this by creating an enumeration – or an ENUM type. This allows validation on input, and makes the values more efficient.
Comments on database objects
Why and how to write comments on database, tables, and other objects on most DBMSs. How to read comments.
Find texts starting, ending or containing a given string
How to select rows where a text column starts by, ends by, or contains a string. Including slightly less trivial cases.
Find the maximum text length in a column
How to get the length of the longest text in a column. Including Db2 specific syntax.