PostgreSQL

Name

1. postgresql / PostgreSQL
   1. postgresql.org
2. pgsql - PL/pgSQL
   1. mod_pgsql
   2. pdo_pgsql
3. postgres
   1. https://wiki.postgresql.org/wiki/Postgres
4. psql
   1. https://www.postgresql.org/docs/10/static/app-psql.html
5. pg
   1. pgloader
   2. https://www.pgadmin.org/

Creating cluster

https://www.postgresql.org/docs/10/static/creating-cluster.html

"/usr/local/pgsql/data or /var/lib/pgsql/data are popular"

• in Ubuntu 'apt-get install postgresql' creates /var/lib/postgresql/

initdb not found

• https://askubuntu.com/questions/371737/install-postgresql-why-is-initdb-unavailable
• https://superuser.com/questions/513386/setting-up-postgresql-gives-error-sudo-initdb-command-not-found

Command line

psql -e db < example.sql

Link

• https://www.tutorialspoint.com/postgresql/index.htm
• http://www.postgresqltutorial.com/

Documentation

curious cross

https://www.postgresql.org/docs/12/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP "The SIMILAR TO operator returns true or false depending on whether its pattern matches the given string. It is similar to LIKE, except that it interprets the pattern using the SQL standard's definition of a regular expression. SQL regular expressions are a curious cross between LIKE notation and common regular expression notation."

COPY

mulitline to file https://www.manniwood.com/2021_11_13/multiline_copy_command.html

# copy (
select i,
       d
  from t
 where i > 1) to stdout \g q.txt

pgloader

root@bionic:/var/log/apache2# psql -d anna
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
Type "help" for help.

anna=# DROP DATABASE tidb_all;
DROP DATABASE
anna=# CREATE DATABASE tidb_all;
CREATE DATABASE
anna=# \q

root@bionic:/var/log/apache2# pgloader
mysql://root:XXXXX@localhost/tidb_all pgsql:///tidb_all

(dann tablle siehe unten)

root@bionic:/var/log/apache2# psql -d tidb_all
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
Type "help" for help.

tidb_all=# REASSIGN OWNED BY root TO tobiasco;
REASSIGN OWNED
tidb_all=#

LC_COLLATE and LC_CTYPE

https://www.postgresql.org/docs/11/locale.html

"Some locale categories must have their values fixed when the database is created. You can use different settings for different databases, but once a database is created, you cannot change them for that database anymore. LC_COLLATE and LC_CTYPE are these categories. They affect the sort order of indexes, so they must be kept fixed, or indexes on text columns would become corrupt. (But you can alleviate this restriction using collations, as discussed in Section 23.2.) The default values for these categories are determined when initdb is run, and those values are used when new databases are created, unless specified otherwise in the CREATE DATABASE command."

15 schema public

"Nennenswert ist zudem, dass User aus Sicherheitsgründen im public beziehungsweise Default-Schema keine Tabellen mehr mit CREATE erzeugen dürfen." https://www.heise.de/news/Datenbank-PostgreSQL-15-fuehrt-endlich-MERGE-ein-7308296.html