# Databases : PostgreSQL : Overview > [!NOTE] [Data Source Name](https://postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) > ``` > postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...] > > postgresql:// postgresql://localhost postgresql://localhost:5432 postgresql://localhost/mydb postgresql://user@localhost postgresql://user:secret@localhost postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp postgresql://localhost/mydb?user=other&password=secret ## Overview PostgreSQL is a database server. It can host many named **databases**, which contain one or more **schemas**, which contain one or more **objects**: ``` Server Database (default: "postgres") Schema (default: "public") Data Types Domains Functions Operators Procedures Rewrite Rules Tables Columns Constraints Indexes Triggers Views ``` Starts with three databases: - `template0` — usable as template to create a "pristine" new database (without the customizations applied to `template1`); should never be modified - `template1` — used as default template when creating new databases; customize by adding objects - `postgres` — the "default" database; used by `psql` to connect when no database specified The server forks a new process for each connection. The supervisor process is always running, waiting for client connections, whereas client and associated server processes come and go. Queries are transmitted using plain text, i.e., there is no parsing done in the client. #### Installing [Instructions](https://postgresql.org/download/linux/ubuntu/) for setting up the PostgreSQL apt repository. **Packages** - `postgresql` -> postgresql-16 (server) - `postgresql-client` -> postgresql-client-16 - `postgresql-common` (automated apt repo config) #### Docker Compose ```yaml services: database: image: postgis/postgis:15-3.4-alpine volumes: - foo-database:/var/lib/postgresql/data ports: - 5432:5432 environment: POSTGRES_DB: foo POSTGRES_USER: foo POSTGRES_PASSWORD: foo! volumes: foo-database: ``` ## SQL #### Syntax - **statements** must end with a semicolon - the first 1-2 words in each statement is called the **command**. - whitespace is insignficant and can be used to separate tokens to avoid ambiguity - **keywords** and **identifiers** are case-insensitive unless double-quoted - PostgresQL actually treats all identifiers as lower-case unless double-quoted - identifiers must be <= 63 characters, *not* a reserved word, and start with `_a-zA-Z` followed by `_a-zA-Z0-9` - double-quoted identifiers can contain any character except code 0 aka NUL - single-line comments: `--` - multi-line *nestable* comments: `/* */` ###### Literals There are three kinds of implicitly-typed constants: strings, bit strings, and numbers. - Strings are single-quoted. Quotes are escaped with another quote: 'It''s easy!' - Consecutive strings separated by a newline are concatenated. ```sql SELECT 'foo' 'bar'; -- 'foobar' ``` - escape string: `E'la la\nnext line la la'` - bit string: `B'1001'` - hex string: `X'1FF'` - numerics: ```sql 42, 3.5, 4., .001, 5e2, 1.925e-3 0x12EF, 0o765, 0b1001 1_234_567, 42.345_678 -- underscores ignored 'Infinity', '-Infinity', 'NaN', 'inf', '-inf' -- case insensitive, must be quoted ``` - without decimal -> treated as `integer` (if fits), else `bigint` (if fits), else `numeric` - booleans: keyword `TRUE`, `FALSE`, `NULL` (also: 'true', 'false', 'yes', 'no', 'on', 'off', '1', '0')` - In some contexts you might have to cast `NULL` to `boolean` explicitly. (`NULL::boolean`) - UUIDs: accepts any case, with or without hyphens Constants can also be specified with explicit types using [[Tech/Databases/PostgreSQL/Types#Casting|Casting syntax]]. #### Suggested Style - upper case keywords (`SELECT`) - lower case types and functions (`bigint`, `count()`) - snake_case object names (`order_item`) - pluralized tables (`products`) - try to stay short, but avoid cryptic abbreviations #### Command Groups ``` DDL – Data Definition Language CREATE, DROP, ALTER, TRUNCATE DQL – Data Query Language SELECT DML – Data Manipulation Language INSERT, UPDATE, DELETE, CALL, LOCK DCL – Data Control Language GRANT, REVOKE TCL – Transaction Control Language COMMIT, SAVEPOINT, ROLLBACK, SET tx, SET constraint ``` #### Reserved Words ``` ALL, ANALYZE, AND, ANY, ARRAY, AS, ASC, ASYMMETRIC, AUTHORIZATION, BINARY, BOTH, CASE, CAST, CHECK, COLLATE, COLLATION, COLUMN, CONCURRENTLY, CONSTRAINT, CREATE, CROSS, CURRENT_CATALOG, CURRENT_DATE, CURRENT_ROLE, CURRENT_SCHEMA, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, DEFAULT, DEFERRABLE, DESC, DISTINCT, DO, ELSE, END, EXCEPT, FALSE, FETCH, FOR, FOREIGN, FREEZE, FROM, FULL, GRANT, GROUP, HAVING, ILIKE, IN, INITIALLY, INNER, INTERSECT, INTO, IS, ISNULL, JOIN, LATERAL, LEADING, LEFT, LIKE, LIMIT, LOCALTIME, LOCALTIMESTAMP, NATURAL, NOT, NOTNULL, NULL, OFFSET, ON, ONLY, OR, ORDER, OUTER, OVERLAPS, PLACING, PRIMARY, REFERENCES, RETURNING, RIGHT, SELECT, SESSION_USER, SIMILAR, SOME, SYMMETRIC, SYSTEM_USER, TABLE, TABLESAMPLE, THEN, TO, TRAILING, TRUE, UNION, UNIQUE, USER, USING, VARIADIC, VERBOSE, WHEN, WHERE, WINDOW, WITH ``` #### Value Expressions Value expressions are also called scalar expressions (or even simply expressions). A value expression is one of the following: - constant or literal value - column reference (`table.column`) - positional parameter reference, in the body of a function definition or prepared statement (`$number`) - subscripted expression (`exp[ subscript ]` or `exp[ low:hi ]`) - field selection expression (where exp is row type: `exp.field` or `exp.*`) - operator invocation - function call - aggregate expression - window function call - type cast - collation expression - scalar subquery (`SELECT max( pop ) FROM cities WHERE cities.state = states.name`) - array constructor - row constructor (`SELECT ROW( 1, 2.5, 'this is a test' )`) - another value expression in parentheses (used to group subexpressions and override precedence) ## Benchmarking ```sql EXPLAIN <query> -> show query plan EXPLAIN ANALYZE <query> -> run query and profile ```