### Install and Verify PostgreSQL Unit Extension

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

SQL commands to install the PostgreSQL Unit extension and verify its installation by checking the extension version.

```sql
-- Install the extension in your database
CREATE EXTENSION unit;

-- Verify installation
SELECT extversion FROM pg_extension WHERE extname = 'unit';
```

--------------------------------

### Install postgresql-unit

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Commands to compile and install the postgresql-unit extension from source. Requires PostgreSQL server headers, flex, and bison. The PG_CONFIG environment variable must be set correctly.

```bash
sudo apt-get install build-essential postgresql-server-dev-10 flex bison
make PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config
sudo make install PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config
```

--------------------------------

### Example Usage: Custom Unit Conversion

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Example SQL query showing a unit conversion using a custom-defined prefix 'foobar'.

```sql
SELECT '1 foobar'::unit;
```

--------------------------------

### Create Unit Values in PostgreSQL

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Examples of creating unit values using string literal casting, constructor functions, and handling complex expressions like fractions and time formats.

```sql
-- String literal casting
SELECT '800 m'::unit;
-- Output: 800 m

SELECT '120 km/h'::unit;
-- Output: 33.3333333333333 m/s

SELECT '9.81 N'::unit / 'kg';
-- Output: 9.81 m/s^2

-- Using constructor functions
SELECT meter(100);
-- Output: 100 m

SELECT kilogram(2.5);
-- Output: 2.5 kg

SELECT second(3600);
-- Output: 01:00:00 s

-- Complex expressions
SELECT '1|2 m / h'::unit;  -- Fractions using |
-- Output: 138.888888888889 µm/s

SELECT '10:05:30 s'::unit;  -- Time format
-- Output: 10:05:30 s
```

--------------------------------

### Example Usage: Earth Mass

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Example SQL query demonstrating the usage of the 'unit' type to represent the Earth's mass.

```sql
SELECT 'earthmass'::unit as earth;
```

--------------------------------

### PostgreSQL Unit Type Example

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Demonstrates a basic usage of the 'unit' data type in PostgreSQL, showing how to cast a string to a unit and perform a simple conversion. This example is useful for understanding the fundamental syntax.

```sql
SELECT '1 m'::unit @ 'legobricks' AS one_meter;
-- Expected Output:
-- one_meter
-----------------------------
-- 104.166666666667 legobricks
```

--------------------------------

### Perform Temperature Conversions in PostgreSQL

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Examples of absolute temperature conversion (e.g., Fahrenheit to Kelvin) and temperature difference calculation using the Unit extension. Also shows Celsius constructor.

```sql
-- Absolute temperature conversion
SELECT '5 °F'::unit;
-- Output: 258.15 K

SELECT '5 °F'::unit @ '°C';
-- Output: -14.9999999999999 °C

SELECT celsius(100);
-- Output: 373.15 K

-- Temperature difference (no offset applied)
SELECT '5 * °F'::unit;
-- Output: 2.77777777777778 K
```

--------------------------------

### Adding Custom Unit Prefixes

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Illustrates how to extend the unit system by adding custom prefixes. This involves inserting rows into the `unit_prefixes` table with the prefix name, factor, and definition. The example shows adding 'foo' as a prefix and then using it.

```sql
-- Check if unit exists
SELECT '1 foobar'::unit;
-- ERROR: unit "foobar" is not known

-- Add custom prefix
INSERT INTO unit_prefixes (prefix, factor, definition)
VALUES ('foo', 42, 'custom prefix for testing');

-- Now it works (42 * bar where bar = 100000 Pa)
SELECT '1 foobar'::unit;
-- Output: 4.2 MPa

-- View all prefixes
SELECT * FROM unit_prefixes WHERE prefix LIKE 'foo%'
```

--------------------------------

### Perform Root Operations on Units in PostgreSQL

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Examples of performing square root and cube root operations on unit values using the `sqrt`, `|/`, `cbrt`, and `||/` operators.

```sql
-- Square root
SELECT sqrt('4 m^2'::unit);
-- Output: 2 m

SELECT |/'16 s^-4'::unit;
-- Output: 4 Hz^2

-- Cube root
SELECT cbrt('8 m^3'::unit);
-- Output: 2 m

SELECT ||/'-27 s^-6'::unit;
-- Output: -3 Hz^2
```

--------------------------------

### PostgreSQL Unit Output with IEC Binary Prefixes

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

This example demonstrates setting the 'unit.byte_output_iec' option to 'on' to enable IEC binary prefixes (KiB, MiB, etc.) for byte output. It then shows the conversion of '4 TB' to 'TiB'.

```sql
SET unit.byte_output_iec = on;
SELECT '4 TB'::unit AS disk_sold_as_4tb;
```

--------------------------------

### PostgreSQL Unit Conversion Example

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Demonstrates converting a temperature value from Fahrenheit to Celsius using the 'unit' extension. It showcases the `@` operator for unit conversion and how to specify target units and increments.

```sql
SELECT '5 °F'::unit, '5 * °F'::unit, '5 °F'::unit @ '°C' AS to_celsius, '5 °F'::unit @ '1 * °C' AS celsius_increments;
-- Expected Output:
--    unit   |        unit        |      to_celsius      | celsius_increments
-- --------+------------------------+----------------------+--------------------
-- 258.15 K | 2.77777777777778 K | -14.9999999999999 °C | 258.15 * 1 * °C
```

--------------------------------

### Extract Dimension and Value in PostgreSQL

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

SQL examples for extracting the dimensional representation and the numeric value of a unit using the `dimension` and `value` functions. Includes rounding.

```sql
-- Get dimension of a unit (base units with value 1)
SELECT dimension('500 km'::unit);
-- Output: 1 m

SELECT dimension('9.81 N'::unit);
-- Output: 1 kg m/s^2

SELECT dimension('100 W'::unit);
-- Output: 1 kg m^2/s^3

-- Extract numeric value
SELECT value('500 km'::unit);
-- Output: 500000

SELECT value('9.81 N'::unit);
-- Output: 9.81

-- Round to nearest integer
SELECT round('3.7 m'::unit);
-- Output: 4 m
```

--------------------------------

### Unit Input Expression Syntax Examples

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Illustrates various ways to construct input expressions for the PostgreSQL 'unit' data type. This covers arithmetic operations, exponentiation, fractions, time formats, and function calls, showcasing the flexibility of the input parser.

```sql
-- Multiplication: expr expr
'2 m' * '3 kg'

-- Operators: +, -, *, /
'10 m/s + 5 m/s'

-- Exponentiation: expr^integer or Unicode superscripts
'10 m/s^2'
'10 m/s²'

-- Parentheses
'(10 m) / (2 s)'

-- Multiplication/Division precedence (equivalent)
'kg/s^2*A'
'kg/s^2 A'

-- Numeric Fraction: N|M
'3|4 m'

-- Time values: hh:mm:ss[.sss]
'10:05:30 s'

-- Functions: sqrt, exp, ln, log2, asin, tan
'sqrt(16 m^2)'
'exp(2)'
'ln(100)'
'log2(8)'
'asin(1)'
'tan(0)'

-- Example with a cast:
'1|2 m / h'::unit
```

--------------------------------

### Unit Output Formatting Examples

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Demonstrates how the PostgreSQL 'unit' data type formats its output. It covers the general format `+-N x*y/z*w`, special formatting for time values, and conversions to well-known SI derived units like Hertz, Newton, and Pascal.

```sql
-- General Output Format: +-N x*y/z*w
SELECT '100 kg m / s^2'::unit;

-- Time Formatting (if unit.time_output_custom is set and value >= 1 minute):
SELECT '3600 s'::unit;

-- SI Derived Units (example: Hertz)
SELECT '1/s'::unit;

-- SI Derived Units (example: Newton)
SELECT '1 kg m / s^2'::unit;

-- SI Derived Units (example: Pascal)
SELECT '1 kg / (m s^2)'::unit;

-- Note: Dimension preservation may not occur (e.g., N m converted to J)
SELECT '1 N m'::unit;
```

--------------------------------

### SQL: Optimize Unit Column Queries with Indexing

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Explains how to create an index on a unit column in PostgreSQL to improve query performance for filtering and ordering operations. Examples show creating an index and using it in WHERE clauses and ORDER BY clauses.

```sql
-- Create index on unit column
CREATE INDEX idx_product_weight ON products (weight);

-- Use in WHERE clause
SELECT * FROM products
WHERE weight > '3 kg'::unit
AND weight < '10 kg'::unit;

-- Ordering by units
SELECT name, power FROM products
ORDER BY power DESC;
```

--------------------------------

### PostgreSQL Unit Conversion with Scale and Time

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

This example shows a unit conversion from '2 MB/min' to 'GB/d' using the '@' operator for arbitrary scale conversion. It highlights the flexibility in converting between different units and time scales.

```sql
SELECT '2 MB/min'::unit @ 'GB/d' AS traffic;
```

--------------------------------

### SQL: Enforce Unit Constraints for Data Validation

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Demonstrates creating a table with unit constraints to validate sensor readings for temperature and pressure. It includes examples of valid and invalid inserts to show how constraints prevent incorrect data entry based on unit dimensions.

```sql
-- Create a table with unit constraints
CREATE TABLE sensor_readings (
    id serial PRIMARY KEY,
    timestamp timestamptz DEFAULT now(),
    temperature unit,
    pressure unit,
    CONSTRAINT valid_temperature CHECK (
        dimension(temperature) = dimension('1 K'::unit)
        AND value(temperature @ 'K') > 0
    ),
    CONSTRAINT valid_pressure CHECK (
        dimension(pressure) = dimension('1 Pa'::unit)
        AND value(pressure) > 0
    )
);

-- Valid insert
INSERT INTO sensor_readings (temperature, pressure)
VALUES ('25 °C'::unit, '101325 Pa'::unit);

-- Invalid insert (wrong dimension)
INSERT INTO sensor_readings (temperature, pressure)
VALUES ('25 m'::unit, '101325 Pa'::unit);
-- ERROR: dimension check fails
```

--------------------------------

### PostgreSQL Unit Addition Example

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Demonstrates adding two unit values ('800 m' and '500 m') using the custom 'unit' datatype in PostgreSQL. The result is a combined length, automatically scaled to '1.3 km'.

```sql
SELECT '800 m'::unit + '500 m' AS length;
```

--------------------------------

### PostgreSQL Range Type with Units

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

This example utilizes the 'unitrange' function to create a range type encompassing two unit values ('earthradius_polar' and 'earthradius_equatorial'). The output displays the defined range in meters.

```sql
SELECT unitrange('earthradius_polar', 'earthradius_equatorial') AS earthradius;
```

--------------------------------

### SQL: Manage Product Catalog with Unit Types and Conversions

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Shows how to create a product catalog table with columns for weight, volume, and power using the unit data type. It includes inserting data with unit annotations and querying with unit conversions and aggregation.

```sql
-- Create a product catalog with units
CREATE TABLE products (
    id serial PRIMARY KEY,
    name text,
    weight unit,
    volume unit,
    power unit
);

INSERT INTO products (name, weight, volume, power) VALUES
    ('Laptop', '2 kg'::unit, '2 l'::unit, '65 W'::unit),
    ('Monitor', '5 kg'::unit, '15 l'::unit, '30 W'::unit),
    ('Server', '25 kg'::unit, '50 l'::unit, '500 W'::unit);

-- Query with unit conversions
SELECT
    name,
    weight @ 'lb' AS weight_lbs,
    power @ 'kW' AS power_kw,
    (power * hour(24) * day(365)) @ 'kWh' AS annual_energy
FROM products;

-- Aggregate across products
SELECT
    sum(weight) AS total_weight,
    sum(volume) AS total_volume,
    sum(power) AS total_power
FROM products;
```

--------------------------------

### Configure PostgreSQL Unit Extension Options

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

SQL commands to configure various output formats for the Unit extension, including IEC binary prefixes, base units, superscripts, custom time formats, and float precision.

```sql
-- Output byte quantities using IEC binary prefixes (Ki, Mi, Gi)
SET unit.byte_output_iec = on;
SELECT '4 TB'::unit;
-- Output: 3.63797880709171 TiB

-- Output values using only base types without prefixes
SET unit.output_base_units = on;
SELECT '5 km'::unit;
-- Output: 5000 m

-- Output unit exponents using Unicode superscripts
SET unit.output_superscript = on;
SELECT '9.81 m/s^2'::unit;
-- Output: 9.81 m/s²

-- Format time values using custom units (minutes, hours, days)
SET unit.time_output_custom = on;
SELECT '1 kilosecond'::unit;
-- Output: 00:16:40 s

-- Control display precision
SET extra_float_digits = -12;
SELECT '25m'::unit @ 'ft';
-- Output: 82 ft
```

--------------------------------

### PostgreSQL CREATE EXTENSION Command

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

This snippet shows the SQL command to create the 'unit' extension in PostgreSQL. It's a prerequisite for using the SI unit datatype. No specific inputs or outputs are defined beyond the successful creation of the extension.

```sql
CREATE extension unit;
```

--------------------------------

### Available Unit Types and Operators

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Lists all available unit types, functions, and operators provided by the PostgreSQL unit extension for comprehensive unit management.

```APIDOC
## PostgreSQL Unit Extension Objects

### Description
This section provides a comprehensive list of all functions, operators, and types available in the PostgreSQL `unit` extension.

### Method
N/A (SQL Object Listing)

### Endpoint
N/A

### Parameters
N/A

### Request Example
N/A

### Response
#### Success Response (Listing)
- **Functions**: A list of all available functions with their signatures and descriptions.
- **Operators**: A list of all overloaded operators for unit manipulation.
- **Types**: A list of custom data types, including `unit`, `unit_accum_t`, and `unitrange`.

#### Response Example
```
           Objects in extension "unit"
                Object description
--------------------------------------------------
 function ampere(double precision)
 function au(double precision)
 function avg(unit)
 function becquerel(double precision)
 function byte(double precision)
 function candela(double precision)
 function cbrt(unit)
 function celsius(double precision)
 function coulomb(double precision)
 function day(double precision)
 function dbl_unit_div(double precision,unit)
 function dbl_unit_mul(double precision,unit)
 function decibel(double precision)
 function degree_arc(double precision)
 function dimension(unit)
 function farad(double precision)
 function gray(double precision)
 function hectare(double precision)
 function henry(double precision)
 function hertz(double precision)
 function hour(double precision)
 function joule(double precision)
 function katal(double precision)
 function kelvin(double precision)
 function kilogram(double precision)
 function liter(double precision)
 function lumen(double precision)
 function lux(double precision)
 function max(unit)
 function meter(double precision)
 function min(unit)
 function minute_arc(double precision)
 function minute(double precision)
 function mole(double precision)
 function newton(double precision)
 function ohm(double precision)
 function pascal(double precision)
 function radian(double precision)
 function round(unit)
 function second_arc(double precision)
 function second(double precision)
 function siemens(double precision)
 function sievert(double precision)
 function sqrt(unit)
 function stddev_pop(unit)
 function stddev_samp(unit)
 function stddev(unit)
 function steradian(double precision)
 function sum(unit)
 function tesla(double precision)
 function tonne(double precision)
 function unit_accum(unit_accum_t,unit)
 function unit_add(unit,unit)
 function unit_at_double(unit,text)
 function unit_at(unit,text)
 function unit_avg(unit_accum_t)
 function unit_cmp(unit,unit)
 function unit_dbl_div(unit,double precision)
 function unit_dbl_mul(unit,double precision)
 function unit_diff(unit,unit)
 function unit_div(unit,unit)
 function unit(double precision)
 function unit_eq(unit,unit)
 function unit_ge(unit,unit)
 function unit_greatest(unit,unit)
 function unit_gt(unit,unit)
 function unit_in(cstring)
 function unit_is_hashed(cstring)
 function unit_least(unit,unit)
 function unit_le(unit,unit)
 function unit_load()
 function unit_lt(unit,unit)
 function unit_mul(unit,unit)
 function unit_neg(unit)
 function unit_ne(unit,unit)
 function unit_out(unit)
 function unit_pow(unit,integer)
 function unitrange(unit,unit)
 function unitrange(unit,unit,text)
 function unit_recv(internal)
 function unit_reset()
 function unit_send(unit)
 function unit_stddev_pop(unit_accum_t)
 function unit_stddev_samp(unit_accum_t)
 function unit_strict_cmp(unit,unit)
 function unit_strict_eq(unit,unit)
 function unit_strict_ge(unit,unit)
 function unit_strict_gt(unit,unit)
 function unit_strict_le(unit,unit)
 function unit_strict_lt(unit,unit)
 function unit_strict_ne(unit,unit)
 function unit_sub(unit,unit)
 function unit_var_pop(unit_accum_t)
 function unit_var_samp(unit_accum_t)
 function value(unit)
 function variance(unit)
 function var_pop(unit)
 function var_samp(unit)
 function volt(double precision)
 function watt(double precision)
 function weber(double precision)
 operator class unit_ops for access method btree
 operator class unit_strict_ops for access method btree
 operator /(double precision,unit)
 operator *(double precision,unit)
 operator family unit_ops for access method btree
 operator family unit_strict_ops for access method btree
 operator ||/(NONE,unit)
 operator |/(NONE,unit)
 operator -(NONE,unit)
 operator /(unit,double precision)
 operator *(unit,double precision)
 operator ^(unit,integer)
 operator @(unit,text)
 operator @@(unit,text)
 operator <<=(unit,unit)
 operator <<>>(unit,unit)
 operator <<(unit,unit)
 operator <=(unit,unit)
 operator <>(unit,unit)
 operator <(unit,unit)
 operator ==(unit,unit)
 operator =(unit,unit)
 operator >=(unit,unit)
 operator >>=(unit,unit)
 operator >>(unit,unit)
 operator >(unit,unit)
 operator -(unit,unit)
 operator /(unit,unit)
 operator *(unit,unit)
 operator +(unit,unit)
 table unit_prefixes
 table unit_units
 type unit
 type unit_accum_t
 type unitrange
(136 rows)
```
```

--------------------------------

### Perform Arithmetic Operations with Units in PostgreSQL

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Demonstrates various arithmetic operations on unit values, including addition, subtraction, multiplication, division, exponentiation, and negation, while maintaining dimensional consistency.

```sql
-- Addition and subtraction (same dimension required)
SELECT '800 m'::unit + '500 m';
-- Output: 1.3 km

SELECT '3 m'::unit - '1 µm';
-- Output: 2.999999 m

-- Multiplication and division
SELECT '5 kg'::unit * '2 m/s^2';
-- Output: 10 N

SELECT '100 J'::unit / '10 s';
-- Output: 10 W

SELECT meter() * meter();
-- Output: 1 m^2

-- Exponentiation
SELECT meter(100) ^ 2;
-- Output: 10 km^2

SELECT second(0.02) ^ -1;
-- Output: 50 Hz

-- Negation
SELECT -'5 N'::unit;
-- Output: -5 N
```

--------------------------------

### Adding Custom Units

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Shows how to define new units within the system by inserting into the `unit_units` table. This includes defining units with and without offsets, and the importance of clearing the unit cache using `unit_reset()` after modifications. It also demonstrates checking if a unit is cached.

```sql
-- Add a custom unit
INSERT INTO unit_units (name, unit, shift, definition)
VALUES ('legobrick', '9.6 mm'::unit, NULL, 'Height of a LEGO brick');

-- Use the custom unit
SELECT '1 m'::unit @ 'legobricks';
-- Output: 104.166666666667 legobricks

-- Add a shifted unit (with offset)
INSERT INTO unit_units (name, unit, shift, definition)
VALUES ('customtemp', '1 K'::unit, 100.0, 'Custom temperature scale');

-- Clear cache after modifying units
SELECT unit_reset();

-- Check if unit is cached
SELECT unit_is_hashed('legobrick');
-- Output: true/false
```

--------------------------------

### PostgreSQL Unit Extension Objects

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Lists all available objects (functions, operators, types, tables) provided by the PostgreSQL 'unit' extension. This serves as a reference for the extension's capabilities.

```sql
-- Functions
function ampere(double precision)
function au(double precision)
function avg(unit)
function becquerel(double precision)
function byte(double precision)
function candela(double precision)
function cbrt(unit)
function celsius(double precision)
function coulomb(double precision)
function day(double precision)
function dbl_unit_div(double precision,unit)
function dbl_unit_mul(double precision,unit)
function decibel(double precision)
function degree_arc(double precision)
function dimension(unit)
function farad(double precision)
function gray(double precision)
function hectare(double precision)
function henry(double precision)
function hertz(double precision)
function hour(double precision)
function joule(double precision)
function katal(double precision)
function kelvin(double precision)
function kilogram(double precision)
function liter(double precision)
function lumen(double precision)
function lux(double precision)
function max(unit)
function meter(double precision)
function min(unit)
function minute_arc(double precision)
function minute(double precision)
function mole(double precision)
function newton(double precision)
function ohm(double precision)
function pascal(double precision)
function radian(double precision)
function round(unit)
function second_arc(double precision)
function second(double precision)
function siemens(double precision)
function sievert(double precision)
function sqrt(unit)
function stddev_pop(unit)
function stddev_samp(unit)
function stddev(unit)
function steradian(double precision)
function sum(unit)
function tesla(double precision)
function tonne(double precision)
function unit_accum(unit_accum_t,unit)
function unit_add(unit,unit)
function unit_at_double(unit,text)
function unit_at(unit,text)
function unit_avg(unit_accum_t)
function unit_cmp(unit,unit)
function unit_dbl_div(unit,double precision)
function unit_dbl_mul(unit,double precision)
function unit_diff(unit,unit)
function unit_div(unit,unit)
function unit(double precision)
function unit_eq(unit,unit)
function unit_ge(unit,unit)
function unit_greatest(unit,unit)
function unit_gt(unit,unit)
function unit_in(cstring)
function unit_is_hashed(cstring)
function unit_least(unit,unit)
function unit_le(unit,unit)
function unit_load()
function unit_lt(unit,unit)
function unit_mul(unit,unit)
function unit_neg(unit)
function unit_ne(unit,unit)
function unit_out(unit)
function unit_pow(unit,integer)
function unitrange(unit,unit)
function unitrange(unit,unit,text)
function unit_recv(internal)
function unit_reset()
function unit_send(unit)
function unit_stddev_pop(unit_accum_t)
function unit_stddev_samp(unit_accum_t)
function unit_strict_cmp(unit,unit)
function unit_strict_eq(unit,unit)
function unit_strict_ge(unit,unit)
function unit_strict_gt(unit,unit)
function unit_strict_le(unit,unit)
function unit_strict_lt(unit,unit)
function unit_strict_ne(unit,unit)
function unit_sub(unit,unit)
function unit_var_pop(unit_accum_t)
function unit_var_samp(unit_accum_t)
function value(unit)
function variance(unit)
function var_pop(unit)
function var_samp(unit)
function volt(double precision)
function watt(double precision)
function weber(double precision)

-- Operator Classes
operator class unit_ops for access method btree
operator class unit_strict_ops for access method btree

-- Operators
operator /(double precision,unit)
operator *(double precision,unit)
operator family unit_ops for access method btree
operator family unit_strict_ops for access method btree
operator ||/(NONE,unit)
operator |/(NONE,unit)
operator -(NONE,unit)
operator /(unit,double precision)
operator *(unit,double precision)
operator ^(unit,integer)
operator @(unit,text)
operator @@(unit,text)
operator <<=(unit,unit)
operator <<>>(unit,unit)
operator <<(unit,unit)
operator <=(unit,unit)
operator <>(unit,unit)
operator <(unit,unit)
operator ==(unit,unit)
operator =(unit,unit)
operator >=(unit,unit)
operator >>=(unit,unit)
operator >>(unit,unit)
operator >(unit,unit)
operator -(unit,unit)
operator /(unit,unit)
operator *(unit,unit)
operator +(unit,unit)

-- Tables
table unit_prefixes
table unit_units

-- Types
type unit
type unit_accum_t
type unitrange
```

--------------------------------

### Creating and Using Unit Ranges

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Details how to create and manipulate ranges of units using the `unitrange` function. It covers creating ranges with built-in or custom bounds, and performing range operations such as containment (@>) and overlap (&&).

```sql
-- Create a range using built-in function
SELECT unitrange('5 m'::unit, '10 m'::unit);
-- Output: ["5 m","10 m")

-- Named units from database
SELECT unitrange('earthradius_polar', 'earthradius_equatorial');
-- Output: ["6.35675174834046 Mm","6.37813649 Mm")

-- Range with custom bounds
SELECT unitrange('0 K'::unit, '100 K'::unit, '[)');
-- Output: ["0 K","100 K")

-- Range operations
SELECT unitrange('1 m'::unit, '5 m'::unit) @> '3 m'::unit;
-- Output: true (contains)

SELECT unitrange('1 m'::unit, '5 m'::unit) && unitrange('4 m'::unit, '8 m'::unit);
-- Output: true (overlaps)
```

--------------------------------

### Reloading Unit Definitions

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Explains the `unit_load()` function, which reloads unit definitions from disk files (`unit_prefixes.data` and `unit_units.data`). This process preserves user-defined entries while updating the system with any changes made to these definition files.

```sql
-- Reload units from disk files
SELECT unit_load();

-- This reloads unit_prefixes.data and unit_units.data
-- User-defined entries are preserved
```

--------------------------------

### SI Coherent Derived Units Operations

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Demonstrates the use of functions for common SI coherent derived units such as Hertz, Newton, Joule, Watt, Volt, Ohm, and Farad. It shows how to create these units using their respective functions or by converting compatible base units.

```sql
-- Frequency (Hertz)
SELECT hertz(50);
-- Output: 50 Hz

SELECT '1/0.02 s'::unit;
-- Output: 50 Hz

-- Force (Newton)
SELECT newton(9.81);
-- Output: 9.81 N

SELECT '9.81 kg m/s^2'::unit;
-- Output: 9.81 N

-- Energy (Joule)
SELECT joule(1000);
-- Output: 1 kJ

SELECT '1000 N m'::unit;
-- Output: 1 kJ

-- Power (Watt)
SELECT watt(100);
-- Output: 100 W

SELECT '100 J/s'::unit;
-- Output: 100 W

-- Electrical units
SELECT volt(230);
-- Output: 230 V

SELECT ohm(1000);
-- Output: 1 kΩ

SELECT farad(0.000001);
-- Output: 1 µF
```

--------------------------------

### Statistical Aggregates with Units

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Demonstrates how to use SQL aggregate functions like SUM, AVG, MIN, MAX, VARIANCE, and STDDEV on columns with the 'unit' data type. It shows that aggregates can handle different prefixes of the same dimension but will error on incompatible dimensions.

```sql
-- Create sample data
CREATE TABLE measurements (
    distance unit
);

INSERT INTO measurements VALUES
    ('5 m'::unit),
    ('10 m'::unit),
    ('15 m'::unit),
    ('20 m'::unit);

-- Sum
SELECT sum(distance) FROM measurements;
-- Output: 50 m

-- Average
SELECT avg(distance) FROM measurements;
-- Output: 12.5 m

-- Min and Max
SELECT min(distance), max(distance) FROM measurements;
-- Output: 5 m | 20 m

-- Variance and Standard Deviation
SELECT
    var_pop(distance) AS population_variance,
    var_samp(distance) AS sample_variance,
    stddev_pop(distance) AS population_stddev,
    stddev_samp(distance) AS sample_stddev,
    stddev(distance) AS stddev_alias
FROM measurements;
-- All return unit^2 or unit values

-- Aggregates work across different prefixes (same dimension)
CREATE TABLE mixed_lengths (len unit);
INSERT INTO mixed_lengths VALUES
    ('1 mm'::unit),
    ('5 cm'::unit),
    ('2 m'::unit),
    ('3 km'::unit);

SELECT sum(len) FROM mixed_lengths;
-- Output: 3.002051 km

-- Error if dimensions don't match
CREATE TABLE mixed_units (u unit);
INSERT INTO mixed_units VALUES ('1 m'::unit), ('1 kg'::unit);
SELECT sum(u) FROM mixed_units;
-- ERROR: dimension mismatch in "+" operation
```

--------------------------------

### Unit Conversion and Operations

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Demonstrates basic unit conversion and arithmetic operations using the unit extension. The '@' operator is used for conversion, and standard arithmetic operators are overloaded for unit types.

```APIDOC
## SELECT '5 °F'::unit, '5 * °F'::unit, '5 °F'::unit @ '°C' AS to_celsius, '5 °F'::unit @ '1 * °C' AS celsius_increments;

### Description
Performs unit conversion from Fahrenheit to Celsius and demonstrates arithmetic operations with units.

### Method
N/A (SQL Query Example)

### Endpoint
N/A

### Parameters
N/A

### Request Example
```sql
SELECT '5 °F'::unit, '5 * °F'::unit, '5 °F'::unit @ '°C' AS to_celsius, '5 °F'::unit @ '1 * °C' AS celsius_increments;
```

### Response
#### Success Response (SQL Result)
- **unit** (unit) - The input unit value.
- **unit** (unit) - The input unit value with multiplication.
- **to_celsius** (unit) - The converted value in Celsius.
- **celsius_increments** (unit) - The converted value with Celsius increments.

#### Response Example
```
   unit   |        unit        |      to_celsius      | celsius_increments
----------+--------------------+----------------------+---------------------
 258.15 K | 2.77777777777778 K | -14.9999999999999 °C | 258.15 * 1 * °C
```
```

--------------------------------

### Unit Comparison Operators

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Explains the standard and strict comparison operators available for the 'unit' data type in PostgreSQL. Standard operators compare values, while strict operators (==, <<, >>) compare text representations. Functions like unit_greatest and unit_least are also covered.

```sql
-- Equal and not equal
SELECT '1 m'::unit = '100 cm'::unit;
-- Output: true

SELECT '5 kg'::unit <> '5 g'::unit;
-- Output: true

-- Less than, greater than
SELECT '50 cm'::unit < '1 m'::unit;
-- Output: true

SELECT '2 km'::unit > '1500 m'::unit;
-- Output: true

-- Less than or equal, greater than or equal
SELECT '1000 g'::unit <= '1 kg'::unit;
-- Output: true

SELECT '24 hr'::unit >= '1 day'::unit;
-- Output: true

-- Strict operators for text representation comparison
SELECT '1 m'::unit == '100 cm'::unit;
-- Output: false (different text representation)

SELECT '1 m'::unit = '100 cm'::unit;
-- Output: true (same value)

-- Strict less than/greater than
SELECT '500 m'::unit << '1 km'::unit;
-- Output: true

-- Use GREATEST/LEAST functions
SELECT unit_greatest('5 m'::unit, '600 cm'::unit);
-- Output: 6 m

SELECT unit_least('5 m'::unit, '600 cm'::unit);
-- Output: 5 m
```

--------------------------------

### Define Unit Prefixes Table

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

SQL DDL to create the 'unit_prefixes' table. This table stores information about unit prefixes, including their name, conversion factor, definition, and whether they should be dumped.

```sql
CREATE TABLE unit_prefixes (
    prefix varchar(32) PRIMARY KEY,
    factor double precision NOT NULL,
    definition text, -- original definition, informational
    dump boolean DEFAULT true
);
```

--------------------------------

### SQL: Convert Non-SI Units

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Demonstrates the conversion of various non-SI units including time, area, volume, mass, astronomical, and sound units using SQL functions.

```sql
-- Time units
SELECT minute(60);
-- Output: 01:00:00 s

SELECT hour(24);
-- Output: 1 d

SELECT day(365);
-- Output: 1 commonyear

-- Area and volume
SELECT hectare(1);
-- Output: 10 km^2

SELECT liter(1000);
-- Output: 1 m^3

-- Mass
SELECT tonne(5);
-- Output: 5 t

-- Astronomical
SELECT au(1);
-- Output: 149.59787 Gm

-- Sound
SET extra_float_digits = 0;
SELECT decibel(10);
-- Output: 10 dB
```

--------------------------------

### Define Unit Units Table

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

SQL DDL to create the 'unit_units' table. This table stores information about base units, their corresponding 'unit' type, optional shift values, definition, and dump status.

```sql
CREATE TABLE unit_units (
    name varchar(32) PRIMARY KEY,
    unit unit NOT NULL,
    shift double precision, -- NULL means 0.0 here
    definition text, -- original definition, informational
    dump boolean DEFAULT true
);
```

--------------------------------

### Convert Units using @ Operator in PostgreSQL

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Demonstrates unit conversion using the `@` operator, which returns the converted value along with the unit. Supports various unit types and multipliers.

```sql
-- Convert to different units (output includes unit)
SELECT '5 m'::unit @ 'cm';
-- Output: 500 cm

SELECT '2 MB/min'::unit @ 'GB/d';
-- Output: 2.88 GB/d

SELECT '500 mi'::unit @ 'km';
-- Output: 804.672 km

-- Convert with multiplier
SELECT '1 hl'::unit @ '0.5 l';
-- Output: 200 * 0.5 l

SELECT '16384 B'::unit @ '8192 B';
-- Output: 2 * 8192 B
```

--------------------------------

### PostgreSQL Unit Conversion with Prefixes

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Demonstrates converting a duration ('1 kilosecond') using the 'unit' datatype into a more human-readable format ('00:16:40 s'). This showcases the handling of SI prefixes.

```sql
SELECT '1 kilosecond'::unit AS time;
```

--------------------------------

### SQL: Perform Complex Physics and Electrical Calculations with Units

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Illustrates how to perform complex calculations for physics (kinetic energy) and electrical (Ohm's law, power) using the unit extension in SQL. It requires defining base units like kilogram, meter, second, volt, and ampere.

```sql
-- Physics: Calculate kinetic energy
SELECT 0.5 * kilogram(1000) * (meter(20)/second())^2 @ 'kJ';
-- Output: 200 kJ

-- Electrical: Ohm's law
SELECT volt(230) / ampere(10);
-- Output: 23 Ω

-- Calculate power from voltage and current
SELECT volt(230) * ampere(10);
-- Output: 2.3 kW

-- Pressure calculation
SELECT newton(1000) / (meter(2) * meter(2));
-- Output: 250 Pa
```

--------------------------------

### Insert Custom Unit

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

SQL command to insert a new custom unit 'legobrick' with a definition of '9.6 mm' into the 'unit_units' table.

```sql
INSERT INTO unit_units VALUES ('legobrick', '9.6 mm');
```

--------------------------------

### Load Unit Definitions

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

SQL function call to reload unit prefixes and units definitions from disk. This is used during extension upgrades or when definitions are changed.

```sql
SELECT unit_load();
```

--------------------------------

### Handling of Shifted Units (Temperature)

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Explains the concept of 'shifted units' in PostgreSQL, primarily for temperature scales like Celsius (°C) and Fahrenheit (°F). It details how the `unit_units.shift` column is used and clarifies the behavior of these units in different expression contexts (e.g., absolute value vs. difference).

```sql
-- Example of calculating a temperature difference:
-- The result is in Kelvin (K) because the shift is ignored for differences.
SELECT '20 °C'::unit - '15 °C'::unit;

-- Example of using a shifted unit for an absolute value:
-- The shift is applied, resulting in an absolute value in base units (Kelvin).
SELECT '5 °C'::unit;

-- Example of a thermodynamic unit definition that avoids shift interpretation:
celsiusheatunit = cal lb (degC) / gram K

-- Example illustrating the difference in interpretation:
-- '5 °F' will use the shift offset.
SELECT '5 °F'::unit;

-- In contrast, other contexts might treat it as a base unit difference.
```

--------------------------------

### PostgreSQL Unit Division for Derived Units

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Illustrates calculating acceleration ('gravity') by dividing a force ('9.81 N') by a mass ('kg') using the 'unit' datatype. The result is presented in derived SI units ('m/s^2').

```sql
SELECT '9.81 N'::unit / 'kg' AS gravity;
```

--------------------------------

### PostgreSQL Unit Conversion for Volume

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

This snippet demonstrates converting a volume ('1 hl') into a specified number of smaller units ('0.5 l') using the '@' operator for conversion. It's useful for calculations involving discrete quantities.

```sql
SELECT '1 hl'::unit @ '0.5 l' AS bottles_of_beer;
```

--------------------------------

### Insert Custom Unit Prefix

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

SQL command to insert a new custom prefix 'foo' with a factor of 42 into the 'unit_prefixes' table.

```sql
INSERT INTO unit_prefixes VALUES ('foo', 42);
```

--------------------------------

### Scale Conversion with Unit Display (PostgreSQL)

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

The `@` operator performs scale conversion between units of the same dimension. It takes a unit value on the left and a target unit (or scaled unit) on the right. The output includes the converted value and the target unit. If the target unit includes a numeric component, the output is `value * num unit`.

```sql
SELECT '100m'::unit @ 'km';
-- Returns: 0.1km

SELECT '10m'::unit @ '5ft';
-- Returns: 32.8084ft
```

--------------------------------

### Convert Units using @@ Operator in PostgreSQL

Source: https://context7.com/df7cb/postgresql-unit/llms.txt

Shows unit conversion using the `@@` operator, which returns only the numeric value of the converted unit. Requires setting `extra_float_digits` for precision control.

```sql
-- Convert to different units (output numeric value only)
SET extra_float_digits = 0;

SELECT '10 m'::unit @@ 'cm';
-- Output: 1000

SELECT '9.81 m/s^2'::unit @@ 'N/kg';
-- Output: 9.81

SELECT '100 mi'::unit @@ 'km';
-- Output: 160.9344
```

--------------------------------

### Unit Reset Function

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Explains the necessity and usage of the `unit_reset()` function in PostgreSQL. This function is crucial for clearing the internal cache of unit definitions, ensuring that subsequent operations reflect any updated unit tables.

```sql
-- Call unit_reset() to clear the hash table that caches the lookup result.
unit_reset();
```

--------------------------------

### PostgreSQL Unit Conversion to Base Units

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Shows how to represent a speed ('120 km/h') using the 'unit' datatype and convert it to its base SI units ('m/s'). The output displays the converted speed.

```sql
SELECT '120 km/h'::unit AS speed;
```

--------------------------------

### PostgreSQL Unit Extension: Dimension Function

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

Explains the 'dimension(unit)' function from the PostgreSQL 'unit' extension. It returns the base units of a given unit value, normalized to a value of 1.

```sql
SELECT dimension(unit);
-- Returns the dimension of a unit value, i.e. its base units with a normalized
-- value of 1.
```

--------------------------------

### Extract Numeric Value from Unit (PostgreSQL)

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

The `value(unit)` function extracts the numeric part of a unit value. It takes a unit as input and returns a double-precision number representing its magnitude. No external dependencies are required beyond the unit type itself.

```sql
SELECT value('10m'::unit);
-- Returns: 10.0
```

--------------------------------

### Internal Unit Representation

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

The C struct 'Unit' used internally by the extension to store unit values. It consists of a double-precision floating-point number for the value and an array of signed characters for exponenting base units.

```c
typedef struct Unit {
    double      value;
    signed char units[N_UNITS];
} Unit;
```

--------------------------------

### PostgreSQL Unit Conversion to Metric Units

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

This SQL query converts a distance given in US customary units ('500 mi') to kilometers using the 'unit' datatype. It showcases the extension's ability to handle and convert between different unit systems.

```sql
SELECT '500 mi'::unit AS walk_500_miles;
```

--------------------------------

### Round Unit Value to Nearest Integer (PostgreSQL)

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

The `round(unit)` function rounds a unit value to the nearest integer, expressed in its base units. It takes a unit as input and returns the rounded unit. This is useful for simplifying unit representations.

```sql
SELECT round('25.7m'::unit);
-- Returns: 26m
```

--------------------------------

### Configure Float Precision (PostgreSQL)

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

The `extra_float_digits` GUC setting in PostgreSQL controls the output precision for floating-point numbers, affecting how unit values are displayed. Valid values range from -16 to +3. Setting it to 0 or negative values can restore older, less precise display behavior. Values >= 1 enforce exact representations.

```sql
SET extra_float_digits = -12;
SELECT 'c'::unit AS lightspeed;

SET extra_float_digits = 0;
SELECT '25m'::unit @ 'ft' AS feet;
```

--------------------------------

### Scale Conversion Value Only (PostgreSQL)

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

The `@@` operator is similar to the `@` operator for scale conversion but returns only the numeric value of the scaled unit as a double-precision number. It requires units of the same dimension. The output is purely numeric, without unit symbols.

```sql
SELECT '100m'::unit @@ 'km';
-- Returns: 0.1
```

--------------------------------

### Dimension Function

Source: https://github.com/df7cb/postgresql-unit/blob/master/README.md

The `dimension` function returns the base units of a given unit value with a normalized value of 1.

```APIDOC
## dimension(unit): unit

### Description
Returns the dimension of a unit value, i.e., its base units with a normalized value of 1.

### Method
N/A (SQL Function Example)

### Endpoint
N/A

### Parameters
#### Path Parameters
N/A

#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
```sql
SELECT dimension('5 °F'::unit);
```

### Response
#### Success Response (200)
- **dimension** (unit) - The base units of the input unit with a normalized value of 1.

#### Response Example
```
 dimension
-----------
 1 * K
```
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.