New articles - OurBigBook.com

Country in Oceania by

Ciro Santilli 37 Updated 2025-07-16

Set function by

Ciro Santilli 37 Updated 2025-07-16

This section is about functions that operates on arbitrary sets.

SQL example by

Ciro Santilli 37 Updated 2025-07-16

We have some runnable SQL examples with assertion under the sequelize/raw directory.

These examples are written in the Sequelize library using raw queries.

Sequelize is used minimally, just to feed raw queries in transparently to any underlying database, and get minimally parsed results out for us, which we then assert with standard JavaScript. The queries themselves are all written by hand.

By default the examples run on SQLite. Just like the examples from sequelize example, you can set the database at runtime as:

./index.js or ./index.js l: SQLite
./index.js p: PostgreSQL. You must manually create a database called tmp and ensure that peer authentication works for it

Here we list only examples which we believe are standard SQL, and should therefore work across different SQL implementations:

nodejs/sequelize/raw/index.js: basic hello world to demonstrate the setup and very simple functionality
nodejs/sequelize/raw/many_to_many.js: illustrates many-to-many relations with JOIN. Contains:
SQL transaction examples:
- nodejs/sequelize/raw/commit_error.js: stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on PostgreSQL, once something fails inside a transaction, all queries in the current transaction are ignored, and COMMIT simply does a ROLLBACK. Let's check. Yup, true for Postgres, but false for SQLite, SQLite just happily runs anything it can, you really need ROLLBACK for it.
- SQL isolation level example
GROUP BY and SQL aggregate functions:
- nodejs/sequelize/raw/group_by_extra_column.js: let's see if it blows up or not on different DB systems, sqlite3 Node.js package allows it:
  - github.com/sequelize/sequelize/issues/5481#issuecomment-964387232
  - dba.stackexchange.com/questions/141594/how-select-column-does-not-list-in-group-by-clause/141600 says that it was allowed in SQL:1999 when there are no ambiguities due to constraints, e.g. when grouping by unique columns
  - github.com/postgres/postgres/blob/REL_13_5/src/test/regress/sql/functional_deps.sql#L27 shows that PostgreSQL wants it to work for UNIQUE NOT NULL, but they just haven't implemented it as of 13.5, where it only works if you group by PRIMARY KEY
  - dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b also says that UNIQUE NOT NULL doesn't work. Dan Lenski then points to a rationale mailing list thread.
- nodejs/sequelize/raw/group_by_max_full_row.js: here we try to get the full row of each group at which a given column reaches the max of the group
  - Postgres: has SELECT DISCINTCT ON which works perfectly if you only want one row in case of multiple rows attaining the max. ON is an extension to the standard unfortunately: www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT Docs specify that it always respects ORDER BY when selecting the row.
    stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case
    stackoverflow.com/questions/586781/postgresql-fetch-the-rows-which-have-the-max-value-for-a-column-in-each-group/587209#587209 also present in simpler form at stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of/123481#123481 gives a very nice OUTER JOIN only solution! Incredible, very elegant.
    dba.stackexchange.com/questions/171938/get-only-rows-with-max-group-value asks specifically the case of multiple matches to the max
  - SQLite:
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/48328243#48328243 teaches us that in SQLite min and max are magic and guarantee that the matching row is returned
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/72996649#72996649 Ciro Santilli uses the magic of ROW_NUMBER
    stackoverflow.com/questions/17277152/sqlite-select-distinct-of-one-column-and-get-the-others/71924314#71924314 get any full row without specifying which, we teach how to specify
    code.djangoproject.com/ticket/22696 WONTFIXed DISTINCT ON
    stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494 DISTINCT vs DISTINCT ON, somewhat related question
  - stackoverflow.com/questions/5803032/group-by-to-return-entire-row asks how to take the top N with distinct after order limit. I don't know how to do it in Postgres
- nodejs/sequelize/raw/most_frequent.js: illustrates a few variants of findind the mode, including across GROUP
  - stackoverflow.com/questions/12235595/find-most-frequent-value-in-sql-column/72979899#72979899
- nodejs/sequelize/raw/group_by_max_n.js: get the top N in each group
order results in the same order as IN:
- MysQL: stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause
- PostgreSQL:
  - stackoverflow.com/questions/866465/order-by-the-in-value-list
  - dba.stackexchange.com/questions/59394/order-of-returned-rows-with-in-statement
LIMIT by a running total: TODO links

MariaDB by

Ciro Santilli 37 Updated 2025-07-16

Dude's a legend. Sells company for a few million. Then forks the open source project next year. Love it.

PostgreSQL getting started by

Ciro Santilli 37 Updated 2025-07-16

On Ubuntu 20.10 PostgreSQL 12.6, login with psql on my default username without sudo fails with: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist

This is the one that worked on Ubuntu 21.04: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152

sudo -u postgres createuser -s $(whoami)
createdb $(whoami)

Explanation:

sudo -u postgres uses the postgres user via peer authentication
-s in createuser -s: make it a superuser
createdb: TODO why do we have to create a table with the same name as the user? Otherwise login fails.

You can now run psql without any password. This works without password due to peer authentication:

sudo cat /etc/postgresql/12/main/pg_hba.conf

shows that peer authentication is available to all users apparently:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

List users:

psql -c '\du'

output:

                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 ciro        | Superuser, Create role, Create DB                          | {}
 owning_user |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Delete user later on:

psql -c 'DROP USER username;'

Create a database:

createdb testdb0

Help toplevel:

help

Get help for Postgres commands such as \h and so on:

\?

List supported SQL commands:

\h

Show syntax for one type of command:

\h SELECT

List all databases:

psql -c '\l'

which shows:

    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
 ciro        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 testdb0     | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

Delete a database:

psql -c 'DROP DATABASE "testdb0";'

If you didn't give a database from the command line e.g.:

psql

you can do that afterwards with:

\c testdb0

Let's create a table and test that it is working:

psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'

List tables, no special tables:

psql testdb0 -c '\dt'

gives:

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | table0 | table | ciro
(1 row)

View table schema: stackoverflow.com/questions/109325/postgresql-describe-table

psql testdb0 -c '\d+ table0'

output:

                                      Table "public.table0"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 int0   | integer       |           |          |         | plain    |              |
 char0  | character(16) |           |          |         | extended |              |

Insert some data into it and get the data out:

psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;'

output:

 int0 |      char0
------+------------------
    2 | two
    3 | three
    5 | five
    7 | seven
(4 rows)

Delete the table:

psql testdb0 -c 'DROP TABLE table0;'

Hamilton's equations by

Ciro Santilli 37 Updated 2025-07-16

Analogous to what the Euler-Lagrange equation is to Lagrangian mechanics, Hamilton's equations give the equations of motion from a given input Hamiltonian:

\frac{\partial H}{\partial q _{j}} = - \overset{p}{˙}_{j} \frac{\partial H}{\partial p _{j}} = \overset{q}{˙}_{j}

(1)

So once you have the Hamiltonian, you can write down this system of partial differential equations which can then be numerically solved.

SQL COUNT function by

Ciro Santilli 37 Updated 2025-07-16

Have a look at some interesting examples under nodejs/sequelize/raw/many_to_many.js.

GROUP BY (SQL) by

Ciro Santilli 37 Updated 2025-07-16

Base pair by

Ciro Santilli 37 Updated 2025-07-16

SELECT (SQL) by

Ciro Santilli 37 Updated 2025-07-16

UPDATE (SQL) by

Ciro Santilli 37 Updated 2025-07-16

Mean absolute error by

Mean Absolute Error (MAE) is a common metric used to evaluate the performance of regression models. It measures the average magnitude of the errors in a set of predictions, without considering their direction (i.e., it takes the absolute values of the errors).

SQL standard version by

Ciro Santilli 37 Updated 2025-07-16

Spin representation by

The term "spin representation" is commonly used in the context of quantum mechanics and refers to a mathematical framework for describing the intrinsic angular momentum (spin) of quantum particles. Spin is a fundamental property of quantum particles like electrons, protons, neutrons, and other elementary and composite particles. ### Key Elements of Spin Representation: 1. **Quantum States**: Spin states are represented as vectors in a Hilbert space.

Euclidean symmetries by

Euclidean symmetries refer to the transformations that preserve the structure of Euclidean space, which is the familiar geometry of flat spaces (typically two-dimensional and three-dimensional spaces). These symmetries encompass various operations that can be applied to geometric figures without altering their fundamental properties, such as distances and angles. The main types of Euclidean symmetries include: 1. **Translations**: Shifting a figure from one location to another without rotation or reflection.

Affine symmetric group by

The affine symmetric group, often denoted as \( \text{Aff}(\mathbb{Z}/n\mathbb{Z}) \) or \( \text{Aff}(n) \), is an extension of the symmetric group that includes not only permutations of a finite set but also affine transformations. Specifically, it refers to a group of transformations that act on a finite cyclic group, typically represented as \( \mathbb{Z}/n\mathbb{Z} \).

Axiality (geometry) by

In geometry, axiality refers to a property or characteristic related to axes, particularly concerning symmetry and orientation. While the term isn't frequently used in mainstream geometry literature, it often relates to how certain objects or shapes are organized around an axis. In the context of geometry, axiality can describe: 1. **Symmetry**: An object is said to have axiality if it exhibits symmetry about an axis.

Circular symmetry by

Circular symmetry, often referred to as radial symmetry, is a type of symmetry where an object or shape appears the same when rotated around a central point. In other words, if you were to rotate the object through any angle about that central point, it would look unchanged. In the context of two-dimensional shapes, examples of circular symmetry include circles, wheels, and starfish. In three dimensions, objects like spheres and some types of flower arrangements exhibit circular symmetry.

Facial symmetry by

Facial symmetry refers to the degree to which one side of a person's face is a mirror image of the other side. In a perfectly symmetrical face, corresponding features (such as eyes, eyebrows, lips, and jawline) match in size, shape, and position on both sides. However, most human faces are not perfectly symmetrical; slight asymmetries are common and can even contribute to an individual's uniqueness and attractiveness.