Posts in 2020
-
PostgreSQL mini cookbook: Controlling Access to your data
February 27, 2020 in PostgreSQL
PostgreSQL mini cookbook: Controlling Access to your data, those tricks back to 2001 and still works today. 😉
Restricting users to certain records or certain fields with views The accounts team don’t need to see unfilled orders and the sales team don’t need to see bank details. You want to enforce this. Solution Create one view for each group of …
-
PostgreSQL mini cookbook: Automating processes
February 26, 2020 in PostgreSQL
PostgreSQL mini cookbook: Automating processes, those tricks back to 2001 and still works today. 😉
Enabling plpgsql You want to write functions and build triggers in Postgres' scripting language plpgsql. PL/pgSQL is a loadable procedural language for the PostgreSQL database system. The design goals of PL/pgSQL were to create a loadable procedural …
-
PostgreSQL mini cookbook: advanced query tricks
February 26, 2020 in PostgreSQL
PostgreSQL mini cookbook: advanced query tricks, those tricks back to 2001 and still works today. 😉
Having a dynamic field value without a lookup table You want a “calculated” column returned from a select but joining to a lookup table is either not practical or not desirable. Solution Use the CASE...WHEN...ELSE...END structure or write …
-
PostgreSQL mini cookbook: aggregate query tricks
February 25, 2020 in PostgreSQL
PostgreSQL mini cookbook: PostgreSQL aggregate query tricks, those tricks back to 2001 and still works today. 😉
Sum a field You want to calculate the total number of payments per customer, staff. Solution Use SUM() and GROUP BY: SELECTa,b,sum(c)FROMtGROUPBYa,b;Example: …
-
PostgreSQL mini cookbook: basic query tricks
February 24, 2020 in PostgreSQL
PostgreSQL mini cookbook: PostgreSQL basic query tricks, those tricks back to 2001 and still works today. 😉
Case insensitive searches You want to check equality or do a regular expression search but ignoring case Solution Either use lower() on the field and the value being compared or use the ILIKE or ˜˜* regular expression operators. Discussion If you …
-
Python unicode string lowercase and caseless match
February 23, 2020 in Python
str.lower()
andstr.casefold()
str.lower() and str.casefold() Starting with Python 3.0, strings are stored as Unicode. Python defined to two functions str.lower() and str.casefold() can be used to convert string to lowercase: str.lower() Return a copy of the string with all the …
-
psql 101
February 20, 2020 in PostgreSQL
psql
is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file.psql shell command List exist databases $ psql --list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ------------------------+-------+----------+-------------+-------------+------------------- dvdrental | dbch | UTF8 …
-
Azure function Did not find any initialized language workers
February 17, 2020 in Azure
Azure function app random response
500 Internal error
to client. log indicate Azure function: Did not find any initialized language workers.Symptom We deployed a very simple Azure function with nodejs runtime, on an Linux container. However after deployment, the service is not stable stable, it goes up and down, server random response 500 Internal error to client. From Azure’s log, …
-
hugo-page-lastmod
February 13, 2020 in Hugo
Use
.GitInfo
or.Page.Lastmod
as last update date in hugo pageUse .GitInfo or .Page.Lastmod Hugo have build-in support to get the last Git revision information for every content file. To enable Hugo get git commit info, first need change enableGitInfo to true in config.toml: Page.Lastmod is fetched from …
-
Add git commit to docker image tag in Azure pipeline
February 12, 2020 in Azure
Add git commit to docker image tag in Azure pipeline
Problem I have Azure devops pipeline to build docker image from source. I want the docker image have the source code revision info like git commit hash. I tried to use Build.SourceVersion as a docker image tag, Build.SourceVersion: The latest …