| /** | |
| Check number of connections to the DB. | |
| */ | |
| SELECT count(distinct(numbackends)) FROM pg_stat_database; |
| /** | |
| Usage and expansion of variables in SQL scripts to be executed with psql \i command. | |
| */ | |
| -- Expansion with quotes as varchar variables example | |
| \set analysis_id a | |
| delete from warehouse.analysis_tasks where analysis_id = :'analysis_id'; |
| -- Refresh a materialized view | |
| REFRESH MATERIALIZED VIEW whatever; |
| /** | |
| * | |
| * A Pl/PgSql function example. | |
| * | |
| */ | |
| create or replace function public.gs__split_qualified_name( | |
| _name text | |
| ) returns public.gs__qualified_name as | |
| $$ | |
| declare |
| /** | |
| Configure a new database for production, cutting | |
| default permissions from public to the postgres | |
| default database. | |
| */ | |
| \c postgres | |
| -- Drop default privileges | |
| revoke all privileges on database postgres |
Some hints and important stuff about PostgreSQL.
Reordering columns in existing tables with data: never do that, pointers to data seems to be lost. Redo the table if needed with create select and such.
Avoiding divisions by zero: the nullif function is an useful way to avoid them:
round(((sum(e001518)::float) / (nullif(sum(e166418), 0))::float)::numeric, 2)
| /** | |
| * | |
| * Example 0. | |
| * | |
| */ | |
| update trash.arqueta_point b set "Text"=a.text | |
| from | |
| ( | |
| select | |
| a.gid as gid, |
In the past, we tend to overstuff objects with fixed dependencies. Object A uses object B for a lot of things. Is the relation is 1:1, it may be ok to fix the instance of B as a member in A, but if not, if A can use in a given method ANY instance of B, then provide the B object directly to the method. This way the code is free to check for loaded dependencies into A before executing methods, it's up to the API user to ensure they are providing the right object to the method. This is specially useful when dealing with dependencies that comes from a DB, since they are async procedures and the code goes too complicated. Leave this complication to the client code.
| # ----- | |
| # | |
| # Some recipes for ImageMagick | |
| # | |
| # ----- | |
| # Cropping one pixel from the border, in place, batch version | |
| mogrify -crop +1+1 -crop -1-1 ../src/images/*.png |
First, the term working tree refers to the current checked out branch, the working directory in it's present state.
It is recommended that comparison are made on commited, clean states, and not on uncommited changes. To compare with the current uncommited state of the working tree move the changes to staged. If not, new files won't show up at the SEARCH & COMPARE dialog, but this is tricky.
Use the VSC Git extension. To evaluate if a stalled branch is worth to be maintained, check out the develop, master, or main branch. If not very sure of the result, create a merge test branch.
Right click on the stalled branch and select Compare with Working Tree. The SEARCH & COMPARE section will read Compating XXX to Working Tree, being XXX the right clicked branch. The interpretation of the output is based on the changes to be done to XXX to reach the state of WT. Therefore: