PostgreSQL 中的数据类型和内置函数

介绍

PostgreSQL 是世界上最先进、使用最广泛的开源数据库之一。多年来，由全球志愿者团队开发的众多数据类型和函数已添加到 PostgreSQL。在本指南中，您将了解 PostgreSQL 中可用的基本数据类型和函数，并查看它们的用例演示，这将帮助您更好地完成数据库开发和数据分析任务。

PostgreSQL 中的数据类型

在 PostgreSQL 中，大多数数据类型都有以短或长关键字形式出现的多个别名，并且可以提及长度、精度和比例等可选属性。

特点

用于存储字符值的最基本的数据类型是CHAR或CHARACTER。字符值的固定长度必须与关键字一起定义为CHAR(n)或CHARACTER(n)。如果添加长度较小的字符值，则用空格填充剩余字符。

      SELECT 'Art'::CHAR(7) AS "Padded";
--Output:"Art    "
SELECT 'Artwork'::CHAR(7) AS "NotPadded";
--Output:"Artwork"
    

::运算符用于根据 PostgreSQL 中的语法进行显式类型转换。也可以通过使用Cast ()函数来实现。

      SELECT CAST('Art' AS CHAR(7)) AS "Padded";
--Output:"Art    "
SELECT CAST('Artwork' AS CHAR(7)) AS "NotPadded";
--Output:"Artwork"
    

CHAR等固定字符数据类型的一个良好用例是存储某些固定长度值或代码的列，例如邮政编码。对于长度可变的字符值，使用数据类型VARCHAR(n)或CHARACTER VARYING(n)或CHAR VARYING(n)。长度可以变化，直至达到 n 定义的最大长度。小于最大长度的字符值不会在此数据类型中填充。

      SELECT 'Gill Bates'::VARCHAR(14) AS "SmallerNamesAreNotPadded";
--Output: "Gill Bates"
SELECT 'Zark Muckerberg'::VARCHAR(14) AS "LongerNameClipped";
--Output: "Zark Muckerber"
    

在转换时，较长的值会被截断，但插入比列定义中定义的更长的数据值将导致错误。对于由于字符值极其多变的性质而无法定义最大长度的列，可以在语法中省略最大长度以及关键字。还有一种专门用于此类情况的TEXT数据类型。

      SELECT 'The quick brown fox jumps over the lazy dog.'::VARCHAR AS "UsingVARCHAR";
--Output: "The quick brown fox jumps over the lazy dog.”
SELECT 'The quick brown fox jumps over the lazy dog.'::TEXT AS "UsingTEXT";
--Output: "The quick brown fox jumps over the lazy dog.”
    

数字

对于存储没有小数部分的数字，PostgreSQL 中有三种可用的数据类型：

SMALLINT或INT2

如果仅需存储小范围的整数值，则使用此方法，并且只有在磁盘空间受限的情况下才必须优先使用。它占用两个字节的存储空间。

INTEGER或INT或INT4

这是最常用和首选的数据类型，因为它在范围、存储大小和性能之间提供了最佳平衡。它占用 4 个字节的存储空间。

BIGINT或INT8

当INTEGER数据类型的范围不够，需要存储更大的整数值时，BIGINT是首选数据类型。它占用 8 个字节的存储空间。

对于具有固定精度的数值，可以使用NUMERIC或DECIMAL数据类型。长度和精度属性与NUMERIC或DECIMAL关键字一起提及。

      SELECT 9.99::NUMERIC(8,3) AS "NumericValue";
--Output: 9.990
    

如果省略它们，则可以在列中存储任意精度和小数位数的数值，直至达到实现限制。

      SELECT 999989999999.889999::NUMERIC AS "NumericValue";
--Output: 999989999999.889999
    

如果要存储的值大于声明的比例，则将值四舍五入到指定的小数位数。

      SELECT 99998.889999::NUMERIC(8,3) AS "NumericValue";
--Output: 99998.890
    

除了普通的数值外，数字类型还允许特殊值NaN（即非数字）。

      SELECT 'NaN'::NUMERIC AS "Not-a-Number";
--Output: NaN
    

如果要存储的值可能具有可变精度，则通过使用以下浮点数据类型将它们存储为近似值：

REAL（或FLOAT4）

      SELECT 99998999.889999::REAL             AS "RealValue";
--Output: 9.9999e+007
    

双精度（或FLOAT8）

      SELECT 99998999.889999::DOUBLE PRECISION AS "DoublePrecisionValue";
--Output: 99998999.889999
    

注意：存储和检索此类值可能会导致略有差异，具体取决于底层处理器、操作系统和编译器。

为了存储货币值，可以使用MONEY数据类型。它允许存储货币符号和逗号。

      SELECT '$100,000.00'::MONEY             AS "CTC";
--Output: "$100,000.00"
    

日期/时间

DATE用于存储不包含时间的日期值。

      SELECT '2020, 4 July'::DATE AS "US Independence Day";
--Output:"2020-04-07"
SELECT '20200407'::DATE AS " US Independence Day";
--Output:"2020-04-07"
    

时间这仅用于存储一天中的时间值，可以带有或不带有时区。

      SELECT '12:00:00.12345678'::TIME(2) AS "Noon with 2 micro-second precision";
--Output: "12:00:00.12"

SELECT '00:00:00 IST'::TIME WITH TIME ZONE AS "Midnight as per Indian Standard Time";
--Output: "00:00:00+02:00"
    

时间戳用于存储日期和时间。

      SELECT '2020, 4 July 12:00:00'::TIMESTAMP AS "US Independence Day NOON";
--Output: "2020-07-04 12:00:00"
SELECT '2020, 4 July 12:00:00 EST'::TIMESTAMP WITH TIME ZONE AS "US Independence Day NOON with Time zone";
--Output: "2020-07-04 22:30:00+05:30"
    

INTERVAL这是一种非常通用的数据类型，可以接受各种格式的基于日期/时间间隔。

      SELECT '1 Y 5 Mon 2 H 5 M'::INTERVAL  AS "1Year2Months2Hours5Minutes";
--Output:"1 year 5 mons 02:05:00"
select '11 hours, 30 minutes, 40 seconds ago'::INTERVAL  AS "11 and a half hours, 40 seconds ago";
--Output: "-11:30:40"
    

布尔值

有时可能需要在特定列中存储 TRUE、FALSE 或 UNKOWN 标志。对于这种情况，可以使用BOOLEAN数据类型。TRUE/FALSE 值也可以表示为 Y/N、T/F、1/0 和 ON/OFF。UNKOWN 值用 NULL 表示。

      SELECT 1::BOOLEAN AS "TrueValue", 0::BOOLEAN AS "FalseValue",NULL::BOOL  AS "Unknown";
--Output: true	false	[null]
    

大批

如果需要存储属于内置或用户定义数据类型的值集合，则可以通过简单地添加方括号和数据类型的关键字来定义ARRAY 。

      select '{ "Certification practice exams", "Interactive courses", "Technical Projects" }'::VARCHAR[] as "Pluralsight Premium"
--Output: "{"Certification practice exams","Interactive courses","Technical Projects"}"
SELECT ARRAY[55,66,77]::INTEGER[] AS "Integer Array"
--Output: {55,66,77}
    

与编程语言不同，PostgreSQL 中的数组索引从 1 开始，而不是从 0 开始。

      SELECT (ARRAY[55,66,77]::INTEGER[])[1] AS "First Array Element"
--Output: 55
    

PostgreSQL 中的函数

在本节中，您将了解在与数据分析、数据库管理和数据库开发相关的日常任务中非常有用的各种功能。

数据相关函数

NOW()返回带有时区的当前时间戳。

      select NOW()
--Output:"2020-07-14 21:29:17.003693+05:30"
    

EXTRACT()或DATE_PART()返回所提及日期的一部分。

      select EXTRACT('Year' from NOW()),EXTRACT('Century' from NOW()), DATE_PART('MONTH',NOW())
--Output: 2020	21	7
    

AGE()返回作为参数传递的日期值/列的时间段。

      SELECT AGE('July 4, 1776'::date) as "Age since US Independence"
--Output: "244 years 10 days"
    

SUBSTRING()从起始位置（作为第二个参数传递）返回一定长度的子字符串（作为第三个参数传递）。请注意，在 PostgreSQL 中，字符串被视为从 0 索引位置开始。

<div class="code-block -hljs-dark aem-GridColumn--tablet--12 aem-GridColumn--phone--12 aem-GridColumn aem-GridCo

_{免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！}

_查看原文