~vcs-imports/mammoth-replicator/trunk

<entry><literal><function>round</function>(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type>)</literal></entry>

718

<entry><type>numeric</type></entry>

719

<entry>round to <parameter>s</parameter> decimal places</entry>

720

<entry><literal>round(42.4382, 2)</literal></entry>

721

722

</row>

723

724

<row>

725

<entry><literal><function>setseed</function>(<type>dp</type>)</literal></entry>

726

<entry><type>integer</type></entry>

727

<entry>set seed for subsequent <literal>random()</literal> calls</entry>

728

<entry><literal>setseed(0.54823)</literal></entry>

729

730

</row>

731

732

<row>

733

<entry><literal><function>sign</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>

734

<entry>(same as input)</entry>

735

<entry>sign of the argument (-1, 0, +1)</entry>

736

737

738

</row>

739

740

<row>

741

<entry><literal><function>sqrt</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>

742

<entry>(same as input)</entry>

743

<entry>square root</entry>

744

745

746

</row>

747

748

<row>

749

<entry><literal><function>trunc</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>

750

<entry>(same as input)</entry>

751

<entry>truncate toward zero</entry>

752

<entry><literal>trunc(42.8)</literal></entry>

753

754

</row>

755

756

<row>

757

<entry><literal><function>trunc</function>(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type>)</literal></entry>

758

<entry><type>numeric</type></entry>

759

<entry>truncate to <parameter>s</parameter> decimal places</entry>

760

<entry><literal>trunc(42.4382, 2)</literal></entry>

761

762

</row>

763

764

<row>

765

<entry><literal><function>width_bucket</function>(<parameter>op</parameter> <type>numeric</type>, <parameter>b1</parameter> <type>numeric</type>, <parameter>b2</parameter> <type>numeric</type>, <parameter>count</parameter> <type>integer</type>)</literal></entry>

766

<entry><type>integer</type></entry>

767

<entry>return the bucket to which <parameter>operand</> would

768

be assigned in an equidepth histogram with <parameter>count</>

769

buckets, an upper bound of <parameter>b1</>, and a lower bound

770

of <parameter>b2</></entry>

771

<entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>

772

773

</row>

774

</tbody>

775

</tgroup>

776

</table>

777

778

<para>

779

Finally, <xref linkend="functions-math-trig-table"> shows the

780

available trigonometric functions. All trigonometric functions

781

take arguments and return values of type <type>double

782

precision</type>.

783

</para>

784

785

786

<title>Trigonometric Functions</title>

787

788

789

<thead>

790

<row>

791

<entry>Function</entry>

792

<entry>Description</entry>

793

</row>

794

</thead>

795

796

<tbody>

797

<row>

798

799

<entry>inverse cosine</entry>

800

</row>

801

802

<row>

803

804

<entry>inverse sine</entry>

805

</row>

806

807

<row>

808

809

<entry>inverse tangent</entry>

810

</row>

811

812

<row>

813

<entry><literal><function>atan2</function>(<replaceable>x</replaceable>,

814

815

<entry>inverse tangent of

816

817

</row>

818

819

<row>

820

821

<entry>cosine</entry>

822

</row>

823

824

<row>

825

826

<entry>cotangent</entry>

827

</row>

828

829

<row>

830

831

832

</row>

833

834

<row>

835

836

<entry>tangent</entry>

837

</row>

838

</tbody>

839

</tgroup>

840

</table>

841

842

</sect1>

843

844

845

846

<title>String Functions and Operators</title>

847

848

<para>

849

This section describes functions and operators for examining and

850

manipulating string values. Strings in this context include values

851

of all the types <type>character</type>, <type>character

852

varying</type>, and <type>text</type>. Unless otherwise noted, all

853

of the functions listed below work on all of these types, but be

854

wary of potential effects of the automatic padding when using the

855

<type>character</type> type. Generally, the functions described

856

here also work on data of non-string types by converting that data

857

to a string representation first. Some functions also exist

858

natively for the bit-string types.

859

</para>

860

861

<para>

862

<acronym>SQL</acronym> defines some string functions with a special syntax where

863

certain key words rather than commas are used to separate the

864

arguments. Details are in <xref linkend="functions-string-sql">.

865

These functions are also implemented using the regular syntax for

866

function invocation. (See <xref linkend="functions-string-other">.)

867

</para>

868

869

870

<title><acronym>SQL</acronym> String Functions and Operators</title>

871

872

<thead>

873

<row>

874

<entry>Function</entry>

875

<entry>Return Type</entry>

876

<entry>Description</entry>

877

<entry>Example</entry>

878

<entry>Result</entry>

879

</row>

880

</thead>

881

882

<tbody>

883

<row>

884

<entry><literal><parameter>string</parameter> <literal>||</literal>

885

<parameter>string</parameter></literal></entry>

886

887

<entry>

888

String concatenation

889

890

<primary>character string</primary>

891

<secondary>concatenation</secondary>

892

</indexterm>

893

</entry>

894

<entry><literal>'Post' || 'greSQL'</literal></entry>

895

<entry><literal>PostgreSQL</literal></entry>

896

</row>

897

898

<row>

899

<entry><literal><function>bit_length</function>(<parameter>string</parameter>)</literal></entry>

900

<entry><type>integer</type></entry>

901

<entry>Number of bits in string</entry>

902

<entry><literal>bit_length('jose')</literal></entry>

903

904

</row>

905

906

<row>

907

<entry><literal><function>char_length</function>(<parameter>string</parameter>)</literal> or <literal><function>character_length</function>(<parameter>string</parameter>)</literal></entry>

908

<entry><type>integer</type></entry>

909

<entry>

910

Number of characters in string

911

912

<primary>character string</primary>

913

<secondary>length</secondary>

914

</indexterm>

915

916

<primary>length</primary>

917

<secondary sortas="character string">of a character string</secondary>

918

<see>character strings, length</see>

919

</indexterm>

920

</entry>

921

<entry><literal>char_length('jose')</literal></entry>

922

923

</row>

924

925

<row>

926

<entry><literal><function>convert</function>(<parameter>string</parameter>

927

using <parameter>conversion_name</parameter>)</literal></entry>

928

929

<entry>

930

Change encoding using specified conversion name. Conversions

931

can be defined by <command>CREATE CONVERSION</command>. Also

932

there are some pre-defined conversion names. See <xref

933

linkend="conversion-names"> for available conversion

934

names.

935

</entry>

936

<entry><literal>convert('PostgreSQL' using iso_8859_1_to_utf_8)</literal></entry>

937

<entry><literal>'PostgreSQL'</literal> in Unicode (UTF-8) encoding</entry>

938

</row>

939

940

<row>

941

<entry><literal><function>lower</function>(<parameter>string</parameter>)</literal></entry>

942

943

<entry>Convert string to lower case</entry>

944

<entry><literal>lower('TOM')</literal></entry>

945

946

</row>

947

948

<row>

949

<entry><literal><function>octet_length</function>(<parameter>string</parameter>)</literal></entry>

950

<entry><type>integer</type></entry>

951

<entry>Number of bytes in string</entry>

952

<entry><literal>octet_length('jose')</literal></entry>

953

954

</row>

955

956

<row>

957

<entry><literal><function>overlay</function>(<parameter>string</parameter> placing <parameter>string</parameter> from <type>integer</type> <optional>for <type>integer</type></optional>)</literal></entry>

958

959

<entry>

960

Replace substring

961

962

<primary>overlay</primary>

963

</indexterm>

964

</entry>

965

<entry><literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal></entry>

966

<entry><literal>Thomas</literal></entry>

967

</row>

968

969

<row>

970

<entry><literal><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</literal></entry>

971

<entry><type>integer</type></entry>

972

<entry>Location of specified substring</entry>

973

<entry><literal>position('om' in 'Thomas')</literal></entry>

974

975

</row>

976

977

<row>

978

<entry><literal><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</literal></entry>

979

980

<entry>

981

Extract substring

982

983

<primary>substring</primary>

984

</indexterm>

985

</entry>

986

<entry><literal>substring('Thomas' from 2 for 3)</literal></entry>

987

988

</row>

989

990

<row>

991

<entry><literal><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable>)</literal></entry>

992

993

<entry>

994

Extract substring matching POSIX regular expression

995

996

<primary>substring</primary>

997

</indexterm>

998

</entry>

999

<entry><literal>substring('Thomas' from '...$')</literal></entry>

1000

1001

</row>

1002

1003

<row>

1004

<entry><literal><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable> for <replaceable>escape</replaceable>)</literal></entry>

1005

1006

<entry>

1007

Extract substring matching <acronym>SQL</acronym> regular

1008

expression

1009

1010

<primary>substring</primary>

1011

</indexterm>

1012

</entry>

1013

<entry><literal>substring('Thomas' from '%#"o_a#"_' for '#')</literal></entry>

1014

1015

</row>

1016

1017

<row>

1018

<entry>

1019

<literal><function>trim</function>(<optional>leading | trailing | both</optional>

1020

<optional><parameter>characters</parameter></optional> from

1021

<parameter>string</parameter>)</literal>

1022

</entry>

1023

1024

<entry>

1025

Remove the longest string containing only the

1026

<parameter>characters</parameter> (a space by default) from the

1027

start/end/both ends of the <parameter>string</parameter>.

1028

</entry>

1029

<entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>

1030

1031

</row>

1032

1033

<row>

1034

<entry><literal><function>upper</function>(<parameter>string</parameter>)</literal></entry>

1035

1036

<entry>Convert string to uppercase</entry>

1037

<entry><literal>upper('tom')</literal></entry>

1038

1039

</row>

1040

</tbody>

1041

</tgroup>

1042

</table>

1043

1044

<para>

1045

Additional string manipulation functions are available and are

1046

listed in <xref linkend="functions-string-other">. Some of them are used internally to implement the

1047

<acronym>SQL</acronym>-standard string functions listed in <xref linkend="functions-string-sql">.

1048

</para>

1049

1050

1051

<title>Other String Functions</title>

1052

1053

<thead>

1054

<row>

1055

<entry>Function</entry>

1056

<entry>Return Type</entry>

1057

<entry>Description</entry>

1058

<entry>Example</entry>

1059

<entry>Result</entry>

1060

</row>

1061

</thead>

1062

1063

<tbody>

1064

<row>

1065

<entry><literal><function>ascii</function>(<type>text</type>)</literal></entry>

1066

<entry><type>integer</type></entry>

1067

<entry><acronym>ASCII</acronym> code of the first character of the argument</entry>

1068

<entry><literal>ascii('x')</literal></entry>

1069

1070

</row>

1071

1072

<row>

1073

<entry><literal><function>btrim</function>(<parameter>string</parameter> <type>text</type>

1074

<optional>, <parameter>characters</parameter> <type>text</type></optional>)</literal></entry>

1075

1076

<entry>

1077

Remove the longest string consisting only of characters

1078

in <parameter>characters</parameter> (a space by default)

1079

from the start and end of <parameter>string</parameter>.

1080

</entry>

1081

<entry><literal>btrim('xyxtrimyyx', 'xy')</literal></entry>

1082

1083

</row>

1084

1085

<row>

1086

<entry><literal><function>chr</function>(<type>integer</type>)</literal></entry>

1087

1088

<entry>Character with the given <acronym>ASCII</acronym> code</entry>

1089

1090

1091

</row>

1092

1093

<row>

1094

<entry>

1095

<literal><function>convert</function>(<parameter>string</parameter>

1096

<type>text</type>,

1097

<optional><parameter>src_encoding</parameter> <type>name</type>,</optional>

1098

<parameter>dest_encoding</parameter> <type>name</type>)</literal>

1099

</entry>

1100

1101

<entry>

1102

Convert string to <parameter>dest_encoding</parameter>.

1103

The original encoding is specified by

1104

<parameter>src_encoding</parameter>. If

1105

<parameter>src_encoding</parameter> is omitted, database

1106

encoding is assumed.

1107

</entry>

1108

<entry><literal>convert( 'text_in_unicode', 'UNICODE', 'LATIN1')</literal></entry>

1109

<entry><literal>text_in_unicode</literal> represented in ISO 8859-1 encoding</entry>

1110

</row>

1111

1112

<row>

1113

<entry>

1114

<literal><function>decode</function>(<parameter>string</parameter> <type>text</type>,

1115

1116

</entry>

1117

<entry><type>bytea</type></entry>

1118

<entry>

1119

Decode binary data from <parameter>string</parameter> previously

1120

encoded with <function>encode</>. Parameter type is same as in <function>encode</>.

1121

</entry>

1122

<entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>

1123

1124

</row>

1125

1126

<row>

1127

<entry>

1128

<literal><function>encode</function>(<parameter>data</parameter> <type>bytea</type>,

1129

1130

</entry>

1131

1132

<entry>

1133

Encode binary data to <acronym>ASCII</acronym>-only representation. Supported

1134

types are: <literal>base64</>, <literal>hex</>, <literal>escape</>.

1135

</entry>

1136

<entry><literal>encode( '123\\000\\001', 'base64')</literal></entry>

1137

<entry><literal>MTIzAAE=</literal></entry>

1138

</row>

1139

1140

<row>

1141

<entry><literal><function>initcap</function>(<type>text</type>)</literal></entry>

1142

1143

<entry>

1144

Convert the first letter of each word to uppercase and the

1145

rest to lowercase. Words are sequences of alphanumeric

1146

characters separated by non-alphanumeric characters.

1147

</entry>

1148

<entry><literal>initcap('hi THOMAS')</literal></entry>

1149

<entry><literal>Hi Thomas</literal></entry>

1150

</row>

1151

1152

<row>

1153

<entry><literal><function>length</function>(<parameter>string</parameter> <type>text</type>)</literal></entry>

1154

<entry><type>integer</type></entry>

1155

<entry>

1156

Number of characters in <parameter>string</parameter>.

1157

1158

<primary>character string</primary>

1159

<secondary>length</secondary>

1160

</indexterm>

1161

1162

<primary>length</primary>

1163

<secondary sortas="character string">of a character string</secondary>

1164

<see>character strings, length</see>

1165

</indexterm>

1166

</entry>

1167

<entry><literal>length('jose')</literal></entry>

1168

1169

</row>

1170

1171

<row>

1172

<entry>

1173

<literal><function>lpad</function>(<parameter>string</parameter> <type>text</type>,

1174

<parameter>length</parameter> <type>integer</type>

1175

1176

</entry>

1177

1178

<entry>

1179

Fill up the <parameter>string</parameter> to length

1180

<parameter>length</parameter> by prepending the characters

1181

<parameter>fill</parameter> (a space by default). If the

1182

<parameter>string</parameter> is already longer than

1183

<parameter>length</parameter> then it is truncated (on the

1184

right).

1185

</entry>

1186

1187

<entry><literal>xyxhi</literal></entry>

1188

</row>

1189

1190

<row>

1191

<entry><literal><function>ltrim</function>(<parameter>string</parameter> <type>text</type>

1192

<optional>, <parameter>characters</parameter> <type>text</type></optional>)</literal>

1193

</entry>

1194

1195

<entry>

1196

Remove the longest string containing only characters from

1197

<parameter>characters</parameter> (a space by default) from the start of

1198

<parameter>string</parameter>.

1199

</entry>

1200

<entry><literal>ltrim('zzzytrim', 'xyz')</literal></entry>

1201

1202

</row>

1203

1204

<row>

1205

<entry><literal><function>md5</function>(<parameter>string</parameter> <type>text</type>)</literal></entry>

1206

1207

<entry>

1208

Calculates the MD5 hash of <parameter>string</parameter>,

1209

returning the result in hexadecimal.

1210

</entry>

1211

1212

1213

</row>

1214

1215

<row>

1216

<entry><literal><function>pg_client_encoding</function>()</literal></entry>

1217

1218

<entry>

1219

Current client encoding name

1220

</entry>

1221

<entry><literal>pg_client_encoding()</literal></entry>

1222

<entry><literal>SQL_ASCII</literal></entry>

1223

</row>

1224

1225

<row>

1226

<entry><literal><function>quote_ident</function>(<parameter>string</parameter> text)</literal><indexterm><primary>quote_ident</></></entry>

1227

1228

<entry>

1229

Return the given string suitably quoted to be used as an identifier

1230

in an <acronym>SQL</acronym> statement string.

1231

Quotes are added only if necessary (i.e., if the string contains

1232

non-identifier characters or would be case-folded).

1233

Embedded quotes are properly doubled.

1234

</entry>

1235

<entry><literal>quote_ident('Foo bar')</literal></entry>

1236

1237

</row>

1238

1239

<row>

1240

<entry><literal><function>quote_literal</function>(<parameter>string</parameter> text)</literal><indexterm><primary>quote_literal</></></entry>

1241

1242

<entry>

1243

Return the given string suitably quoted to be used as a string literal

1244

in an <acronym>SQL</acronym> statement string.

1245

Embedded quotes and backslashes are properly doubled.

1246

</entry>

1247

<entry><literal>quote_literal( 'O\'Reilly')</literal></entry>

1248

<entry><literal>'O''Reilly'</literal></entry>

1249

</row>

1250

1251

<row>

1252

<entry><literal><function>repeat</function>(<parameter>string</parameter> <type>text</type>, <parameter>number</parameter> <type>integer</type>)</literal></entry>

1253

1254

<entry>Repeat <parameter>string</parameter> the specified

1255

<parameter>number</parameter> of times</entry>

1256

<entry><literal>repeat('Pg', 4)</literal></entry>

1257

<entry><literal>PgPgPgPg</literal></entry>

1258

</row>

1259

1260

<row>

1261

<entry><literal><function>replace</function>(<parameter>string</parameter> <type>text</type>,

1262

<parameter>from</parameter> <type>text</type>,

1263

1264

1265

<entry>Replace all occurrences in <parameter>string</parameter> of substring

1266

<parameter>from</parameter> with substring <parameter>to</parameter>.

1267

</entry>

1268

<entry><literal>replace( 'abcdefabcdef', 'cd', 'XX')</literal></entry>

1269

<entry><literal>abXXefabXXef</literal></entry>

1270

</row>

1271

1272

<row>

1273

<entry>

1274

<literal><function>rpad</function>(<parameter>string</parameter> <type>text</type>,

1275

<parameter>length</parameter> <type>integer</type>

1276

1277

</entry>

1278

1279

<entry>

1280

Fill up the <parameter>string</parameter> to length

1281

<parameter>length</parameter> by appending the characters

1282

<parameter>fill</parameter> (a space by default). If the

1283

<parameter>string</parameter> is already longer than

1284

<parameter>length</parameter> then it is truncated.

1285

</entry>

1286

1287

<entry><literal>hixyx</literal></entry>

1288

</row>

1289

1290

<row>

1291

<entry><literal><function>rtrim</function>(<parameter>string</parameter> <type>text</type>

1292

<optional>, <parameter>characters</parameter> <type>text</type></optional>)</literal>

1293

</entry>

1294

1295

<entry>

1296

Remove the longest string containing only characters from

1297

<parameter>characters</parameter> (a space by default) from the end of

1298

<parameter>string</parameter>.

1299

</entry>

1300

<entry><literal>rtrim('trimxxxx', 'x')</literal></entry>

1301

1302

</row>

1303

1304

<row>

1305

<entry><literal><function>split_part</function>(<parameter>string</parameter> <type>text</type>,

1306

<parameter>delimiter</parameter> <type>text</type>,

1307

<parameter>field</parameter> <type>integer</type>)</literal></entry>

1308

1309

<entry>Split <parameter>string</parameter> on <parameter>delimiter</parameter>

1310

and return the given field (counting from one)

1311

</entry>

1312

<entry><literal>split_part( 'abc~@~def~@~ghi', '~@~', 2)</literal></entry>

1313

1314

</row>

1315

1316

<row>

1317

<entry><literal><function>strpos</function>(<parameter>string</parameter>, <parameter>substring</parameter>)</literal></entry>

1318

1319

<entry>

1320

Location of specified substring (same as

1321

<literal>position(<parameter>substring</parameter> in

1322

<parameter>string</parameter>)</literal>, but note the reversed

1323

argument order)

1324

</entry>

1325

<entry><literal>strpos('high', 'ig')</literal></entry>

1326

1327

</row>

1328

1329

<row>

1330

<entry><literal><function>substr</function>(<parameter>string</parameter>, <parameter>from</parameter> <optional>, <parameter>count</parameter></optional>)</literal></entry>

1331

1332

<entry>

1333

Extract substring (same as

1334

<literal>substring(<parameter>string</parameter> from <parameter>from</parameter> for <parameter>count</parameter>)</literal>)

1335

</entry>

1336

<entry><literal>substr('alphabet', 3, 2)</literal></entry>

1337

1338

</row>

1339

1340

<row>

1341

<entry><literal><function>to_ascii</function>(<type>text</type>

1342

<optional>, <parameter>encoding</parameter></optional>)</literal></entry>

1343

1344

1345

<entry>

1346

Convert <parameter>text</parameter> to <acronym>ASCII</acronym> from another encoding

1347

1348

<para>

1349

The <function>to_ascii</function> function supports conversion from

1350

<literal>LATIN1</>, <literal>LATIN2</>, <literal>LATIN9</>,

1351

and <literal>WIN1250</> encodings only.

1352

</para>

1353

</footnote>

1354

</entry>

1355

1356

<entry><literal>to_ascii('Karel')</literal></entry>

1357

<entry><literal>Karel</literal></entry>

1358

</row>

1359

1360

<row>

1361

<entry><literal><function>to_hex</function>(<parameter>number</parameter> <type>integer</type>

1362

or <type>bigint</type>)</literal></entry>

1363

1364

<entry>Convert <parameter>number</parameter> to its equivalent hexadecimal

1365

representation

1366

</entry>

1367

1368

<entry><literal>7fffffff</literal></entry>

1369

</row>

1370

1371

<row>

1372

<entry>

1373

<literal><function>translate</function>(<parameter>string</parameter>

1374

<type>text</type>,

1375

<parameter>from</parameter> <type>text</type>,

1376

1377

</entry>

1378

1379

<entry>

1380

Any character in <parameter>string</parameter> that matches a

1381

character in the <parameter>from</parameter> set is replaced by

1382

the corresponding character in the <parameter>to</parameter>

1383

set.

1384

</entry>

1385

<entry><literal>translate('12345', '14', 'ax')</literal></entry>

1386

1387

</row>

1388

1389

</tbody>

1390

</tgroup>

1391

</table>

1392

1393

1394

1395

<title>Built-in Conversions</title>

1396

1397

<thead>

1398

<row>

1399

<entry>Conversion Name

1400

1401

<para>

1402

The conversion names follow a standard naming scheme: The

1403

official name of the source encoding with all

1404

non-alphanumeric characters replaced by underscores followed

1405

by <literal>_to_</literal> followed by the equally processed

1406

destination encoding name. Therefore the names might deviate

1407

from the customary encoding names.

1408

</para>

1409

</footnote>

1410

</entry>

1411

<entry>Source Encoding</entry>

1412

<entry>Destination Encoding</entry>

1413

</row>

1414

</thead>

1415

1416

<tbody>

1417

<row>

1418

<entry><literal>ascii_to_mic</literal></entry>

1419

<entry><literal>SQL_ASCII</literal></entry>

1420

<entry><literal>MULE_INTERNAL</literal></entry>

1421

</row>

1422

1423

<row>

1424

<entry><literal>ascii_to_utf_8</literal></entry>

1425

<entry><literal>SQL_ASCII</literal></entry>

1426

<entry><literal>UNICODE</literal></entry>

1427

</row>

1428

1429

<row>

1430

1431

1432

1433

</row>

1434

1435

<row>

1436

1437

1438

<entry><literal>MULE_INTERNAL</literal></entry>

1439

</row>

1440

1441

<row>

1442

1443

1444

<entry><literal>UNICODE</literal></entry>

1445

</row>

1446

1447

<row>

1448

1449

1450

<entry><literal>MULE_INTERNAL</literal></entry>

1451

</row>

1452

1453

<row>

1454

1455

1456

<entry><literal>UNICODE</literal></entry>

1457

</row>

1458

1459

<row>

1460

1461

1462

<entry><literal>MULE_INTERNAL</literal></entry>

1463

</row>

1464

1465

<row>

1466

1467

1468

1469

</row>

1470

1471

<row>

1472

1473

1474

<entry><literal>UNICODE</literal></entry>

1475

</row>

1476

1477

<row>

1478

1479

1480

<entry><literal>MULE_INTERNAL</literal></entry>

1481

</row>

1482

1483

<row>

1484

1485

1486

<entry><literal>UNICODE</literal></entry>

1487

</row>

1488

1489

<row>

1490

1491

1492

1493

</row>

1494

1495

<row>

1496

1497

1498

<entry><literal>MULE_INTERNAL</literal></entry>

1499

</row>

1500

1501

<row>

1502

1503

1504

<entry><literal>UNICODE</literal></entry>

1505

</row>

1506

1507

<row>

1508

1509

1510

<entry><literal>UNICODE</literal></entry>

1511

</row>

1512

1513

<row>

1514

1515

1516

<entry><literal>UNICODE</literal></entry>

1517

</row>

1518

1519

<row>

1520

1521

<entry><literal>LATIN6</literal></entry>

1522

<entry><literal>UNICODE</literal></entry>

1523

</row>

1524

1525

<row>

1526

1527

<entry><literal>LATIN7</literal></entry>

1528

<entry><literal>UNICODE</literal></entry>

1529

</row>

1530

1531

<row>

1532

1533

<entry><literal>LATIN8</literal></entry>

1534

<entry><literal>UNICODE</literal></entry>

1535

</row>

1536

1537

<row>

1538

1539

<entry><literal>LATIN9</literal></entry>

1540

<entry><literal>UNICODE</literal></entry>

1541

</row>

1542

1543

<row>

1544

1545

<entry><literal>LATIN10</literal></entry>

1546

<entry><literal>UNICODE</literal></entry>

1547

</row>

1548

1549

<row>

1550

1551

<entry><literal>LATIN1</literal></entry>

1552

<entry><literal>MULE_INTERNAL</literal></entry>

1553

</row>

1554

1555

<row>

1556

1557

<entry><literal>LATIN1</literal></entry>

1558

<entry><literal>UNICODE</literal></entry>

1559

</row>

1560

1561

<row>

1562

1563

<entry><literal>LATIN2</literal></entry>

1564

<entry><literal>MULE_INTERNAL</literal></entry>

1565

</row>

1566

1567

<row>

1568

1569

<entry><literal>LATIN2</literal></entry>

1570

<entry><literal>UNICODE</literal></entry>

1571

</row>

1572

1573

<row>

1574

<entry><literal>iso_8859_2_to_windows_1250</literal></entry>

1575

<entry><literal>LATIN2</literal></entry>

1576

1577

</row>

1578

1579

<row>

1580

1581

<entry><literal>LATIN3</literal></entry>

1582

<entry><literal>MULE_INTERNAL</literal></entry>

1583

</row>

1584

1585

<row>

1586

1587

<entry><literal>LATIN3</literal></entry>

1588

<entry><literal>UNICODE</literal></entry>

1589

</row>

1590

1591

<row>

1592

1593

<entry><literal>LATIN4</literal></entry>

1594

<entry><literal>MULE_INTERNAL</literal></entry>

1595

</row>

1596

1597

<row>

1598

1599

<entry><literal>LATIN4</literal></entry>

1600

<entry><literal>UNICODE</literal></entry>

1601

</row>

1602

1603

<row>

1604

1605

1606

1607

</row>

1608

1609

<row>

1610

1611

1612

<entry><literal>MULE_INTERNAL</literal></entry>

1613

</row>

1614

1615

<row>

1616

1617

1618

<entry><literal>UNICODE</literal></entry>

1619

</row>

1620

1621

<row>

1622

<entry><literal>iso_8859_5_to_windows_1251</literal></entry>

1623

1624

1625

</row>

1626

1627

<row>

1628

<entry><literal>iso_8859_5_to_windows_866</literal></entry>

1629

1630

1631

</row>

1632

1633

<row>

1634

1635

1636

<entry><literal>UNICODE</literal></entry>

1637

</row>

1638

1639

<row>

1640

1641

1642

<entry><literal>UNICODE</literal></entry>

1643

</row>

1644

1645

<row>

1646

1647

1648

<entry><literal>UNICODE</literal></entry>

1649

</row>

1650

1651

<row>

1652

1653

<entry><literal>LATIN5</literal></entry>

1654

<entry><literal>UNICODE</literal></entry>

1655

</row>

1656

1657

<row>

1658

<entry><literal>johab_to_utf_8</literal></entry>

1659

<entry><literal>JOHAB</literal></entry>

1660

<entry><literal>UNICODE</literal></entry>

1661

</row>

1662

1663

<row>

1664

1665

1666

1667

</row>

1668

1669

<row>

1670

1671

1672

<entry><literal>MULE_INTERNAL</literal></entry>

1673

</row>

1674

1675

<row>

1676

1677

1678

<entry><literal>UNICODE</literal></entry>

1679

</row>

1680

1681

<row>

1682

<entry><literal>koi8_r_to_windows_1251</literal></entry>

1683

1684

1685

</row>

1686

1687

<row>

1688

<entry><literal>koi8_r_to_windows_866</literal></entry>

1689

1690

1691

</row>

1692

1693

<row>

1694

<entry><literal>mic_to_ascii</literal></entry>

1695

<entry><literal>MULE_INTERNAL</literal></entry>

1696

<entry><literal>SQL_ASCII</literal></entry>

1697

</row>

1698

1699

<row>

1700

1701

<entry><literal>MULE_INTERNAL</literal></entry>

1702

1703

</row>

1704

1705

<row>

1706

1707

<entry><literal>MULE_INTERNAL</literal></entry>

1708

1709

</row>

1710

1711

<row>

1712

1713

<entry><literal>MULE_INTERNAL</literal></entry>

1714

1715

</row>

1716

1717

<row>

1718

1719

<entry><literal>MULE_INTERNAL</literal></entry>

1720

1721

</row>

1722

1723

<row>

1724

1725

<entry><literal>MULE_INTERNAL</literal></entry>

1726

1727

</row>

1728

1729

<row>

1730

1731

<entry><literal>MULE_INTERNAL</literal></entry>

1732

<entry><literal>LATIN1</literal></entry>

1733

</row>

1734

1735

<row>

1736

1737

<entry><literal>MULE_INTERNAL</literal></entry>

1738

<entry><literal>LATIN2</literal></entry>

1739

</row>

1740

1741

<row>

1742

1743

<entry><literal>MULE_INTERNAL</literal></entry>

1744

<entry><literal>LATIN3</literal></entry>

1745

</row>

1746

1747

<row>

1748

1749

<entry><literal>MULE_INTERNAL</literal></entry>

1750

<entry><literal>LATIN4</literal></entry>

1751

</row>

1752

1753

<row>

1754

1755

<entry><literal>MULE_INTERNAL</literal></entry>

1756

1757

</row>

1758

1759

<row>

1760

1761

<entry><literal>MULE_INTERNAL</literal></entry>

1762

1763

</row>

1764

1765

<row>

1766

1767

<entry><literal>MULE_INTERNAL</literal></entry>

1768

1769

</row>

1770

1771

<row>

1772

<entry><literal>mic_to_windows_1250</literal></entry>

1773

<entry><literal>MULE_INTERNAL</literal></entry>

1774

1775

</row>

1776

1777

<row>

1778

<entry><literal>mic_to_windows_1251</literal></entry>

1779

<entry><literal>MULE_INTERNAL</literal></entry>

1780

1781

</row>

1782

1783

<row>

1784

<entry><literal>mic_to_windows_866</literal></entry>

1785

<entry><literal>MULE_INTERNAL</literal></entry>

1786

1787

</row>

1788

1789

<row>

1790

1791

1792

1793

</row>

1794

1795

<row>

1796

1797

1798

<entry><literal>MULE_INTERNAL</literal></entry>

1799

</row>

1800

1801

<row>

1802

1803

1804

<entry><literal>UNICODE</literal></entry>

1805

</row>

1806

1807

<row>

1808

1809

1810

<entry><literal>UNICODE</literal></entry>

1811

</row>

1812

1813

<row>

1814

1815

1816

<entry><literal>UNICODE</literal></entry>

1817

</row>

1818

1819

<row>

1820

<entry><literal>utf_8_to_ascii</literal></entry>

1821

<entry><literal>UNICODE</literal></entry>

1822

<entry><literal>SQL_ASCII</literal></entry>

1823

</row>

1824

1825

<row>

1826

1827

<entry><literal>UNICODE</literal></entry>

1828

1829

</row>

1830

1831

<row>

1832

1833

<entry><literal>UNICODE</literal></entry>

1834

1835

</row>

1836

1837

<row>

1838

1839

<entry><literal>UNICODE</literal></entry>

1840

1841

</row>

1842

1843

<row>

1844

1845

<entry><literal>UNICODE</literal></entry>

1846

1847

</row>

1848

1849

<row>

1850

1851

<entry><literal>UNICODE</literal></entry>

1852

1853

</row>

1854

1855

<row>

1856

1857

<entry><literal>UNICODE</literal></entry>

1858

1859

</row>

1860

1861

<row>

1862

1863

<entry><literal>UNICODE</literal></entry>

1864

1865

</row>

1866

1867

<row>

1868

1869

<entry><literal>UNICODE</literal></entry>

1870

<entry><literal>LATIN1</literal></entry>

1871

</row>

1872

1873

<row>

1874

1875

<entry><literal>UNICODE</literal></entry>

1876

<entry><literal>LATIN6</literal></entry>

1877

</row>

1878

1879

<row>

1880

1881

<entry><literal>UNICODE</literal></entry>

1882

<entry><literal>LATIN7</literal></entry>

1883

</row>

1884

1885

<row>

1886

1887

<entry><literal>UNICODE</literal></entry>

1888

<entry><literal>LATIN8</literal></entry>

1889

</row>

1890

1891

<row>

1892

1893

<entry><literal>UNICODE</literal></entry>

1894

<entry><literal>LATIN9</literal></entry>

1895

</row>

1896

1897

<row>

1898

1899

<entry><literal>UNICODE</literal></entry>

1900

<entry><literal>LATIN10</literal></entry>

1901

</row>

1902

1903

<row>

1904

1905

<entry><literal>UNICODE</literal></entry>

1906

<entry><literal>LATIN2</literal></entry>

1907

</row>

1908

1909

<row>

1910

1911

<entry><literal>UNICODE</literal></entry>

1912

<entry><literal>LATIN3</literal></entry>

1913

</row>

1914

1915

<row>

1916

1917

<entry><literal>UNICODE</literal></entry>

1918

<entry><literal>LATIN4</literal></entry>

1919

</row>

1920

1921

<row>

1922

1923

<entry><literal>UNICODE</literal></entry>

1924

1925

</row>

1926

1927

<row>

1928

1929

<entry><literal>UNICODE</literal></entry>

1930

1931

</row>

1932

1933

<row>

1934

1935

<entry><literal>UNICODE</literal></entry>

1936

1937

</row>

1938

1939

<row>

1940

1941

<entry><literal>UNICODE</literal></entry>

1942

1943

</row>

1944

1945

<row>

1946

1947

<entry><literal>UNICODE</literal></entry>

1948

<entry><literal>LATIN5</literal></entry>

1949

</row>

1950

1951

<row>

1952

<entry><literal>utf_8_to_johab</literal></entry>

1953

<entry><literal>UNICODE</literal></entry>

1954

<entry><literal>JOHAB</literal></entry>

1955

</row>

1956

1957

<row>

1958

1959

<entry><literal>UNICODE</literal></entry>

1960

1961

</row>

1962

1963

<row>

1964

1965

<entry><literal>UNICODE</literal></entry>

1966

1967

</row>

1968

1969

<row>

1970

1971

<entry><literal>UNICODE</literal></entry>

1972

1973

</row>

1974

1975

<row>

1976

1977

<entry><literal>UNICODE</literal></entry>

1978

1979

</row>

1980

1981

<row>

1982

<entry><literal>utf_8_to_windows_1250</literal></entry>

1983

<entry><literal>UNICODE</literal></entry>

1984

1985

</row>

1986

1987

<row>

1988

<entry><literal>utf_8_to_windows_1251</literal></entry>

1989

<entry><literal>UNICODE</literal></entry>

1990

1991

</row>

1992

1993

<row>

1994

<entry><literal>utf_8_to_windows_1256</literal></entry>

1995

<entry><literal>UNICODE</literal></entry>

1996

1997

</row>

1998

1999

<row>

2000

<entry><literal>utf_8_to_windows_866</literal></entry>

2001

<entry><literal>UNICODE</literal></entry>

2002

2003

</row>

2004

2005

<row>

2006

<entry><literal>utf_8_to_windows_874</literal></entry>

2007

<entry><literal>UNICODE</literal></entry>

2008

2009

</row>

2010

2011

<row>

2012

<entry><literal>windows_1250_to_iso_8859_2</literal></entry>

2013

2014

<entry><literal>LATIN2</literal></entry>

2015

</row>

2016

2017

<row>

2018

<entry><literal>windows_1250_to_mic</literal></entry>

2019

2020

<entry><literal>MULE_INTERNAL</literal></entry>

2021

</row>

2022

2023

<row>

2024

<entry><literal>windows_1250_to_utf_8</literal></entry>

2025

2026

<entry><literal>UNICODE</literal></entry>

2027

</row>

2028

2029

<row>

2030

<entry><literal>windows_1251_to_iso_8859_5</literal></entry>

2031

2032

2033

</row>

2034

2035

<row>

2036

<entry><literal>windows_1251_to_koi8_r</literal></entry>

2037

2038

2039

</row>

2040

2041

<row>

2042

<entry><literal>windows_1251_to_mic</literal></entry>

2043

2044

<entry><literal>MULE_INTERNAL</literal></entry>

2045

</row>

2046

2047

<row>

2048

<entry><literal>windows_1251_to_utf_8</literal></entry>

2049

2050

<entry><literal>UNICODE</literal></entry>

2051

</row>

2052

2053

<row>

2054

<entry><literal>windows_1251_to_windows_866</literal></entry>

2055

2056

2057

</row>

2058

2059

<row>

2060

<entry><literal>windows_1256_to_utf_8</literal></entry>

2061

2062

<entry><literal>UNICODE</literal></entry>

2063

</row>

2064

2065

<row>

2066

<entry><literal>windows_866_to_iso_8859_5</literal></entry>

2067

2068

2069

</row>

2070

2071

<row>

2072

<entry><literal>windows_866_to_koi8_r</literal></entry>

2073

2074

2075

</row>

2076

2077

<row>

2078

<entry><literal>windows_866_to_mic</literal></entry>

2079

2080

<entry><literal>MULE_INTERNAL</literal></entry>

2081

</row>

2082

2083

<row>

2084

<entry><literal>windows_866_to_utf_8</literal></entry>

2085

2086

<entry><literal>UNICODE</literal></entry>

2087

</row>

2088

2089

<row>

2090

<entry><literal>windows_866_to_windows_1251</literal></entry>

2091

2092

2093

</row>

2094

2095

<row>

2096

<entry><literal>windows_874_to_utf_8</literal></entry>

2097

2098

<entry><literal>UNICODE</literal></entry>

2099

</row>

2100

2101

</tbody>

2102

</tgroup>

2103

</table>

2104

2105

</sect1>

2106

2107

2108

2109

<title>Binary String Functions and Operators</title>

2110

2111

2112

<primary>binary data</primary>

2113

<secondary>functions</secondary>

2114

</indexterm>

2115

2116

<para>

2117

This section describes functions and operators for examining and

2118

manipulating values of type <type>bytea</type>.

2119

</para>

2120

2121

<para>

2122

<acronym>SQL</acronym> defines some string functions with a

2123

special syntax where

2124

certain key words rather than commas are used to separate the

2125

arguments. Details are in

2126

<xref linkend="functions-binarystring-sql">.

2127

Some functions are also implemented using the regular syntax for

2128

function invocation.

2129

(See <xref linkend="functions-binarystring-other">.)

2130

</para>

2131

2132

2133

<title><acronym>SQL</acronym> Binary String Functions and Operators</title>

2134

2135

<thead>

2136

<row>

2137

<entry>Function</entry>

2138

<entry>Return Type</entry>

2139

<entry>Description</entry>

2140

<entry>Example</entry>

2141

<entry>Result</entry>

2142

</row>

2143

</thead>

2144

2145

<tbody>

2146

<row>

2147

<entry><literal><parameter>string</parameter> <literal>||</literal>

2148

<parameter>string</parameter></literal></entry>

2149

<entry> <type>bytea</type> </entry>

2150

<entry>

2151

String concatenation

2152

2153

<primary>binary string</primary>

2154

<secondary>concatenation</secondary>

2155

</indexterm>

2156

</entry>

2157

<entry><literal>'\\\\Post'::bytea || '\\047gres\\000'::bytea</literal></entry>

2158

2159

</row>

2160

2161

<row>

2162

<entry><literal><function>octet_length</function>(<parameter>string</parameter>)</literal></entry>

2163

<entry><type>integer</type></entry>

2164

<entry>Number of bytes in binary string</entry>

2165

<entry><literal>octet_length( 'jo\\000se'::bytea)</literal></entry>

2166

2167

</row>

2168

2169

<row>

2170

<entry><literal><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</literal></entry>

2171

<entry><type>integer</type></entry>

2172

<entry>Location of specified substring</entry>

2173

<entry><literal>position('\\000om'::bytea in 'Th\\000omas'::bytea)</literal></entry>

2174

2175

</row>

2176

2177

<row>

2178

<entry><literal><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</literal></entry>

2179

<entry><type>bytea</type></entry>

2180

<entry>

2181

Extract substring

2182

2183

<primary>substring</primary>

2184

</indexterm>

2185

</entry>

2186

<entry><literal>substring('Th\\000omas'::bytea from 2 for 3)</literal></entry>

2187

2188

</row>

2189

2190

<row>

2191

<entry>

2192

2193

<parameter>bytes</parameter> from

2194

<parameter>string</parameter>)</literal>

2195

</entry>

2196

<entry><type>bytea</type></entry>

2197

<entry>

2198

Remove the longest string containing only the bytes in

2199

<parameter>bytes</parameter> from the start

2200

and end of <parameter>string</parameter>

2201

</entry>

2202

<entry><literal>trim('\\000'::bytea from '\\000Tom\\000'::bytea)</literal></entry>

2203

2204

</row>

2205

2206

<row>

2207

<entry><function>get_byte</function>(<parameter>string</parameter>, <parameter>offset</parameter>)</entry>

2208

<entry><type>integer</type></entry>

2209

<entry>

2210

Extract byte from string.

2211

2212

2213

</indexterm>

2214

</entry>

2215

<entry><literal>get_byte('Th\\000omas'::bytea, 4)</literal></entry>

2216

2217

</row>

2218

2219

<row>

2220

<entry><function>set_byte</function>(<parameter>string</parameter>,

2221

<parameter>offset</parameter>, <parameter>newvalue</>)</entry>

2222

<entry><type>bytea</type></entry>

2223

<entry>

2224

Set byte in string.

2225

2226

2227

</indexterm>

2228

</entry>

2229

<entry><literal>set_byte('Th\\000omas'::bytea, 4, 64)</literal></entry>

2230

2231

</row>

2232

2233

<row>

2234

<entry><function>get_bit</function>(<parameter>string</parameter>, <parameter>offset</parameter>)</entry>

2235

<entry><type>integer</type></entry>

2236

<entry>

2237

Extract bit from string.

2238

2239

2240

</indexterm>

2241

</entry>

2242

<entry><literal>get_bit('Th\\000omas'::bytea, 45)</literal></entry>

2243

2244

</row>

2245

2246

<row>

2247

<entry><function>set_bit</function>(<parameter>string</parameter>,

2248

<parameter>offset</parameter>, <parameter>newvalue</>)</entry>

2249

<entry><type>bytea</type></entry>

2250

<entry>

2251

Set bit in string.

2252

2253

2254

</indexterm>

2255

</entry>

2256

<entry><literal>set_bit('Th\\000omas'::bytea, 45, 0)</literal></entry>

2257

2258

</row>

2259

</tbody>

2260

</tgroup>

2261

</table>

2262

2263

<para>

2264

Additional binary string manipulation functions are available and

2265

are listed in <xref linkend="functions-binarystring-other">. Some

2266

of them are used internally to implement the

2267

<acronym>SQL</acronym>-standard string functions listed in <xref

2268

linkend="functions-binarystring-sql">.

2269

</para>

2270

2271

2272

<title>Other Binary String Functions</title>

2273

2274

<thead>

2275

<row>

2276

<entry>Function</entry>

2277

<entry>Return Type</entry>

2278

<entry>Description</entry>

2279

<entry>Example</entry>

2280

<entry>Result</entry>

2281

</row>

2282

</thead>

2283

2284

<tbody>

2285

<row>

2286

<entry><literal><function>btrim</function>(<parameter>string</parameter>

2287

<type>bytea</type>, <parameter>bytes</parameter> <type>bytea</type>)</literal></entry>

2288

<entry><type>bytea</type></entry>

2289

<entry>

2290

Remove the longest string consisting only of bytes

2291

in <parameter>bytes</parameter> from the start and end of

2292

<parameter>string</parameter>.

2293

</entry>

2294

<entry><literal>btrim('\\000trim\\000'::bytea, '\\000'::bytea)</literal></entry>

2295

2296

</row>

2297

2298

<row>

2299

<entry><literal><function>length</function>(<parameter>string</parameter>)</literal></entry>

2300

<entry><type>integer</type></entry>

2301

<entry>

2302

Length of binary string

2303

2304

<primary>binary string</primary>

2305

<secondary>length</secondary>

2306

</indexterm>

2307

2308

<primary>length</primary>

2309

<secondary sortas="binary string">of a binary string</secondary>

2310

<see>binary strings, length</see>

2311

</indexterm>

2312

</entry>

2313

<entry><literal>length('jo\\000se'::bytea)</literal></entry>

2314

2315

</row>

2316

2317

<row>

2318

<entry>

2319

<literal><function>decode</function>(<parameter>string</parameter> <type>text</type>,

2320

2321

</entry>

2322

<entry><type>bytea</type></entry>

2323

<entry>

2324

Decode binary string from <parameter>string</parameter> previously

2325

encoded with <literal>encode</>. Parameter type is same as in <literal>encode</>.

2326

</entry>

2327

<entry><literal>decode('123\\000456', 'escape')</literal></entry>

2328

2329

</row>

2330

2331

<row>

2332

<entry>

2333

<literal><function>encode</function>(<parameter>string</parameter> <type>bytea</type>,

2334

2335

</entry>

2336

2337

<entry>

2338

Encode binary string to <acronym>ASCII</acronym>-only representation. Supported

2339

types are: <literal>base64</>, <literal>hex</>, <literal>escape</>.

2340

</entry>

2341

<entry><literal>encode('123\\000456'::bytea, 'escape')</literal></entry>

2342

2343

</row>

2344

2345

</tbody>

2346

</tgroup>

2347

</table>

2348

2349

</sect1>

2350

2351

2352

2353

<title>Bit String Functions and Operators</title>

2354

2355

2356

<primary>bit strings</primary>

2357

<secondary>functions</secondary>

2358

</indexterm>

2359

2360

<para>

2361

This section describes functions and operators for examining and

2362

manipulating bit strings, that is values of the types

2363

<type>bit</type> and <type>bit varying</type>. Aside from the

2364

usual comparison operators, the operators

2365

shown in <xref linkend="functions-bit-string-op-table"> can be used.

2366

Bit string operands of <literal>&</literal>, <literal>|</literal>,

2367

and <literal>#</literal> must be of equal length. When bit

2368

shifting, the original length of the string is preserved, as shown

2369

in the examples.

2370

</para>

2371

2372

2373

<title>Bit String Operators</title>

2374

2375

2376

<thead>

2377

<row>

2378

<entry>Operator</entry>

2379

<entry>Description</entry>

2380

<entry>Example</entry>

2381

<entry>Result</entry>

2382

</row>

2383

</thead>

2384

2385

<tbody>

2386

<row>

2387

2388

<entry>concatenation</entry>

2389

2390

2391

</row>

2392

2393

<row>

2394

2395

<entry>bitwise AND</entry>

2396

2397

2398

</row>

2399

2400

<row>

2401

2402

<entry>bitwise OR</entry>

2403

2404

2405

</row>

2406

2407

<row>

2408

2409

<entry>bitwise XOR</entry>

2410

2411

2412

</row>

2413

2414

<row>

2415

2416

<entry>bitwise NOT</entry>

2417

2418

2419

</row>

2420

2421

<row>

2422

2423

<entry>bitwise shift left</entry>

2424

2425

2426

</row>

2427

2428

<row>

2429

2430

<entry>bitwise shift right</entry>

2431

2432

2433

</row>

2434

</tbody>

2435

</tgroup>

2436

</table>

2437

2438

<para>

2439

The following <acronym>SQL</acronym>-standard functions work on bit

2440

strings as well as character strings:

2441

<literal><function>length</function></literal>,

2442

<literal><function>bit_length</function></literal>,

2443

<literal><function>octet_length</function></literal>,

2444

<literal><function>position</function></literal>,

2445

<literal><function>substring</function></literal>.

2446

</para>

2447

2448

<para>

2449

In addition, it is possible to cast integral values to and from type

2450

<type>bit</>.

2451

Some examples:

2452

2453

44::bit(10) <lineannotation>0000101100</lineannotation>

2454

44::bit(3) <lineannotation>100</lineannotation>

2455

cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation>

2456

'1110'::bit(4)::integer <lineannotation>14</lineannotation>

2457

</programlisting>

2458

Note that casting to just <quote>bit</> means casting to

2459

<literal>bit(1)</>, and so it will deliver only the least significant

2460

bit of the integer.

2461

</para>

2462

2463

<note>

2464

<para>

2465

Prior to <productname>PostgreSQL</productname> 8.0, casting an

2466

integer to <type>bit(n)</> would copy the leftmost <literal>n</>

2467

bits of the integer, whereas now it copies the rightmost <literal>n</>

2468

bits. Also, casting an integer to a bit string width wider than

2469

the integer itself will sign-extend on the left.

2470

</para>

2471

</note>

2472

2473

</sect1>

2474

2475

2476

2477

<title>Pattern Matching</title>

2478

2479

2480

<primary>pattern matching</primary>

2481

</indexterm>

2482

2483

<para>

2484

There are three separate approaches to pattern matching provided

2485

by <productname>PostgreSQL</productname>: the traditional

2486

<acronym>SQL</acronym> <function>LIKE</function> operator, the

2487

more recent <function>SIMILAR TO</function> operator (added in

2488

SQL:1999), and <acronym>POSIX</acronym>-style regular expressions.

2489

Additionally, a pattern matching function,

2490

<function>substring</function>, is available, using either

2491

<function>SIMILAR TO</function>-style or POSIX-style regular

2492

expressions.

2493

</para>

2494

2495

<tip>

2496

<para>

2497

If you have pattern matching needs that go beyond this,

2498

consider writing a user-defined function in Perl or Tcl.

2499

</para>

2500

</tip>

2501

2502

2503

2504

2505

2506

2507

</indexterm>

2508

2509

2510

<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>

2511

<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>

2512

</synopsis>

2513

2514

<para>

2515

Every <replaceable>pattern</replaceable> defines a set of strings.

2516

The <function>LIKE</function> expression returns true if the

2517

<replaceable>string</replaceable> is contained in the set of

2518

strings represented by <replaceable>pattern</replaceable>. (As

2519

expected, the <function>NOT LIKE</function> expression returns

2520

false if <function>LIKE</function> returns true, and vice versa.

2521

An equivalent expression is

2522

<literal>NOT (<replaceable>string</replaceable> LIKE

2523

<replaceable>pattern</replaceable>)</literal>.)

2524

</para>

2525

2526

<para>

2527

If <replaceable>pattern</replaceable> does not contain percent

2528

signs or underscore, then the pattern only represents the string

2529

itself; in that case <function>LIKE</function> acts like the

2530

equals operator. An underscore (<literal>_</literal>) in

2531

<replaceable>pattern</replaceable> stands for (matches) any single

2532

character; a percent sign (<literal>%</literal>) matches any string

2533

of zero or more characters.

2534

</para>

2535

2536

<para>

2537

Some examples:

2538

2539

'abc' LIKE 'abc' <lineannotation>true</lineannotation>

2540

'abc' LIKE 'a%' <lineannotation>true</lineannotation>

2541

'abc' LIKE '_b_' <lineannotation>true</lineannotation>

2542

'abc' LIKE 'c' <lineannotation>false</lineannotation>

2543

</programlisting>

2544

</para>

2545

2546

<para>

2547

<function>LIKE</function> pattern matches always cover the entire

2548

string. To match a sequence anywhere within a string, the

2549

pattern must therefore start and end with a percent sign.

2550

</para>

2551

2552

<para>

2553

To match a literal underscore or percent sign without matching

2554

other characters, the respective character in

2555

<replaceable>pattern</replaceable> must be

2556

preceded by the escape character. The default escape

2557

character is the backslash but a different one may be selected by

2558

using the <literal>ESCAPE</literal> clause. To match the escape

2559

character itself, write two escape characters.

2560

</para>

2561

2562

<para>

2563

Note that the backslash already has a special meaning in string

2564

literals, so to write a pattern constant that contains a backslash

2565

you must write two backslashes in an SQL statement. Thus, writing a pattern

2566

that actually matches a literal backslash means writing four backslashes

2567

in the statement. You can avoid this by selecting a different escape

2568

character with <literal>ESCAPE</literal>; then a backslash is not special

2569

to <function>LIKE</function> anymore. (But it is still special to the string

2570

literal parser, so you still need two of them.)

2571

</para>

2572

2573

<para>

2574

It's also possible to select no escape character by writing

2575

<literal>ESCAPE ''</literal>. This effectively disables the

2576

escape mechanism, which makes it impossible to turn off the

2577

special meaning of underscore and percent signs in the pattern.

2578

</para>

2579

2580

<para>

2581

The key word <token>ILIKE</token> can be used instead of

2582

<token>LIKE</token> to make the match case-insensitive according

2583

to the active locale. This is not in the <acronym>SQL</acronym> standard but is a

2584

<productname>PostgreSQL</productname> extension.

2585

</para>

2586

2587

<para>

2588

The operator <literal>~~</literal> is equivalent to

2589

<function>LIKE</function>, and <literal>~~*</literal> corresponds to

2590

<function>ILIKE</function>. There are also

2591

<literal>!~~</literal> and <literal>!~~*</literal> operators that

2592

represent <function>NOT LIKE</function> and <function>NOT

2593

ILIKE</function>, respectively. All of these operators are

2594

<productname>PostgreSQL</productname>-specific.

2595

</para>

2596

</sect2>

2597

2598

2599

2600

<title><function>SIMILAR TO</function> Regular Expressions</title>

2601

2602

2603

<primary>regular expression</primary>

2604

2605

</indexterm>

2606

2607

2608

<primary>SIMILAR TO</primary>

2609

</indexterm>

2610

2611

2612

<primary>substring</primary>

2613

</indexterm>

2614

2615

2616

<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>

2617

<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>

2618

</synopsis>

2619

2620

<para>

2621

The <function>SIMILAR TO</function> operator returns true or

2622

false depending on whether its pattern matches the given string.

2623

It is much like <function>LIKE</function>, except that it

2624

interprets the pattern using the SQL standard's definition of a

2625

regular expression. SQL regular expressions are a curious cross

2626

between <function>LIKE</function> notation and common regular

2627

expression notation.

2628

</para>

2629

2630

<para>

2631

Like <function>LIKE</function>, the <function>SIMILAR TO</function>

2632

operator succeeds only if its pattern matches the entire string;

2633

this is unlike common regular expression practice, wherein the pattern

2634

may match any part of the string.

2635

Also like

2636

<function>LIKE</function>, <function>SIMILAR TO</function> uses

2637

<literal>_</> and <literal>%</> as wildcard characters denoting

2638

any single character and any string, respectively (these are

2639

comparable to <literal>.</> and <literal>.*</> in POSIX regular

2640

expressions).

2641

</para>

2642

2643

<para>

2644

In addition to these facilities borrowed from <function>LIKE</function>,

2645

<function>SIMILAR TO</function> supports these pattern-matching

2646

metacharacters borrowed from POSIX regular expressions:

2647

2648

2649

2650

<para>

2651

<literal>|</literal> denotes alternation (either of two alternatives).

2652

</para>

2653

</listitem>

2654

2655

<para>

2656

<literal>*</literal> denotes repetition of the previous item zero

2657

or more times.

2658

</para>

2659

</listitem>

2660

2661

<para>

2662

<literal>+</literal> denotes repetition of the previous item one

2663

or more times.

2664

</para>

2665

</listitem>

2666

2667

<para>

2668

Parentheses <literal>()</literal> may be used to group items into

2669

a single logical item.

2670

</para>

2671

</listitem>

2672

2673

<para>

2674

A bracket expression <literal>[...]</literal> specifies a character

2675

class, just as in POSIX regular expressions.

2676

</para>

2677

</listitem>

2678

</itemizedlist>

2679

2680

Notice that bounded repetition (<literal>?</> and <literal>{...}</>)

2681

are not provided, though they exist in POSIX. Also, the dot (<literal>.</>)

2682

is not a metacharacter.

2683

</para>

2684

2685

<para>

2686

As with <function>LIKE</>, a backslash disables the special meaning

2687

of any of these metacharacters; or a different escape character can

2688

be specified with <literal>ESCAPE</>.

2689

</para>

2690

2691

<para>

2692

Some examples:

2693

2694

'abc' SIMILAR TO 'abc' <lineannotation>true</lineannotation>

2695

'abc' SIMILAR TO 'a' <lineannotation>false</lineannotation>

2696

'abc' SIMILAR TO '%(b|d)%' <lineannotation>true</lineannotation>

2697

'abc' SIMILAR TO '(b|c)%' <lineannotation>false</lineannotation>

2698

</programlisting>

2699

</para>

2700

2701

<para>

2702

The <function>substring</> function with three parameters,

2703

<function>substring(<parameter>string</parameter> from

2704

<replaceable>pattern</replaceable> for

2705

<replaceable>escape-character</replaceable>)</function>, provides

2706

extraction of a substring that matches an SQL

2707

regular expression pattern. As with <literal>SIMILAR TO</>, the

2708

specified pattern must match to the entire data string, else the

2709

function fails and returns null. To indicate the part of the

2710

pattern that should be returned on success, the pattern must contain

2711

two occurrences of the escape character followed by a double quote

2712

(<literal>"</>). The text matching the portion of the pattern

2713

between these markers is returned.

2714

</para>

2715

2716

<para>

2717

Some examples:

2718

2719

substring('foobar' from '%#"o_b#"%' for '#') <lineannotation>oob</lineannotation>

2720

substring('foobar' from '#"o_b#"%' for '#') <lineannotation>NULL</lineannotation>

2721

</programlisting>

2722

</para>

2723

</sect2>

2724

2725

2726

<title><acronym>POSIX</acronym> Regular Expressions</title>

2727

2728

2729

<primary>regular expression</primary>

2730

<seealso>pattern matching</seealso>

2731

</indexterm>

2732

2733

<para>

2734

<xref linkend="functions-posix-table"> lists the available

2735

operators for pattern matching using POSIX regular expressions.

2736

</para>

2737

2738

2739

<title>Regular Expression Match Operators</title>

2740

2741

2742

<thead>

2743

<row>

2744

<entry>Operator</entry>

2745

<entry>Description</entry>

2746

<entry>Example</entry>

2747

</row>

2748

</thead>

2749

2750

<tbody>

2751

<row>

2752

2753

<entry>Matches regular expression, case sensitive</entry>

2754

<entry><literal>'thomas' ~ '.*thomas.*'</literal></entry>

2755

</row>

2756

2757

<row>

2758

2759

<entry>Matches regular expression, case insensitive</entry>

2760

<entry><literal>'thomas' ~* '.*Thomas.*'</literal></entry>

2761

</row>

2762

2763

<row>

2764

2765

<entry>Does not match regular expression, case sensitive</entry>

2766

<entry><literal>'thomas' !~ '.*Thomas.*'</literal></entry>

2767

</row>

2768

2769

<row>

2770

2771

<entry>Does not match regular expression, case insensitive</entry>

2772

<entry><literal>'thomas' !~* '.*vadim.*'</literal></entry>

2773

</row>

2774

</tbody>

2775

</tgroup>

2776

</table>

2777

2778

<para>

2779

<acronym>POSIX</acronym> regular expressions provide a more

2780

powerful means for

2781

pattern matching than the <function>LIKE</function> and

2782

<function>SIMILAR TO</> operators.

2783

Many Unix tools such as <command>egrep</command>,

2784

<command>sed</command>, or <command>awk</command> use a pattern

2785

matching language that is similar to the one described here.

2786

</para>

2787

2788

<para>

2789

A regular expression is a character sequence that is an

2790

abbreviated definition of a set of strings (a <firstterm>regular

2791

set</firstterm>). A string is said to match a regular expression

2792

if it is a member of the regular set described by the regular

2793

expression. As with <function>LIKE</function>, pattern characters

2794

match string characters exactly unless they are special characters

2795

in the regular expression language — but regular expressions use

2796

different special characters than <function>LIKE</function> does.

2797

Unlike <function>LIKE</function> patterns, a

2798

regular expression is allowed to match anywhere within a string, unless

2799

the regular expression is explicitly anchored to the beginning or

2800

end of the string.

2801

</para>

2802

2803

<para>

2804

Some examples:

2805

2806

'abc' ~ 'abc' <lineannotation>true</lineannotation>

2807

'abc' ~ '^a' <lineannotation>true</lineannotation>

2808

'abc' ~ '(b|d)' <lineannotation>true</lineannotation>

2809

'abc' ~ '^(b|c)' <lineannotation>false</lineannotation>

2810

</programlisting>

2811

</para>

2812

2813

<para>

2814

The <function>substring</> function with two parameters,

2815

<function>substring(<parameter>string</parameter> from

2816

<replaceable>pattern</replaceable>)</function>, provides extraction of a substring

2817

that matches a POSIX regular expression pattern. It returns null if

2818

there is no match, otherwise the portion of the text that matched the

2819

pattern. But if the pattern contains any parentheses, the portion

2820

of the text that matched the first parenthesized subexpression (the

2821

one whose left parenthesis comes first) is

2822

returned. You can put parentheses around the whole expression

2823

if you want to use parentheses within it without triggering this

2824

exception. If you need parentheses in the pattern before the

2825

subexpression you want to extract, see the non-capturing parentheses

2826

described below.

2827

</para>

2828

2829

<para>

2830

Some examples:

2831

2832

substring('foobar' from 'o.b') <lineannotation>oob</lineannotation>

2833

substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>

2834

</programlisting>

2835

</para>

2836

2837

<para>

2838

<productname>PostgreSQL</productname>'s regular expressions are implemented

2839

using a package written by Henry Spencer. Much of

2840

the description of regular expressions below is copied verbatim from his

2841

manual entry.

2842

</para>

2843

2844

2845

2846

2847

<title>Regular Expression Details</title>

2848

2849

<para>

2850

Regular expressions (<acronym>RE</acronym>s), as defined in

2851

<acronym>POSIX</acronym> 1003.2, come in two forms:

2852

<firstterm>extended</> <acronym>RE</acronym>s or <acronym>ERE</>s

2853

(roughly those of <command>egrep</command>), and

2854

<firstterm>basic</> <acronym>RE</acronym>s or <acronym>BRE</>s

2855

(roughly those of <command>ed</command>).

2856

<productname>PostgreSQL</productname> supports both forms, and

2857

also implements some extensions

2858

that are not in the POSIX standard, but have become widely used anyway

2859

due to their availability in programming languages such as Perl and Tcl.

2860

<acronym>RE</acronym>s using these non-POSIX extensions are called

2861

<firstterm>advanced</> <acronym>RE</acronym>s or <acronym>ARE</>s

2862

in this documentation. AREs are almost an exact superset of EREs,

2863

but BREs have several notational incompatibilities (as well as being

2864

much more limited).

2865

We first describe the ARE and ERE forms, noting features that apply

2866

only to AREs, and then describe how BREs differ.

2867

</para>

2868

2869

<note>

2870

<para>

2871

The form of regular expressions accepted by

2872

<productname>PostgreSQL</> can be chosen by setting the <xref

2873

linkend="guc-regex-flavor"> run-time parameter. The usual

2874

setting is <literal>advanced</>, but one might choose

2875

<literal>extended</> for maximum backwards compatibility with

2876

pre-7.4 releases of <productname>PostgreSQL</>.

2877

</para>

2878

</note>

2879

2880

<para>

2881

A regular expression is defined as one or more

2882

<firstterm>branches</firstterm>, separated by

2883

<literal>|</literal>. It matches anything that matches one of the

2884

branches.

2885

</para>

2886

2887

<para>

2888

A branch is zero or more <firstterm>quantified atoms</> or

2889

<firstterm>constraints</>, concatenated.

2890

It matches a match for the first, followed by a match for the second, etc;

2891

an empty branch matches the empty string.

2892

</para>

2893

2894

<para>

2895

A quantified atom is an <firstterm>atom</> possibly followed

2896

by a single <firstterm>quantifier</>.

2897

Without a quantifier, it matches a match for the atom.

2898

With a quantifier, it can match some number of matches of the atom.

2899

An <firstterm>atom</firstterm> can be any of the possibilities

2900

shown in <xref linkend="posix-atoms-table">.

2901

The possible quantifiers and their meanings are shown in

2902

<xref linkend="posix-quantifiers-table">.

2903

</para>

2904

2905

<para>

2906

A <firstterm>constraint</> matches an empty string, but matches only when

2907

specific conditions are met. A constraint can be used where an atom

2908

could be used, except it may not be followed by a quantifier.

2909

The simple constraints are shown in

2910

<xref linkend="posix-constraints-table">;

2911

some more constraints are described later.

2912

</para>

2913

2914

2915

2916

<title>Regular Expression Atoms</title>

2917

2918

2919

<thead>

2920

<row>

2921

2922

<entry>Description</entry>

2923

</row>

2924

</thead>

2925

2926

<tbody>

2927

<row>

2928

2929

<entry> (where <replaceable>re</> is any regular expression)

2930

matches a match for

2931

<replaceable>re</>, with the match noted for possible reporting </entry>

2932

</row>

2933

2934

<row>

2935

2936

<entry> as above, but the match is not noted for reporting

2937

(a <quote>non-capturing</> set of parentheses)

2938

(AREs only) </entry>

2939

</row>

2940

2941

<row>

2942

2943

<entry> matches any single character </entry>

2944

</row>

2945

2946

<row>

2947

<entry> <literal>[</><replaceable>chars</><literal>]</> </entry>

2948

<entry> a <firstterm>bracket expression</>,

2949

matching any one of the <replaceable>chars</> (see

2950

<xref linkend="posix-bracket-expressions"> for more detail) </entry>

2951

</row>

2952

2953

<row>

2954

2955

<entry> (where <replaceable>k</> is a non-alphanumeric character)

2956

matches that character taken as an ordinary character,

2957

e.g. <literal>\\</> matches a backslash character </entry>

2958

</row>

2959

2960

<row>

2961

2962

<entry> where <replaceable>c</> is alphanumeric

2963

(possibly followed by other characters)

2964

is an <firstterm>escape</>, see <xref linkend="posix-escape-sequences">

2965

(AREs only; in EREs and BREs, this matches <replaceable>c</>) </entry>

2966

</row>

2967

2968

<row>

2969

2970

<entry> when followed by a character other than a digit,

2971

matches the left-brace character <literal>{</>;

2972

when followed by a digit, it is the beginning of a

2973

<replaceable>bound</> (see below) </entry>

2974

</row>

2975

2976

<row>

2977

2978

<entry> where <replaceable>x</> is a single character with no other

2979

significance, matches that character </entry>

2980

</row>

2981

</tbody>

2982

</tgroup>

2983

</table>

2984

2985

<para>

2986

An RE may not end with <literal>\</>.

2987

</para>

2988

2989

<note>

2990

<para>

2991

Remember that the backslash (<literal>\</literal>) already has a special

2992

meaning in <productname>PostgreSQL</> string literals.

2993

To write a pattern constant that contains a backslash,

2994

you must write two backslashes in the statement.

2995

</para>

2996

</note>

2997

2998

2999

<title>Regular Expression Quantifiers</title>

3000

3001

3002

<thead>

3003

<row>

3004

<entry>Quantifier</entry>

3005

<entry>Matches</entry>

3006

</row>

3007

</thead>

3008

3009

<tbody>

3010

<row>

3011

3012

<entry> a sequence of 0 or more matches of the atom </entry>

3013

</row>

3014

3015

<row>

3016

3017

<entry> a sequence of 1 or more matches of the atom </entry>

3018

</row>

3019

3020

<row>

3021

3022

<entry> a sequence of 0 or 1 matches of the atom </entry>

3023

</row>

3024

3025

<row>

3026

3027

<entry> a sequence of exactly <replaceable>m</> matches of the atom </entry>

3028

</row>

3029

3030

<row>

3031

3032

<entry> a sequence of <replaceable>m</> or more matches of the atom </entry>

3033

</row>

3034

3035

<row>

3036

<entry>

3037

3038

<entry> a sequence of <replaceable>m</> through <replaceable>n</>

3039

(inclusive) matches of the atom; <replaceable>m</> may not exceed

3040

3041

</row>

3042

3043

<row>

3044

3045

<entry> non-greedy version of <literal>*</> </entry>

3046

</row>

3047

3048

<row>

3049

3050

<entry> non-greedy version of <literal>+</> </entry>

3051

</row>

3052

3053

<row>

3054

3055

<entry> non-greedy version of <literal>?</> </entry>

3056

</row>

3057

3058

<row>

3059

3060

<entry> non-greedy version of <literal>{</><replaceable>m</><literal>}</> </entry>

3061

</row>

3062

3063

<row>

3064

3065

<entry> non-greedy version of <literal>{</><replaceable>m</><literal>,}</> </entry>

3066

</row>

3067

3068

<row>

3069

<entry>

3070

3071

<entry> non-greedy version of <literal>{</><replaceable>m</><literal>,</><replaceable>n</><literal>}</> </entry>

3072

</row>

3073

</tbody>

3074

</tgroup>

3075

</table>

3076

3077

<para>

3078

The forms using <literal>{</><replaceable>...</><literal>}</>

3079

are known as <firstterm>bounds</>.

3080

The numbers <replaceable>m</> and <replaceable>n</> within a bound are

3081

unsigned decimal integers with permissible values from 0 to 255 inclusive.

3082

</para>

3083

3084

<para>

3085

<firstterm>Non-greedy</> quantifiers (available in AREs only) match the

3086

same possibilities as their corresponding normal (<firstterm>greedy</>)

3087

counterparts, but prefer the smallest number rather than the largest

3088

number of matches.

3089

See <xref linkend="posix-matching-rules"> for more detail.

3090

</para>

3091

3092

<note>

3093

<para>

3094

A quantifier cannot immediately follow another quantifier.

3095

A quantifier cannot

3096

begin an expression or subexpression or follow

3097

<literal>^</literal> or <literal>|</literal>.

3098

</para>

3099

</note>

3100

3101

3102

<title>Regular Expression Constraints</title>

3103

3104

3105

<thead>

3106

<row>

3107

<entry>Constraint</entry>

3108

<entry>Description</entry>

3109

</row>

3110

</thead>

3111

3112

<tbody>

3113

<row>

3114

3115

<entry> matches at the beginning of the string </entry>

3116

</row>

3117

3118

<row>

3119

3120

<entry> matches at the end of the string </entry>

3121

</row>

3122

3123

<row>

3124

3125

<entry> <firstterm>positive lookahead</> matches at any point

3126

where a substring matching <replaceable>re</> begins

3127

(AREs only) </entry>

3128

</row>

3129

3130

<row>

3131

3132

<entry> <firstterm>negative lookahead</> matches at any point

3133

where no substring matching <replaceable>re</> begins

3134

(AREs only) </entry>

3135

</row>

3136

</tbody>

3137

</tgroup>

3138

</table>

3139

3140

<para>

3141

Lookahead constraints may not contain <firstterm>back references</>

3142

(see <xref linkend="posix-escape-sequences">),

3143

and all parentheses within them are considered non-capturing.

3144

</para>

3145

</sect3>

3146

3147

3148

<title>Bracket Expressions</title>

3149

3150

<para>

3151

A <firstterm>bracket expression</firstterm> is a list of

3152

characters enclosed in <literal>[]</literal>. It normally matches

3153

any single character from the list (but see below). If the list

3154

begins with <literal>^</literal>, it matches any single character

3155

<emphasis>not</> from the rest of the list.

3156

If two characters

3157

in the list are separated by <literal>-</literal>, this is

3158

shorthand for the full range of characters between those two

3159

(inclusive) in the collating sequence,

3160

e.g. <literal>[0-9]</literal> in <acronym>ASCII</acronym> matches

3161

any decimal digit. It is illegal for two ranges to share an

3162

endpoint, e.g. <literal>a-c-e</literal>. Ranges are very

3163

collating-sequence-dependent, so portable programs should avoid

3164

relying on them.

3165

</para>

3166

3167

<para>

3168

To include a literal <literal>]</literal> in the list, make it the

3169

first character (following a possible <literal>^</literal>). To

3170

include a literal <literal>-</literal>, make it the first or last

3171

character, or the second endpoint of a range. To use a literal

3172

<literal>-</literal> as the first endpoint of a range, enclose it

3173

in <literal>[.</literal> and <literal>.]</literal> to make it a

3174

collating element (see below). With the exception of these characters,

3175

some combinations using <literal>[</literal>

3176

(see next paragraphs), and escapes (AREs only), all other special

3177

characters lose their special significance within a bracket expression.

3178

In particular, <literal>\</literal> is not special when following

3179

ERE or BRE rules, though it is special (as introducing an escape)

3180

in AREs.

3181

</para>

3182

3183

<para>

3184

Within a bracket expression, a collating element (a character, a

3185

multiple-character sequence that collates as if it were a single

3186

character, or a collating-sequence name for either) enclosed in

3187

<literal>[.</literal> and <literal>.]</literal> stands for the

3188

sequence of characters of that collating element. The sequence is

3189

a single element of the bracket expression's list. A bracket

3190

expression containing a multiple-character collating element can thus

3191

match more than one character, e.g. if the collating sequence

3192

includes a <literal>ch</literal> collating element, then the RE

3193

<literal>[[.ch.]]*c</literal> matches the first five characters of

3194

<literal>chchcc</literal>.

3195

</para>

3196

3197

<note>

3198

<para>

3199

<productname>PostgreSQL</> currently has no multi-character collating

3200

elements. This information describes possible future behavior.

3201

</para>

3202

</note>

3203

3204

<para>

3205

Within a bracket expression, a collating element enclosed in

3206

<literal>[=</literal> and <literal>=]</literal> is an equivalence

3207

class, standing for the sequences of characters of all collating

3208

elements equivalent to that one, including itself. (If there are

3209

no other equivalent collating elements, the treatment is as if the

3210

enclosing delimiters were <literal>[.</literal> and

3211

<literal>.]</literal>.) For example, if <literal>o</literal> and

3212

<literal>^</literal> are the members of an equivalence class, then

3213

<literal>[[=o=]]</literal>, <literal>[[=^=]]</literal>, and

3214

<literal>[o^]</literal> are all synonymous. An equivalence class

3215

may not be an endpoint of a range.

3216

</para>

3217

3218

<para>

3219

Within a bracket expression, the name of a character class

3220

enclosed in <literal>[:</literal> and <literal>:]</literal> stands

3221

for the list of all characters belonging to that class. Standard

3222

character class names are: <literal>alnum</literal>,

3223

<literal>alpha</literal>, <literal>blank</literal>,

3224

<literal>cntrl</literal>, <literal>digit</literal>,

3225

<literal>graph</literal>, <literal>lower</literal>,

3226

<literal>print</literal>, <literal>punct</literal>,

3227

<literal>space</literal>, <literal>upper</literal>,

3228

<literal>xdigit</literal>. These stand for the character classes

3229

defined in

3230

<citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>.

3231

A locale may provide others. A character class may not be used as

3232

an endpoint of a range.

3233

</para>

3234

3235

<para>

3236

There are two special cases of bracket expressions: the bracket

3237

expressions <literal>[[:<:]]</literal> and

3238

<literal>[[:>:]]</literal> are constraints,

3239

matching empty strings at the beginning

3240

and end of a word respectively. A word is defined as a sequence

3241

of word characters that is neither preceded nor followed by word

3242

characters. A word character is an <literal>alnum</> character (as

3243

defined by

3244

<citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>)

3245

or an underscore. This is an extension, compatible with but not

3246

specified by <acronym>POSIX</acronym> 1003.2, and should be used with

3247

caution in software intended to be portable to other systems.

3248

The constraint escapes described below are usually preferable (they

3249

are no more standard, but are certainly easier to type).

3250

</para>

3251

</sect3>

3252

3253

3254

<title>Regular Expression Escapes</title>

3255

3256

<para>

3257

<firstterm>Escapes</> are special sequences beginning with <literal>\</>

3258

followed by an alphanumeric character. Escapes come in several varieties:

3259

character entry, class shorthands, constraint escapes, and back references.

3260

A <literal>\</> followed by an alphanumeric character but not constituting

3261

a valid escape is illegal in AREs.

3262

In EREs, there are no escapes: outside a bracket expression,

3263

a <literal>\</> followed by an alphanumeric character merely stands for

3264

that character as an ordinary character, and inside a bracket expression,

3265

<literal>\</> is an ordinary character.

3266

(The latter is the one actual incompatibility between EREs and AREs.)

3267

</para>

3268

3269

<para>

3270

<firstterm>Character-entry escapes</> exist to make it easier to specify

3271

non-printing and otherwise inconvenient characters in REs. They are

3272

shown in <xref linkend="posix-character-entry-escapes-table">.

3273

</para>

3274

3275

<para>

3276

<firstterm>Class-shorthand escapes</> provide shorthands for certain

3277

commonly-used character classes. They are

3278

shown in <xref linkend="posix-class-shorthand-escapes-table">.

3279

</para>

3280

3281

<para>

3282

A <firstterm>constraint escape</> is a constraint,

3283

matching the empty string if specific conditions are met,

3284

written as an escape. They are

3285

shown in <xref linkend="posix-constraint-escapes-table">.

3286

</para>

3287

3288

<para>

3289

A <firstterm>back reference</> (<literal>\</><replaceable>n</>) matches the

3290

same string matched by the previous parenthesized subexpression specified

3291

by the number <replaceable>n</>

3292

(see <xref linkend="posix-constraint-backref-table">). For example,

3293

<literal>([bc])\1</> matches <literal>bb</> or <literal>cc</>

3294

but not <literal>bc</> or <literal>cb</>.

3295

The subexpression must entirely precede the back reference in the RE.

3296

Subexpressions are numbered in the order of their leading parentheses.

3297

Non-capturing parentheses do not define subexpressions.

3298

</para>

3299

3300

<note>

3301

<para>

3302

Keep in mind that an escape's leading <literal>\</> will need to be

3303

doubled when entering the pattern as an SQL string constant. For example:

3304

3305

'123' ~ '^\\d{3}' <lineannotation>true</lineannotation>

3306

</programlisting>

3307

</para>

3308

</note>

3309

3310

3311

<title>Regular Expression Character-Entry Escapes</title>

3312

3313

3314

<thead>

3315

<row>

3316

<entry>Escape</entry>

3317

<entry>Description</entry>

3318

</row>

3319

</thead>

3320

3321

<tbody>

3322

<row>

3323

3324

<entry> alert (bell) character, as in C </entry>

3325

</row>

3326

3327

<row>

3328

3329

<entry> backspace, as in C </entry>

3330

</row>

3331

3332

<row>

3333

3334

<entry> synonym for <literal>\</> to help reduce the need for backslash

3335

doubling </entry>

3336

</row>

3337

3338

<row>

3339

3340

<entry> (where <replaceable>X</> is any character) the character whose

3341

low-order 5 bits are the same as those of

3342

<replaceable>X</>, and whose other bits are all zero </entry>

3343

</row>

3344

3345

<row>

3346

3347

<entry> the character whose collating-sequence name

3348

is <literal>ESC</>,

3349

or failing that, the character with octal value 033 </entry>

3350

</row>

3351

3352

<row>

3353

3354

3355

</row>

3356

3357

<row>

3358

3359

<entry> newline, as in C </entry>

3360

</row>

3361

3362

<row>

3363

3364

<entry> carriage return, as in C </entry>

3365

</row>

3366

3367

<row>

3368

3369

<entry> horizontal tab, as in C </entry>

3370

</row>

3371

3372

<row>

3373

3374

<entry> (where <replaceable>wxyz</> is exactly four hexadecimal digits)

3375

the Unicode character <literal>U+</><replaceable>wxyz</>

3376

in the local byte ordering </entry>

3377

</row>

3378

3379

<row>

3380

<entry> <literal>\U</><replaceable>stuvwxyz</> </entry>

3381

<entry> (where <replaceable>stuvwxyz</> is exactly eight hexadecimal

3382

digits)

3383

reserved for a somewhat-hypothetical Unicode extension to 32 bits

3384

</entry>

3385

</row>

3386

3387

<row>

3388

3389

<entry> vertical tab, as in C </entry>

3390

</row>

3391

3392

<row>

3393

3394

<entry> (where <replaceable>hhh</> is any sequence of hexadecimal

3395

digits)

3396

the character whose hexadecimal value is

3397

3398

(a single character no matter how many hexadecimal digits are used)

3399

</entry>

3400

</row>

3401

3402

<row>

3403

3404

<entry> the character whose value is <literal>0</> </entry>

3405

</row>

3406

3407

<row>

3408

3409

<entry> (where <replaceable>xy</> is exactly two octal digits,

3410

and is not a <firstterm>back reference</>)

3411

the character whose octal value is

3412

3413

</row>

3414

3415

<row>

3416

3417

<entry> (where <replaceable>xyz</> is exactly three octal digits,

3418

and is not a <firstterm>back reference</>)

3419

the character whose octal value is

3420

3421

</row>

3422

</tbody>

3423

</tgroup>

3424

</table>

3425

3426

<para>

3427

Hexadecimal digits are <literal>0</>-<literal>9</>,

3428

<literal>a</>-<literal>f</>, and <literal>A</>-<literal>F</>.

3429

Octal digits are <literal>0</>-<literal>7</>.

3430

</para>

3431

3432

<para>

3433

The character-entry escapes are always taken as ordinary characters.

3434

For example, <literal>\135</> is <literal>]</> in ASCII, but

3435

<literal>\135</> does not terminate a bracket expression.

3436

</para>

3437

3438

3439

<title>Regular Expression Class-Shorthand Escapes</title>

3440

3441

3442

<thead>

3443

<row>

3444

<entry>Escape</entry>

3445

<entry>Description</entry>

3446

</row>

3447

</thead>

3448

3449

<tbody>

3450

<row>

3451

3452

<entry> <literal>[[:digit:]]</> </entry>

3453

</row>

3454

3455

<row>

3456

3457

<entry> <literal>[[:space:]]</> </entry>

3458

</row>

3459

3460

<row>

3461

3462

<entry> <literal>[[:alnum:]_]</>

3463

(note underscore is included) </entry>

3464

</row>

3465

3466

<row>

3467

3468

<entry> <literal>[^[:digit:]]</> </entry>

3469

</row>

3470

3471

<row>

3472

3473

<entry> <literal>[^[:space:]]</> </entry>

3474

</row>

3475

3476

<row>

3477

3478

<entry> <literal>[^[:alnum:]_]</>

3479

(note underscore is included) </entry>

3480

</row>

3481

</tbody>

3482

</tgroup>

3483

</table>

3484

3485

<para>

3486

Within bracket expressions, <literal>\d</>, <literal>\s</>,

3487

and <literal>\w</> lose their outer brackets,

3488

and <literal>\D</>, <literal>\S</>, and <literal>\W</> are illegal.

3489

(So, for example, <literal>[a-c\d]</> is equivalent to

3490

<literal>[a-c[:digit:]]</>.

3491

Also, <literal>[a-c\D]</>, which is equivalent to

3492

<literal>[a-c^[:digit:]]</>, is illegal.)

3493

</para>

3494

3495

3496

<title>Regular Expression Constraint Escapes</title>

3497

3498

3499

<thead>

3500

<row>

3501

<entry>Escape</entry>

3502

<entry>Description</entry>

3503

</row>

3504

</thead>

3505

3506

<tbody>

3507

<row>

3508

3509

<entry> matches only at the beginning of the string

3510

(see <xref linkend="posix-matching-rules"> for how this differs from

3511

3512

</row>

3513

3514

<row>

3515

3516

<entry> matches only at the beginning of a word </entry>

3517

</row>

3518

3519

<row>

3520

3521

<entry> matches only at the end of a word </entry>

3522

</row>

3523

3524

<row>

3525

3526

<entry> matches only at the beginning or end of a word </entry>

3527

</row>

3528

3529

<row>

3530

3531

<entry> matches only at a point that is not the beginning or end of a

3532

word </entry>

3533

</row>

3534

3535

<row>

3536

3537

<entry> matches only at the end of the string

3538

(see <xref linkend="posix-matching-rules"> for how this differs from

3539

3540

</row>

3541

</tbody>

3542

</tgroup>

3543

</table>

3544

3545

<para>

3546

A word is defined as in the specification of

3547

<literal>[[:<:]]</> and <literal>[[:>:]]</> above.

3548

Constraint escapes are illegal within bracket expressions.

3549

</para>

3550

3551

3552

<title>Regular Expression Back References</title>

3553

3554

3555

<thead>

3556

<row>

3557

<entry>Escape</entry>

3558

<entry>Description</entry>

3559

</row>

3560

</thead>

3561

3562

<tbody>

3563

<row>

3564

3565

<entry> (where <replaceable>m</> is a nonzero digit)

3566

a back reference to the <replaceable>m</>'th subexpression </entry>

3567

</row>

3568

3569

<row>

3570

3571

<entry> (where <replaceable>m</> is a nonzero digit, and

3572

<replaceable>nn</> is some more digits, and the decimal value

3573

<replaceable>mnn</> is not greater than the number of closing capturing

3574

parentheses seen so far)

3575

a back reference to the <replaceable>mnn</>'th subexpression </entry>

3576

</row>

3577

</tbody>

3578

</tgroup>

3579

</table>

3580

3581

<note>

3582

<para>

3583

There is an inherent historical ambiguity between octal character-entry

3584

escapes and back references, which is resolved by heuristics,

3585

as hinted at above.

3586

A leading zero always indicates an octal escape.

3587

A single non-zero digit, not followed by another digit,

3588

is always taken as a back reference.

3589

A multi-digit sequence not starting with a zero is taken as a back

3590

reference if it comes after a suitable subexpression

3591

(i.e. the number is in the legal range for a back reference),

3592

and otherwise is taken as octal.

3593

</para>

3594

</note>

3595

</sect3>

3596

3597

3598

<title>Regular Expression Metasyntax</title>

3599

3600

<para>

3601

In addition to the main syntax described above, there are some special

3602

forms and miscellaneous syntactic facilities available.

3603

</para>

3604

3605

<para>

3606

Normally the flavor of RE being used is determined by

3607

<varname>regex_flavor</>.

3608

However, this can be overridden by a <firstterm>director</> prefix.

3609

If an RE begins with <literal>***:</>,

3610

the rest of the RE is taken as an ARE regardless of

3611

<varname>regex_flavor</>.

3612

If an RE begins with <literal>***=</>,

3613

the rest of the RE is taken to be a literal string,

3614

with all characters considered ordinary characters.

3615

</para>

3616

3617

<para>

3618

An ARE may begin with <firstterm>embedded options</>:

3619

a sequence <literal>(?</><replaceable>xyz</><literal>)</>

3620

(where <replaceable>xyz</> is one or more alphabetic characters)

3621

specifies options affecting the rest of the RE.

3622

These options override any previously determined options (including

3623

both the RE flavor and case sensitivity).

3624

The available option letters are

3625

shown in <xref linkend="posix-embedded-options-table">.

3626

</para>

3627

3628

3629

<title>ARE Embedded-Option Letters</title>

3630

3631

3632

<thead>

3633

<row>

3634

<entry>Option</entry>

3635

<entry>Description</entry>

3636

</row>

3637

</thead>

3638

3639

<tbody>

3640

<row>

3641

3642

3643

</row>

3644

3645

<row>

3646

3647

<entry> case-sensitive matching (overrides operator type) </entry>

3648

</row>

3649

3650

<row>

3651

3652

3653

</row>

3654

3655

<row>

3656

3657

<entry> case-insensitive matching (see

3658

<xref linkend="posix-matching-rules">) (overrides operator type) </entry>

3659

</row>

3660

3661

<row>

3662

3663

<entry> historical synonym for <literal>n</> </entry>

3664

</row>

3665

3666

<row>

3667

3668

<entry> newline-sensitive matching (see

3669

3670

</row>

3671

3672

<row>

3673

3674

<entry> partial newline-sensitive matching (see

3675

3676

</row>

3677

3678

<row>

3679

3680

<entry> rest of RE is a literal (<quote>quoted</>) string, all ordinary

3681

characters </entry>

3682

</row>

3683

3684

<row>

3685

3686

<entry> non-newline-sensitive matching (default) </entry>

3687

</row>

3688

3689

<row>

3690

3691

<entry> tight syntax (default; see below) </entry>

3692

</row>

3693

3694

<row>

3695

3696

<entry> inverse partial newline-sensitive (<quote>weird</>) matching

3697

(see <xref linkend="posix-matching-rules">) </entry>

3698

</row>

3699

3700

<row>

3701

3702

<entry> expanded syntax (see below) </entry>

3703

</row>

3704

</tbody>

3705

</tgroup>

3706

</table>

3707

3708

<para>

3709

Embedded options take effect at the <literal>)</> terminating the sequence.

3710

They may appear only at the start of an ARE (after the

3711

<literal>***:</> director if any).

3712

</para>

3713

3714

<para>

3715

In addition to the usual (<firstterm>tight</>) RE syntax, in which all

3716

characters are significant, there is an <firstterm>expanded</> syntax,

3717

available by specifying the embedded <literal>x</> option.

3718

In the expanded syntax,

3719

white-space characters in the RE are ignored, as are

3720

all characters between a <literal>#</>

3721

and the following newline (or the end of the RE). This

3722

permits paragraphing and commenting a complex RE.

3723

There are three exceptions to that basic rule:

3724

3725

3726

3727

<para>

3728

a white-space character or <literal>#</> preceded by <literal>\</> is

3729

retained

3730

</para>

3731

</listitem>

3732

3733

<para>

3734

white space or <literal>#</> within a bracket expression is retained

3735

</para>

3736

</listitem>

3737

3738

<para>

3739

white space and comments cannot appear within multi-character symbols,

3740

such as <literal>(?:</>

3741

</para>

3742

</listitem>

3743

</itemizedlist>

3744

3745

For this purpose, white-space characters are blank, tab, newline, and

3746

any character that belongs to the <replaceable>space</> character class.

3747

</para>

3748

3749

<para>

3750

Finally, in an ARE, outside bracket expressions, the sequence

3751

3752

(where <replaceable>ttt</> is any text not containing a <literal>)</>)

3753

is a comment, completely ignored.

3754

Again, this is not allowed between the characters of

3755

multi-character symbols, like <literal>(?:</>.

3756

Such comments are more a historical artifact than a useful facility,

3757

and their use is deprecated; use the expanded syntax instead.

3758

</para>

3759

3760

<para>

3761

<emphasis>None</> of these metasyntax extensions is available if

3762

an initial <literal>***=</> director

3763

has specified that the user's input be treated as a literal string

3764

rather than as an RE.

3765

</para>

3766

</sect3>

3767

3768

3769

<title>Regular Expression Matching Rules</title>

3770

3771

<para>

3772

In the event that an RE could match more than one substring of a given

3773

string, the RE matches the one starting earliest in the string.

3774

If the RE could match more than one substring starting at that point,

3775

either the longest possible match or the shortest possible match will

3776

be taken, depending on whether the RE is <firstterm>greedy</> or

3777

<firstterm>non-greedy</>.

3778

</para>

3779

3780

<para>

3781

Whether an RE is greedy or not is determined by the following rules:

3782

3783

3784

<para>

3785

Most atoms, and all constraints, have no greediness attribute (because

3786

they cannot match variable amounts of text anyway).

3787

</para>

3788

</listitem>

3789

3790

<para>

3791

Adding parentheses around an RE does not change its greediness.

3792

</para>

3793

</listitem>

3794

3795

<para>

3796

A quantified atom with a fixed-repetition quantifier

3797

(<literal>{</><replaceable>m</><literal>}</>

3798

3799

<literal>{</><replaceable>m</><literal>}?</>)

3800

has the same greediness (possibly none) as the atom itself.

3801

</para>

3802

</listitem>

3803

3804

<para>

3805

A quantified atom with other normal quantifiers (including

3806

3807

with <replaceable>m</> equal to <replaceable>n</>)

3808

is greedy (prefers longest match).

3809

</para>

3810

</listitem>

3811

3812

<para>

3813

A quantified atom with a non-greedy quantifier (including

3814

3815

with <replaceable>m</> equal to <replaceable>n</>)

3816

is non-greedy (prefers shortest match).

3817

</para>

3818

</listitem>

3819

3820

<para>

3821

A branch — that is, an RE that has no top-level

3822

<literal>|</> operator — has the same greediness as the first

3823

quantified atom in it that has a greediness attribute.

3824

</para>

3825

</listitem>

3826

3827

<para>

3828

An RE consisting of two or more branches connected by the

3829

<literal>|</> operator is always greedy.

3830

</para>

3831

</listitem>

3832

</itemizedlist>

3833

</para>

3834

3835

<para>

3836

The above rules associate greediness attributes not only with individual

3837

quantified atoms, but with branches and entire REs that contain quantified

3838

atoms. What that means is that the matching is done in such a way that

3839

the branch, or whole RE, matches the longest or shortest possible

3840

substring <emphasis>as a whole</>. Once the length of the entire match

3841

is determined, the part of it that matches any particular subexpression

3842

is determined on the basis of the greediness attribute of that

3843

subexpression, with subexpressions starting earlier in the RE taking

3844

priority over ones starting later.

3845

</para>

3846

3847

<para>

3848

An example of what this means:

3849

3850

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');

3851

<lineannotation>Result: </lineannotation><computeroutput>123</computeroutput>

3852

SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');

3853

<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>

3854

</screen>

3855

In the first case, the RE as a whole is greedy because <literal>Y*</>

3856

is greedy. It can match beginning at the <literal>Y</>, and it matches

3857

the longest possible string starting there, i.e., <literal>Y123</>.

3858

The output is the parenthesized part of that, or <literal>123</>.

3859

In the second case, the RE as a whole is non-greedy because <literal>Y*?</>

3860

is non-greedy. It can match beginning at the <literal>Y</>, and it matches

3861

the shortest possible string starting there, i.e., <literal>Y1</>.

3862

The subexpression <literal>[0-9]{1,3}</> is greedy but it cannot change

3863

the decision as to the overall match length; so it is forced to match

3864

just <literal>1</>.

3865

</para>

3866

3867

<para>

3868

In short, when an RE contains both greedy and non-greedy subexpressions,

3869

the total match length is either as long as possible or as short as

3870

possible, according to the attribute assigned to the whole RE. The

3871

attributes assigned to the subexpressions only affect how much of that

3872

match they are allowed to <quote>eat</> relative to each other.

3873

</para>

3874

3875

<para>

3876

The quantifiers <literal>{1,1}</> and <literal>{1,1}?</>

3877

can be used to force greediness or non-greediness, respectively,

3878

on a subexpression or a whole RE.

3879

</para>

3880

3881

<para>

3882

Match lengths are measured in characters, not collating elements.

3883

An empty string is considered longer than no match at all.

3884

For example:

3885

3886

matches the three middle characters of <literal>abbbc</>;

3887

<literal>(week|wee)(night|knights)</>

3888

matches all ten characters of <literal>weeknights</>;

3889

when <literal>(.*).*</>

3890

is matched against <literal>abc</> the parenthesized subexpression

3891

matches all three characters; and when

3892

<literal>(a*)*</> is matched against <literal>bc</>

3893

both the whole RE and the parenthesized

3894

subexpression match an empty string.

3895

</para>

3896

3897

<para>

3898

If case-independent matching is specified,

3899

the effect is much as if all case distinctions had vanished from the

3900

alphabet.

3901

When an alphabetic that exists in multiple cases appears as an

3902

ordinary character outside a bracket expression, it is effectively

3903

transformed into a bracket expression containing both cases,

3904

e.g. <literal>x</> becomes <literal>[xX]</>.

3905

When it appears inside a bracket expression, all case counterparts

3906

of it are added to the bracket expression, e.g.

3907

<literal>[x]</> becomes <literal>[xX]</>

3908

and <literal>[^x]</> becomes <literal>[^xX]</>.

3909

</para>

3910

3911

<para>

3912

If newline-sensitive matching is specified, <literal>.</>

3913

and bracket expressions using <literal>^</>

3914

will never match the newline character

3915

(so that matches will never cross newlines unless the RE

3916

explicitly arranges it)

3917

and <literal>^</>and <literal>$</>

3918

will match the empty string after and before a newline

3919

respectively, in addition to matching at beginning and end of string

3920

respectively.

3921

But the ARE escapes <literal>\A</> and <literal>\Z</>

3922

continue to match beginning or end of string <emphasis>only</>.

3923

</para>

3924

3925

<para>

3926

If partial newline-sensitive matching is specified,

3927

this affects <literal>.</> and bracket expressions

3928

as with newline-sensitive matching, but not <literal>^</>

3929

and <literal>$</>.

3930

</para>

3931

3932

<para>

3933

If inverse partial newline-sensitive matching is specified,

3934

this affects <literal>^</> and <literal>$</>

3935

as with newline-sensitive matching, but not <literal>.</>

3936

and bracket expressions.

3937

This isn't very useful but is provided for symmetry.

3938

</para>

3939

</sect3>

3940

3941

3942

<title>Limits and Compatibility</title>

3943

3944

<para>

3945

No particular limit is imposed on the length of REs in this

3946

implementation. However,

3947

programs intended to be highly portable should not employ REs longer

3948

than 256 bytes,

3949

as a POSIX-compliant implementation can refuse to accept such REs.

3950

</para>

3951

3952

<para>

3953

The only feature of AREs that is actually incompatible with

3954

POSIX EREs is that <literal>\</> does not lose its special

3955

significance inside bracket expressions.

3956

All other ARE features use syntax which is illegal or has

3957

undefined or unspecified effects in POSIX EREs;

3958

the <literal>***</> syntax of directors likewise is outside the POSIX

3959

syntax for both BREs and EREs.

3960

</para>

3961

3962

<para>

3963

Many of the ARE extensions are borrowed from Perl, but some have

3964

been changed to clean them up, and a few Perl extensions are not present.

3965

Incompatibilities of note include <literal>\b</>, <literal>\B</>,

3966

the lack of special treatment for a trailing newline,

3967

the addition of complemented bracket expressions to the things

3968

affected by newline-sensitive matching,

3969

the restrictions on parentheses and back references in lookahead

3970

constraints, and the longest/shortest-match (rather than first-match)

3971

matching semantics.

3972

</para>

3973

3974

<para>

3975

Two significant incompatibilities exist between AREs and the ERE syntax

3976

recognized by pre-7.4 releases of <productname>PostgreSQL</>:

3977

3978

3979

3980

<para>

3981

In AREs, <literal>\</> followed by an alphanumeric character is either

3982

an escape or an error, while in previous releases, it was just another

3983

way of writing the alphanumeric.

3984

This should not be much of a problem because there was no reason to

3985

write such a sequence in earlier releases.

3986

</para>

3987

</listitem>

3988

3989

<para>

3990

In AREs, <literal>\</> remains a special character within

3991

<literal>[]</>, so a literal <literal>\</> within a bracket

3992

expression must be written <literal>\\</>.

3993

</para>

3994

</listitem>

3995

</itemizedlist>

3996

3997

While these differences are unlikely to create a problem for most

3998

applications, you can avoid them if necessary by

3999

setting <varname>regex_flavor</> to <literal>extended</>.

4000

</para>

4001

</sect3>

4002

4003

4004

<title>Basic Regular Expressions</title>

4005

4006

<para>

4007

BREs differ from EREs in several respects.

4008

4009

are ordinary characters and there is no equivalent

4010

for their functionality.

4011

The delimiters for bounds are

4012

<literal>\{</> and <literal>\}</>,

4013

with <literal>{</> and <literal>}</>

4014

by themselves ordinary characters.

4015

The parentheses for nested subexpressions are

4016

<literal>$</> and <literal>$</>,

4017

with <literal>(</> and <literal>)</> by themselves ordinary characters.

4018

<literal>^</> is an ordinary character except at the beginning of the

4019

RE or the beginning of a parenthesized subexpression,

4020

<literal>$</> is an ordinary character except at the end of the

4021

RE or the end of a parenthesized subexpression,

4022

and <literal>*</> is an ordinary character if it appears at the beginning

4023

of the RE or the beginning of a parenthesized subexpression

4024

(after a possible leading <literal>^</>).

4025

Finally, single-digit back references are available, and

4026

4027

are synonyms for

4028

4029

respectively; no other escapes are available.

4030

</para>

4031

</sect3>

4032

4033

4034

4035

</sect2>

4036

</sect1>

4037

4038

4039

4040

<title>Data Type Formatting Functions</title>

4041

4042

4043

<primary>formatting</primary>

4044

</indexterm>

4045

4046

4047

4048

</indexterm>

4049

4050

<para>

4051

The <productname>PostgreSQL</productname> formatting functions

4052

provide a powerful set of tools for converting various data types

4053

(date/time, integer, floating point, numeric) to formatted strings

4054

and for converting from formatted strings to specific data types.

4055

<xref linkend="functions-formatting-table"> lists them.

4056

These functions all follow a common calling convention: the first

4057

argument is the value to be formatted and the second argument is a

4058

template that defines the output or input format.

4059

</para>

4060

4061

4062

<title>Formatting Functions</title>

4063

4064

<thead>

4065

<row>

4066

<entry>Function</entry>

4067

<entry>Return Type</entry>

4068

<entry>Description</entry>

4069

<entry>Example</entry>

4070

</row>

4071

</thead>

4072

<tbody>

4073

<row>

4074

<entry><literal><function>to_char</function>(<type>timestamp</type>, <type>text</type>)</literal></entry>

4075

4076

<entry>convert time stamp to string</entry>

4077

<entry><literal>to_char(current_timestamp, 'HH12:MI:SS')</literal></entry>

4078

</row>

4079

<row>

4080

<entry><literal><function>to_char</function>(<type>interval</type>, <type>text</type>)</literal></entry>

4081

4082

<entry>convert interval to string</entry>

4083

<entry><literal>to_char(interval '15h 2m 12s', 'HH24:MI:SS')</literal></entry>

4084

</row>

4085

<row>

4086

4087

4088

<entry>convert integer to string</entry>

4089

4090

</row>

4091

<row>

4092

<entry><literal><function>to_char</function>(<type>double precision</type>,

4093

4094

4095

<entry>convert real/double precision to string</entry>

4096

4097

</row>

4098

<row>

4099

<entry><literal><function>to_char</function>(<type>numeric</type>, <type>text</type>)</literal></entry>

4100

4101

<entry>convert numeric to string</entry>

4102

4103

</row>

4104

<row>

4105

4106

4107

<entry>convert string to date</entry>

4108

4109

</row>

4110

<row>

4111

<entry><literal><function>to_timestamp</function>(<type>text</type>, <type>text</type>)</literal></entry>

4112

<entry><type>timestamp with time zone</type></entry>

4113

<entry>convert string to time stamp</entry>

4114

<entry><literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal></entry>

4115

</row>

4116

<row>

4117

<entry><literal><function>to_number</function>(<type>text</type>, <type>text</type>)</literal></entry>

4118

<entry><type>numeric</type></entry>

4119

<entry>convert string to numeric</entry>

4120

<entry><literal>to_number('12,454.8-', '99G999D9S')</literal></entry>

4121

</row>

4122

</tbody>

4123

</tgroup>

4124

</table>

4125

4126

<para>

4127

Warning: <literal><function>to_char</function>(<type>interval</type>, <type>text</type>)</literal>

4128

is deprecated and should not be used in newly-written code. It will be removed in the next version.

4129

</para>

4130

4131

<para>

4132

In an output template string (for <function>to_char</>), there are certain patterns that are

4133

recognized and replaced with appropriately-formatted data from the value

4134

to be formatted. Any text that is not a template pattern is simply

4135

copied verbatim. Similarly, in an input template string (for anything but <function>to_char</>), template patterns

4136

identify the parts of the input data string to be looked at and the

4137

values to be found there.

4138

</para>

4139

4140

<para>

4141

<xref linkend="functions-formatting-datetime-table"> shows the

4142

template patterns available for formatting date and time values.

4143

</para>

4144

4145

4146

<title>Template Patterns for Date/Time Formatting</title>

4147

4148

<thead>

4149

<row>

4150

<entry>Pattern</entry>

4151

<entry>Description</entry>

4152

</row>

4153

</thead>

4154

<tbody>

4155

<row>

4156

4157

4158

</row>

4159

<row>

4160

4161

4162

</row>

4163

<row>

4164

4165

4166

</row>

4167

<row>

4168

4169

<entry>minute (00-59)</entry>

4170

</row>

4171

<row>

4172

4173

<entry>second (00-59)</entry>

4174

</row>

4175

<row>

4176

4177

<entry>millisecond (000-999)</entry>

4178

</row>

4179

<row>

4180

4181

<entry>microsecond (000000-999999)</entry>

4182

</row>

4183

<row>

4184

4185

<entry>seconds past midnight (0-86399)</entry>

4186

</row>

4187

<row>

4188

<entry><literal>AM</literal> or <literal>A.M.</literal> or

4189

4190

<entry>meridian indicator (uppercase)</entry>

4191

</row>

4192

<row>

4193

<entry><literal>am</literal> or <literal>a.m.</literal> or

4194

4195

<entry>meridian indicator (lowercase)</entry>

4196

</row>

4197

<row>

4198

4199

<entry>year (4 and more digits) with comma</entry>

4200

</row>

4201

<row>

4202

4203

<entry>year (4 and more digits)</entry>

4204

</row>

4205

<row>

4206

4207

<entry>last 3 digits of year</entry>

4208

</row>

4209

<row>

4210

4211

<entry>last 2 digits of year</entry>

4212

</row>

4213

<row>

4214

4215

<entry>last digit of year</entry>

4216

</row>

4217

<row>

4218

4219

<entry>ISO year (4 and more digits)</entry>

4220

</row>

4221

<row>

4222

4223

<entry>last 3 digits of ISO year</entry>

4224

</row>

4225

<row>

4226

4227

<entry>last 2 digits of ISO year</entry>

4228

</row>

4229

<row>

4230

4231

<entry>last digits of ISO year</entry>

4232

</row>

4233

<row>

4234

<entry><literal>BC</literal> or <literal>B.C.</literal> or

4235

4236

<entry>era indicator (uppercase)</entry>

4237

</row>

4238

<row>

4239

<entry><literal>bc</literal> or <literal>b.c.</literal> or

4240

4241

<entry>era indicator (lowercase)</entry>

4242

</row>

4243

<row>

4244

<entry><literal>MONTH</literal></entry>

4245

<entry>full uppercase month name (blank-padded to 9 chars)</entry>

4246

</row>

4247

<row>

4248

<entry><literal>Month</literal></entry>

4249

<entry>full mixed-case month name (blank-padded to 9 chars)</entry>

4250

</row>

4251

<row>

4252

<entry><literal>month</literal></entry>

4253

<entry>full lowercase month name (blank-padded to 9 chars)</entry>

4254

</row>

4255

<row>

4256

4257

<entry>abbreviated uppercase month name (3 chars)</entry>

4258

</row>

4259

<row>

4260

4261

<entry>abbreviated mixed-case month name (3 chars)</entry>

4262

</row>

4263

<row>

4264

4265

<entry>abbreviated lowercase month name (3 chars)</entry>

4266

</row>

4267

<row>

4268

4269

<entry>month number (01-12)</entry>

4270

</row>

4271

<row>

4272

4273

<entry>full uppercase day name (blank-padded to 9 chars)</entry>

4274

</row>

4275

<row>

4276

4277

<entry>full mixed-case day name (blank-padded to 9 chars)</entry>

4278

</row>

4279

<row>

4280

4281

<entry>full lowercase day name (blank-padded to 9 chars)</entry>

4282

</row>

4283

<row>

4284

4285

<entry>abbreviated uppercase day name (3 chars)</entry>

4286

</row>

4287

<row>

4288

4289

<entry>abbreviated mixed-case day name (3 chars)</entry>

4290

</row>

4291

<row>

4292

4293

<entry>abbreviated lowercase day name (3 chars)</entry>

4294

</row>

4295

<row>

4296

4297

4298

</row>

4299

<row>

4300

4301

<entry>day of month (01-31)</entry>

4302

</row>

4303

<row>

4304

4305

<entry>day of week (1-7; Sunday is 1)</entry>

4306

</row>

4307

<row>

4308

4309

<entry>week of month (1-5) (The first week starts on the first day of the month.)</entry>

4310

</row>

4311

<row>

4312

4313

<entry>week number of year (1-53) (The first week starts on the first day of the year.)</entry>

4314

</row>

4315

<row>

4316

4317

<entry>ISO week number of year (The first Thursday of the new year is in week 1.)</entry>

4318

</row>

4319

<row>

4320

4321

<entry>century (2 digits)</entry>

4322

</row>

4323

<row>

4324

4325

<entry>Julian Day (days since January 1, 4712 BC)</entry>

4326

</row>

4327

<row>

4328

4329

<entry>quarter</entry>

4330

</row>

4331

<row>

4332

4333

<entry>month in Roman numerals (I-XII; I=January) (uppercase)</entry>

4334

</row>

4335

<row>

4336

4337

<entry>month in Roman numerals (i-xii; i=January) (lowercase)</entry>

4338

</row>

4339

<row>

4340

4341

<entry>time-zone name (uppercase)</entry>

4342

</row>

4343

<row>

4344

4345

<entry>time-zone name (lowercase)</entry>

4346

</row>

4347

</tbody>

4348

</tgroup>

4349

</table>

4350

4351

<para>

4352

Certain modifiers may be applied to any template pattern to alter its

4353

behavior. For example, <literal>FMMonth</literal>

4354

is the <literal>Month</literal> pattern with the

4355

<literal>FM</literal> modifier.

4356

<xref linkend="functions-formatting-datetimemod-table"> shows the

4357

modifier patterns for date/time formatting.

4358

</para>

4359

4360

4361

<title>Template Pattern Modifiers for Date/Time Formatting</title>

4362

4363

<thead>

4364

<row>

4365

<entry>Modifier</entry>

4366

<entry>Description</entry>

4367

<entry>Example</entry>

4368

</row>

4369

</thead>

4370

<tbody>

4371

<row>

4372

<entry><literal>FM</literal> prefix</entry>

4373

<entry>fill mode (suppress padding blanks and zeroes)</entry>

4374

<entry><literal>FMMonth</literal></entry>

4375

</row>

4376

<row>

4377

<entry><literal>TH</literal> suffix</entry>

4378

<entry>uppercase ordinal number suffix</entry>

4379

4380

</row>

4381

<row>

4382

<entry><literal>th</literal> suffix</entry>

4383

<entry>lowercase ordinal number suffix</entry>

4384

4385

</row>

4386

<row>

4387

<entry><literal>FX</literal> prefix</entry>

4388

<entry>fixed format global option (see usage notes)</entry>

4389

<entry><literal>FX Month DD Day</literal></entry>

4390

</row>

4391

<row>

4392

<entry><literal>SP</literal> suffix</entry>

4393

<entry>spell mode (not yet implemented)</entry>

4394

4395

</row>

4396

</tbody>

4397

</tgroup>

4398

</table>

4399

4400

<para>

4401

Usage notes for date/time formatting:

4402

4403

4404

4405

<para>

4406

<literal>FM</literal> suppresses leading zeroes and trailing blanks

4407

that would otherwise be added to make the output of a pattern be

4408

fixed-width.

4409

</para>

4410

</listitem>

4411

4412

4413

<para>

4414

<function>to_timestamp</function> and <function>to_date</function>

4415

skip multiple blank spaces in the input string if the <literal>FX</literal> option

4416

is not used. <literal>FX</literal> must be specified as the first item

4417

in the template. For example

4418

<literal>to_timestamp('2000    JUN', 'YYYY MON')</literal> is correct, but

4419

<literal>to_timestamp('2000    JUN', 'FXYYYY MON')</literal> returns an error,

4420

because <function>to_timestamp</function> expects one space only.

4421

</para>

4422

</listitem>

4423

4424

4425

<para>

4426

Ordinary text is allowed in <function>to_char</function>

4427

templates and will be output literally. You can put a substring

4428

in double quotes to force it to be interpreted as literal text

4429

even if it contains pattern key words. For example, in

4430

<literal>'"Hello Year "YYYY'</literal>, the <literal>YYYY</literal>

4431

will be replaced by the year data, but the single <literal>Y</literal> in <literal>Year</literal>

4432

will not be.

4433

</para>

4434

</listitem>

4435

4436

4437

<para>

4438

If you want to have a double quote in the output you must

4439

precede it with a backslash, for example <literal>'\\"YYYY

4440

Month\\"'</literal>.

4441

(Two backslashes are necessary because the backslash already

4442

has a special meaning in a string constant.)

4443

</para>

4444

</listitem>

4445

4446

4447

<para>

4448

The <literal>YYYY</literal> conversion from string to <type>timestamp</type> or

4449

<type>date</type> has a restriction if you use a year with more than 4 digits. You must

4450

use some non-digit character or template after <literal>YYYY</literal>,

4451

otherwise the year is always interpreted as 4 digits. For example

4452

(with the year 20000):

4453

<literal>to_date('200001131', 'YYYYMMDD')</literal> will be

4454

interpreted as a 4-digit year; instead use a non-digit

4455

separator after the year, like

4456

<literal>to_date('20000-1131', 'YYYY-MMDD')</literal> or

4457

<literal>to_date('20000Nov31', 'YYYYMonDD')</literal>.

4458

</para>

4459

</listitem>

4460

4461

4462

<para>

4463

In conversions from string to <type>timestamp</type> or

4464

<type>date</type>, the <literal>CC</literal> field is ignored if there

4465

is a <literal>YYY</literal>, <literal>YYYY</literal> or

4466

<literal>Y,YYY</literal> field. If <literal>CC</literal> is used with

4467

<literal>YY</literal> or <literal>Y</literal> then the year is computed

4468

as <literal>(CC-1)*100+YY</literal>.

4469

</para>

4470

</listitem>

4471

4472

4473

<para>

4474

Millisecond (<literal>MS</literal>) and microsecond (<literal>US</literal>)

4475

values in a conversion from string to <type>timestamp</type> are used as part of the

4476

seconds after the decimal point. For example

4477

<literal>to_timestamp('12:3', 'SS:MS')</literal> is not 3 milliseconds,

4478

but 300, because the conversion counts it as 12 + 0.3 seconds.

4479

This means for the format <literal>SS:MS</literal>, the input values

4480

<literal>12:3</literal>, <literal>12:30</literal>, and <literal>12:300</literal> specify the

4481

same number of milliseconds. To get three milliseconds, one must use

4482

<literal>12:003</literal>, which the conversion counts as

4483

12 + 0.003 = 12.003 seconds.

4484

</para>

4485

4486

<para>

4487

Here is a more

4488

complex example:

4489

<literal>to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')</literal>

4490

is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +

4491

1230 microseconds = 2.021230 seconds.

4492

</para>

4493

</listitem>

4494

4495

4496

<para><function>to_char</function>'s day of the week numbering

4497

(see the 'D' formatting pattern) is different from that of the

4498

<function>extract</function> function.

4499

</para>

4500

</listitem>

4501

</itemizedlist>

4502

</para>

4503

4504

<para>

4505

<xref linkend="functions-formatting-numeric-table"> shows the

4506

template patterns available for formatting numeric values.

4507

</para>

4508

4509

4510

<title>Template Patterns for Numeric Formatting</title>

4511

4512

<thead>

4513

<row>

4514

<entry>Pattern</entry>

4515

<entry>Description</entry>

4516

</row>

4517

</thead>

4518

<tbody>

4519

<row>

4520

4521

<entry>value with the specified number of digits</entry>

4522

</row>

4523

<row>

4524

4525

<entry>value with leading zeros</entry>

4526

</row>

4527

<row>

4528

<entry><literal>.</literal> (period)</entry>

4529

<entry>decimal point</entry>

4530

</row>

4531

<row>

4532

<entry><literal>,</literal> (comma)</entry>

4533

<entry>group (thousand) separator</entry>

4534

</row>

4535

<row>

4536

4537

<entry>negative value in angle brackets</entry>

4538

</row>

4539

<row>

4540

4541

<entry>sign anchored to number (uses locale)</entry>

4542

</row>

4543

<row>

4544

4545

<entry>currency symbol (uses locale)</entry>

4546

</row>

4547

<row>

4548

4549

<entry>decimal point (uses locale)</entry>

4550

</row>

4551

<row>

4552

4553

<entry>group separator (uses locale)</entry>

4554

</row>

4555

<row>

4556

4557

<entry>minus sign in specified position (if number < 0)</entry>

4558

</row>

4559

<row>

4560

4561

<entry>plus sign in specified position (if number > 0)</entry>

4562

</row>

4563

<row>

4564

4565

<entry>plus/minus sign in specified position</entry>

4566

</row>

4567

<row>

4568

4569

<entry>roman numeral (input between 1 and 3999)</entry>

4570

</row>

4571

<row>

4572

4573

<entry>ordinal number suffix</entry>

4574

</row>

4575

<row>

4576

4577

<entry>shift specified number of digits (see notes)</entry>

4578

</row>

4579

<row>

4580

4581

<entry>scientific notation (not implemented yet)</entry>

4582

</row>

4583

</tbody>

4584

</tgroup>

4585

</table>

4586

4587

<para>

4588

Usage notes for numeric formatting:

4589

4590

4591

4592

<para>

4593

A sign formatted using <literal>SG</literal>, <literal>PL</literal>, or

4594

<literal>MI</literal> is not anchored to

4595

the number; for example,

4596

<literal>to_char(-12, 'S9999')</literal> produces <literal>'  -12'</literal>,

4597

but <literal>to_char(-12, 'MI9999')</literal> produces <literal>'-  12'</literal>.

4598

The Oracle implementation does not allow the use of

4599

<literal>MI</literal> ahead of <literal>9</literal>, but rather

4600

requires that <literal>9</literal> precede

4601

<literal>MI</literal>.

4602

</para>

4603

</listitem>

4604

4605

4606

<para>

4607

<literal>9</literal> results in a value with the same number of

4608

digits as there are <literal>9</literal>s. If a digit is

4609

not available it outputs a space.

4610

</para>

4611

</listitem>

4612

4613

4614

<para>

4615

<literal>TH</literal> does not convert values less than zero

4616

and does not convert fractional numbers.

4617

</para>

4618

</listitem>

4619

4620

4621

<para>

4622

<literal>PL</literal>, <literal>SG</literal>, and

4623

<literal>TH</literal> are <productname>PostgreSQL</productname>

4624

extensions.

4625

</para>

4626

</listitem>

4627

4628

4629

<para>

4630

<literal>V</literal> effectively

4631

multiplies the input values by

4632

<literal>10^<replaceable>n</replaceable></literal>, where

4633

<replaceable>n</replaceable> is the number of digits following

4634

<literal>V</literal>.

4635

<function>to_char</function> does not support the use of

4636

<literal>V</literal> combined with a decimal point.

4637

(E.g., <literal>99.9V99</literal> is not allowed.)

4638

</para>

4639

</listitem>

4640

</itemizedlist>

4641

</para>

4642

4643

<para>

4644

<xref linkend="functions-formatting-examples-table"> shows some

4645

examples of the use of the <function>to_char</function> function.

4646

</para>

4647

4648

4649

<title><function>to_char</function> Examples</title>

4650

4651

<thead>

4652

<row>

4653

<entry>Expression</entry>

4654

<entry>Result</entry>

4655

</row>

4656

</thead>

4657

<tbody>

4658

<row>

4659

<entry><literal>to_char(current_timestamp, 'Day, DD  HH12:MI:SS')</literal></entry>

4660

<entry><literal>'Tuesday  , 06  05:39:18'</literal></entry>

4661

</row>

4662

<row>

4663

<entry><literal>to_char(current_timestamp, 'FMDay, FMDD  HH12:MI:SS')</literal></entry>

4664

<entry><literal>'Tuesday, 6  05:39:18'</literal></entry>

4665

</row>

4666

<row>

4667

4668

4669

</row>

4670

<row>

4671

4672

4673

</row>

4674

<row>

4675

4676

4677

</row>

4678

<row>

4679

4680

4681

</row>

4682

<row>

4683

4684

4685

</row>

4686

<row>

4687

4688

4689

</row>

4690

<row>

4691

4692

4693

</row>

4694

<row>

4695

4696

4697

</row>

4698

<row>

4699

4700

4701

</row>

4702

<row>

4703

4704

4705

</row>

4706

<row>

4707

4708

4709

</row>

4710

<row>

4711

4712

4713

</row>

4714

<row>

4715

4716

4717

</row>

4718

<row>

4719

4720

4721

</row>

4722

<row>

4723

4724

4725

</row>

4726

<row>

4727

4728

4729

</row>

4730

<row>

4731

4732

4733

</row>

4734

<row>

4735

4736

4737

</row>

4738

<row>

4739

4740

4741

</row>

4742

<row>

4743

4744

4745

</row>

4746

<row>

4747

4748

4749

</row>

4750

<row>

4751

4752

4753

</row>

4754

<row>

4755

4756

4757

</row>

4758

<row>

4759

4760

4761

</row>

4762

<row>

4763

4764

4765

</row>

4766

<row>

4767

4768

<entry><literal>'        CDLXXXV'</literal></entry>

4769

</row>

4770

<row>

4771

4772

<entry><literal>'CDLXXXV'</literal></entry>

4773

</row>

4774

<row>

4775

4776

4777

</row>

4778

<row>

4779

4780

4781

</row>

4782

<row>

4783

<entry><literal>to_char(485, '"Good number:"999')</literal></entry>

4784

<entry><literal>'Good number: 485'</literal></entry>

4785

</row>

4786

<row>

4787

4788

4789

</row>

4790

<row>

4791

4792

4793

</row>

4794

<row>

4795

4796

4797

</row>

4798

<row>

4799

4800

4801

</row>

4802

</tbody>

4803

</tgroup>

4804

</table>

4805

4806

</sect1>

4807

4808

4809

4810

<title>Date/Time Functions and Operators</title>

4811

4812

<para>

4813

<xref linkend="functions-datetime-table"> shows the available

4814

functions for date/time value processing, with details appearing in

4815

the following subsections. <xref

4816

linkend="operators-datetime-table"> illustrates the behaviors of

4817

the basic arithmetic operators (<literal>+</literal>,

4818

<literal>*</literal>, etc.). For formatting functions, refer to

4819

<xref linkend="functions-formatting">. You should be familiar with

4820

the background information on date/time data types from <xref

4821

linkend="datatype-datetime">.

4822

</para>

4823

4824

<para>

4825

All the functions and operators described below that take <type>time</type> or <type>timestamp</type>

4826

inputs actually come in two variants: one that takes <type>time with time zone</type> or <type>timestamp

4827

with time zone</type>, and one that takes <type>time without time zone</type> or <type>timestamp without time zone</type>.

4828

For brevity, these variants are not shown separately. Also, the

4829

<literal>+</> and <literal>*</> operators come in commutative pairs (for

4830

example both date + integer and integer + date); we show only one of each

4831

such pair.

4832

</para>

4833

4834

4835

<title>Date/Time Operators</title>

4836

4837

4838

<thead>

4839

<row>

4840

<entry>Operator</entry>

4841

<entry>Example</entry>

4842

<entry>Result</entry>

4843

</row>

4844

</thead>

4845

4846

<tbody>

4847

<row>

4848

4849

<entry><literal>date '2001-09-28' + integer '7'</literal></entry>

4850

4851

</row>

4852

4853

<row>

4854

4855

<entry><literal>date '2001-09-28' + interval '1 hour'</literal></entry>

4856

<entry><literal>timestamp '2001-09-28 01:00'</literal></entry>

4857

</row>

4858

4859

<row>

4860

4861

4862

<entry><literal>timestamp '2001-09-28 03:00'</literal></entry>

4863

</row>

4864

4865

<row>

4866

4867

<entry><literal>interval '1 day' + interval '1 hour'</literal></entry>

4868

<entry><literal>interval '1 day 01:00'</literal></entry>

4869

</row>

4870

4871

<row>

4872

4873

<entry><literal>timestamp '2001-09-28 01:00' + interval '23 hours'</literal></entry>

4874

<entry><literal>timestamp '2001-09-29 00:00'</literal></entry>

4875

</row>

4876

4877

<row>

4878

4879

<entry><literal>time '01:00' + interval '3 hours'</literal></entry>

4880

4881

</row>

4882

4883

<row>

4884

4885

<entry><literal>- interval '23 hours'</literal></entry>

4886

<entry><literal>interval '-23:00'</literal></entry>

4887

</row>

4888

4889

<row>

4890

4891

4892

<entry><literal>integer '3'</literal></entry>

4893

</row>

4894

4895

<row>

4896

4897

<entry><literal>date '2001-10-01' - integer '7'</literal></entry>

4898

4899

</row>

4900

4901

<row>

4902

4903

<entry><literal>date '2001-09-28' - interval '1 hour'</literal></entry>

4904

<entry><literal>timestamp '2001-09-27 23:00'</literal></entry>

4905

</row>

4906

4907

<row>

4908

4909

4910

<entry><literal>interval '02:00'</literal></entry>

4911

</row>

4912

4913

<row>

4914

4915

<entry><literal>time '05:00' - interval '2 hours'</literal></entry>

4916

4917

</row>

4918

4919

<row>

4920

4921

<entry><literal>timestamp '2001-09-28 23:00' - interval '23 hours'</literal></entry>

4922

<entry><literal>timestamp '2001-09-28 00:00'</literal></entry>

4923

</row>

4924

4925

<row>

4926

4927

<entry><literal>interval '1 day' - interval '1 hour'</literal></entry>

4928

<entry><literal>interval '23:00'</literal></entry>

4929

</row>

4930

4931

<row>

4932

4933

<entry><literal>timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'</literal></entry>

4934

<entry><literal>interval '1 day 15:00'</literal></entry>

4935

</row>

4936

4937

<row>

4938

4939

<entry><literal>interval '1 hour' * double precision '3.5'</literal></entry>

4940

<entry><literal>interval '03:30'</literal></entry>

4941

</row>

4942

4943

<row>

4944

4945

<entry><literal>interval '1 hour' / double precision '1.5'</literal></entry>

4946

<entry><literal>interval '00:40'</literal></entry>

4947

</row>

4948

</tbody>

4949

</tgroup>

4950

</table>

4951

4952

4953

<title>Date/Time Functions</title>

4954

4955

<thead>

4956

<row>

4957

<entry>Function</entry>

4958

<entry>Return Type</entry>

4959

<entry>Description</entry>

4960

<entry>Example</entry>

4961

<entry>Result</entry>

4962

</row>

4963

</thead>

4964

4965

<tbody>

4966

<row>

4967

<entry><literal><function>age</function>(<type>timestamp</type>, <type>timestamp</type>)</literal></entry>

4968

<entry><type>interval</type></entry>

4969

<entry>Subtract arguments, producing a <quote>symbolic</> result that

4970

uses years and months</entry>

4971

<entry><literal>age(timestamp '2001-04-10', timestamp '1957-06-13')</literal></entry>

4972

<entry><literal>43 years 9 mons 27 days</literal></entry>

4973

</row>

4974

4975

<row>

4976

<entry><literal><function>age</function>(<type>timestamp</type>)</literal></entry>

4977

<entry><type>interval</type></entry>

4978

<entry>Subtract from <function>current_date</function></entry>

4979

<entry><literal>age(timestamp '1957-06-13')</literal></entry>

4980

<entry><literal>43 years 8 mons 3 days</literal></entry>

4981

</row>

4982

4983

<row>

4984

<entry><literal><function>current_date</function></literal></entry>

4985

4986

<entry>Today's date; see <xref linkend="functions-datetime-current">

4987

</entry>

4988

4989

4990

</row>

4991

4992

<row>

4993

<entry><literal><function>current_time</function></literal></entry>

4994

4995

4996

</entry>

4997

4998

4999

</row>

5000

5001

<row>

5002

<entry><literal><function>current_timestamp</function></literal></entry>

5003

<entry><type>timestamp with time zone</type></entry>

5004

5005

</entry>

5006

5007

5008

</row>

5009

5010

<row>

5011

<entry><literal><function>date_part</function>(<type>text</type>, <type>timestamp</type>)</literal></entry>

5012

<entry><type>double precision</type></entry>

5013

<entry>Get subfield (equivalent to

5014

<function>extract</function>); see <xref linkend="functions-datetime-extract">

5015

</entry>

5016

<entry><literal>date_part('hour', timestamp '2001-02-16 20:38:40')</literal></entry>

5017

5018

</row>

5019

5020

<row>

5021

<entry><literal><function>date_part</function>(<type>text</type>, <type>interval</type>)</literal></entry>

5022

<entry><type>double precision</type></entry>

5023

<entry>Get subfield (equivalent to

5024

<function>extract</function>); see <xref linkend="functions-datetime-extract">

5025

</entry>

5026

<entry><literal>date_part('month', interval '2 years 3 months')</literal></entry>

5027

5028

</row>

5029

5030

<row>

5031

<entry><literal><function>date_trunc</function>(<type>text</type>, <type>timestamp</type>)</literal></entry>

5032

<entry><type>timestamp</type></entry>

5033

<entry>Truncate to specified precision; see also <xref

5034

linkend="functions-datetime-trunc">

5035

</entry>

5036

<entry><literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal></entry>

5037

5038

</row>

5039

5040

<row>

5041

<entry><literal><function>extract</function>(<parameter>field</parameter> from

5042

<type>timestamp</type>)</literal></entry>

5043

<entry><type>double precision</type></entry>

5044

<entry>Get subfield; see <xref linkend="functions-datetime-extract">

5045

</entry>

5046

<entry><literal>extract(hour from timestamp '2001-02-16 20:38:40')</literal></entry>

5047

5048

</row>

5049

5050

<row>

5051

<entry><literal><function>extract</function>(<parameter>field</parameter> from

5052

<type>interval</type>)</literal></entry>

5053

<entry><type>double precision</type></entry>

5054

<entry>Get subfield; see <xref linkend="functions-datetime-extract">

5055

</entry>

5056

<entry><literal>extract(month from interval '2 years 3 months')</literal></entry>

5057

5058

</row>

5059

5060

<row>

5061

<entry><literal><function>isfinite</function>(<type>timestamp</type>)</literal></entry>

5062

<entry><type>boolean</type></entry>

5063

<entry>Test for finite time stamp (not equal to infinity)</entry>

5064

<entry><literal>isfinite(timestamp '2001-02-16 21:28:30')</literal></entry>

5065

5066

</row>

5067

5068

<row>

5069

<entry><literal><function>isfinite</function>(<type>interval</type>)</literal></entry>

5070

<entry><type>boolean</type></entry>

5071

<entry>Test for finite interval</entry>

5072

<entry><literal>isfinite(interval '4 hours')</literal></entry>

5073

5074

</row>

5075

5076

<row>

5077

<entry><literal><function>localtime</function></literal></entry>

5078

5079

5080

</entry>

5081

5082

5083

</row>

5084

5085

<row>

5086

<entry><literal><function>localtimestamp</function></literal></entry>

5087

<entry><type>timestamp</type></entry>

5088

5089

</entry>

5090

5091

5092

</row>

5093

5094

<row>

5095

5096

<entry><type>timestamp with time zone</type></entry>

5097

<entry>Current date and time (equivalent to

5098

<function>current_timestamp</function>); see <xref

5099

linkend="functions-datetime-current">

5100

</entry>

5101

5102

5103

</row>

5104

5105

<row>

5106

<entry><literal><function>timeofday()</function></literal></entry>

5107

5108

<entry>Current date and time; see <xref

5109

linkend="functions-datetime-current">

5110

</entry>

5111

5112

5113

</row>

5114

5115

</tbody>

5116

</tgroup>

5117

</table>

5118

5119

<para>

5120

In addition to these functions, the SQL <literal>OVERLAPS</> operator is

5121

supported:

5122

5123

( <replaceable>start1</replaceable>, <replaceable>end1</replaceable> ) OVERLAPS ( <replaceable>start2</replaceable>, <replaceable>end2</replaceable> )

5124

( <replaceable>start1</replaceable>, <replaceable>length1</replaceable> ) OVERLAPS ( <replaceable>start2</replaceable>, <replaceable>length2</replaceable> )

5125

</synopsis>

5126

This expression yields true when two time periods (defined by their

5127

endpoints) overlap, false when they do not overlap. The endpoints

5128

can be specified as pairs of dates, times, or time stamps; or as

5129

a date, time, or time stamp followed by an interval.

5130

</para>

5131

5132

5133

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS

5134

(DATE '2001-10-30', DATE '2002-10-30');

5135

<lineannotation>Result: </lineannotation><computeroutput>true</computeroutput>

5136

SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS

5137

(DATE '2001-10-30', DATE '2002-10-30');

5138

<lineannotation>Result: </lineannotation><computeroutput>false</computeroutput>

5139

</screen>

5140

5141

5142

<title><function>EXTRACT</function>, <function>date_part</function></title>

5143

5144

5145

EXTRACT (<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)

5146

</synopsis>

5147

5148

<para>

5149

The <function>extract</function> function retrieves subfields

5150

such as year or hour from date/time values.

5151

<replaceable>source</replaceable> must be a value expression of

5152

type <type>timestamp</type>, <type>time</type>, or <type>interval</type>.

5153

(Expressions of type <type>date</type> will

5154

be cast to <type>timestamp</type> and can therefore be used as

5155

well.) <replaceable>field</replaceable> is an identifier or

5156

string that selects what field to extract from the source value.

5157

The <function>extract</function> function returns values of type

5158

<type>double precision</type>.

5159

The following are valid field names:

5160

5161

5162

5163

5164

<term><literal>century</literal></term>

5165

5166

<para>

5167

The century

5168

</para>

5169

5170

5171

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');

5172

<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>

5173

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');

5174

<lineannotation>Result: </lineannotation><computeroutput>21</computeroutput>

5175

</screen>

5176

5177

<para>

5178

The first century starts at 0001-01-01 00:00:00 AD, although

5179

they did not know it at the time. This definition applies to all

5180

Gregorian calendar countries. There is no century number 0,

5181

you go from -1 to 1.

5182

5183

If you disagree with this, please write your complaint to:

5184

Pope, Cathedral Saint-Peter of Roma, Vatican.

5185

</para>

5186

5187

<para>

5188

<productname>PostgreSQL</productname> releases before 8.0 did not

5189

follow the conventional numbering of centuries, but just returned

5190

the year field divided by 100.

5191

</para>

5192

</listitem>

5193

</varlistentry>

5194

5195

5196

5197

5198

<para>

5199

The day (of the month) field (1 - 31)

5200

</para>

5201

5202

5203

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');

5204

<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>

5205

</screen>

5206

</listitem>

5207

</varlistentry>

5208

5209

5210

<term><literal>decade</literal></term>

5211

5212

<para>

5213

The year field divided by 10

5214

</para>

5215

5216

5217

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');

5218

<lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>

5219

</screen>

5220

</listitem>

5221

</varlistentry>

5222

5223

5224

5225

5226

<para>

5227

The day of the week (0 - 6; Sunday is 0) (for

5228

<type>timestamp</type> values only)

5229

</para>

5230

5231

5232

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');

5233

<lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>

5234

</screen>

5235

<para>

5236

Note that <function>extract</function>'s day of the week numbering is

5237

different from that of the <function>to_char</function> function.

5238

</para>

5239

5240

</listitem>

5241

</varlistentry>

5242

5243

5244

5245

5246

<para>

5247

The day of the year (1 - 365/366) (for <type>timestamp</type> values only)

5248

</para>

5249

5250

5251

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');

5252

<lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>

5253

</screen>

5254

</listitem>

5255

</varlistentry>

5256

5257

5258

<term><literal>epoch</literal></term>

5259

5260

<para>

5261

For <type>date</type> and <type>timestamp</type> values, the

5262

number of seconds since 1970-01-01 00:00:00-00 (can be negative);

5263

for <type>interval</type> values, the total number

5264

of seconds in the interval

5265

</para>

5266

5267

5268

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');

5269

<lineannotation>Result: </lineannotation><computeroutput>982384720</computeroutput>

5270

5271

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');

5272

<lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>

5273

</screen>

5274

5275

<para>

5276

Here is how you can convert an epoch value back to a time

5277

stamp:

5278

</para>

5279

5280

5281

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

5282

</screen>

5283

</listitem>

5284

</varlistentry>

5285

5286

5287

5288

5289

<para>

5290

The hour field (0 - 23)

5291

</para>

5292

5293

5294

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');

5295

<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>

5296

</screen>

5297

</listitem>

5298

</varlistentry>

5299

5300

5301

<term><literal>microseconds</literal></term>

5302

5303

<para>

5304

The seconds field, including fractional parts, multiplied by 1

5305

000 000. Note that this includes full seconds.

5306

</para>

5307

5308

5309

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');

5310

<lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>

5311

</screen>

5312

</listitem>

5313

</varlistentry>

5314

5315

5316

<term><literal>millennium</literal></term>

5317

5318

<para>

5319

The millennium

5320

</para>

5321

5322

5323

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');

5324

<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>

5325

</screen>

5326

5327

<para>

5328

Years in the 1900s are in the second millennium.

5329

The third millennium starts January 1, 2001.

5330

</para>

5331

5332

<para>

5333

<productname>PostgreSQL</productname> releases before 8.0 did not

5334

follow the conventional numbering of millennia, but just returned

5335

the year field divided by 1000.

5336

</para>

5337

</listitem>

5338

</varlistentry>

5339

5340

5341

<term><literal>milliseconds</literal></term>

5342

5343

<para>

5344

The seconds field, including fractional parts, multiplied by

5345

1000. Note that this includes full seconds.

5346

</para>

5347

5348

5349

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');

5350

<lineannotation>Result: </lineannotation><computeroutput>28500</computeroutput>

5351

</screen>

5352

</listitem>

5353

</varlistentry>

5354

5355

5356

<term><literal>minute</literal></term>

5357

5358

<para>

5359

The minutes field (0 - 59)

5360

</para>

5361

5362

5363

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');

5364

<lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>

5365

</screen>

5366

</listitem>

5367

</varlistentry>

5368

5369

5370

<term><literal>month</literal></term>

5371

5372

<para>

5373

For <type>timestamp</type> values, the number of the month

5374

within the year (1 - 12) ; for <type>interval</type> values

5375

the number of months, modulo 12 (0 - 11)

5376

</para>

5377

5378

5379

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');

5380

<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>

5381

5382

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');

5383

<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>

5384

5385

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');

5386

<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>

5387

</screen>

5388

</listitem>

5389

</varlistentry>

5390

5391

5392

<term><literal>quarter</literal></term>

5393

5394

<para>

5395

The quarter of the year (1 - 4) that the day is in (for

5396

<type>timestamp</type> values only)

5397

</para>

5398

5399

5400

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');

5401

<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>

5402

</screen>

5403

</listitem>

5404

</varlistentry>

5405

5406

5407

<term><literal>second</literal></term>

5408

5409

<para>

5410

The seconds field, including fractional parts (0 -

5411

59<footnote><simpara>60 if leap seconds are

5412

implemented by the operating system</simpara></footnote>)

5413

</para>

5414

5415

5416

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');

5417

<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>

5418

5419

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');

5420

<lineannotation>Result: </lineannotation><computeroutput>28.5</computeroutput>

5421

</screen>

5422

</listitem>

5423

</varlistentry>

5424

5425

<term><literal>timezone</literal></term>

5426

5427

<para>

5428

The time zone offset from UTC, measured in seconds. Positive values

5429

correspond to time zones east of UTC, negative values to

5430

zones west of UTC.

5431

</para>

5432

</listitem>

5433

</varlistentry>

5434

5435

5436

<term><literal>timezone_hour</literal></term>

5437

5438

<para>

5439

The hour component of the time zone offset

5440

</para>

5441

</listitem>

5442

</varlistentry>

5443

5444

5445

<term><literal>timezone_minute</literal></term>

5446

5447

<para>

5448

The minute component of the time zone offset

5449

</para>

5450

</listitem>

5451

</varlistentry>

5452

5453

5454

5455

5456

<para>

5457

The number of the week of the year that the day is in. By definition

5458

(<acronym>ISO</acronym> 8601), the first week of a year

5459

contains January 4 of that year. (The <acronym>ISO</acronym>-8601

5460

week starts on Monday.) In other words, the first Thursday of

5461

a year is in week 1 of that year. (for <type>timestamp</type> values only)

5462

</para>

5463

<para>

5464

Because of this, it is possible for early January dates to be part of the

5465

52nd or 53rd week of the previous year. For example, <literal>2005-01-01</>

5466

is part of the 53rd week of year 2004, and <literal>2006-01-01</> is part of

5467

the 52nd week of year 2005.

5468

</para>

5469

5470

5471

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');

5472

<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>

5473

</screen>

5474

</listitem>

5475

</varlistentry>

5476

5477

5478

5479

5480

<para>

5481

The year field. Keep in mind there is no <literal>0 AD</>, so subtracting

5482

<literal>BC</> years from <literal>AD</> years should be done with care.

5483

</para>

5484

5485

5486

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');

5487

<lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>

5488

</screen>

5489

</listitem>

5490

</varlistentry>

5491

5492

</variablelist>

5493

5494

</para>

5495

5496

<para>

5497

The <function>extract</function> function is primarily intended

5498

for computational processing. For formatting date/time values for

5499

display, see <xref linkend="functions-formatting">.

5500

</para>

5501

5502

<para>

5503

The <function>date_part</function> function is modeled on the traditional

5504

<productname>Ingres</productname> equivalent to the

5505

<acronym>SQL</acronym>-standard function <function>extract</function>:

5506

5507

date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)

5508

</synopsis>

5509

Note that here the <replaceable>field</replaceable> parameter needs to

5510

be a string value, not a name. The valid field names for

5511

<function>date_part</function> are the same as for

5512

<function>extract</function>.

5513

</para>

5514

5515

5516

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');

5517

<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>

5518

5519

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');

5520

<lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>

5521

</screen>

5522

5523

</sect2>

5524

5525

5526

<title><function>date_trunc</function></title>

5527

5528

<para>

5529

The function <function>date_trunc</function> is conceptually

5530

similar to the <function>trunc</function> function for numbers.

5531

</para>

5532

5533

<para>

5534

5535

date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)

5536

</synopsis>

5537

<replaceable>source</replaceable> is a value expression of type

5538

<type>timestamp</type> or <type>interval</>.

5539

(Values of type <type>date</type> and

5540

<type>time</type> are cast automatically, to <type>timestamp</type> or

5541

<type>interval</> respectively.)

5542

<replaceable>field</replaceable> selects to which precision to

5543

truncate the input value. The return value is of type

5544

<type>timestamp</type> or <type>interval</>

5545

with all fields that are less significant than the

5546

selected one set to zero (or one, for day and month).

5547

</para>

5548

5549

<para>

5550

Valid values for <replaceable>field</replaceable> are:

5551

5552

<member><literal>microseconds</literal></member>

5553

<member><literal>milliseconds</literal></member>

5554

<member><literal>second</literal></member>

5555

<member><literal>minute</literal></member>

5556

5557

5558

5559

<member><literal>month</literal></member>

5560

5561

<member><literal>decade</literal></member>

5562

<member><literal>century</literal></member>

5563

<member><literal>millennium</literal></member>

5564

</simplelist>

5565

</para>

5566

5567

<para>

5568

Examples:

5569

5570

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');

5571

<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>

5572

5573

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');

5574

<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>

5575

</screen>

5576

</para>

5577

</sect2>

5578

5579

5580

5581

5582

5583

5584

<secondary>conversion</secondary>

5585

</indexterm>

5586

5587

<para>

5588

The <literal>AT TIME ZONE</literal> construct allows conversions

5589

of time stamps to different time zones. <xref

5590

linkend="functions-datetime-zoneconvert-table"> shows its

5591

variants.

5592

</para>

5593

5594

5595

<title><literal>AT TIME ZONE</literal> Variants</title>

5596

5597

<thead>

5598

<row>

5599

<entry>Expression</entry>

5600

<entry>Return Type</entry>

5601

<entry>Description</entry>

5602

</row>

5603

</thead>

5604

5605

<tbody>

5606

<row>

5607

<entry>

5608

<literal><type>timestamp without time zone</type> AT TIME ZONE <replaceable>zone</></literal>

5609

</entry>

5610

<entry><type>timestamp with time zone</type></entry>

5611

<entry>Convert local time in given time zone to UTC</entry>

5612

</row>

5613

5614

<row>

5615

<entry>

5616

<literal><type>timestamp with time zone</type> AT TIME ZONE <replaceable>zone</></literal>

5617

</entry>

5618

<entry><type>timestamp without time zone</type></entry>

5619

<entry>Convert UTC to local time in given time zone</entry>

5620

</row>

5621

5622

<row>

5623

<entry>

5624

5625

</entry>

5626

5627

<entry>Convert local time across time zones</entry>

5628

</row>

5629

</tbody>

5630

</tgroup>

5631

</table>

5632

5633

<para>

5634

In these expressions, the desired time zone <replaceable>zone</> can be

5635

specified either as a text string (e.g., <literal>'PST'</literal>)

5636

or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).

5637

In the text case, the available zone names are those shown in

5638

<xref linkend="datetime-timezone-input-table">. (It would be useful

5639

to support the more general names shown in

5640

<xref linkend="datetime-timezone-set-table">, but this is not yet

5641

implemented.)

5642

</para>

5643

5644

<para>

5645

Examples (supposing that the local time zone is <literal>PST8PDT</>):

5646

5647

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';

5648

<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>

5649

5650

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';

5651

<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>

5652

</screen>

5653

The first example takes a zone-less time stamp and interprets it as MST time

5654

(UTC-7) to produce a UTC time stamp, which is then rotated to PST (UTC-8)

5655

for display. The second example takes a time stamp specified in EST

5656

(UTC-5) and converts it to local time in MST (UTC-7).

5657

</para>

5658

5659

<para>

5660

The function <literal><function>timezone</function>(<replaceable>zone</>,

5661

<replaceable>timestamp</>)</literal> is equivalent to the SQL-conforming construct

5662

<literal><replaceable>timestamp</> AT TIME ZONE

5663

<replaceable>zone</></literal>.

5664

</para>

5665

</sect2>

5666

5667

5668

<title>Current Date/Time</title>

5669

5670

5671

5672

<secondary>current</secondary>

5673

</indexterm>

5674

5675

5676

5677

<secondary>current</secondary>

5678

</indexterm>

5679

5680

<para>

5681

The following functions are available to obtain the current date and/or

5682

time:

5683

5684

CURRENT_DATE

5685

CURRENT_TIME

5686

CURRENT_TIMESTAMP

5687

CURRENT_TIME ( <replaceable>precision</replaceable> )

5688

CURRENT_TIMESTAMP ( <replaceable>precision</replaceable> )

5689

LOCALTIME

5690

LOCALTIMESTAMP

5691

LOCALTIME ( <replaceable>precision</replaceable> )

5692

LOCALTIMESTAMP ( <replaceable>precision</replaceable> )

5693

</synopsis>

5694

</para>

5695

5696

<para>

5697

<function>CURRENT_TIME</function> and

5698

<function>CURRENT_TIMESTAMP</function> deliver values with time zone;

5699

<function>LOCALTIME</function> and

5700

<function>LOCALTIMESTAMP</function> deliver values without time zone.

5701

</para>

5702

5703

<para>

5704

<function>CURRENT_TIME</function>,

5705

<function>CURRENT_TIMESTAMP</function>,

5706

<function>LOCALTIME</function>, and

5707

<function>LOCALTIMESTAMP</function>

5708

can optionally be given

5709

a precision parameter, which causes the result to be rounded

5710

to that many fractional digits in the seconds field. Without a precision parameter,

5711

the result is given to the full available precision.

5712

</para>

5713

5714

<note>

5715

<para>

5716

Prior to <productname>PostgreSQL</productname> 7.2, the precision

5717

parameters were unimplemented, and the result was always given

5718

in integer seconds.

5719

</para>

5720

</note>

5721

5722

<para>

5723

Some examples:

5724

5725

SELECT CURRENT_TIME;

5726

<lineannotation>Result: </lineannotation><computeroutput>14:39:53.662522-05</computeroutput>

5727

5728

SELECT CURRENT_DATE;

5729

<lineannotation>Result: </lineannotation><computeroutput>2001-12-23</computeroutput>

5730

5731

SELECT CURRENT_TIMESTAMP;

5732

<lineannotation>Result: </lineannotation><computeroutput>2001-12-23 14:39:53.662522-05</computeroutput>

5733

5734

SELECT CURRENT_TIMESTAMP(2);

5735

<lineannotation>Result: </lineannotation><computeroutput>2001-12-23 14:39:53.66-05</computeroutput>

5736

5737

SELECT LOCALTIMESTAMP;

5738

<lineannotation>Result: </lineannotation><computeroutput>2001-12-23 14:39:53.662522</computeroutput>

5739

</screen>

5740

</para>

5741

5742

<para>

5743

The function <function>now()</function> is the traditional

5744

<productname>PostgreSQL</productname> equivalent to

5745

<function>CURRENT_TIMESTAMP</function>.

5746

</para>

5747

5748

<para>

5749

There is also the function <function>timeofday()</function>, which for historical

5750

reasons returns a <type>text</type> string rather than a <type>timestamp</type> value:

5751

5752

SELECT timeofday();

5753

<lineannotation>Result: </lineannotation><computeroutput>Sat Feb 17 19:07:32.000126 2001 EST</computeroutput>

5754

</screen>

5755

</para>

5756

5757

<para>

5758

It is important to know that

5759

<function>CURRENT_TIMESTAMP</function> and related functions return

5760

the start time of the current transaction; their values do not

5761

change during the transaction. This is considered a feature:

5762

the intent is to allow a single transaction to have a consistent

5763

notion of the <quote>current</quote> time, so that multiple

5764

modifications within the same transaction bear the same

5765

time stamp. <function>timeofday()</function>

5766

returns the wall-clock time and does advance during transactions.

5767

</para>

5768

5769

<note>

5770

<para>

5771

Other database systems may advance these values more

5772

frequently.

5773

</para>

5774

</note>

5775

5776

<para>

5777

All the date/time data types also accept the special literal value

5778

<literal>now</literal> to specify the current date and time. Thus,

5779

the following three all return the same result:

5780

5781

SELECT CURRENT_TIMESTAMP;

5782

SELECT now();

5783

SELECT TIMESTAMP 'now';

5784

</programlisting>

5785

</para>

5786

5787

<tip>

5788

<para>

5789

You do not want to use the third form when specifying a <literal>DEFAULT</>

5790

clause while creating a table. The system will convert <literal>now</literal>

5791

to a <type>timestamp</type> as soon as the constant is parsed, so that when

5792

the default value is needed,

5793

the time of the table creation would be used! The first two

5794

forms will not be evaluated until the default value is used,

5795

because they are function calls. Thus they will give the desired

5796

behavior of defaulting to the time of row insertion.

5797

</para>

5798

</tip>

5799

</sect2>

5800

</sect1>

5801

5802

5803

5804

<title>Geometric Functions and Operators</title>

5805

5806

<para>

5807

The geometric types <type>point</type>, <type>box</type>,

5808

<type>lseg</type>, <type>line</type>, <type>path</type>,

5809

<type>polygon</type>, and <type>circle</type> have a large set of

5810

native support functions and operators, shown in <xref

5811

linkend="functions-geometry-op-table">, <xref

5812

linkend="functions-geometry-func-table">, and <xref

5813

linkend="functions-geometry-conv-table">.

5814

</para>

5815

5816

5817

<title>Geometric Operators</title>

5818

5819

<thead>

5820

<row>

5821

<entry>Operator</entry>

5822

<entry>Description</entry>

5823

<entry>Example</entry>

5824

</row>

5825

</thead>

5826

<tbody>

5827

<row>

5828

5829

<entry>Translation</entry>

5830

<entry><literal>box '((0,0),(1,1))' + point '(2.0,0)'</literal></entry>

5831

</row>

5832

<row>

5833

5834

<entry>Translation</entry>

5835

<entry><literal>box '((0,0),(1,1))' - point '(2.0,0)'</literal></entry>

5836

</row>

5837

<row>

5838

5839

<entry>Scaling/rotation</entry>

5840

<entry><literal>box '((0,0),(1,1))' * point '(2.0,0)'</literal></entry>

5841

</row>

5842

<row>

5843

5844

<entry>Scaling/rotation</entry>

5845

<entry><literal>box '((0,0),(2,2))' / point '(2.0,0)'</literal></entry>

5846

</row>

5847

<row>

5848

5849

<entry>Point or box of intersection</entry>

5850

5851

</row>

5852

<row>

5853

5854

<entry>Number of points in path or polygon</entry>

5855

5856

</row>

5857

<row>

5858

5859

<entry>Length or circumference</entry>

5860

5861

</row>

5862

<row>

5863

5864

<entry>Center</entry>

5865

<entry><literal>@@ circle '((0,0),10)'</literal></entry>

5866

</row>

5867

<row>

5868

5869

<entry>Closest point to first operand on second operand</entry>

5870

<entry><literal>point '(0,0)' ## lseg '((2,0),(0,2))'</literal></entry>

5871

</row>

5872

<row>

5873

5874

<entry>Distance between</entry>

5875

<entry><literal>circle '((0,0),1)' <-> circle '((5,0),1)'</literal></entry>

5876

</row>

5877

<row>

5878

5879

<entry>Overlaps?</entry>

5880

5881

</row>

5882

<row>

5883

5884

<entry>Does not extend to the right of?</entry>

5885

5886

</row>

5887

<row>

5888

5889

<entry>Does not extend to the left of?</entry>

5890

5891

</row>

5892

<row>

5893

5894

5895

<entry><literal>circle '((0,0),1)' << circle '((5,0),1)'</literal></entry>

5896

</row>

5897

<row>

5898

5899

<entry>Is right of?</entry>

5900

<entry><literal>circle '((5,0),1)' >> circle '((0,0),1)'</literal></entry>

5901

</row>

5902

<row>

5903

5904

<entry>Is below?</entry>

5905

<entry><literal>circle '((0,0),1)' <^ circle '((0,5),1)'</literal></entry>

5906

</row>

5907

<row>

5908

5909

<entry>Is above?</entry>

5910

<entry><literal>circle '((0,5),1)' >^ circle '((0,0),1)'</literal></entry>

5911

</row>

5912

<row>

5913

5914

<entry>Intersects?</entry>

5915

5916

</row>

5917

<row>

5918

5919

<entry>Is horizontal?</entry>

5920

5921

</row>

5922

<row>

5923

5924

<entry>Are horizontally aligned?</entry>

5925

<entry><literal>point '(1,0)' ?- point '(0,0)'</literal></entry>

5926

</row>

5927

<row>

5928

5929

<entry>Is vertical?</entry>

5930

5931

</row>

5932

<row>

5933

5934

<entry>Are vertically aligned?</entry>

5935

<entry><literal>point '(0,1)' ?| point '(0,0)'</literal></entry>

5936

</row>

5937

<row>

5938

5939

<entry>Is perpendicular?</entry>

5940

5941

</row>

5942

<row>

5943

5944

<entry>Are parallel?</entry>

5945

5946

</row>

5947

<row>

5948

5949

<entry>Contains?</entry>

5950

<entry><literal>circle '((0,0),2)' ~ point '(1,1)'</literal></entry>

5951

</row>

5952

<row>

5953

5954

<entry>Contained in or on?</entry>

5955

<entry><literal>point '(1,1)' @ circle '((0,0),2)'</literal></entry>

5956

</row>

5957

<row>

5958

5959

5960

<entry><literal>polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'</literal></entry>

5961

</row>

5962

</tbody>

5963

</tgroup>

5964

</table>

5965

5966

5967

5968

<title>Geometric Functions</title>

5969

5970

<thead>

5971

<row>

5972

<entry>Function</entry>

5973

<entry>Return Type</entry>

5974

<entry>Description</entry>

5975

<entry>Example</entry>

5976

</row>

5977

</thead>

5978

<tbody>

5979

<row>

5980

<entry><literal><function>area</function>(<replaceable>object</>)</literal></entry>

5981

<entry><type>double precision</type></entry>

5982

5983

5984

</row>

5985

<row>

5986

<entry><literal><function>box_intersect</function>(<type>box</>, <type>box</>)</literal></entry>

5987

5988

<entry>intersection box</entry>

5989

<entry><literal>box_intersect(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</literal></entry>

5990

</row>

5991

<row>

5992

<entry><literal><function>center</function>(<replaceable>object</>)</literal></entry>

5993

<entry><type>point</type></entry>

5994

<entry>center</entry>

5995

<entry><literal>center(box '((0,0),(1,2))')</literal></entry>

5996

</row>

5997

<row>

5998

<entry><literal><function>diameter</function>(<type>circle</>)</literal></entry>

5999

<entry><type>double precision</type></entry>

6000

<entry>diameter of circle</entry>

6001

<entry><literal>diameter(circle '((0,0),2.0)')</literal></entry>

6002

</row>

6003

<row>

6004

<entry><literal><function>height</function>(<type>box</>)</literal></entry>

6005

<entry><type>double precision</type></entry>

6006

<entry>vertical size of box</entry>

6007

<entry><literal>height(box '((0,0),(1,1))')</literal></entry>

6008

</row>

6009

<row>

6010

<entry><literal><function>isclosed</function>(<type>path</>)</literal></entry>

6011

<entry><type>boolean</type></entry>

6012

<entry>a closed path?</entry>

6013

<entry><literal>isclosed(path '((0,0),(1,1),(2,0))')</literal></entry>

6014

</row>

6015

<row>

6016

<entry><literal><function>isopen</function>(<type>path</>)</literal></entry>

6017

<entry><type>boolean</type></entry>

6018

6019

<entry><literal>isopen(path '[(0,0),(1,1),(2,0)]')</literal></entry>

6020

</row>

6021

<row>

6022

<entry><literal><function>length</function>(<replaceable>object</>)</literal></entry>

6023

<entry><type>double precision</type></entry>

6024

<entry>length</entry>

6025

<entry><literal>length(path '((-1,0),(1,0))')</literal></entry>

6026

</row>

6027

<row>

6028

<entry><literal><function>npoints</function>(<type>path</>)</literal></entry>

6029

<entry><type>integer</type></entry>

6030

<entry>number of points</entry>

6031

<entry><literal>npoints(path '[(0,0),(1,1),(2,0)]')</literal></entry>

6032

</row>

6033

<row>

6034

<entry><literal><function>npoints</function>(<type>polygon</>)</literal></entry>

6035

<entry><type>integer</type></entry>

6036

<entry>number of points</entry>

6037

<entry><literal>npoints(polygon '((1,1),(0,0))')</literal></entry>

6038

</row>

6039

<row>

6040

<entry><literal><function>pclose</function>(<type>path</>)</literal></entry>

6041

6042

<entry>convert path to closed</entry>

6043

<entry><literal>pclose(path '[(0,0),(1,1),(2,0)]')</literal></entry>

6044

</row>

6045

<![IGNORE[

6046

6047

<row>

6048

<entry><literal><function>point</function>(<type>lseg</>, <type>lseg</>)</literal></entry>

6049

<entry><type>point</type></entry>

6050

<entry>intersection</entry>

6051

<entry><literal>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</literal></entry>

6052

</row>

6053

]]>

6054

<row>

6055

<entry><literal><function>popen</function>(<type>path</>)</literal></entry>

6056

6057

<entry>convert path to open</entry>

6058

<entry><literal>popen(path '((0,0),(1,1),(2,0))')</literal></entry>

6059

</row>

6060

<row>

6061

<entry><literal><function>radius</function>(<type>circle</type>)</literal></entry>

6062

<entry><type>double precision</type></entry>

6063

<entry>radius of circle</entry>

6064

<entry><literal>radius(circle '((0,0),2.0)')</literal></entry>

6065

</row>

6066

<row>

6067

<entry><literal><function>width</function>(<type>box</>)</literal></entry>

6068

<entry><type>double precision</type></entry>

6069

<entry>horizontal size of box</entry>

6070

<entry><literal>width(box '((0,0),(1,1))')</literal></entry>

6071

</row>

6072

</tbody>

6073

</tgroup>

6074

</table>

6075

6076

6077

6078

<title>Geometric Type Conversion Functions</title>

6079

6080

<thead>

6081

<row>

6082

<entry>Function</entry>

6083

<entry>Return Type</entry>

6084

<entry>Description</entry>

6085

<entry>Example</entry>

6086

</row>

6087

</thead>

6088

<tbody>

6089

<row>

6090

<entry><literal><function>box</function>(<type>circle</type>)</literal></entry>

6091

6092

<entry>circle to box</entry>

6093

<entry><literal>box(circle '((0,0),2.0)')</literal></entry>

6094

</row>

6095

<row>

6096

<entry><literal><function>box</function>(<type>point</type>, <type>point</type>)</literal></entry>

6097

6098

<entry>points to box</entry>

6099

<entry><literal>box(point '(0,0)', point '(1,1)')</literal></entry>

6100

</row>

6101

<row>

6102

<entry><literal><function>box</function>(<type>polygon</type>)</literal></entry>

6103

6104

<entry>polygon to box</entry>

6105

<entry><literal>box(polygon '((0,0),(1,1),(2,0))')</literal></entry>

6106

</row>

6107

<row>

6108

<entry><literal><function>circle</function>(<type>box</type>)</literal></entry>

6109

<entry><type>circle</type></entry>

6110

<entry>box to circle</entry>

6111

<entry><literal>circle(box '((0,0),(1,1))')</literal></entry>

6112

</row>

6113

<row>

6114

<entry><literal><function>circle</function>(<type>point</type>, <type>double precision</type>)</literal></entry>

6115

<entry><type>circle</type></entry>

6116

<entry>center and radius to circle</entry>

6117

<entry><literal>circle(point '(0,0)', 2.0)</literal></entry>

6118

</row>

6119

<row>

6120

6121

6122

<entry>box diagonal to line segment</entry>

6123

6124

</row>

6125

<row>

6126

<entry><literal><function>lseg</function>(<type>point</type>, <type>point</type>)</literal></entry>

6127

6128

<entry>points to line segment</entry>

6129

<entry><literal>lseg(point '(-1,0)', point '(1,0)')</literal></entry>

6130

</row>

6131

<row>

6132

<entry><literal><function>path</function>(<type>polygon</type>)</literal></entry>

6133

<entry><type>point</type></entry>

6134

<entry>polygon to path</entry>

6135

<entry><literal>path(polygon '((0,0),(1,1),(2,0))')</literal></entry>

6136

</row>

6137

<row>

6138

<entry><literal><function>point</function>(<type>double

6139

precision</type>, <type>double precision</type>)</literal></entry>

6140

<entry><type>point</type></entry>

6141

<entry>construct point</entry>

6142

<entry><literal>point(23.4, -44.5)</literal></entry>

6143

</row>

6144

<row>

6145

<entry><literal><function>point</function>(<type>box</type>)</literal></entry>

6146

<entry><type>point</type></entry>

6147

<entry>center of box</entry>

6148

<entry><literal>point(box '((-1,0),(1,0))')</literal></entry>

6149

</row>

6150

<row>

6151

<entry><literal><function>point</function>(<type>circle</type>)</literal></entry>

6152

<entry><type>point</type></entry>

6153

<entry>center of circle</entry>

6154

<entry><literal>point(circle '((0,0),2.0)')</literal></entry>

6155

</row>

6156

<row>

6157

<entry><literal><function>point</function>(<type>lseg</type>)</literal></entry>

6158

<entry><type>point</type></entry>

6159

<entry>center of lseg</entry>

6160

<entry><literal>point(lseg '((-1,0),(1,0))')</literal></entry>

6161

</row>

6162

<row>

6163

<entry><literal><function>point</function>(<type>lseg</type>, <type>lseg</type>)</literal></entry>

6164

<entry><type>point</type></entry>

6165

<entry>intersection</entry>

6166

<entry><literal>point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')</literal></entry>

6167

</row>

6168

<row>

6169

<entry><literal><function>point</function>(<type>polygon</type>)</literal></entry>

6170

<entry><type>point</type></entry>

6171

<entry>center of polygon</entry>

6172

<entry><literal>point(polygon '((0,0),(1,1),(2,0))')</literal></entry>

6173

</row>

6174

<row>

6175

<entry><literal><function>polygon</function>(<type>box</type>)</literal></entry>

6176

<entry><type>polygon</type></entry>

6177

<entry>box to 4-point polygon</entry>

6178

<entry><literal>polygon(box '((0,0),(1,1))')</literal></entry>

6179

</row>

6180

<row>

6181

<entry><literal><function>polygon</function>(<type>circle</type>)</literal></entry>

6182

<entry><type>polygon</type></entry>

6183

<entry>circle to 12-point polygon</entry>

6184

<entry><literal>polygon(circle '((0,0),2.0)')</literal></entry>

6185

</row>

6186

<row>

6187

<entry><literal><function>polygon</function>(<replaceable class="parameter">npts</replaceable>, <type>circle</type>)</literal></entry>

6188

<entry><type>polygon</type></entry>

6189

<entry>circle to <replaceable class="parameter">npts</replaceable>-point polygon</entry>

6190

<entry><literal>polygon(12, circle '((0,0),2.0)')</literal></entry>

6191

</row>

6192

<row>

6193

<entry><literal><function>polygon</function>(<type>path</type>)</literal></entry>

6194

<entry><type>polygon</type></entry>

6195

<entry>path to polygon</entry>

6196

<entry><literal>polygon(path '((0,0),(1,1),(2,0))')</literal></entry>

6197

</row>

6198

</tbody>

6199

</tgroup>

6200

</table>

6201

6202

<para>

6203

It is possible to access the two component numbers of a <type>point</>

6204

as though it were an array with indices 0 and 1. For example, if

6205

<literal>t.p</> is a <type>point</> column then

6206

<literal>SELECT p[0] FROM t</> retrieves the X coordinate and

6207

<literal>UPDATE t SET p[1] = ...</> changes the Y coordinate.

6208

In the same way, a value of type <type>box</> or <type>lseg</> may be treated

6209

as an array of two <type>point</> values.

6210

</para>

6211

6212

<para>

6213

The <function>area</function> function works for the types

6214

<type>box</type>, <type>circle</type>, and <type>path</type>.

6215

The <function>area</function> function only works on the

6216

<type>path</type> data type if the points in the

6217

<type>path</type> are non-intersecting. For example, the

6218

6219

6220

won't work, however, the following visually identical

6221

6222

6223

will work. If the concept of an intersecting versus

6224

non-intersecting <type>path</type> is confusing, draw both of the

6225

above <type>path</type>s side by side on a piece of graph paper.

6226

</para>

6227

6228

</sect1>

6229

6230

6231

6232

<title>Network Address Functions and Operators</title>

6233

6234

<para>

6235

<xref linkend="cidr-inet-operators-table"> shows the operators

6236

available for the <type>cidr</type> and <type>inet</type> types.

6237

The operators <literal><<</literal>,

6238

<literal><<=</literal>, <literal>>></literal>, and

6239

<literal>>>=</literal> test for subnet inclusion. They

6240

consider only the network parts of the two addresses, ignoring any

6241

host part, and determine whether one network part is identical to

6242

or a subnet of the other.

6243

</para>

6244

6245

6246

<title><type>cidr</type> and <type>inet</type> Operators</title>

6247

6248

<thead>

6249

<row>

6250

<entry>Operator</entry>

6251

<entry>Description</entry>

6252

<entry>Example</entry>

6253

</row>

6254

</thead>

6255

<tbody>

6256

<row>

6257

6258

6259

6260

</row>

6261

<row>

6262

6263

<entry>is less than or equal</entry>

6264

6265

</row>

6266

<row>

6267

6268

<entry>equals</entry>

6269

6270

</row>

6271

<row>

6272

6273

<entry>is greater or equal</entry>

6274

6275

</row>

6276

<row>

6277

6278

<entry>is greater than</entry>

6279

6280

</row>

6281

<row>

6282

6283

<entry>is not equal</entry>

6284

6285

</row>

6286

<row>

6287

6288

<entry>is contained within</entry>

6289

6290

</row>

6291

<row>

6292

6293

<entry>is contained within or equals</entry>

6294

6295

</row>

6296

<row>

6297

6298

<entry>contains</entry>

6299

6300

</row>

6301

<row>

6302

6303

<entry>contains or equals</entry>

6304

6305

</row>

6306

</tbody>

6307

</tgroup>

6308

</table>

6309

6310

<para>

6311

<xref linkend="cidr-inet-functions-table"> shows the functions

6312

available for use with the <type>cidr</type> and <type>inet</type>

6313

types. The <function>host</function>,

6314

<function>text</function>, and <function>abbrev</function>

6315

functions are primarily intended to offer alternative display

6316

formats. You can cast a text value to <type>inet</> using normal casting

6317

syntax: <literal>inet(<replaceable>expression</>)</literal> or

6318

<literal><replaceable>colname</>::inet</literal>.

6319

</para>

6320

6321

6322

<title><type>cidr</type> and <type>inet</type> Functions</title>

6323

6324

<thead>

6325

<row>

6326

<entry>Function</entry>

6327

<entry>Return Type</entry>

6328

<entry>Description</entry>

6329

<entry>Example</entry>

6330

<entry>Result</entry>

6331

</row>

6332

</thead>

6333

<tbody>

6334

<row>

6335

<entry><literal><function>broadcast</function>(<type>inet</type>)</literal></entry>

6336

6337

<entry>broadcast address for network</entry>

6338

<entry><literal>broadcast('192.168.1.5/24')</literal></entry>

6339

6340

</row>

6341

<row>

6342

6343

6344

<entry>extract IP address as text</entry>

6345

6346

6347

</row>

6348

<row>

6349

<entry><literal><function>masklen</function>(<type>inet</type>)</literal></entry>

6350

<entry><type>integer</type></entry>

6351

<entry>extract netmask length</entry>

6352

<entry><literal>masklen('192.168.1.5/24')</literal></entry>

6353

6354

</row>

6355

<row>

6356

<entry><literal><function>set_masklen</function>(<type>inet</type>, <type>integer</type>)</literal></entry>

6357

6358

<entry>set netmask length for <type>inet</type> value</entry>

6359

<entry><literal>set_masklen('192.168.1.5/24', 16)</literal></entry>

6360

6361

</row>

6362

<row>

6363

<entry><literal><function>netmask</function>(<type>inet</type>)</literal></entry>

6364

6365

<entry>construct netmask for network</entry>

6366

<entry><literal>netmask('192.168.1.5/24')</literal></entry>

6367

6368

</row>

6369

<row>

6370

<entry><literal><function>hostmask</function>(<type>inet</type>)</literal></entry>

6371

6372

<entry>construct host mask for network</entry>

6373

<entry><literal>hostmask('192.168.23.20/30')</literal></entry>

6374

6375

</row>

6376

<row>

6377

<entry><literal><function>network</function>(<type>inet</type>)</literal></entry>

6378

6379

<entry>extract network part of address</entry>

6380

<entry><literal>network('192.168.1.5/24')</literal></entry>

6381

6382

</row>

6383

<row>

6384

6385

6386

<entry>extract IP address and netmask length as text</entry>

6387

6388

6389

</row>

6390

<row>

6391

<entry><literal><function>abbrev</function>(<type>inet</type>)</literal></entry>

6392

6393

<entry>abbreviated display format as text</entry>

6394

<entry><literal>abbrev(cidr '10.1.0.0/16')</literal></entry>

6395

6396

</row>

6397

<row>

6398

<entry><literal><function>family</function>(<type>inet</type>)</literal></entry>

6399

<entry><type>integer</type></entry>

6400

<entry>extract family of address; <literal>4</literal> for IPv4,

6401

6402

<entry><literal>family('::1')</literal></entry>

6403

6404

</row>

6405

</tbody>

6406

</tgroup>

6407

</table>

6408

6409

<para>

6410

<xref linkend="macaddr-functions-table"> shows the functions

6411

available for use with the <type>macaddr</type> type. The function

6412

<literal><function>trunc</function>(<type>macaddr</type>)</literal> returns a MAC

6413

address with the last 3 bytes set to zero. This can be used to

6414

associate the remaining prefix with a manufacturer. The directory

6415

<filename>contrib/mac</filename> in the source distribution

6416

contains some utilities to create and maintain such an association

6417

table.

6418

</para>

6419

6420

6421

<title><type>macaddr</type> Functions</title>

6422

6423

<thead>

6424

<row>

6425

<entry>Function</entry>

6426

<entry>Return Type</entry>

6427

<entry>Description</entry>

6428

<entry>Example</entry>

6429

<entry>Result</entry>

6430

</row>

6431

</thead>

6432

<tbody>

6433

<row>

6434

<entry><literal><function>trunc</function>(<type>macaddr</type>)</literal></entry>

6435

<entry><type>macaddr</type></entry>

6436

<entry>set last 3 bytes to zero</entry>

6437

<entry><literal>trunc(macaddr '12:34:56:78:90:ab')</literal></entry>

6438

6439

</row>

6440

</tbody>

6441

</tgroup>

6442

</table>

6443

6444

<para>

6445

The <type>macaddr</type> type also supports the standard relational

6446

operators (<literal>></literal>, <literal><=</literal>, etc.) for

6447

lexicographical ordering.

6448

</para>

6449

6450

</sect1>

6451

6452

6453

6454

<title>Sequence Manipulation Functions</title>

6455

6456

6457

<primary>sequence</primary>

6458

</indexterm>

6459

6460

<primary>nextval</primary>

6461

</indexterm>

6462

6463

<primary>currval</primary>

6464

</indexterm>

6465

6466

<primary>setval</primary>

6467

</indexterm>

6468

6469

<para>

6470

This section describes <productname>PostgreSQL</productname>'s functions

6471

for operating on <firstterm>sequence objects</firstterm>.

6472

Sequence objects (also called sequence generators or

6473

just sequences) are special single-row tables created with

6474

<command>CREATE SEQUENCE</command>. A sequence object is usually used to

6475

generate unique identifiers for rows of a table. The sequence functions,

6476

listed in <xref linkend="functions-sequence-table">,

6477

provide simple, multiuser-safe methods for obtaining successive

6478

sequence values from sequence objects.

6479

</para>

6480

6481

6482

<title>Sequence Functions</title>

6483

6484

<thead>

6485

<row><entry>Function</entry> <entry>Return Type</entry> <entry>Description</entry></row>

6486

</thead>

6487

6488

<tbody>

6489

<row>

6490

<entry><literal><function>nextval</function>(<type>text</type>)</literal></entry>

6491

<entry><type>bigint</type></entry>

6492

<entry>Advance sequence and return new value</entry>

6493

</row>

6494

<row>

6495

<entry><literal><function>currval</function>(<type>text</type>)</literal></entry>

6496

<entry><type>bigint</type></entry>

6497

<entry>Return value most recently obtained with <function>nextval</function></entry>

6498

</row>

6499

<row>

6500

<entry><literal><function>setval</function>(<type>text</type>, <type>bigint</type>)</literal></entry>

6501

<entry><type>bigint</type></entry>

6502

<entry>Set sequence's current value</entry>

6503

</row>

6504

<row>

6505

<entry><literal><function>setval</function>(<type>text</type>, <type>bigint</type>, <type>boolean</type>)</literal></entry>

6506

<entry><type>bigint</type></entry>

6507

<entry>Set sequence's current value and <literal>is_called</literal> flag</entry>

6508

</row>

6509

</tbody>

6510

</tgroup>

6511

</table>

6512

6513

<para>

6514

For largely historical reasons, the sequence to be operated on by a

6515

sequence-function call is specified by a text-string argument. To

6516

achieve some compatibility with the handling of ordinary

6517

<acronym>SQL</acronym> names, the sequence functions convert their

6518

argument to lowercase unless the string is double-quoted. Thus

6519

6520

nextval('foo') <lineannotation>operates on sequence <literal>foo</literal></>

6521

nextval('FOO') <lineannotation>operates on sequence <literal>foo</literal></>

6522

nextval('"Foo"') <lineannotation>operates on sequence <literal>Foo</literal></>

6523

</programlisting>

6524

The sequence name can be schema-qualified if necessary:

6525

6526

nextval('myschema.foo') <lineannotation>operates on <literal>myschema.foo</literal></>

6527

nextval('"myschema".foo') <lineannotation>same as above</lineannotation>

6528

nextval('foo') <lineannotation>searches search path for <literal>foo</literal></>

6529

</programlisting>

6530

Of course, the text argument can be the result of an expression,

6531

not only a simple literal, which is occasionally useful.

6532

</para>

6533

6534

<para>

6535

The available sequence functions are:

6536

6537

6538

6539

<term><function>nextval</function></term>

6540

6541

<para>

6542

Advance the sequence object to its next value and return that

6543

value. This is done atomically: even if multiple sessions

6544

execute <function>nextval</function> concurrently, each will safely receive

6545

a distinct sequence value.

6546

</para>

6547

</listitem>

6548

</varlistentry>

6549

6550

6551

<term><function>currval</function></term>

6552

6553

<para>

6554

Return the value most recently obtained by <function>nextval</function>

6555

for this sequence in the current session. (An error is

6556

reported if <function>nextval</function> has never been called for this

6557

sequence in this session.) Notice that because this is returning

6558

a session-local value, it gives a predictable answer whether or not

6559

other sessions have executed <function>nextval</function> since the

6560

current session did.

6561

</para>

6562

</listitem>

6563

</varlistentry>

6564

6565

6566

<term><function>setval</function></term>

6567

6568

<para>

6569

Reset the sequence object's counter value. The two-parameter

6570

form sets the sequence's <literal>last_value</literal> field to the specified

6571

value and sets its <literal>is_called</literal> field to <literal>true</literal>,

6572

meaning that the next <function>nextval</function> will advance the sequence

6573

before returning a value. In the three-parameter form,

6574

<literal>is_called</literal> may be set either <literal>true</literal> or

6575

<literal>false</literal>. If it's set to <literal>false</literal>,

6576

the next <function>nextval</function> will return exactly the specified

6577

value, and sequence advancement commences with the following

6578

<function>nextval</function>. For example,

6579

6580

6581

SELECT setval('foo', 42); <lineannotation>Next <function>nextval</> will return 43</lineannotation>

6582

SELECT setval('foo', 42, true); <lineannotation>Same as above</lineannotation>

6583

SELECT setval('foo', 42, false); <lineannotation>Next <function>nextval</> will return 42</lineannotation>

6584

</screen>

6585

6586

The result returned by <function>setval</function> is just the value of its

6587

second argument.

6588

</para>

6589

</listitem>

6590

</varlistentry>

6591

</variablelist>

6592

</para>

6593

6594

6595

<para>

6596

To avoid blocking of concurrent transactions that obtain numbers from the

6597

same sequence, a <function>nextval</function> operation is never rolled back;

6598

that is, once a value has been fetched it is considered used, even if the

6599

transaction that did the <function>nextval</function> later aborts. This means

6600

that aborted transactions may leave unused <quote>holes</quote> in the

6601

sequence of assigned values. <function>setval</function> operations are never

6602

rolled back, either.

6603

</para>

6604

</important>

6605

6606

<para>

6607

If a sequence object has been created with default parameters,

6608

<function>nextval</function> calls on it will return successive values

6609

beginning with 1. Other behaviors can be obtained by using

6610

special parameters in the <xref linkend="SQL-CREATESEQUENCE"> command;

6611

see its command reference page for more information.

6612

</para>

6613

6614

</sect1>

6615

6616

6617

6618

<title>Conditional Expressions</title>

6619

6620

6621

6622

</indexterm>

6623

6624

6625

<primary>conditional expression</primary>

6626

</indexterm>

6627

6628

<para>

6629

This section describes the <acronym>SQL</acronym>-compliant conditional expressions

6630

available in <productname>PostgreSQL</productname>.

6631

</para>

6632

6633

<tip>

6634

<para>

6635

If your needs go beyond the capabilities of these conditional

6636

expressions you might want to consider writing a stored procedure

6637

in a more expressive programming language.

6638

</para>

6639

</tip>

6640

6641

<sect2>

6642

6643

6644

<para>

6645

The <acronym>SQL</acronym> <token>CASE</token> expression is a

6646

generic conditional expression, similar to if/else statements in

6647

other languages:

6648

6649

6650

CASE WHEN <replaceable>condition</replaceable> THEN <replaceable>result</replaceable>

6651

6652

<optional>ELSE <replaceable>result</replaceable></optional>

6653

END

6654

</synopsis>

6655

6656

<token>CASE</token> clauses can be used wherever

6657

an expression is valid. <replaceable>condition</replaceable> is an

6658

expression that returns a <type>boolean</type> result. If the result is true

6659

then the value of the <token>CASE</token> expression is the

6660

<replaceable>result</replaceable> that follows the condition. If the result is false any

6661

subsequent <token>WHEN</token> clauses are searched in the same

6662

manner. If no <token>WHEN</token>

6663

<replaceable>condition</replaceable> is true then the value of the

6664

case expression is the <replaceable>result</replaceable> in the

6665

<token>ELSE</token> clause. If the <token>ELSE</token> clause is

6666

omitted and no condition matches, the result is null.

6667

</para>

6668

6669

<para>

6670

An example:

6671

6672

SELECT * FROM test;

6673

6674

6675

---

6676

6677

6678

6679

6680

6681

SELECT a,

6682

CASE WHEN a=1 THEN 'one'

6683

WHEN a=2 THEN 'two'

6684

ELSE 'other'

6685

END

6686

FROM test;

6687

6688

a | case

6689

---+-------

6690

1 | one

6691

2 | two

6692

3 | other

6693

</screen>

6694

</para>

6695

6696

<para>

6697

The data types of all the <replaceable>result</replaceable>

6698

expressions must be convertible to a single output type.

6699

See <xref linkend="typeconv-union-case"> for more detail.

6700

</para>

6701

6702

<para>

6703

The following <quote>simple</quote> <token>CASE</token> expression is a

6704

specialized variant of the general form above:

6705

6706

6707

CASE <replaceable>expression</replaceable>

6708

WHEN <replaceable>value</replaceable> THEN <replaceable>result</replaceable>

6709

6710

<optional>ELSE <replaceable>result</replaceable></optional>

6711

END

6712

</synopsis>

6713

6714

The

6715

<replaceable>expression</replaceable> is computed and compared to

6716

all the <replaceable>value</replaceable> specifications in the

6717

<token>WHEN</token> clauses until one is found that is equal. If

6718

no match is found, the <replaceable>result</replaceable> in the

6719

<token>ELSE</token> clause (or a null value) is returned. This is similar

6720

to the <function>switch</function> statement in C.

6721

</para>

6722

6723

<para>

6724

The example above can be written using the simple

6725

<token>CASE</token> syntax:

6726

6727

SELECT a,

6728

CASE a WHEN 1 THEN 'one'

6729

WHEN 2 THEN 'two'

6730

ELSE 'other'

6731

END

6732

FROM test;

6733

6734

a | case

6735

---+-------

6736

1 | one

6737

2 | two

6738

3 | other

6739

</screen>

6740

</para>

6741

6742

<para>

6743

A <token>CASE</token> expression does not evaluate any subexpressions

6744

that are not needed to determine the result. For example, this is a

6745

possible way of avoiding a division-by-zero failure:

6746

6747

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

6748

</programlisting>

6749

</para>

6750

</sect2>

6751

6752

<sect2>

6753

<title><literal>COALESCE</></title>

6754

6755

6756

<primary>COALESCE</primary>

6757

</indexterm>

6758

6759

6760

<function>COALESCE</function>(<replaceable>value</replaceable> <optional>, ...</optional>)

6761

</synopsis>

6762

6763

<para>

6764

The <function>COALESCE</function> function returns the first of its

6765

arguments that is not null. Null is returned only if all arguments

6766

are null. This is often useful to substitute a

6767

default value for null values when data is retrieved for display,

6768

for example:

6769

6770

SELECT COALESCE(description, short_description, '(none)') ...

6771

</programlisting>

6772

</para>

6773

6774

<para>

6775

Like a <token>CASE</token> expression, <function>COALESCE</function> will

6776

not evaluate arguments that are not needed to determine the result;

6777

that is, arguments to the right of the first non-null argument are

6778

not evaluated.

6779

</para>

6780

</sect2>

6781

6782

<sect2>

6783

<title><literal>NULLIF</></title>

6784

6785

6786

<primary>NULLIF</primary>

6787

</indexterm>

6788

6789

6790

<function>NULLIF</function>(<replaceable>value1</replaceable>, <replaceable>value2</replaceable>)

6791

</synopsis>

6792

6793

<para>

6794

The <function>NULLIF</function> function returns a null value if and only

6795

if <replaceable>value1</replaceable> and

6796

<replaceable>value2</replaceable> are equal. Otherwise it returns

6797

<replaceable>value1</replaceable>. This can be used to perform the

6798

inverse operation of the <function>COALESCE</function> example

6799

given above:

6800

6801

SELECT NULLIF(value, '(none)') ...

6802

</programlisting>

6803

</para>

6804

6805

</sect2>

6806

6807

</sect1>

6808

6809

6810

6811

<title>Array Functions and Operators</title>

6812

6813

<para>

6814

<xref linkend="array-operators-table"> shows the operators

6815

available for <type>array</type> types.

6816

</para>

6817

6818

6819

<title><type>array</type> Operators</title>

6820

6821

<thead>

6822

<row>

6823

<entry>Operator</entry>

6824

<entry>Description</entry>

6825

<entry>Example</entry>

6826

<entry>Result</entry>

6827

</row>

6828

</thead>

6829

<tbody>

6830

<row>

6831

6832

<entry>equal</entry>

6833

<entry><literal>ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]</literal></entry>

6834

6835

</row>

6836

6837

<row>

6838

6839

<entry>not equal</entry>

6840

<entry><literal>ARRAY[1,2,3] <> ARRAY[1,2,4]</literal></entry>

6841

6842

</row>

6843

6844

<row>

6845

6846

6847

<entry><literal>ARRAY[1,2,3] < ARRAY[1,2,4]</literal></entry>

6848

6849

</row>

6850

6851

<row>

6852

6853

<entry>greater than</entry>

6854

<entry><literal>ARRAY[1,4,3] > ARRAY[1,2,4]</literal></entry>

6855

6856

</row>

6857

6858

<row>

6859

6860

<entry>less than or equal</entry>

6861

<entry><literal>ARRAY[1,2,3] <= ARRAY[1,2,3]</literal></entry>

6862

6863

</row>

6864

6865

<row>

6866

6867

<entry>greater than or equal</entry>

6868

<entry><literal>ARRAY[1,4,3] >= ARRAY[1,4,3]</literal></entry>

6869

6870

</row>

6871

6872

<row>

6873

6874

<entry>array-to-array concatenation</entry>

6875

<entry><literal>ARRAY[1,2,3] || ARRAY[4,5,6]</literal></entry>

6876

6877

</row>

6878

6879

<row>

6880

6881

<entry>array-to-array concatenation</entry>

6882

<entry><literal>ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]</literal></entry>

6883

6884

</row>

6885

6886

<row>

6887

6888

<entry>element-to-array concatenation</entry>

6889

<entry><literal>3 || ARRAY[4,5,6]</literal></entry>

6890

6891

</row>

6892

6893

<row>

6894

6895

<entry>array-to-element concatenation</entry>

6896

<entry><literal>ARRAY[4,5,6] || 7</literal></entry>

6897

6898

</row>

6899

</tbody>

6900

</tgroup>

6901

</table>

6902

6903

<para>

6904

See <xref linkend="arrays"> for more details about array operator

6905

behavior.

6906

</para>

6907

6908

<para>

6909

<xref linkend="array-functions-table"> shows the functions

6910

available for use with array types. See <xref linkend="arrays">

6911

for more discussion and examples of the use of these functions.

6912

</para>

6913

6914

6915

<title><type>array</type> Functions</title>

6916

6917

<thead>

6918

<row>

6919

<entry>Function</entry>

6920

<entry>Return Type</entry>

6921

<entry>Description</entry>

6922

<entry>Example</entry>

6923

<entry>Result</entry>

6924

</row>

6925

</thead>

6926

<tbody>

6927

<row>

6928

<entry>

6929

6930

<function>array_cat</function>

6931

(<type>anyarray</type>, <type>anyarray</type>)

6932

</literal>

6933

</entry>

6934

<entry><type>anyarray</type></entry>

6935

<entry>concatenate two arrays</entry>

6936

<entry><literal>array_cat(ARRAY[1,2,3], ARRAY[4,5])</literal></entry>

6937

6938

</row>

6939

<row>

6940

<entry>

6941

6942

<function>array_append</function>

6943

(<type>anyarray</type>, <type>anyelement</type>)

6944

</literal>

6945

</entry>

6946

<entry><type>anyarray</type></entry>

6947

<entry>append an element to the end of an array</entry>

6948

<entry><literal>array_append(ARRAY[1,2], 3)</literal></entry>

6949

6950

</row>

6951

<row>

6952

<entry>

6953

6954

<function>array_prepend</function>

6955

(<type>anyelement</type>, <type>anyarray</type>)

6956

</literal>

6957

</entry>

6958

<entry><type>anyarray</type></entry>

6959

<entry>append an element to the beginning of an array</entry>

6960

<entry><literal>array_prepend(1, ARRAY[2,3])</literal></entry>

6961

6962

</row>

6963

<row>

6964

<entry>

6965

6966

<function>array_dims</function>

6967

(<type>anyarray</type>)

6968

</literal>

6969

</entry>

6970

6971

<entry>returns a text representation of array's dimensions</entry>

6972

<entry><literal>array_dims(array[[1,2,3], [4,5,6]])</literal></entry>

6973

6974

</row>

6975

<row>

6976

<entry>

6977

6978

<function>array_lower</function>

6979

(<type>anyarray</type>, <type>integer</type>)

6980

</literal>

6981

</entry>

6982

<entry><type>integer</type></entry>

6983

<entry>returns lower bound of the requested array dimension</entry>

6984

<entry><literal>array_lower(array_prepend(0, ARRAY[1,2,3]), 1)</literal></entry>

6985

6986

</row>

6987

<row>

6988

<entry>

6989

6990

<function>array_upper</function>

6991

(<type>anyarray</type>, <type>integer</type>)

6992

</literal>

6993

</entry>

6994

<entry><type>integer</type></entry>

6995

<entry>returns upper bound of the requested array dimension</entry>

6996

<entry><literal>array_upper(ARRAY[1,2,3,4], 1)</literal></entry>

6997

6998

</row>

6999

<row>

7000

<entry>

7001

7002

<function>array_to_string</function>

7003

(<type>anyarray</type>, <type>text</type>)

7004

</literal>

7005

</entry>

7006

7007

<entry>concatenates array elements using provided delimiter</entry>

7008

<entry><literal>array_to_string(array[1, 2, 3], '~^~')</literal></entry>

7009

7010

</row>

7011

<row>

7012

<entry>

7013

7014

<function>string_to_array</function>

7015

(<type>text</type>, <type>text</type>)

7016

</literal>

7017

</entry>

7018

7019

<entry>splits string into array elements using provided delimiter</entry>

7020

<entry><literal>string_to_array( 'xx~^~yy~^~zz', '~^~')</literal></entry>

7021

7022

</row>

7023

</tbody>

7024

</tgroup>

7025

</table>

7026

</sect1>

7027

7028

7029

<title>Aggregate Functions</title>

7030

7031

7032

<primary>aggregate function</primary>

7033

<secondary>built-in</secondary>

7034

</indexterm>

7035

7036

<para>

7037

<firstterm>Aggregate functions</firstterm> compute a single result

7038

value from a set of input values. <xref

7039

linkend="functions-aggregate-table"> shows the built-in aggregate

7040

functions. The special syntax considerations for aggregate

7041

functions are explained in <xref linkend="syntax-aggregates">.

7042

Consult <xref linkend="tutorial-agg"> for additional introductory

7043

information.

7044

</para>

7045

7046

7047

<title>Aggregate Functions</title>

7048

7049

7050

<thead>

7051

<row>

7052

<entry>Function</entry>

7053

<entry>Argument Type</entry>

7054

<entry>Return Type</entry>

7055

<entry>Description</entry>

7056

</row>

7057

</thead>

7058

7059

<tbody>

7060

<row>

7061

<entry>

7062

7063

<primary>average</primary>

7064

</indexterm>

7065

<function>avg(<replaceable class="parameter">expression</replaceable>)</function>

7066

</entry>

7067

<entry>

7068

<type>smallint</type>, <type>integer</type>,

7069

<type>bigint</type>, <type>real</type>, <type>double

7070

precision</type>, <type>numeric</type>, or <type>interval</type>

7071

</entry>

7072

<entry>

7073

<type>numeric</type> for any integer type argument,

7074

<type>double precision</type> for a floating-point argument,

7075

otherwise the same as the argument data type

7076

</entry>

7077

<entry>the average (arithmetic mean) of all input values</entry>

7078

</row>

7079

7080

<row>

7081

<entry>

7082

7083

7084

</indexterm>

7085

<function>bit_and(<replaceable class="parameter">expression</replaceable>)</function>

7086

</entry>

7087

<entry>

7088

<type>smallint</type>, <type>integer</type>, <type>bigint</type>, or

7089

7090

</entry>

7091

<entry>

7092

same as argument data type

7093

</entry>

7094

<entry>the bitwise AND of all non-null input values, or null if none</entry>

7095

</row>

7096

7097

<row>

7098

<entry>

7099

7100

7101

</indexterm>

7102

<function>bit_or(<replaceable class="parameter">expression</replaceable>)</function>

7103

</entry>

7104

<entry>

7105

<type>smallint</type>, <type>integer</type>, <type>bigint</type>, or

7106

7107

</entry>

7108

<entry>

7109

same as argument data type

7110

</entry>

7111

<entry>the bitwise OR of all non-null input values, or null if none</entry>

7112

</row>

7113

7114

<row>

7115

<entry>

7116

7117

7118

</indexterm>

7119

<function>bool_and(<replaceable class="parameter">expression</replaceable>)</function>

7120

</entry>

7121

<entry>

7122

7123

</entry>

7124

<entry>

7125

7126

</entry>

7127

<entry>true if all input values are true, otherwise false</entry>

7128

</row>

7129

7130

<row>

7131

<entry>

7132

7133

7134

</indexterm>

7135

<function>bool_or(<replaceable class="parameter">expression</replaceable>)</function>

7136

</entry>

7137

<entry>

7138

7139

</entry>

7140

<entry>

7141

7142

</entry>

7143

<entry>true if at least one input value is true, otherwise false</entry>

7144

</row>

7145

7146

<row>

7147

<entry><function>count(*)</function></entry>

7148

7149

<entry><type>bigint</type></entry>

7150

<entry>number of input values</entry>

7151

</row>

7152

7153

<row>

7154

<entry><function>count(<replaceable class="parameter">expression</replaceable>)</function></entry>

7155

7156

<entry><type>bigint</type></entry>

7157

<entry>

7158

number of input values for which the value of <replaceable

7159

class="parameter">expression</replaceable> is not null

7160

</entry>

7161

</row>

7162

7163

<row>

7164

<entry>

7165

7166

<primary>every</primary>

7167

</indexterm>

7168

<function>every(<replaceable class="parameter">expression</replaceable>)</function>

7169

</entry>

7170

<entry>

7171

7172

</entry>

7173

<entry>

7174

7175

</entry>

7176

<entry>equivalent to <function>bool_and</function></entry>

7177

</row>

7178

7179

<row>

7180

<entry><function>max(<replaceable class="parameter">expression</replaceable>)</function></entry>

7181

<entry>any numeric, string, or date/time type</entry>

7182

<entry>same as argument type</entry>

7183

<entry>

7184

maximum value of <replaceable

7185

class="parameter">expression</replaceable> across all input

7186

values

7187

</entry>

7188

</row>

7189

7190

<row>

7191

<entry><function>min(<replaceable class="parameter">expression</replaceable>)</function></entry>

7192

<entry>any numeric, string, or date/time type</entry>

7193

<entry>same as argument type</entry>

7194

<entry>

7195

minimum value of <replaceable

7196

class="parameter">expression</replaceable> across all input

7197

values

7198

</entry>

7199

</row>

7200

7201

<row>

7202

<entry>

7203

7204

<primary>standard deviation</primary>

7205

</indexterm>

7206

<function>stddev(<replaceable class="parameter">expression</replaceable>)</function>

7207

</entry>

7208

<entry>

7209

<type>smallint</type>, <type>integer</type>,

7210

<type>bigint</type>, <type>real</type>, <type>double

7211

precision</type>, or <type>numeric</type>

7212

</entry>

7213

<entry>

7214

<type>double precision</type> for floating-point arguments,

7215

otherwise <type>numeric</type>

7216

</entry>

7217

<entry>sample standard deviation of the input values</entry>

7218

</row>

7219

7220

<row>

7221

<entry><function>sum(<replaceable class="parameter">expression</replaceable>)</function></entry>

7222

<entry>

7223

<type>smallint</type>, <type>integer</type>,

7224

<type>bigint</type>, <type>real</type>, <type>double

7225

precision</type>, <type>numeric</type>, or

7226

<type>interval</type>

7227

</entry>

7228

<entry>

7229

<type>bigint</type> for <type>smallint</type> or

7230

<type>integer</type> arguments, <type>numeric</type> for

7231

<type>bigint</type> arguments, <type>double precision</type>

7232

for floating-point arguments, otherwise the same as the

7233

argument data type

7234

</entry>

7235

<entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>

7236

</row>

7237

7238

<row>

7239

<entry>

7240

7241

<primary>variance</primary>

7242

</indexterm>

7243

<function>variance</function>(<replaceable class="parameter">expression</replaceable>)

7244

</entry>

7245

<entry>

7246

<type>smallint</type>, <type>integer</type>,

7247

<type>bigint</type>, <type>real</type>, <type>double

7248

precision</type>, or <type>numeric</type>

7249

</entry>

7250

<entry>

7251

<type>double precision</type> for floating-point arguments,

7252

otherwise <type>numeric</type>

7253

</entry>

7254

<entry>sample variance of the input values (square of the sample standard deviation)</entry>

7255

</row>

7256

7257

</tbody>

7258

</tgroup>

7259

</table>

7260

7261

<para>

7262

It should be noted that except for <function>count</function>,

7263

these functions return a null value when no rows are selected. In

7264

particular, <function>sum</function> of no rows returns null, not

7265

zero as one might expect. The <function>coalesce</function> function may be

7266

used to substitute zero for null when necessary.

7267

</para>

7268

7269

<note>

7270

7271

7272

</indexterm>

7273

7274

7275

</indexterm>

7276

<para>

7277

Boolean aggregates <function>bool_and</function> and

7278

<function>bool_or</function> correspond to standard SQL aggregates

7279

<function>every</function> and <function>any</function> or

7280

<function>some</function>.

7281

As for <function>any</function> and <function>some</function>,

7282

it seems that there is an ambiguity built into the standard syntax:

7283

7284

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

7285

</programlisting>

7286

Here <function>ANY</function> can be considered both as leading

7287

to a subquery or as an aggregate if the select expression returns 1 row.

7288

Thus the standard name cannot be given to these aggregates.

7289

</para>

7290

</note>

7291

7292

<note>

7293

<para>

7294

Users accustomed to working with other SQL database management

7295

systems may be surprised by the performance characteristics of

7296

certain aggregate functions in

7297

<productname>PostgreSQL</productname> when the aggregate is

7298

applied to the entire table (in other words, no

7299

<literal>WHERE</literal> clause is specified). In particular, a

7300

query like

7301

7302

SELECT min(col) FROM sometable;

7303

</programlisting>

7304

will be executed by <productname>PostgreSQL</productname> using a

7305

sequential scan of the entire table. Other database systems may

7306

optimize queries of this form to use an index on the column, if

7307

one is available. Similarly, the aggregate functions

7308

<function>max()</function> and <function>count()</function> always

7309

require a sequential scan if applied to the entire table in

7310

<productname>PostgreSQL</productname>.

7311

</para>

7312

7313

<para>

7314

<productname>PostgreSQL</productname> cannot easily implement this

7315

optimization because it also allows for user-defined aggregate

7316

queries. Since <function>min()</function>,

7317

<function>max()</function>, and <function>count()</function> are

7318

defined using a generic API for aggregate functions, there is no

7319

provision for special-casing the execution of these functions

7320

under certain circumstances.

7321

</para>

7322

7323

<para>

7324

Fortunately, there is a simple workaround for

7325

<function>min()</function> and <function>max()</function>. The

7326

query shown below is equivalent to the query above, except that it

7327

can take advantage of a B-tree index if there is one present on

7328

the column in question.

7329

7330

SELECT col FROM sometable ORDER BY col ASC LIMIT 1;

7331

</programlisting>

7332

A similar query (obtained by substituting <literal>DESC</literal>

7333

for <literal>ASC</literal> in the query above) can be used in the

7334

place of <function>max()</function>.

7335

</para>

7336

7337

<para>

7338

Unfortunately, there is no similarly trivial query that can be

7339

used to improve the performance of <function>count()</function>

7340

when applied to the entire table.

7341

</para>

7342

</note>

7343

7344

</sect1>

7345

7346

7347

7348

<title>Subquery Expressions</title>

7349

7350

7351

<primary>EXISTS</primary>

7352

</indexterm>

7353

7354

7355

7356

</indexterm>

7357

7358

7359

7360

</indexterm>

7361

7362

7363

7364

</indexterm>

7365

7366

7367

7368

</indexterm>

7369

7370

7371

7372

</indexterm>

7373

7374

7375

<primary>subquery</primary>

7376

</indexterm>

7377

7378

<para>

7379

This section describes the <acronym>SQL</acronym>-compliant subquery

7380

expressions available in <productname>PostgreSQL</productname>.

7381

All of the expression forms documented in this section return

7382

Boolean (true/false) results.

7383

</para>

7384

7385

<sect2>

7386

<title><literal>EXISTS</literal></title>

7387

7388

7389

EXISTS ( <replaceable>subquery</replaceable> )

7390

</synopsis>

7391

7392

<para>

7393

The argument of <token>EXISTS</token> is an arbitrary <command>SELECT</> statement,

7394

or <firstterm>subquery</firstterm>. The

7395

subquery is evaluated to determine whether it returns any rows.

7396

If it returns at least one row, the result of <token>EXISTS</token> is

7397

<quote>true</>; if the subquery returns no rows, the result of <token>EXISTS</token>

7398

is <quote>false</>.

7399

</para>

7400

7401

<para>

7402

The subquery can refer to variables from the surrounding query,

7403

which will act as constants during any one evaluation of the subquery.

7404

</para>

7405

7406

<para>

7407

The subquery will generally only be executed far enough to determine

7408

whether at least one row is returned, not all the way to completion.

7409

It is unwise to write a subquery that has any side effects (such as

7410

calling sequence functions); whether the side effects occur or not

7411

may be difficult to predict.

7412

</para>

7413

7414

<para>

7415

Since the result depends only on whether any rows are returned,

7416

and not on the contents of those rows, the output list of the

7417

subquery is normally uninteresting. A common coding convention is

7418

to write all <literal>EXISTS</> tests in the form

7419

<literal>EXISTS(SELECT 1 WHERE ...)</literal>. There are exceptions to

7420

this rule however, such as subqueries that use <token>INTERSECT</token>.

7421

</para>

7422

7423

<para>

7424

This simple example is like an inner join on <literal>col2</>, but

7425

it produces at most one output row for each <literal>tab1</> row,

7426

even if there are multiple matching <literal>tab2</> rows:

7427

7428

SELECT col1 FROM tab1

7429

WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);

7430

</screen>

7431

</para>

7432

</sect2>

7433

7434

<sect2>

7435

7436

7437

7438

<replaceable>expression</replaceable> IN (<replaceable>subquery</replaceable>)

7439

</synopsis>

7440

7441

<para>

7442

The right-hand side is a parenthesized

7443

subquery, which must return exactly one column. The left-hand expression

7444

is evaluated and compared to each row of the subquery result.

7445

The result of <token>IN</token> is <quote>true</> if any equal subquery row is found.

7446

The result is <quote>false</> if no equal row is found (including the special

7447

case where the subquery returns no rows).

7448

</para>

7449

7450

<para>

7451

Note that if the left-hand expression yields null, or if there are

7452

no equal right-hand values and at least one right-hand row yields

7453

null, the result of the <token>IN</token> construct will be null, not false.

7454

This is in accordance with SQL's normal rules for Boolean combinations

7455

of null values.

7456

</para>

7457

7458

<para>

7459

As with <token>EXISTS</token>, it's unwise to assume that the subquery will

7460

be evaluated completely.

7461

</para>

7462

7463

7464

<replaceable>row_constructor</replaceable> IN (<replaceable>subquery</replaceable>)

7465

</synopsis>

7466

7467

<para>

7468

The left-hand side of this form of <token>IN</token> is a row constructor,

7469

as described in <xref linkend="sql-syntax-row-constructors">.

7470

The right-hand side is a parenthesized

7471

subquery, which must return exactly as many columns as there are

7472

expressions in the left-hand row. The left-hand expressions are

7473

evaluated and compared row-wise to each row of the subquery result.

7474

The result of <token>IN</token> is <quote>true</> if any equal subquery row is found.

7475

The result is <quote>false</> if no equal row is found (including the special

7476

case where the subquery returns no rows).

7477

</para>

7478

7479

<para>

7480

As usual, null values in the rows are combined per

7481

the normal rules of SQL Boolean expressions. Two rows are considered

7482

equal if all their corresponding members are non-null and equal; the rows

7483

are unequal if any corresponding members are non-null and unequal;

7484

otherwise the result of that row comparison is unknown (null).

7485

If all the row results are either unequal or null, with at least one null,

7486

then the result of <token>IN</token> is null.

7487

</para>

7488

</sect2>

7489

7490

<sect2>

7491

7492

7493

7494

<replaceable>expression</replaceable> NOT IN (<replaceable>subquery</replaceable>)

7495

</synopsis>

7496

7497

<para>

7498

The right-hand side is a parenthesized

7499

subquery, which must return exactly one column. The left-hand expression

7500

is evaluated and compared to each row of the subquery result.

7501

The result of <token>NOT IN</token> is <quote>true</> if only unequal subquery rows

7502

are found (including the special case where the subquery returns no rows).

7503

The result is <quote>false</> if any equal row is found.

7504

</para>

7505

7506

<para>

7507

Note that if the left-hand expression yields null, or if there are

7508

no equal right-hand values and at least one right-hand row yields

7509

null, the result of the <token>NOT IN</token> construct will be null, not true.

7510

This is in accordance with SQL's normal rules for Boolean combinations

7511

of null values.

7512

</para>

7513

7514

<para>

7515

As with <token>EXISTS</token>, it's unwise to assume that the subquery will

7516

be evaluated completely.

7517

</para>

7518

7519

7520

<replaceable>row_constructor</replaceable> NOT IN (<replaceable>subquery</replaceable>)

7521

</synopsis>

7522

7523

<para>

7524

The left-hand side of this form of <token>NOT IN</token> is a row constructor,

7525

as described in <xref linkend="sql-syntax-row-constructors">.

7526

The right-hand side is a parenthesized

7527

subquery, which must return exactly as many columns as there are

7528

expressions in the left-hand row. The left-hand expressions are

7529

evaluated and compared row-wise to each row of the subquery result.

7530

The result of <token>NOT IN</token> is <quote>true</> if only unequal subquery rows

7531

are found (including the special case where the subquery returns no rows).

7532

The result is <quote>false</> if any equal row is found.

7533

</para>

7534

7535

<para>

7536

As usual, null values in the rows are combined per

7537

the normal rules of SQL Boolean expressions. Two rows are considered

7538

equal if all their corresponding members are non-null and equal; the rows

7539

are unequal if any corresponding members are non-null and unequal;

7540

otherwise the result of that row comparison is unknown (null).

7541

If all the row results are either unequal or null, with at least one null,

7542

then the result of <token>NOT IN</token> is null.

7543

</para>

7544

</sect2>

7545

7546

<sect2>

7547

7548

7549

7550

<replaceable>expression</replaceable> <replaceable>operator</replaceable> ANY (<replaceable>subquery</replaceable>)

7551

<replaceable>expression</replaceable> <replaceable>operator</replaceable> SOME (<replaceable>subquery</replaceable>)

7552

</synopsis>

7553

7554

<para>

7555

The right-hand side is a parenthesized

7556

subquery, which must return exactly one column. The left-hand expression

7557

is evaluated and compared to each row of the subquery result using the

7558

given <replaceable>operator</replaceable>, which must yield a Boolean

7559

result.

7560

The result of <token>ANY</token> is <quote>true</> if any true result is obtained.

7561

The result is <quote>false</> if no true result is found (including the special

7562

case where the subquery returns no rows).

7563

</para>

7564

7565

<para>

7566

<token>SOME</token> is a synonym for <token>ANY</token>.

7567

<token>IN</token> is equivalent to <literal>= ANY</literal>.

7568

</para>

7569

7570

<para>

7571

Note that if there are no successes and at least one right-hand row yields

7572

null for the operator's result, the result of the <token>ANY</token> construct

7573

will be null, not false.

7574

This is in accordance with SQL's normal rules for Boolean combinations

7575

of null values.

7576

</para>

7577

7578

<para>

7579

As with <token>EXISTS</token>, it's unwise to assume that the subquery will

7580

be evaluated completely.

7581

</para>

7582

7583

7584

<replaceable>row_constructor</replaceable> <replaceable>operator</> ANY (<replaceable>subquery</replaceable>)

7585

<replaceable>row_constructor</replaceable> <replaceable>operator</> SOME (<replaceable>subquery</replaceable>)

7586

</synopsis>

7587

7588

<para>

7589

The left-hand side of this form of <token>ANY</token> is a row constructor,

7590

as described in <xref linkend="sql-syntax-row-constructors">.

7591

The right-hand side is a parenthesized

7592

subquery, which must return exactly as many columns as there are

7593

expressions in the left-hand row. The left-hand expressions are

7594

evaluated and compared row-wise to each row of the subquery result,

7595

using the given <replaceable>operator</replaceable>. Presently,

7596

only <literal>=</literal> and <literal><></literal> operators are allowed

7597

in row-wise <token>ANY</token> constructs.

7598

The result of <token>ANY</token> is <quote>true</> if any equal or unequal row is

7599

found, respectively.

7600

The result is <quote>false</> if no such row is found (including the special

7601

case where the subquery returns no rows).

7602

</para>

7603

7604

<para>

7605

As usual, null values in the rows are combined per

7606

the normal rules of SQL Boolean expressions. Two rows are considered

7607

equal if all their corresponding members are non-null and equal; the rows

7608

are unequal if any corresponding members are non-null and unequal;

7609

otherwise the result of that row comparison is unknown (null).

7610

If there is at least one null row result, then the result of <token>ANY</token>

7611

cannot be false; it will be true or null.

7612

</para>

7613

</sect2>

7614

7615

<sect2>

7616

7617

7618

7619

<replaceable>expression</replaceable> <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)

7620

</synopsis>

7621

7622

<para>

7623

The right-hand side is a parenthesized

7624

subquery, which must return exactly one column. The left-hand expression

7625

is evaluated and compared to each row of the subquery result using the

7626

given <replaceable>operator</replaceable>, which must yield a Boolean

7627

result.

7628

The result of <token>ALL</token> is <quote>true</> if all rows yield true

7629

(including the special case where the subquery returns no rows).

7630

The result is <quote>false</> if any false result is found.

7631

</para>

7632

7633

<para>

7634

<token>NOT IN</token> is equivalent to <literal><> ALL</literal>.

7635

</para>

7636

7637

<para>

7638

Note that if there are no failures but at least one right-hand row yields

7639

null for the operator's result, the result of the <token>ALL</token> construct

7640

will be null, not true.

7641

This is in accordance with SQL's normal rules for Boolean combinations

7642

of null values.

7643

</para>

7644

7645

<para>

7646

As with <token>EXISTS</token>, it's unwise to assume that the subquery will

7647

be evaluated completely.

7648

</para>

7649

7650

7651

<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)

7652

</synopsis>

7653

7654

<para>

7655

The left-hand side of this form of <token>ALL</token> is a row constructor,

7656

as described in <xref linkend="sql-syntax-row-constructors">.

7657

The right-hand side is a parenthesized

7658

subquery, which must return exactly as many columns as there are

7659

expressions in the left-hand row. The left-hand expressions are

7660

evaluated and compared row-wise to each row of the subquery result,

7661

using the given <replaceable>operator</replaceable>. Presently,

7662

only <literal>=</literal> and <literal><></literal> operators are allowed

7663

in row-wise <token>ALL</token> queries.

7664

The result of <token>ALL</token> is <quote>true</> if all subquery rows are equal

7665

or unequal, respectively (including the special

7666

case where the subquery returns no rows).

7667

The result is <quote>false</> if any row is found to be unequal or equal,

7668

respectively.

7669

</para>

7670

7671

<para>

7672

As usual, null values in the rows are combined per

7673

the normal rules of SQL Boolean expressions. Two rows are considered

7674

equal if all their corresponding members are non-null and equal; the rows

7675

are unequal if any corresponding members are non-null and unequal;

7676

otherwise the result of that row comparison is unknown (null).

7677

If there is at least one null row result, then the result of <token>ALL</token>

7678

cannot be true; it will be false or null.

7679

</para>

7680

</sect2>

7681

7682

<sect2>

7683

<title>Row-wise Comparison</title>

7684

7685

7686

<primary>comparison</primary>

7687

<secondary>subquery result row</secondary>

7688

</indexterm>

7689

7690

7691

<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>)

7692

</synopsis>

7693

7694

<para>

7695

The left-hand side is a row constructor,

7696

as described in <xref linkend="sql-syntax-row-constructors">.

7697

The right-hand side is a parenthesized subquery, which must return exactly

7698

as many columns as there are expressions in the left-hand row. Furthermore,

7699

the subquery cannot return more than one row. (If it returns zero rows,

7700

the result is taken to be null.) The left-hand side is evaluated and

7701

compared row-wise to the single subquery result row.

7702

Presently, only <literal>=</literal> and <literal><></literal> operators are allowed

7703

in row-wise comparisons.

7704

The result is <quote>true</> if the two rows are equal or unequal, respectively.

7705

</para>

7706

7707

<para>

7708

As usual, null values in the rows are combined per

7709

the normal rules of SQL Boolean expressions. Two rows are considered

7710

equal if all their corresponding members are non-null and equal; the rows

7711

are unequal if any corresponding members are non-null and unequal;

7712

otherwise the result of the row comparison is unknown (null).

7713

</para>

7714

</sect2>

7715

</sect1>

7716

7717

7718

7719

<title>Row and Array Comparisons</title>

7720

7721

7722

7723

</indexterm>

7724

7725

7726

7727

</indexterm>

7728

7729

7730

7731

</indexterm>

7732

7733

7734

7735

</indexterm>

7736

7737

7738

7739

</indexterm>

7740

7741

7742

<primary>comparison</primary>

7743

7744

</indexterm>

7745

7746

7747

<primary>IS DISTINCT FROM</primary>

7748

</indexterm>

7749

7750

7751

7752

</indexterm>

7753

7754

7755

7756

</indexterm>

7757

7758

<para>

7759

This section describes several specialized constructs for making

7760

multiple comparisons between groups of values. These forms are

7761

syntactically related to the subquery forms of the previous section,

7762

but do not involve subqueries.

7763

The forms involving array subexpressions are

7764

<productname>PostgreSQL</productname> extensions; the rest are

7765

<acronym>SQL</acronym>-compliant.

7766

All of the expression forms documented in this section return

7767

Boolean (true/false) results.

7768

</para>

7769

7770

<sect2>

7771

7772

7773

7774

<replaceable>expression</replaceable> IN (<replaceable>value</replaceable><optional>, ...</optional>)

7775

</synopsis>

7776

7777

<para>

7778

The right-hand side is a parenthesized list

7779

of scalar expressions. The result is <quote>true</> if the left-hand expression's

7780

result is equal to any of the right-hand expressions. This is a shorthand

7781

notation for

7782

7783

7784

<replaceable>expression</replaceable> = <replaceable>value1</replaceable>

7785

7786

<replaceable>expression</replaceable> = <replaceable>value2</replaceable>

7787

7788

...

7789

</synopsis>

7790

</para>

7791

7792

<para>

7793

Note that if the left-hand expression yields null, or if there are

7794

no equal right-hand values and at least one right-hand expression yields

7795

null, the result of the <token>IN</token> construct will be null, not false.

7796

This is in accordance with SQL's normal rules for Boolean combinations

7797

of null values.

7798

</para>

7799

</sect2>

7800

7801

<sect2>

7802

7803

7804

7805

<replaceable>expression</replaceable> NOT IN (<replaceable>value</replaceable><optional>, ...</optional>)

7806

</synopsis>

7807

7808

<para>

7809

The right-hand side is a parenthesized list

7810

of scalar expressions. The result is <quote>true</quote> if the left-hand expression's

7811

result is unequal to all of the right-hand expressions. This is a shorthand

7812

notation for

7813

7814

7815

<replaceable>expression</replaceable> <> <replaceable>value1</replaceable>

7816

AND

7817

<replaceable>expression</replaceable> <> <replaceable>value2</replaceable>

7818

AND

7819

...

7820

</synopsis>

7821

</para>

7822

7823

<para>

7824

Note that if the left-hand expression yields null, or if there are

7825

no equal right-hand values and at least one right-hand expression yields

7826

null, the result of the <token>NOT IN</token> construct will be null, not true

7827

as one might naively expect.

7828

This is in accordance with SQL's normal rules for Boolean combinations

7829

of null values.

7830

</para>

7831

7832

<tip>

7833

<para>

7834

<literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all

7835

cases. However, null values are much more likely to trip up the novice when

7836

working with <token>NOT IN</token> than when working with <token>IN</token>.

7837

It's best to express your condition positively if possible.

7838

</para>

7839

</tip>

7840

</sect2>

7841

7842

<sect2>

7843

<title><literal>ANY</literal>/<literal>SOME</literal> (array)</title>

7844

7845

7846

<replaceable>expression</replaceable> <replaceable>operator</replaceable> ANY (<replaceable>array expression</replaceable>)

7847

<replaceable>expression</replaceable> <replaceable>operator</replaceable> SOME (<replaceable>array expression</replaceable>)

7848

</synopsis>

7849

7850

<para>

7851

The right-hand side is a parenthesized expression, which must yield an

7852

array value.

7853

The left-hand expression

7854

is evaluated and compared to each element of the array using the

7855

given <replaceable>operator</replaceable>, which must yield a Boolean

7856

result.

7857

The result of <token>ANY</token> is <quote>true</> if any true result is obtained.

7858

The result is <quote>false</> if no true result is found (including the special

7859

case where the array has zero elements).

7860

</para>

7861

7862

<para>

7863

<token>SOME</token> is a synonym for <token>ANY</token>.

7864

</para>

7865

</sect2>

7866

7867

<sect2>

7868

<title><literal>ALL</literal> (array)</title>

7869

7870

7871

<replaceable>expression</replaceable> <replaceable>operator</replaceable> ALL (<replaceable>array expression</replaceable>)

7872

</synopsis>

7873

7874

<para>

7875

The right-hand side is a parenthesized expression, which must yield an

7876

array value.

7877

The left-hand expression

7878

is evaluated and compared to each element of the array using the

7879

given <replaceable>operator</replaceable>, which must yield a Boolean

7880

result.

7881

The result of <token>ALL</token> is <quote>true</> if all comparisons yield true

7882

(including the special case where the array has zero elements).

7883

The result is <quote>false</> if any false result is found.

7884

</para>

7885

</sect2>

7886

7887

<sect2>

7888

<title>Row-wise Comparison</title>

7889

7890

7891

<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> <replaceable>row_constructor</replaceable>

7892

</synopsis>

7893

7894

<para>

7895

Each side is a row constructor,

7896

as described in <xref linkend="sql-syntax-row-constructors">.

7897

The two row values must have the same number of fields.

7898

Each side is evaluated and they are compared row-wise.

7899

Presently, only <literal>=</literal> and <literal><></literal> operators are allowed

7900

in row-wise comparisons.

7901

The result is <quote>true</> if the two rows are equal or unequal, respectively.

7902

</para>

7903

7904

<para>

7905

As usual, null values in the rows are combined per

7906

the normal rules of SQL Boolean expressions. Two rows are considered

7907

equal if all their corresponding members are non-null and equal; the rows

7908

are unequal if any corresponding members are non-null and unequal;

7909

otherwise the result of the row comparison is unknown (null).

7910

</para>

7911

7912

7913

<replaceable>row_constructor</replaceable> IS DISTINCT FROM <replaceable>row_constructor</replaceable>

7914

</synopsis>

7915

7916

<para>

7917

This construct is similar to a <literal><></literal> row comparison,

7918

but it does not yield null for null inputs. Instead, any null value is

7919

considered unequal to (distinct from) any non-null value, and any two

7920

nulls are considered equal (not distinct). Thus the result will always

7921

be either true or false, never null.

7922

</para>

7923

7924

7925

<replaceable>row_constructor</replaceable> IS NULL

7926

<replaceable>row_constructor</replaceable> IS NOT NULL

7927

</synopsis>

7928

7929

<para>

7930

These constructs test a row value for null or not null. A row value

7931

is considered not null if it has at least one field that is not null.

7932

</para>

7933

7934

</sect2>

7935

</sect1>

7936

7937

7938

<title>Set Returning Functions</title>

7939

7940

7941

<primary>set returning functions</primary>

7942

<secondary>functions</secondary>

7943

</indexterm>

7944

7945

<para>

7946

This section describes functions that possibly return more than one row.

7947

Currently the only functions in this class are series generating functions,

7948

as detailed in <xref linkend="functions-srf-series">.

7949

</para>

7950

7951

7952

<title>Series Generating Functions</title>

7953

7954

<thead>

7955

<row>

7956

<entry>Function</entry>

7957

<entry>Argument Type</entry>

7958

<entry>Return Type</entry>

7959

<entry>Description</entry>

7960

</row>

7961

</thead>

7962

7963

<tbody>

7964

<row>

7965

<entry><literal><function>generate_series</function>(<parameter>start</parameter>, <parameter>stop</parameter>)</literal></entry>

7966

<entry><type>int</type> or <type>bigint</type></entry>

7967

<entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>

7968

<entry>

7969

Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>

7970

with a step size of one.

7971

</entry>

7972

</row>

7973

7974

<row>

7975

<entry><literal><function>generate_series</function>(<parameter>start</parameter>, <parameter>stop</parameter>, <parameter>step</parameter>)</literal></entry>

7976

<entry><type>int</type> or <type>bigint</type></entry>

7977

<entry><type>setof int</type> or <type>setof bigint</type> (same as argument type)</entry>

7978

<entry>

7979

Generate a series of values, from <parameter>start</parameter> to <parameter>stop</parameter>

7980

with a step size of <parameter>step</parameter>.

7981

</entry>

7982

</row>

7983

7984

</tbody>

7985

</tgroup>

7986

</table>

7987

7988

<para>

7989

When <parameter>step</parameter> is positive, zero rows are returned if

7990

<parameter>start</parameter> is greater than <parameter>stop</parameter>.

7991

Conversely, when <parameter>step</parameter> is negative, zero rows are

7992

returned if <parameter>start</parameter> is less than <parameter>stop</parameter>.

7993

Zero rows are also returned for <literal>NULL</literal> inputs. It is an error

7994

for <parameter>step</parameter> to be zero. Some examples follow:

7995

7996

select * from generate_series(2,4);

7997

generate_series

7998

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

7999

8000

8001

8002

(3 rows)

8003

8004

select * from generate_series(5,1,-2);

8005

generate_series

8006

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

8007

8008

8009

8010

(3 rows)

8011

8012

select * from generate_series(4,3);

8013

generate_series

8014

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

8015

(0 rows)

8016

8017

select current_date + s.a as dates from generate_series(0,14,7) as s(a);

8018

dates

8019

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

8020

2004-02-05

8021

2004-02-12

8022

2004-02-19

8023

(3 rows)

8024

</programlisting>

8025

</para>

8026

</sect1>

8027

8028

8029

<title>System Information Functions</title>

8030

8031

<para>

8032

<xref linkend="functions-info-session-table"> shows several

8033

functions that extract session and system information.

8034

</para>

8035

8036

8037

<title>Session Information Functions</title>

8038

8039

<thead>

8040

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>

8041

</thead>

8042

8043

<tbody>

8044

<row>

8045

<entry><function>current_database()</function></entry>

8046

8047

<entry>name of current database</entry>

8048

</row>

8049

8050

<row>

8051

<entry><function>current_schema()</function></entry>

8052

8053

<entry>name of current schema</entry>

8054

</row>

8055

8056

<row>

8057

<entry><function>current_schemas(boolean)</function></entry>

8058

8059

<entry>names of schemas in search path optionally including implicit schemas</entry>

8060

</row>

8061

8062

<row>

8063

<entry><function>current_user</function></entry>

8064

8065

<entry>user name of current execution context</entry>

8066

</row>

8067

8068

<row>

8069

<entry><function>inet_client_addr()</function></entry>

8070

8071

<entry>address of the remote connection</entry>

8072

</row>

8073

8074

<row>

8075

<entry><function>inet_client_port()</function></entry>

8076

8077

<entry>port of the remote connection</entry>

8078

</row>

8079

8080

<row>

8081

<entry><function>inet_server_addr()</function></entry>

8082

8083

<entry>address of the local connection</entry>

8084

</row>

8085

8086

<row>

8087

<entry><function>inet_server_port()</function></entry>

8088

8089

<entry>port of the local connection</entry>

8090

</row>

8091

8092

<row>

8093

<entry><function>session_user</function></entry>

8094

8095

<entry>session user name</entry>

8096

</row>

8097

8098

<row>

8099

8100

8101

<entry>equivalent to <function>current_user</function></entry>

8102

</row>

8103

8104

<row>

8105

<entry><function>version()</function></entry>

8106

8107

<entry>PostgreSQL version information</entry>

8108

</row>

8109

</tbody>

8110

</tgroup>

8111

</table>

8112

8113

8114

8115

<secondary>current</secondary>

8116

</indexterm>

8117

8118

8119

<primary>schema</primary>

8120

<secondary>current</secondary>

8121

</indexterm>

8122

8123

8124

<primary>search path</primary>

8125

<secondary>current</secondary>

8126

</indexterm>

8127

8128

<para>

8129

The <function>session_user</function> is normally the user who initiated

8130

the current database connection; but superusers can change this setting

8131

with <xref linkend="sql-set-session-authorization">.

8132

The <function>current_user</function> is the user identifier

8133

that is applicable for permission checking. Normally, it is equal

8134

to the session user, but it changes during the execution of

8135

functions with the attribute <literal>SECURITY DEFINER</literal>.

8136

In Unix parlance, the session user is the <quote>real user</quote> and

8137

the current user is the <quote>effective user</quote>.

8138

</para>

8139

8140

<note>

8141

<para>

8142

<function>current_user</function>, <function>session_user</function>, and

8143

<function>user</function> have special syntactic status in <acronym>SQL</acronym>:

8144

they must be called without trailing parentheses.

8145

</para>

8146

</note>

8147

8148

<para>

8149

<function>current_schema</function> returns the name of the schema that is

8150

at the front of the search path (or a null value if the search path is

8151

empty). This is the schema that will be used for any tables or

8152

other named objects that are created without specifying a target schema.

8153

<function>current_schemas(boolean)</function> returns an array of the names of all

8154

schemas presently in the search path. The Boolean option determines whether or not

8155

implicitly included system schemas such as <literal>pg_catalog</> are included in the search

8156

path returned.

8157

</para>

8158

8159

<note>

8160

<para>

8161

The search path may be altered at run time. The command is:

8162

8163

SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ...</optional>

8164

</programlisting>

8165

</para>

8166

</note>

8167

8168

8169

<primary>inet_client_addr</primary>

8170

</indexterm>

8171

8172

8173

<primary>inet_client_port</primary>

8174

</indexterm>

8175

8176

8177

<primary>inet_server_addr</primary>

8178

</indexterm>

8179

8180

8181

<primary>inet_server_port</primary>

8182

</indexterm>

8183

8184

<para>

8185

<function>inet_client_addr</function> returns the IP address of the

8186

current client, and <function>inet_client_port</function> returns the

8187

port number.

8188

<function>inet_server_addr</function> returns the IP address on which

8189

the server accepted the current connection, and

8190

<function>inet_server_port</function> returns the port number.

8191

All these functions return NULL if the current connection is via a

8192

Unix-domain socket.

8193

</para>

8194

8195

8196

<primary>version</primary>

8197

</indexterm>

8198

8199

<para>

8200

<function>version()</function> returns a string describing the

8201

<productname>PostgreSQL</productname> server's version.

8202

</para>

8203

8204

8205

<primary>privilege</primary>

8206

<secondary>querying</secondary>

8207

</indexterm>

8208

8209

<para>

8210

<xref linkend="functions-info-access-table"> lists functions that

8211

allow the user to query object access privileges programmatically.

8212

See <xref linkend="ddl-priv"> for more information about

8213

privileges.

8214

</para>

8215

8216

8217

<title>Access Privilege Inquiry Functions</title>

8218

8219

<thead>

8220

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>

8221

</thead>

8222

8223

<tbody>

8224

<row>

8225

<entry><literal><function>has_table_privilege</function>(<parameter>user</parameter>,

8226

<parameter>table</parameter>,

8227

<parameter>privilege</parameter>)</literal>

8228

</entry>

8229

<entry><type>boolean</type></entry>

8230

<entry>does user have privilege for table</entry>

8231

</row>

8232

<row>

8233

<entry><literal><function>has_table_privilege</function>(<parameter>table</parameter>,

8234

<parameter>privilege</parameter>)</literal>

8235

</entry>

8236

<entry><type>boolean</type></entry>

8237

<entry>does current user have privilege for table</entry>

8238

</row>

8239

<row>

8240

<entry><literal><function>has_database_privilege</function>(<parameter>user</parameter>,

8241

<parameter>database</parameter>,

8242

<parameter>privilege</parameter>)</literal>

8243

</entry>

8244

<entry><type>boolean</type></entry>

8245

<entry>does user have privilege for database</entry>

8246

</row>

8247

<row>

8248

<entry><literal><function>has_database_privilege</function>(<parameter>database</parameter>,

8249

<parameter>privilege</parameter>)</literal>

8250

</entry>

8251

<entry><type>boolean</type></entry>

8252

<entry>does current user have privilege for database</entry>

8253

</row>

8254

<row>

8255

<entry><literal><function>has_function_privilege</function>(<parameter>user</parameter>,

8256

<parameter>function</parameter>,

8257

<parameter>privilege</parameter>)</literal>

8258

</entry>

8259

<entry><type>boolean</type></entry>

8260

<entry>does user have privilege for function</entry>

8261

</row>

8262

<row>

8263

<entry><literal><function>has_function_privilege</function>(<parameter>function</parameter>,

8264

<parameter>privilege</parameter>)</literal>

8265

</entry>

8266

<entry><type>boolean</type></entry>

8267

<entry>does current user have privilege for function</entry>

8268

</row>

8269

<row>

8270

<entry><literal><function>has_language_privilege</function>(<parameter>user</parameter>,

8271

<parameter>language</parameter>,

8272

<parameter>privilege</parameter>)</literal>

8273

</entry>

8274

<entry><type>boolean</type></entry>

8275

<entry>does user have privilege for language</entry>

8276

</row>

8277

<row>

8278

<entry><literal><function>has_language_privilege</function>(<parameter>language</parameter>,

8279

<parameter>privilege</parameter>)</literal>

8280

</entry>

8281

<entry><type>boolean</type></entry>

8282

<entry>does current user have privilege for language</entry>

8283

</row>

8284

<row>

8285

<entry><literal><function>has_schema_privilege</function>(<parameter>user</parameter>,

8286

<parameter>schema</parameter>,

8287

<parameter>privilege</parameter>)</literal>

8288

</entry>

8289

<entry><type>boolean</type></entry>

8290

<entry>does user have privilege for schema</entry>

8291

</row>

8292

<row>

8293

<entry><literal><function>has_schema_privilege</function>(<parameter>schema</parameter>,

8294

<parameter>privilege</parameter>)</literal>

8295

</entry>

8296

<entry><type>boolean</type></entry>

8297

<entry>does current user have privilege for schema</entry>

8298

</row>

8299

<row>

8300

<entry><literal><function>has_tablespace_privilege</function>(<parameter>user</parameter>,

8301

<parameter>tablespace</parameter>,

8302

<parameter>privilege</parameter>)</literal>

8303

</entry>

8304

<entry><type>boolean</type></entry>

8305

<entry>does user have privilege for tablespace</entry>

8306

</row>

8307

<row>

8308

<entry><literal><function>has_tablespace_privilege</function>(<parameter>tablespace</parameter>,

8309

<parameter>privilege</parameter>)</literal>

8310

</entry>

8311

<entry><type>boolean</type></entry>

8312

<entry>does current user have privilege for tablespace</entry>

8313

</row>

8314

</tbody>

8315

</tgroup>

8316

</table>

8317

8318

8319

<primary>has_table_privilege</primary>

8320

</indexterm>

8321

8322

<primary>has_database_privilege</primary>

8323

</indexterm>

8324

8325

<primary>has_function_privilege</primary>

8326

</indexterm>

8327

8328

<primary>has_language_privilege</primary>

8329

</indexterm>

8330

8331

<primary>has_schema_privilege</primary>

8332

</indexterm>

8333

8334

<primary>has_tablespace_privilege</primary>

8335

</indexterm>

8336

8337

<para>

8338

<function>has_table_privilege</function> checks whether a user

8339

can access a table in a particular way. The user can be

8340

specified by name or by ID

8341

(<literal>pg_user.usesysid</literal>), or if the argument is

8342

omitted

8343

<function>current_user</function> is assumed. The table can be specified

8344

by name or by OID. (Thus, there are actually six variants of

8345

<function>has_table_privilege</function>, which can be distinguished by

8346

the number and types of their arguments.) When specifying by name,

8347

the name can be schema-qualified if necessary.

8348

The desired access privilege type

8349

is specified by a text string, which must evaluate to one of the

8350

values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>,

8351

<literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, or

8352

<literal>TRIGGER</literal>. (Case of the string is not significant, however.)

8353

An example is:

8354

8355

SELECT has_table_privilege('myschema.mytable', 'select');

8356

</programlisting>

8357

</para>

8358

8359

<para>

8360

<function>has_database_privilege</function> checks whether a user

8361

can access a database in a particular way. The possibilities for its

8362

arguments are analogous to <function>has_table_privilege</function>.

8363

The desired access privilege type must evaluate to

8364

<literal>CREATE</literal>,

8365

<literal>TEMPORARY</literal>, or

8366

<literal>TEMP</literal> (which is equivalent to

8367

<literal>TEMPORARY</literal>).

8368

</para>

8369

8370

<para>

8371

<function>has_function_privilege</function> checks whether a user

8372

can access a function in a particular way. The possibilities for its

8373

arguments are analogous to <function>has_table_privilege</function>.

8374

When specifying a function by a text string rather than by OID,

8375

the allowed input is the same as for the <type>regprocedure</> data type

8376

(see <xref linkend="datatype-oid">).

8377

The desired access privilege type must evaluate to

8378

<literal>EXECUTE</literal>.

8379

An example is:

8380

8381

SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');

8382

</programlisting>

8383

</para>

8384

8385

<para>

8386

<function>has_language_privilege</function> checks whether a user

8387

can access a procedural language in a particular way. The possibilities

8388

for its arguments are analogous to <function>has_table_privilege</function>.

8389

The desired access privilege type must evaluate to

8390

<literal>USAGE</literal>.

8391

</para>

8392

8393

<para>

8394

<function>has_schema_privilege</function> checks whether a user

8395

can access a schema in a particular way. The possibilities for its

8396

arguments are analogous to <function>has_table_privilege</function>.

8397

The desired access privilege type must evaluate to

8398

<literal>CREATE</literal> or

8399

<literal>USAGE</literal>.

8400

</para>

8401

8402

<para>

8403

<function>has_tablespace_privilege</function> checks whether a user

8404

can access a tablespace in a particular way. The possibilities for its

8405

arguments are analogous to <function>has_table_privilege</function>.

8406

The desired access privilege type must evaluate to

8407

<literal>CREATE</literal>.

8408

</para>

8409

8410

<para>

8411

To test whether a user holds a grant option on the privilege,

8412

append <literal> WITH GRANT OPTION</literal> to the privilege key

8413

word; for example <literal>'UPDATE WITH GRANT OPTION'</literal>.

8414

</para>

8415

8416

<para>

8417

<xref linkend="functions-info-schema-table"> shows functions that

8418

determine whether a certain object is <firstterm>visible</> in the

8419

current schema search path. A table is said to be visible if its

8420

containing schema is in the search path and no table of the same

8421

name appears earlier in the search path. This is equivalent to the

8422

statement that the table can be referenced by name without explicit

8423

schema qualification. For example, to list the names of all

8424

visible tables:

8425

8426

SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);

8427

</programlisting>

8428

</para>

8429

8430

8431

<title>Schema Visibility Inquiry Functions</title>

8432

8433

<thead>

8434

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>

8435

</thead>

8436

8437

<tbody>

8438

<row>

8439

<entry><literal><function>pg_table_is_visible</function>(<parameter>table_oid</parameter>)</literal>

8440

</entry>

8441

<entry><type>boolean</type></entry>

8442

<entry>is table visible in search path</entry>

8443

</row>

8444

<row>

8445

<entry><literal><function>pg_type_is_visible</function>(<parameter>type_oid</parameter>)</literal>

8446

</entry>

8447

<entry><type>boolean</type></entry>

8448

<entry>is type (or domain) visible in search path</entry>

8449

</row>

8450

<row>

8451

<entry><literal><function>pg_function_is_visible</function>(<parameter>function_oid</parameter>)</literal>

8452

</entry>

8453

<entry><type>boolean</type></entry>

8454

<entry>is function visible in search path</entry>

8455

</row>

8456

<row>

8457

<entry><literal><function>pg_operator_is_visible</function>(<parameter>operator_oid</parameter>)</literal>

8458

</entry>

8459

<entry><type>boolean</type></entry>

8460

<entry>is operator visible in search path</entry>

8461

</row>

8462

<row>

8463

<entry><literal><function>pg_opclass_is_visible</function>(<parameter>opclass_oid</parameter>)</literal>

8464

</entry>

8465

<entry><type>boolean</type></entry>

8466

<entry>is operator class visible in search path</entry>

8467

</row>

8468

<row>

8469

<entry><literal><function>pg_conversion_is_visible</function>(<parameter>conversion_oid</parameter>)</literal>

8470

</entry>

8471

<entry><type>boolean</type></entry>

8472

<entry>is conversion visible in search path</entry>

8473

</row>

8474

</tbody>

8475

</tgroup>

8476

</table>

8477

8478

8479

<primary>pg_table_is_visible</primary>

8480

</indexterm>

8481

8482

<primary>pg_type_is_visible</primary>

8483

</indexterm>

8484

8485

<primary>pg_function_is_visible</primary>

8486

</indexterm>

8487

8488

<primary>pg_operator_is_visible</primary>

8489

</indexterm>

8490

8491

<primary>pg_opclass_is_visible</primary>

8492

</indexterm>

8493

8494

<primary>pg_conversion_is_visible</primary>

8495

</indexterm>

8496

8497

<para>

8498

<function>pg_table_is_visible</function> performs the check for

8499

tables (or views, or any other kind of <literal>pg_class</> entry).

8500

<function>pg_type_is_visible</function>,

8501

<function>pg_function_is_visible</function>,

8502

<function>pg_operator_is_visible</function>,

8503

<function>pg_opclass_is_visible</function>, and

8504

<function>pg_conversion_is_visible</function> perform the same sort of

8505

visibility check for types (and domains), functions, operators, operator classes

8506

and conversions, respectively. For functions and operators, an object in

8507

the search path is visible if there is no object of the same name

8508

<emphasis>and argument data type(s)</> earlier in the path. For

8509

operator classes, both name and associated index access method are

8510

considered.

8511

</para>

8512

8513

<para>

8514

All these functions require object OIDs to identify the object to be

8515

checked. If you want to test an object by name, it is convenient to use

8516

the OID alias types (<type>regclass</>, <type>regtype</>,

8517

<type>regprocedure</>, or <type>regoperator</>), for example

8518

8519

SELECT pg_type_is_visible('myschema.widget'::regtype);

8520

</programlisting>

8521

Note that it would not make much sense to test an unqualified name in

8522

this way — if the name can be recognized at all, it must be visible.

8523

</para>

8524

8525

8526

<primary>format_type</primary>

8527

</indexterm>

8528

8529

8530

<primary>pg_get_viewdef</primary>

8531

</indexterm>

8532

8533

8534

<primary>pg_get_ruledef</primary>

8535

</indexterm>

8536

8537

8538

<primary>pg_get_indexdef</primary>

8539

</indexterm>

8540

8541

8542

<primary>pg_get_triggerdef</primary>

8543

</indexterm>

8544

8545

8546

<primary>pg_get_constraintdef</primary>

8547

</indexterm>

8548

8549

8550

8551

</indexterm>

8552

8553

8554

<primary>pg_get_userbyid</primary>

8555

</indexterm>

8556

8557

8558

<primary>pg_get_serial_sequence</primary>

8559

</indexterm>

8560

8561

8562

<primary>pg_tablespace_databases</primary>

8563

</indexterm>

8564

8565

<para>

8566

<xref linkend="functions-info-catalog-table"> lists functions that

8567

extract information from the system catalogs.

8568

</para>

8569

8570

8571

<title>System Catalog Information Functions</title>

8572

8573

<thead>

8574

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>

8575

</thead>

8576

8577

<tbody>

8578

<row>

8579

<entry><literal><function>format_type</function>(<parameter>type_oid</parameter>, <parameter>typemod</>)</literal></entry>

8580

8581

8582

</row>

8583

<row>

8584

<entry><literal><function>pg_get_viewdef</function>(<parameter>view_name</parameter>)</literal></entry>

8585

8586

<entry>get <command>CREATE VIEW</> command for view (<emphasis>deprecated</emphasis>)</entry>

8587

</row>

8588

<row>

8589

<entry><literal><function>pg_get_viewdef</function>(<parameter>view_name</parameter>, <parameter>pretty_bool</>)</literal></entry>

8590

8591

<entry>get <command>CREATE VIEW</> command for view (<emphasis>deprecated</emphasis>)</entry>

8592

</row>

8593

<row>

8594

<entry><literal><function>pg_get_viewdef</function>(<parameter>view_oid</parameter>)</literal></entry>

8595

8596

<entry>get <command>CREATE VIEW</> command for view</entry>

8597

</row>

8598

<row>

8599

<entry><literal><function>pg_get_viewdef</function>(<parameter>view_oid</parameter>, <parameter>pretty_bool</>)</literal></entry>

8600

8601

<entry>get <command>CREATE VIEW</> command for view</entry>

8602

</row>

8603

<row>

8604

<entry><literal><function>pg_get_ruledef</function>(<parameter>rule_oid</parameter>)</literal></entry>

8605

8606

<entry>get <command>CREATE RULE</> command for rule</entry>

8607

</row>

8608

<row>

8609

<entry><literal><function>pg_get_ruledef</function>(<parameter>rule_oid</parameter>, <parameter>pretty_bool</>)</literal></entry>

8610

8611

<entry>get <command>CREATE RULE</> command for rule</entry>

8612

</row>

8613

<row>

8614

<entry><literal><function>pg_get_indexdef</function>(<parameter>index_oid</parameter>)</literal></entry>

8615

8616

<entry>get <command>CREATE INDEX</> command for index</entry>

8617

</row>

8618

<row>

8619

<entry><literal><function>pg_get_indexdef</function>(<parameter>index_oid</parameter>, <parameter>column_no</>, <parameter>pretty_bool</>)</literal></entry>

8620

8621

<entry>get <command>CREATE INDEX</> command for index,

8622

or definition of just one index column when

8623

<parameter>column_no</> is not zero</entry>

8624

</row>

8625

<row>

8626

<entry><function>pg_get_triggerdef</function>(<parameter>trigger_oid</parameter>)</entry>

8627

8628

<entry>get <command>CREATE [ CONSTRAINT ] TRIGGER</> command for trigger</entry>

8629

</row>

8630

<row>

8631

<entry><literal><function>pg_get_constraintdef</function>(<parameter>constraint_oid</parameter>)</literal></entry>

8632

8633

<entry>get definition of a constraint</entry>

8634

</row>

8635

<row>

8636

<entry><literal><function>pg_get_constraintdef</function>(<parameter>constraint_oid</parameter>, <parameter>pretty_bool</>)</literal></entry>

8637

8638

<entry>get definition of a constraint</entry>

8639

</row>

8640

<row>

8641

<entry><literal><function>pg_get_expr</function>(<parameter>expr_text</parameter>, <parameter>relation_oid</>)</literal></entry>

8642

8643

<entry>decompile internal form of an expression, assuming that any Vars

8644

in it refer to the relation indicated by the second parameter</entry>

8645

</row>

8646

<row>

8647

<entry><literal><function>pg_get_expr</function>(<parameter>expr_text</parameter>, <parameter>relation_oid</>, <parameter>pretty_bool</>)</literal></entry>

8648

8649

<entry>decompile internal form of an expression, assuming that any Vars

8650

in it refer to the relation indicated by the second parameter</entry>

8651

</row>

8652

<row>

8653

<entry><literal><function>pg_get_userbyid</function>(<parameter>userid</parameter>)</literal></entry>

8654

8655

<entry>get user name with given ID</entry>

8656

</row>

8657

<row>

8658

<entry><literal><function>pg_get_serial_sequence</function>(<parameter>table_name</parameter>, <parameter>column_name</parameter>)</literal></entry>

8659

8660

<entry>get name of the sequence that a <type>serial</type> or <type>bigserial</type> column

8661

uses</entry>

8662

</row>

8663

<row>

8664

<entry><literal><function>pg_tablespace_databases</function>(<parameter>tablespace_oid</parameter>)</literal></entry>

8665

<entry><type>setof oid</type></entry>

8666

<entry>get set of database OIDs that have objects in the tablespace</entry>

8667

</row>

8668

</tbody>

8669

</tgroup>

8670

</table>

8671

8672

<para>

8673

<function>format_type</function> returns the SQL name of a data type that

8674

is identified by its type OID and possibly a type modifier. Pass NULL

8675

for the type modifier if no specific modifier is known.

8676

</para>

8677

8678

<para>

8679

<function>pg_get_viewdef</function>,

8680

<function>pg_get_ruledef</function>,

8681

<function>pg_get_indexdef</function>,

8682

<function>pg_get_triggerdef</function>, and

8683

<function>pg_get_constraintdef</function> respectively

8684

reconstruct the creating command for a view, rule, index, trigger, or

8685

constraint. (Note that this is a decompiled reconstruction, not

8686

the original text of the command.)

8687

<function>pg_get_expr</function> decompiles the internal form of an

8688

individual expression, such as the default value for a column. It

8689

may be useful when examining the contents of system catalogs.

8690

Most of these functions come in two

8691

variants, one of which can optionally <quote>pretty-print</> the result.

8692

The pretty-printed format is more readable, but the default format is more

8693

likely to be

8694

interpreted the same way by future versions of <productname>PostgreSQL</>;

8695

avoid using pretty-printed output for dump purposes.

8696

Passing <literal>false</> for the pretty-print parameter yields the

8697

same result as the variant that does not have the parameter at all.

8698

</para>

8699

8700

<para>

8701

<function>pg_get_userbyid</function>

8702

extracts a user's name given a user ID number.

8703

<function>pg_get_serial_sequence</function>

8704

fetches the name of the sequence associated with a serial or

8705

bigserial column. The name is suitably formatted

8706

for passing to the sequence functions (see <xref

8707

linkend="functions-sequence">).

8708

NULL is returned if the column does not have a sequence attached.

8709

</para>

8710

8711

<para>

8712

<function>pg_tablespace_databases</function> allows usage examination of a

8713

tablespace. It will return a set of OIDs of databases that have objects

8714

stored in the tablespace. If this function returns any row, the

8715

tablespace is not empty and cannot be dropped. To

8716

display the specific objects populating the tablespace, you will need

8717

to connect to the databases identified by

8718

<function>pg_tablespace_databases</function> and query their

8719

<structname>pg_class</> catalogs.

8720

</para>

8721

8722

8723

<primary>obj_description</primary>

8724

</indexterm>

8725

8726

8727

<primary>col_description</primary>

8728

</indexterm>

8729

8730

8731

<primary>comment</primary>

8732

<secondary sortas="database objects">about database objects</secondary>

8733

</indexterm>

8734

8735

<para>

8736

The functions shown in <xref

8737

linkend="functions-info-comment-table"> extract comments

8738

previously stored with the <command>COMMENT</command> command. A

8739

null value is returned if no comment could be found matching the

8740

specified parameters.

8741

</para>

8742

8743

8744

<title>Comment Information Functions</title>

8745

8746

<thead>

8747

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>

8748

</thead>

8749

8750

<tbody>

8751

<row>

8752

<entry><literal><function>obj_description</function>(<parameter>object_oid</parameter>, <parameter>catalog_name</parameter>)</literal></entry>

8753

8754

<entry>get comment for a database object</entry>

8755

</row>

8756

<row>

8757

<entry><literal><function>obj_description</function>(<parameter>object_oid</parameter>)</literal></entry>

8758

8759

<entry>get comment for a database object (<emphasis>deprecated</emphasis>)</entry>

8760

</row>

8761

<row>

8762

<entry><literal><function>col_description</function>(<parameter>table_oid</parameter>, <parameter>column_number</parameter>)</literal></entry>

8763

8764

<entry>get comment for a table column</entry>

8765

</row>

8766

</tbody>

8767

</tgroup>

8768

</table>

8769

8770

<para>

8771

The two-parameter form of <function>obj_description</function> returns the

8772

comment for a database object specified by its OID and the name of the

8773

containing system catalog. For example,

8774

<literal>obj_description(123456,'pg_class')</literal>

8775

would retrieve the comment for a table with OID 123456.

8776

The one-parameter form of <function>obj_description</function> requires only

8777

the object OID. It is now deprecated since there is no guarantee that

8778

OIDs are unique across different system catalogs; therefore, the wrong

8779

comment could be returned.

8780

</para>

8781

8782

<para>

8783

<function>col_description</function> returns the comment for a table column,

8784

which is specified by the OID of its table and its column number.

8785

<function>obj_description</function> cannot be used for table columns since

8786

columns do not have OIDs of their own.

8787

</para>

8788

</sect1>

8789

8790

8791

<title>System Administration Functions</title>

8792

8793

<para>

8794

<xref linkend="functions-admin-set-table"> shows the functions

8795

available to query and alter run-time configuration parameters.

8796

</para>

8797

8798

8799

<title>Configuration Settings Functions</title>

8800

8801

<thead>

8802

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>

8803

</thead>

8804

8805

<tbody>

8806

<row>

8807

<entry>

8808

<literal><function>current_setting</function>(<parameter>setting_name</parameter>)</literal>

8809

</entry>

8810

8811

<entry>current value of setting</entry>

8812

</row>

8813

<row>

8814

<entry>

8815

<literal><function>set_config(<parameter>setting_name</parameter>,

8816

<parameter>new_value</parameter>,

8817

<parameter>is_local</parameter>)</function></literal>

8818

</entry>

8819

8820

<entry>set parameter and return new value</entry>

8821

</row>

8822

</tbody>

8823

</tgroup>

8824

</table>

8825

8826

8827

8828

</indexterm>

8829

8830

8831

8832

</indexterm>

8833

8834

8835

<primary>configuration</primary>

8836

<secondary sortas="server">of the server</secondary>

8837

<tertiary>functions</tertiary>

8838

</indexterm>

8839

8840

<para>

8841

The function <function>current_setting</function> yields the

8842

current value of the setting <parameter>setting_name</parameter>.

8843

It corresponds to the <acronym>SQL</acronym> command

8844

<command>SHOW</command>. An example:

8845

8846

SELECT current_setting('datestyle');

8847

8848

current_setting

8849

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

8850

ISO, MDY

8851

(1 row)

8852

</programlisting>

8853

</para>

8854

8855

<para>

8856

<function>set_config</function> sets the parameter

8857

<parameter>setting_name</parameter> to

8858

<parameter>new_value</parameter>. If

8859

<parameter>is_local</parameter> is <literal>true</literal>, the

8860

new value will only apply to the current transaction. If you want

8861

the new value to apply for the current session, use

8862

<literal>false</literal> instead. The function corresponds to the

8863

SQL command <command>SET</command>. An example:

8864

8865

SELECT set_config('log_statement_stats', 'off', false);

8866

8867

set_config

8868

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

8869

off

8870

(1 row)

8871

</programlisting>

8872

</para>

8873

8874

8875

<primary>pg_cancel_backend</primary>

8876

</indexterm>

8877

8878

8879

<primary>signal</primary>

8880

<secondary sortas="backend">backend processes</secondary>

8881

</indexterm>

8882

8883

<para>

8884

The function shown in <xref

8885

linkend="functions-admin-signal-table"> sends control signals to

8886

other server processes. Use of this function is restricted

8887

to superusers.

8888

</para>

8889

8890

8891

<title>Backend Signalling Functions</title>

8892

8893

<thead>

8894

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>

8895

</row>

8896

</thead>

8897

8898

<tbody>

8899

<row>

8900

<entry>

8901

<literal><function>pg_cancel_backend</function>(<parameter>pid</parameter>)</literal>

8902

</entry>

8903

8904

<entry>Cancel a backend's current query</entry>

8905

</row>

8906

</tbody>

8907

</tgroup>

8908

</table>

8909

8910

<para>

8911

This function returns 1 if successful, 0 if not successful.

8912

The process ID (<literal>pid</literal>) of an active backend can be found

8913

from the <structfield>procpid</structfield> column in the

8914

<structname>pg_stat_activity</structname> view, or by listing the <command>postgres</command>

8915

processes on the server with <application>ps</>.

8916

</para>

8917

8918

8919

<primary>pg_start_backup</primary>

8920

</indexterm>

8921

8922

8923

<primary>pg_stop_backup</primary>

8924

</indexterm>

8925

8926

8927

<primary>backup</primary>

8928

</indexterm>

8929

8930

<para>

8931

The functions shown in <xref

8932

linkend="functions-admin-backup-table"> assist in making on-line backups.

8933

Use of these functions is restricted to superusers.

8934

</para>

8935

8936

8937

<title>Backup Control Functions</title>

8938

8939

<thead>

8940

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>

8941

</row>

8942

</thead>

8943

8944

<tbody>

8945

<row>

8946

<entry>

8947

<literal><function>pg_start_backup</function>(<parameter>label_text</parameter>)</literal>

8948

</entry>

8949

8950

<entry>Set up for performing on-line backup</entry>

8951

</row>

8952

<row>

8953

<entry>

8954

<literal><function>pg_stop_backup</function>()</literal>

8955

</entry>

8956

8957

<entry>Finish performing on-line backup</entry>

8958

</row>

8959

</tbody>

8960

</tgroup>

8961

</table>

8962

8963

<para>

8964

<function>pg_start_backup</> accepts a single parameter which is an

8965

arbitrary user-defined label for the backup. (Typically this would be

8966

the name under which the backup dump file will be stored.) The function

8967

writes a backup label file into the database cluster's data directory,

8968

and then returns the backup's starting WAL offset as text. (The user

8969

need not pay any attention to this result value, but it is provided in

8970

case it is of use.)

8971

</para>

8972

8973

<para>

8974

<function>pg_stop_backup</> removes the label file created by

8975

<function>pg_start_backup</>, and instead creates a backup history file in

8976

the WAL archive area. The history file includes the label given to

8977

<function>pg_start_backup</>, the starting and ending WAL offsets for

8978

the backup, and the starting and ending times of the backup. The return

8979

value is the backup's ending WAL offset (which again may be of little

8980

interest).

8981

</para>

8982

8983

<para>

8984

For details about proper usage of these functions, see

8985

<xref linkend="backup-online">.

8986

</para>

8987

</sect1>

8988

</chapter>

8989

8990

<!-- Keep this comment at the end of the file

8991

Local variables:

8992

mode:sgml

8993

sgml-omittag:nil

8994

sgml-shorttag:t

8995

sgml-minimize-attributes:nil

8996

sgml-always-quote-attributes:t

8997

sgml-indent-step:1

8998

sgml-indent-data:t

8999

sgml-parent-document:nil

9000

sgml-default-dtd-file:"./reference.ced"

9001

sgml-exposed-tags:nil

9002

sgml-local-catalogs:("/usr/lib/sgml/catalog")

9003

sgml-local-ecat-files:nil

9004

End:

9005

-->

Older »