~vcs-imports/mammoth-replicator/trunk

<replaceable class="parameter">colname</replaceable> integer DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq') NOT NULL

709

);

710

</programlisting>

711

712

Thus, we have created an integer column and arranged for its default

713

values to be assigned from a sequence generator. A <literal>NOT NULL</>

714

constraint is applied to ensure that a null value cannot be explicitly

715

inserted, either. In most cases you would also want to attach a

716

<literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint to prevent

717

duplicate values from being inserted by accident, but this is

718

not automatic.

719

</para>

720

721

<note>

722

<para>

723

Prior to <productname>PostgreSQL</productname> 7.3, <type>serial</type>

724

implied <literal>UNIQUE</literal>. This is no longer automatic. If

725

you wish a serial column to be in a unique constraint or a

726

primary key, it must now be specified, same as with

727

any other data type.

728

</para>

729

</note>

730

731

<para>

732

To insert the next value of the sequence into the <type>serial</type>

733

column, specify that the <type>serial</type>

734

column should be assigned its default value. This can be done

735

either by excluding the column from the list of columns in

736

the <command>INSERT</command> statement, or through the use of

737

the <literal>DEFAULT</literal> key word.

738

</para>

739

740

<para>

741

The type names <type>serial</type> and <type>serial4</type> are

742

equivalent: both create <type>integer</type> columns. The type

743

names <type>bigserial</type> and <type>serial8</type> work just

744

the same way, except that they create a <type>bigint</type>

745

column. <type>bigserial</type> should be used if you anticipate

746

the use of more than 2<superscript>31</> identifiers over the

747

lifetime of the table.

748

</para>

749

750

<para>

751

The sequence created for a <type>serial</type> column is

752

automatically dropped when the owning column is dropped, and

753

cannot be dropped otherwise. (This was not true in

754

<productname>PostgreSQL</productname> releases before 7.3. Note

755

that this automatic drop linkage will not occur for a sequence

756

created by reloading a dump from a pre-7.3 database; the dump

757

file does not contain the information needed to establish the

758

dependency link.) Furthermore, this dependency between sequence

759

and column is made only for the <type>serial</> column itself. If

760

any other columns reference the sequence (perhaps by manually

761

calling the <function>nextval</> function), they will be broken

762

if the sequence is removed. Using a <type>serial</> column's sequence

763

in such a fashion is considered bad form; if you wish to feed several

764

columns from the same sequence generator, create the sequence as an

765

independent object.

766

</para>

767

</sect2>

768

</sect1>

769

770

771

<title>Monetary Types</title>

772

773

<note>

774

<para>

775

The <type>money</type> type is deprecated. Use

776

<type>numeric</type> or <type>decimal</type> instead, in

777

combination with the <function>to_char</function> function.

778

</para>

779

</note>

780

781

<para>

782

The <type>money</type> type stores a currency amount with a fixed

783

fractional precision; see <xref

784

linkend="datatype-money-table">.

785

Input is accepted in a variety of formats, including integer and

786

floating-point literals, as well as <quote>typical</quote>

787

currency formatting, such as <literal>'$1,000.00'</literal>.

788

Output is generally in the latter form but depends on the locale.

789

</para>

790

791

792

<title>Monetary Types</title>

793

794

<thead>

795

<row>

796

797

<entry>Storage Size</entry>

798

<entry>Description</entry>

799

<entry>Range</entry>

800

</row>

801

</thead>

802

<tbody>

803

<row>

804

<entry>money</entry>

805

<entry>4 bytes</entry>

806

<entry>currency amount</entry>

807

808

</row>

809

</tbody>

810

</tgroup>

811

</table>

812

</sect1>

813

814

815

816

<title>Character Types</title>

817

818

819

<primary>character string</primary>

820

<secondary>data types</secondary>

821

</indexterm>

822

823

824

<primary>string</primary>

825

<see>character string</see>

826

</indexterm>

827

828

829

<primary>character</primary>

830

</indexterm>

831

832

833

<primary>character varying</primary>

834

</indexterm>

835

836

837

838

</indexterm>

839

840

841

842

</indexterm>

843

844

845

<primary>varchar</primary>

846

</indexterm>

847

848

849

<title>Character Types</title>

850

851

<thead>

852

<row>

853

854

<entry>Description</entry>

855

</row>

856

</thead>

857

<tbody>

858

<row>

859

<entry><type>character varying(<replaceable>n</>)</type>, <type>varchar(<replaceable>n</>)</type></entry>

860

<entry>variable-length with limit</entry>

861

</row>

862

<row>

863

<entry><type>character(<replaceable>n</>)</type>, <type>char(<replaceable>n</>)</type></entry>

864

<entry>fixed-length, blank padded</entry>

865

</row>

866

<row>

867

868

<entry>variable unlimited length</entry>

869

</row>

870

</tbody>

871

</tgroup>

872

</table>

873

874

<para>

875

<xref linkend="datatype-character-table"> shows the

876

general-purpose character types available in

877

<productname>PostgreSQL</productname>.

878

</para>

879

880

<para>

881

<acronym>SQL</acronym> defines two primary character types:

882

<type>character varying(<replaceable>n</>)</type> and

883

<type>character(<replaceable>n</>)</type>, where <replaceable>n</>

884

is a positive integer. Both of these types can store strings up to

885

<replaceable>n</> characters in length. An attempt to store a

886

longer string into a column of these types will result in an

887

error, unless the excess characters are all spaces, in which case

888

the string will be truncated to the maximum length. (This somewhat

889

bizarre exception is required by the <acronym>SQL</acronym>

890

standard.) If the string to be stored is shorter than the declared

891

length, values of type <type>character</type> will be space-padded;

892

values of type <type>character varying</type> will simply store the

893

shorter

894

string.

895

</para>

896

897

<para>

898

If one explicitly casts a value to <type>character

899

varying(<replaceable>n</>)</type> or

900

<type>character(<replaceable>n</>)</type>, then an over-length

901

value will be truncated to <replaceable>n</> characters without

902

raising an error. (This too is required by the

903

<acronym>SQL</acronym> standard.)

904

</para>

905

906

<note>

907

<para>

908

Prior to <productname>PostgreSQL</> 7.2, strings that were too long were

909

always truncated without raising an error, in either explicit or

910

implicit casting contexts.

911

</para>

912

</note>

913

914

<para>

915

The notations <type>varchar(<replaceable>n</>)</type> and

916

<type>char(<replaceable>n</>)</type> are aliases for <type>character

917

varying(<replaceable>n</>)</type> and

918

<type>character(<replaceable>n</>)</type>, respectively.

919

<type>character</type> without length specifier is equivalent to

920

<type>character(1)</type>. If <type>character varying</type> is used

921

without length specifier, the type accepts strings of any size. The

922

latter is a <productname>PostgreSQL</> extension.

923

</para>

924

925

<para>

926

In addition, <productname>PostgreSQL</productname> provides the

927

<type>text</type> type, which stores strings of any length.

928

Although the type <type>text</type> is not in the

929

<acronym>SQL</acronym> standard, several other SQL database

930

management systems have it as well.

931

</para>

932

933

<para>

934

Values of type <type>character</type> are physically padded

935

with spaces to the specified width <replaceable>n</>, and are

936

stored and displayed that way. However, the padding spaces are

937

treated as semantically insignificant. Trailing spaces are

938

disregarded when comparing two values of type <type>character</type>,

939

and they will be removed when converting a <type>character</type> value

940

to one of the other string types. Note that trailing spaces

941

<emphasis>are</> semantically significant in

942

<type>character varying</type> and <type>text</type> values.

943

</para>

944

945

<para>

946

The storage requirement for data of these types is 4 bytes plus the

947

actual string, and in case of <type>character</type> plus the

948

padding. Long strings are compressed by the system automatically, so

949

the physical requirement on disk may be less. Long values are also

950

stored in background tables so they do not interfere with rapid

951

access to the shorter column values. In any case, the longest

952

possible character string that can be stored is about 1 GB. (The

953

maximum value that will be allowed for <replaceable>n</> in the data

954

type declaration is less than that. It wouldn't be very useful to

955

change this because with multibyte character encodings the number of

956

characters and bytes can be quite different anyway. If you desire to

957

store long strings with no specific upper limit, use

958

<type>text</type> or <type>character varying</type> without a length

959

specifier, rather than making up an arbitrary length limit.)

960

</para>

961

962

<tip>

963

<para>

964

There are no performance differences between these three types,

965

apart from the increased storage size when using the blank-padded

966

type. While <type>character(<replaceable>n</>)</type> has performance

967

advantages in some other database systems, it has no such advantages in

968

<productname>PostgreSQL</productname>. In most situations

969

<type>text</type> or <type>character varying</type> should be used

970

instead.

971

</para>

972

</tip>

973

974

<para>

975

Refer to <xref linkend="sql-syntax-strings"> for information about

976

the syntax of string literals, and to <xref linkend="functions">

977

for information about available operators and functions. The

978

database character set determines the character set used to store

979

textual values; for more information on character set support,

980

refer to <xref linkend="multibyte">.

981

</para>

982

983

984

<title>Using the character types</title>

985

986

987

CREATE TABLE test1 (a character(4));

988

INSERT INTO test1 VALUES ('ok');

989

SELECT a, char_length(a) FROM test1; -- <co id="co.datatype-char">

990

991

a | char_length

992

------+-------------

993

ok | 2

994

</computeroutput>

995

996

CREATE TABLE test2 (b varchar(5));

997

INSERT INTO test2 VALUES ('ok');

998

INSERT INTO test2 VALUES ('good ');

999

INSERT INTO test2 VALUES ('too long');

1000

<computeroutput>ERROR: value too long for type character varying(5)</computeroutput>

1001

INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation

1002

SELECT b, char_length(b) FROM test2;

1003

1004

b | char_length

1005

-------+-------------

1006

ok | 2

1007

good | 5

1008

too l | 5

1009

</computeroutput>

1010

</programlisting>

1011

1012

1013

<para>

1014

The <function>char_length</function> function is discussed in

1015

<xref linkend="functions-string">.

1016

</para>

1017

</callout>

1018

</calloutlist>

1019

</example>

1020

1021

<para>

1022

There are two other fixed-length character types in

1023

<productname>PostgreSQL</productname>, shown in <xref

1024

linkend="datatype-character-special-table">. The <type>name</type>

1025

type exists <emphasis>only</emphasis> for storage of identifiers

1026

in the internal system catalogs and is not intended for use by the general user. Its

1027

length is currently defined as 64 bytes (63 usable characters plus

1028

terminator) but should be referenced using the constant

1029

<symbol>NAMEDATALEN</symbol>. The length is set at compile time (and

1030

is therefore adjustable for special uses); the default maximum

1031

length may change in a future release. The type <type>"char"</type>

1032

(note the quotes) is different from <type>char(1)</type> in that it

1033

only uses one byte of storage. It is internally used in the system

1034

catalogs as a poor-man's enumeration type.

1035

</para>

1036

1037

1038

<title>Special Character Types</title>

1039

1040

<thead>

1041

<row>

1042

1043

<entry>Storage Size</entry>

1044

<entry>Description</entry>

1045

</row>

1046

</thead>

1047

<tbody>

1048

<row>

1049

1050

1051

<entry>single-character internal type</entry>

1052

</row>

1053

<row>

1054

1055

<entry>64 bytes</entry>

1056

<entry>internal type for object names</entry>

1057

</row>

1058

</tbody>

1059

</tgroup>

1060

</table>

1061

1062

</sect1>

1063

1064

1065

<title>Binary Data Types</title>

1066

1067

1068

<primary>binary data</primary>

1069

</indexterm>

1070

1071

1072

<primary>bytea</primary>

1073

</indexterm>

1074

1075

<para>

1076

The <type>bytea</type> data type allows storage of binary strings;

1077

see <xref linkend="datatype-binary-table">.

1078

</para>

1079

1080

1081

<title>Binary Data Types</title>

1082

1083

<thead>

1084

<row>

1085

1086

<entry>Storage Size</entry>

1087

<entry>Description</entry>

1088

</row>

1089

</thead>

1090

<tbody>

1091

<row>

1092

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

1093

<entry>4 bytes plus the actual binary string</entry>

1094

<entry>variable-length binary string</entry>

1095

</row>

1096

</tbody>

1097

</tgroup>

1098

</table>

1099

1100

<para>

1101

A binary string is a sequence of octets (or bytes). Binary

1102

strings are distinguished from character strings by two

1103

characteristics: First, binary strings specifically allow storing

1104

octets of value zero and other <quote>non-printable</quote>

1105

octets (usually, octets outside the range 32 to 126).

1106

Character strings disallow zero octets, and also disallow any

1107

other octet values and sequences of octet values that are invalid

1108

according to the database's selected character set encoding.

1109

Second, operations on binary strings process the actual bytes,

1110

whereas the processing of character strings depends on locale settings.

1111

In short, binary strings are appropriate for storing data that the

1112

programmer thinks of as <quote>raw bytes</>, whereas character

1113

strings are appropriate for storing text.

1114

</para>

1115

1116

<para>

1117

When entering <type>bytea</type> values, octets of certain values

1118

<emphasis>must</emphasis> be escaped (but all octet values

1119

<emphasis>may</emphasis> be escaped) when used as part of a string

1120

literal in an <acronym>SQL</acronym> statement. In general, to

1121

escape an octet, it is converted into the three-digit octal number

1122

equivalent of its decimal octet value, and preceded by two

1123

backslashes. <xref linkend="datatype-binary-sqlesc"> shows the

1124

characters that must be escaped, and gives the alternate escape

1125

sequences where applicable.

1126

</para>

1127

1128

1129

<title><type>bytea</> Literal Escaped Octets</title>

1130

1131

<thead>

1132

<row>

1133

<entry>Decimal Octet Value</entry>

1134

<entry>Description</entry>

1135

<entry>Escaped Input Representation</entry>

1136

<entry>Example</entry>

1137

<entry>Output Representation</entry>

1138

</row>

1139

</thead>

1140

1141

<tbody>

1142

<row>

1143

1144

<entry>zero octet</entry>

1145

1146

<entry><literal>SELECT '\\000'::bytea;</literal></entry>

1147

1148

</row>

1149

1150

<row>

1151

1152

<entry>single quote</entry>

1153

1154

<entry><literal>SELECT '\''::bytea;</literal></entry>

1155

1156

</row>

1157

1158

<row>

1159

1160

<entry>backslash</entry>

1161

1162

<entry><literal>SELECT '\\\\'::bytea;</literal></entry>

1163

1164

</row>

1165

1166

<row>

1167

1168

<entry><quote>non-printable</quote> octets</entry>

1169

<entry><literal>'\\<replaceable>xxx'</></literal> (octal value)</entry>

1170

<entry><literal>SELECT '\\001'::bytea;</literal></entry>

1171

1172

</row>

1173

1174

</tbody>

1175

</tgroup>

1176

</table>

1177

1178

<para>

1179

The requirement to escape <quote>non-printable</quote> octets actually

1180

varies depending on locale settings. In some instances you can get away

1181

with leaving them unescaped. Note that the result in each of the examples

1182

in <xref linkend="datatype-binary-sqlesc"> was exactly one octet in

1183

length, even though the output representation of the zero octet and

1184

backslash are more than one character.

1185

</para>

1186

1187

<para>

1188

The reason that you have to write so many backslashes, as shown in

1189

<xref linkend="datatype-binary-sqlesc">, is that an input string

1190

written as a string literal must pass through two parse phases in

1191

the <productname>PostgreSQL</productname> server. The first

1192

backslash of each pair is interpreted as an escape character by

1193

the string-literal parser and is therefore consumed, leaving the

1194

second backslash of the pair. The remaining backslash is then

1195

recognized by the <type>bytea</type> input function as starting

1196

either a three digit octal value or escaping another backslash.

1197

For example, a string literal passed to the server as

1198

<literal>'\\001'</literal> becomes <literal>\001</literal> after

1199

passing through the string-literal parser. The

1200

<literal>\001</literal> is then sent to the <type>bytea</type>

1201

input function, where it is converted to a single octet with a

1202

decimal value of 1. Note that the apostrophe character is not

1203

treated specially by <type>bytea</type>, so it follows the normal

1204

rules for string literals. (See also <xref

1205

linkend="sql-syntax-strings">.)

1206

</para>

1207

1208

<para>

1209

<type>Bytea</type> octets are also escaped in the output. In general, each

1210

<quote>non-printable</quote> octet is converted into

1211

its equivalent three-digit octal value and preceded by one backslash.

1212

Most <quote>printable</quote> octets are represented by their standard

1213

representation in the client character set. The octet with decimal

1214

value 92 (backslash) has a special alternative output representation.

1215

Details are in <xref linkend="datatype-binary-resesc">.

1216

</para>

1217

1218

1219

<title><type>bytea</> Output Escaped Octets</title>

1220

1221

<thead>

1222

<row>

1223

<entry>Decimal Octet Value</entry>

1224

<entry>Description</entry>

1225

<entry>Escaped Output Representation</entry>

1226

<entry>Example</entry>

1227

<entry>Output Result</entry>

1228

</row>

1229

</thead>

1230

1231

<tbody>

1232

1233

<row>

1234

1235

<entry>backslash</entry>

1236

1237

<entry><literal>SELECT '\\134'::bytea;</literal></entry>

1238

1239

</row>

1240

1241

<row>

1242

1243

<entry><quote>non-printable</quote> octets</entry>

1244

<entry><literal>\<replaceable>xxx</></literal> (octal value)</entry>

1245

<entry><literal>SELECT '\\001'::bytea;</literal></entry>

1246

1247

</row>

1248

1249

<row>

1250

1251

<entry><quote>printable</quote> octets</entry>

1252

<entry>client character set representation</entry>

1253

<entry><literal>SELECT '\\176'::bytea;</literal></entry>

1254

1255

</row>

1256

1257

</tbody>

1258

</tgroup>

1259

</table>

1260

1261

<para>

1262

Depending on the front end to <productname>PostgreSQL</> you use,

1263

you may have additional work to do in terms of escaping and

1264

unescaping <type>bytea</type> strings. For example, you may also

1265

have to escape line feeds and carriage returns if your interface

1266

automatically translates these.

1267

</para>

1268

1269

<para>

1270

The <acronym>SQL</acronym> standard defines a different binary

1271

string type, called <type>BLOB</type> or <type>BINARY LARGE

1272

OBJECT</type>. The input format is different from

1273

<type>bytea</type>, but the provided functions and operators are

1274

mostly the same.

1275

</para>

1276

</sect1>

1277

1278

1279

1280

<title>Date/Time Types</title>

1281

1282

1283

1284

</indexterm>

1285

1286

1287

</indexterm>

1288

1289

<primary>time without time zone</primary>

1290

</indexterm>

1291

1292

1293

</indexterm>

1294

1295

<primary>timestamp</primary>

1296

</indexterm>

1297

1298

<primary>timestamp with time zone</primary>

1299

</indexterm>

1300

1301

<primary>timestamp without time zone</primary>

1302

</indexterm>

1303

1304

<primary>interval</primary>

1305

</indexterm>

1306

1307

1308

</indexterm>

1309

1310

<para>

1311

<productname>PostgreSQL</productname> supports the full set of

1312

<acronym>SQL</acronym> date and time types, shown in <xref

1313

linkend="datatype-datetime-table">. The operations available

1314

on these data types are described in

1315

<xref linkend="functions-datetime">.

1316

</para>

1317

1318

1319

<title>Date/Time Types</title>

1320

1321

<thead>

1322

<row>

1323

1324

<entry>Storage Size</entry>

1325

<entry>Description</entry>

1326

<entry>Low Value</entry>

1327

<entry>High Value</entry>

1328

<entry>Resolution</entry>

1329

</row>

1330

</thead>

1331

<tbody>

1332

<row>

1333

<entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>

1334

<entry>8 bytes</entry>

1335

1336

1337

1338

<entry>1 microsecond / 14 digits</entry>

1339

</row>

1340

<row>

1341

<entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>

1342

<entry>8 bytes</entry>

1343

1344

1345

1346

<entry>1 microsecond / 14 digits</entry>

1347

</row>

1348

<row>

1349

<entry><type>interval [ (<replaceable>p</replaceable>) ]</type></entry>

1350

<entry>12 bytes</entry>

1351

<entry>time intervals</entry>

1352

<entry>-178000000 years</entry>

1353

<entry>178000000 years</entry>

1354

<entry>1 microsecond / 14 digits</entry>

1355

</row>

1356

<row>

1357

1358

<entry>4 bytes</entry>

1359

<entry>dates only</entry>

1360

1361

1362

1363

</row>

1364

<row>

1365

<entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>

1366

<entry>8 bytes</entry>

1367

<entry>times of day only</entry>

1368

1369

1370

<entry>1 microsecond / 14 digits</entry>

1371

</row>

1372

<row>

1373

1374

<entry>12 bytes</entry>

1375

<entry>times of day only, with time zone</entry>

1376

1377

1378

<entry>1 microsecond / 14 digits</entry>

1379

</row>

1380

</tbody>

1381

</tgroup>

1382

</table>

1383

1384

<note>

1385

<para>

1386

Prior to <productname>PostgreSQL</productname> 7.3, writing just

1387

<type>timestamp</type> was equivalent to <type>timestamp with

1388

time zone</type>. This was changed for SQL compliance.

1389

</para>

1390

</note>

1391

1392

<para>

1393

<type>time</type>, <type>timestamp</type>, and

1394

<type>interval</type> accept an optional precision value

1395

<replaceable>p</replaceable> which specifies the number of

1396

fractional digits retained in the seconds field. By default, there

1397

is no explicit bound on precision. The allowed range of

1398

<replaceable>p</replaceable> is from 0 to 6 for the

1399

<type>timestamp</type> and <type>interval</type> types.

1400

</para>

1401

1402

<note>

1403

<para>

1404

When <type>timestamp</> values are stored as double precision floating-point

1405

numbers (currently the default), the effective limit of precision

1406

may be less than 6. <type>timestamp</type> values are stored as seconds

1407

before or after midnight 2000-01-01. Microsecond precision is achieved for

1408

dates within a few years of 2000-01-01, but the precision degrades for

1409

dates further away. When <type>timestamp</type> values are stored as

1410

eight-byte integers (a compile-time

1411

option), microsecond precision is available over the full range of

1412

values. However eight-byte integer timestamps have a more limited range of

1413

dates than shown above: from 4713 BC up to 294276 AD. The same

1414

compile-time option also determines whether <type>time</type> and

1415

<type>interval</type> values are stored as floating-point or eight-byte

1416

integers. In the floating-point case, large <type>interval</type> values

1417

degrade in precision as the size of the interval increases.

1418

</para>

1419

</note>

1420

1421

<para>

1422

For the <type>time</type> types, the allowed range of

1423

<replaceable>p</replaceable> is from 0 to 6 when eight-byte integer

1424

storage is used, or from 0 to 10 when floating-point storage is used.

1425

</para>

1426

1427

<para>

1428

The type <type>time with time zone</type> is defined by the SQL

1429

standard, but the definition exhibits properties which lead to

1430

questionable usefulness. In most cases, a combination of

1431

<type>date</type>, <type>time</type>, <type>timestamp without time

1432

zone</type>, and <type>timestamp with time zone</type> should

1433

provide a complete range of date/time functionality required by

1434

any application.

1435

</para>

1436

1437

<para>

1438

The types <type>abstime</type>

1439

and <type>reltime</type> are lower precision types which are used internally.

1440

You are discouraged from using these types in new

1441

applications and are encouraged to move any old

1442

ones over when appropriate. Any or all of these internal types

1443

might disappear in a future release.

1444

</para>

1445

1446

1447

<title>Date/Time Input</title>

1448

1449

<para>

1450

Date and time input is accepted in almost any reasonable format, including

1451

ISO 8601, <acronym>SQL</acronym>-compatible,

1452

traditional <productname>POSTGRES</productname>, and others.

1453

For some formats, ordering of month, day, and year in date input is

1454

ambiguous and there is support for specifying the expected

1455

ordering of these fields. Set the <xref linkend="guc-datestyle"> parameter

1456

to <literal>MDY</> to select month-day-year interpretation,

1457

<literal>DMY</> to select day-month-year interpretation, or

1458

<literal>YMD</> to select year-month-day interpretation.

1459

</para>

1460

1461

<para>

1462

<productname>PostgreSQL</productname> is more flexible in

1463

handling date/time input than the

1464

<acronym>SQL</acronym> standard requires.

1465

See <xref linkend="datetime-appendix">

1466

for the exact parsing rules of date/time input and for the

1467

recognized text fields including months, days of the week, and

1468

time zones.

1469

</para>

1470

1471

<para>

1472

Remember that any date or time literal input needs to be enclosed

1473

in single quotes, like text strings. Refer to

1474

<xref linkend="sql-syntax-constants-generic"> for more

1475

information.

1476

<acronym>SQL</acronym> requires the following syntax

1477

1478

<replaceable>type</replaceable> [ (<replaceable>p</replaceable>) ] '<replaceable>value</replaceable>'

1479

</synopsis>

1480

where <replaceable>p</replaceable> in the optional precision

1481

specification is an integer corresponding to the number of

1482

fractional digits in the seconds field. Precision can be

1483

specified for <type>time</type>, <type>timestamp</type>, and

1484

<type>interval</type> types. The allowed values are mentioned

1485

above. If no precision is specified in a constant specification,

1486

it defaults to the precision of the literal value.

1487

</para>

1488

1489

<sect3>

1490

<title>Dates</title>

1491

1492

1493

1494

</indexterm>

1495

1496

<para>

1497

<xref linkend="datatype-datetime-date-table"> shows some possible

1498

inputs for the <type>date</type> type.

1499

</para>

1500

1501

1502

<title>Date Input</title>

1503

1504

<thead>

1505

<row>

1506

<entry>Example</entry>

1507

<entry>Description</entry>

1508

</row>

1509

</thead>

1510

<tbody>

1511

<row>

1512

<entry>January 8, 1999</entry>

1513

<entry>unambiguous in any <varname>datestyle</varname> input mode</entry>

1514

</row>

1515

<row>

1516

1517

<entry>ISO 8601; January 8 in any mode

1518

(recommended format)</entry>

1519

</row>

1520

<row>

1521

1522

<entry>January 8 in <literal>MDY</> mode;

1523

August 1 in <literal>DMY</> mode</entry>

1524

</row>

1525

<row>

1526

1527

<entry>January 18 in <literal>MDY</> mode;

1528

rejected in other modes</entry>

1529

</row>

1530

<row>

1531

1532

<entry>January 2, 2003 in <literal>MDY</> mode;

1533

February 1, 2003 in <literal>DMY</> mode;

1534

February 3, 2001 in <literal>YMD</> mode

1535

</entry>

1536

</row>

1537

<row>

1538

1539

<entry>January 8 in any mode</entry>

1540

</row>

1541

<row>

1542

1543

<entry>January 8 in any mode</entry>

1544

</row>

1545

<row>

1546

1547

<entry>January 8 in any mode</entry>

1548

</row>

1549

<row>

1550

1551

<entry>January 8 in <literal>YMD</> mode, else error</entry>

1552

</row>

1553

<row>

1554

1555

<entry>January 8, except error in <literal>YMD</> mode</entry>

1556

</row>

1557

<row>

1558

1559

<entry>January 8, except error in <literal>YMD</> mode</entry>

1560

</row>

1561

<row>

1562

1563

<entry>ISO 8601; January 8, 1999 in any mode</entry>

1564

</row>

1565

<row>

1566

1567

<entry>ISO 8601; January 8, 1999 in any mode</entry>

1568

</row>

1569

<row>

1570

1571

1572

</row>

1573

<row>

1574

1575

<entry>Julian day</entry>

1576

</row>

1577

<row>

1578

<entry>January 8, 99 BC</entry>

1579

<entry>year 99 before the Common Era</entry>

1580

</row>

1581

</tbody>

1582

</tgroup>

1583

</table>

1584

</sect3>

1585

1586

<sect3>

1587

<title>Times</title>

1588

1589

1590

1591

</indexterm>

1592

1593

<primary>time without time zone</primary>

1594

</indexterm>

1595

1596

1597

</indexterm>

1598

1599

<para>

1600

The time-of-day types are <type>time [

1601

(<replaceable>p</replaceable>) ] without time zone</type> and

1602

<type>time [ (<replaceable>p</replaceable>) ] with time

1603

zone</type>. Writing just <type>time</type> is equivalent to

1604

<type>time without time zone</type>.

1605

</para>

1606

1607

<para>

1608

Valid input for these types consists of a time of day followed

1609

by an optional time zone. (See <xref

1610

linkend="datatype-datetime-time-table">

1611

and <xref linkend="datatype-timezone-table">.) If a time zone is

1612

specified in the input for <type>time without time zone</type>,

1613

it is silently ignored.

1614

</para>

1615

1616

1617

<title>Time Input</title>

1618

1619

<thead>

1620

<row>

1621

<entry>Example</entry>

1622

<entry>Description</entry>

1623

</row>

1624

</thead>

1625

<tbody>

1626

<row>

1627

1628

1629

</row>

1630

<row>

1631

1632

1633

</row>

1634

<row>

1635

1636

1637

</row>

1638

<row>

1639

1640

1641

</row>

1642

<row>

1643

1644

<entry>same as 04:05; AM does not affect value</entry>

1645

</row>

1646

<row>

1647

1648

<entry>same as 16:05; input hour must be <= 12</entry>

1649

</row>

1650

<row>

1651

1652

1653

</row>

1654

<row>

1655

1656

1657

</row>

1658

<row>

1659

1660

1661

</row>

1662

<row>

1663

1664

1665

</row>

1666

<row>

1667

1668

<entry>time zone specified by name</entry>

1669

</row>

1670

</tbody>

1671

</tgroup>

1672

</table>

1673

1674

1675

<title>Time Zone Input</title>

1676

1677

<thead>

1678

<row>

1679

<entry>Example</entry>

1680

<entry>Description</entry>

1681

</row>

1682

</thead>

1683

<tbody>

1684

<row>

1685

1686

<entry>Pacific Standard Time</entry>

1687

</row>

1688

<row>

1689

1690

<entry>ISO-8601 offset for PST</entry>

1691

</row>

1692

<row>

1693

1694

<entry>ISO-8601 offset for PST</entry>

1695

</row>

1696

<row>

1697

1698

<entry>ISO-8601 offset for PST</entry>

1699

</row>

1700

<row>

1701

1702

<entry>Military abbreviation for UTC</entry>

1703

</row>

1704

<row>

1705

1706

<entry>Short form of <literal>zulu</literal></entry>

1707

</row>

1708

</tbody>

1709

</tgroup>

1710

</table>

1711

1712

<para>

1713

Refer to <xref linkend="datetime-appendix"> for a list of

1714

time zone names that are recognized for input.

1715

</para>

1716

</sect3>

1717

1718

<sect3>

1719

<title>Time Stamps</title>

1720

1721

1722

<primary>timestamp</primary>

1723

</indexterm>

1724

1725

1726

<primary>timestamp with time zone</primary>

1727

</indexterm>

1728

1729

1730

<primary>timestamp without time zone</primary>

1731

</indexterm>

1732

1733

<para>

1734

Valid input for the time stamp types consists of a concatenation

1735

of a date and a time, followed by an optional time zone,

1736

followed by an optional <literal>AD</literal> or <literal>BC</literal>.

1737

(Alternatively, <literal>AD</literal>/<literal>BC</literal> can appear

1738

before the time zone, but this is not the preferred ordering.)

1739

Thus

1740

1741

1742

1999-01-08 04:05:06

1743

</programlisting>

1744

and

1745

1746

1999-01-08 04:05:06 -8:00

1747

</programlisting>

1748

1749

are valid values, which follow the <acronym>ISO</acronym> 8601

1750

standard. In addition, the wide-spread format

1751

1752

1753

January 8 04:05:06 1999 PST

1754

</programlisting>

1755

is supported.

1756

</para>

1757

1758

<para>

1759

The <acronym>SQL</acronym> standard differentiates <type>timestamp without time zone</type>

1760

and <type>timestamp with time zone</type> literals by the existence of a

1761

<quote>+</quote>; or <quote>-</quote>. Hence, according to the standard,

1762

<programlisting>TIMESTAMP '2004-10-19 10:23:54'</programlisting>

1763

is a <type>timestamp without time zone</type>, while

1764

<programlisting>TIMESTAMP '2004-10-19 10:23:54+02'</programlisting>

1765

is a <type>timestamp with time zone</type>.

1766

<productname>PostgreSQL</productname>

1767

differs from the standard by requiring that <type>timestamp with time zone</type>

1768

literals be explicitly typed:

1769

<programlisting>TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'</programlisting>

1770

If a literal is not explicitly indicated as being of <type>timestamp with time zone</type>,

1771

PostgreSQL will silently ignore any time zone indication in the literal.

1772

That is, the resulting date/time value is derived from the date/time

1773

fields in the input value, and is not adjusted for time zone.

1774

</para>

1775

1776

<para>

1777

For <type>timestamp with time zone</type>, the internally stored

1778

value is always in UTC (Universal

1779

Coordinated Time, traditionally known as Greenwich Mean Time,

1780

<acronym>GMT</>). An input value that has an explicit

1781

time zone specified is converted to UTC using the appropriate offset

1782

for that time zone. If no time zone is stated in the input string,

1783

then it is assumed to be in the time zone indicated by the system's

1784

<xref linkend="guc-timezone"> parameter, and is converted to UTC using the

1785

offset for the <varname>timezone</> zone.

1786

</para>

1787

1788

<para>

1789

When a <type>timestamp with time

1790

zone</type> value is output, it is always converted from UTC to the

1791

current <varname>timezone</> zone, and displayed as local time in that

1792

zone. To see the time in another time zone, either change

1793

<varname>timezone</> or use the <literal>AT TIME ZONE</> construct

1794

(see <xref linkend="functions-datetime-zoneconvert">).

1795

</para>

1796

1797

<para>

1798

Conversions between <type>timestamp without time zone</type> and

1799

<type>timestamp with time zone</type> normally assume that the

1800

<type>timestamp without time zone</type> value should be taken or given

1801

as <varname>timezone</> local time. A different zone reference can

1802

be specified for the conversion using <literal>AT TIME ZONE</>.

1803

</para>

1804

</sect3>

1805

1806

<sect3>

1807

<title>Intervals</title>

1808

1809

1810

<primary>interval</primary>

1811

</indexterm>

1812

1813

<para>

1814

<type>interval</type> values can be written with the following syntax:

1815

1816

1817

<optional>@</> <replaceable>quantity</> <replaceable>unit</> <optional><replaceable>quantity</> <replaceable>unit</>...</> <optional><replaceable>direction</></optional>

1818

</programlisting>

1819

1820

Where: <replaceable>quantity</> is a number (possibly signed);

1821

<replaceable>unit</> is <literal>second</literal>,

1822

<literal>minute</literal>, <literal>hour</literal>, <literal>day</literal>,

1823

<literal>week</literal>, <literal>month</literal>, <literal>year</literal>,

1824

<literal>decade</literal>, <literal>century</literal>, <literal>millennium</literal>,

1825

or abbreviations or plurals of these units;

1826

<replaceable>direction</> can be <literal>ago</literal> or

1827

empty. The at sign (<literal>@</>) is optional noise. The amounts

1828

of different units are implicitly added up with appropriate

1829

sign accounting.

1830

</para>

1831

1832

<para>

1833

Quantities of days, hours, minutes, and seconds can be specified without

1834

explicit unit markings. For example, <literal>'1 12:59:10'</> is read

1835

the same as <literal>'1 day 12 hours 59 min 10 sec'</>.

1836

</para>

1837

1838

<para>

1839

The optional precision

1840

<replaceable>p</replaceable> should be between 0 and 6, and

1841

defaults to the precision of the input literal.

1842

</para>

1843

</sect3>

1844

1845

<sect3>

1846

<title>Special Values</title>

1847

1848

1849

1850

<secondary>constants</secondary>

1851

</indexterm>

1852

1853

1854

1855

<secondary>constants</secondary>

1856

</indexterm>

1857

1858

<para>

1859

<productname>PostgreSQL</productname> supports several

1860

special date/time input values for convenience, as shown in <xref

1861

linkend="datatype-datetime-special-table">. The values

1862

<literal>infinity</literal> and <literal>-infinity</literal>

1863

are specially represented inside the system and will be displayed

1864

the same way; but the others are simply notational shorthands

1865

that will be converted to ordinary date/time values when read.

1866

(In particular, <literal>now</> and related strings are converted

1867

to a specific time value as soon as they are read.)

1868

All of these values need to be written in single quotes when used

1869

as constants in SQL commands.

1870

</para>

1871

1872

1873

<title>Special Date/Time Inputs</title>

1874

1875

<thead>

1876

<row>

1877

<entry>Input String</entry>

1878

<entry>Valid Types</entry>

1879

<entry>Description</entry>

1880

</row>

1881

</thead>

1882

<tbody>

1883

<row>

1884

<entry><literal>epoch</literal></entry>

1885

<entry><type>date</type>, <type>timestamp</type></entry>

1886

<entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>

1887

</row>

1888

<row>

1889

<entry><literal>infinity</literal></entry>

1890

<entry><type>timestamp</type></entry>

1891

<entry>later than all other time stamps</entry>

1892

</row>

1893

<row>

1894

<entry><literal>-infinity</literal></entry>

1895

<entry><type>timestamp</type></entry>

1896

<entry>earlier than all other time stamps</entry>

1897

</row>

1898

<row>

1899

1900

<entry><type>date</type>, <type>time</type>, <type>timestamp</type></entry>

1901

<entry>current transaction's start time</entry>

1902

</row>

1903

<row>

1904

<entry><literal>today</literal></entry>

1905

<entry><type>date</type>, <type>timestamp</type></entry>

1906

<entry>midnight today</entry>

1907

</row>

1908

<row>

1909

<entry><literal>tomorrow</literal></entry>

1910

<entry><type>date</type>, <type>timestamp</type></entry>

1911

<entry>midnight tomorrow</entry>

1912

</row>

1913

<row>

1914

<entry><literal>yesterday</literal></entry>

1915

<entry><type>date</type>, <type>timestamp</type></entry>

1916

<entry>midnight yesterday</entry>

1917

</row>

1918

<row>

1919

<entry><literal>allballs</literal></entry>

1920

1921

1922

</row>

1923

</tbody>

1924

</tgroup>

1925

</table>

1926

1927

<para>

1928

The following <acronym>SQL</acronym>-compatible functions can also

1929

be used to obtain the current time value for the corresponding data

1930

type:

1931

<literal>CURRENT_DATE</literal>, <literal>CURRENT_TIME</literal>,

1932

<literal>CURRENT_TIMESTAMP</literal>, <literal>LOCALTIME</literal>,

1933

<literal>LOCALTIMESTAMP</literal>. The latter four accept an

1934

optional precision specification. (See <xref

1935

linkend="functions-datetime-current">.) Note however that these are

1936

SQL functions and are <emphasis>not</> recognized as data input strings.

1937

</para>

1938

1939

</sect3>

1940

</sect2>

1941

1942

1943

<title>Date/Time Output</title>

1944

1945

1946

1947

<secondary>output format</secondary>

1948

<seealso>formatting</seealso>

1949

</indexterm>

1950

1951

1952

1953

<secondary>output format</secondary>

1954

<seealso>formatting</seealso>

1955

</indexterm>

1956

1957

<para>

1958

The output format of the date/time types can be set to one of the four

1959

styles ISO 8601,

1960

<acronym>SQL</acronym> (Ingres), traditional POSTGRES, and

1961

German, using the command <literal>SET datestyle</literal>. The default

1962

is the <acronym>ISO</acronym> format. (The

1963

<acronym>SQL</acronym> standard requires the use of the ISO 8601

1964

format. The name of the <quote>SQL</quote> output format is a

1965

historical accident.) <xref

1966

linkend="datatype-datetime-output-table"> shows examples of each

1967

output style. The output of the <type>date</type> and

1968

<type>time</type> types is of course only the date or time part

1969

in accordance with the given examples.

1970

</para>

1971

1972

1973

<title>Date/Time Output Styles</title>

1974

1975

<thead>

1976

<row>

1977

<entry>Style Specification</entry>

1978

<entry>Description</entry>

1979

<entry>Example</entry>

1980

</row>

1981

</thead>

1982

<tbody>

1983

<row>

1984

1985

<entry>ISO 8601/SQL standard</entry>

1986

1987

</row>

1988

<row>

1989

1990

<entry>traditional style</entry>

1991

1992

</row>

1993

<row>

1994

<entry>POSTGRES</entry>

1995

<entry>original style</entry>

1996

1997

</row>

1998

<row>

1999

<entry>German</entry>

2000

<entry>regional style</entry>

2001

2002

</row>

2003

</tbody>

2004

</tgroup>

2005

</table>

2006

2007

<para>

2008

In the <acronym>SQL</acronym> and POSTGRES styles, day appears before

2009

month if DMY field ordering has been specified, otherwise month appears

2010

before day.

2011

(See <xref linkend="datatype-datetime-input">

2012

for how this setting also affects interpretation of input values.)

2013

<xref linkend="datatype-datetime-output2-table"> shows an

2014

example.

2015

</para>

2016

2017

2018

<title>Date Order Conventions</title>

2019

2020

<thead>

2021

<row>

2022

<entry><varname>datestyle</varname> Setting</entry>

2023

<entry>Input Ordering</entry>

2024

<entry>Example Output</entry>

2025

</row>

2026

</thead>

2027

<tbody>

2028

<row>

2029

2030

<entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>

2031

2032

</row>

2033

<row>

2034

2035

<entry><replaceable>month</replaceable>/<replaceable>day</replaceable>/<replaceable>year</replaceable></entry>

2036

2037

</row>

2038

<row>

2039

<entry><literal>Postgres, DMY</></entry>

2040

<entry><replaceable>day</replaceable>/<replaceable>month</replaceable>/<replaceable>year</replaceable></entry>

2041

2042

</row>

2043

</tbody>

2044

</tgroup>

2045

</table>

2046

2047

<para>

2048

<type>interval</type> output looks like the input format, except

2049

that units like <literal>century</literal> or

2050

<literal>week</literal> are converted to years and days and

2051

<literal>ago</literal> is converted to an appropriate sign. In

2052

ISO mode the output looks like

2053

2054

2055

<optional> <replaceable>quantity</> <replaceable>unit</> <optional> ... </> </> <optional> <replaceable>days</> </> <optional> <replaceable>hours</>:<replaceable>minutes</>:<replaceable>seconds</> </optional>

2056

</programlisting>

2057

</para>

2058

2059

<para>

2060

The date/time styles can be selected by the user using the

2061

<command>SET datestyle</command> command, the <xref

2062

linkend="guc-datestyle"> parameter in the

2063

<filename>postgresql.conf</filename> configuration file, or the

2064

<envar>PGDATESTYLE</envar> environment variable on the server or

2065

client. The formatting function <function>to_char</function>

2066

(see <xref linkend="functions-formatting">) is also available as

2067

a more flexible way to format the date/time output.

2068

</para>

2069

</sect2>

2070

2071

2072

<title>Time Zones</title>

2073

2074

2075

2076

</indexterm>

2077

2078

<para>

2079

Time zones, and time-zone conventions, are influenced by

2080

political decisions, not just earth geometry. Time zones around the

2081

world became somewhat standardized during the 1900's,

2082

but continue to be prone to arbitrary changes, particularly with

2083

respect to daylight-savings rules.

2084

<productname>PostgreSQL</productname> currently supports daylight-savings

2085

rules over the time period 1902 through 2038 (corresponding to the full

2086

range of conventional Unix system time). Times outside that range are

2087

taken to be in <quote>standard time</> for the selected time zone, no

2088

matter what part of the year they fall in.

2089

</para>

2090

2091

<para>

2092

<productname>PostgreSQL</productname> endeavors to be compatible with

2093

the <acronym>SQL</acronym> standard definitions for typical usage.

2094

However, the <acronym>SQL</acronym> standard has an odd mix of date and

2095

time types and capabilities. Two obvious problems are:

2096

2097

2098

2099

<para>

2100

Although the <type>date</type> type

2101

does not have an associated time zone, the

2102

<type>time</type> type can.

2103

Time zones in the real world have little meaning unless

2104

associated with a date as well as a time,

2105

since the offset may vary through the year with daylight-saving

2106

time boundaries.

2107

</para>

2108

</listitem>

2109

2110

2111

<para>

2112

The default time zone is specified as a constant numeric offset

2113

from <acronym>UTC</>. It is therefore not possible to adapt to

2114

daylight-saving time when doing date/time arithmetic across

2115

<acronym>DST</acronym> boundaries.

2116

</para>

2117

</listitem>

2118

2119

</itemizedlist>

2120

</para>

2121

2122

<para>

2123

To address these difficulties, we recommend using date/time types

2124

that contain both date and time when using time zones. We

2125

recommend <emphasis>not</emphasis> using the type <type>time with

2126

time zone</type> (though it is supported by

2127

<productname>PostgreSQL</productname> for legacy applications and

2128

for compliance with the <acronym>SQL</acronym> standard).

2129

<productname>PostgreSQL</productname> assumes

2130

your local time zone for any type containing only date or time.

2131

</para>

2132

2133

<para>

2134

All timezone-aware dates and times are stored internally in

2135

<acronym>UTC</acronym>. They are converted to local time

2136

in the zone specified by the <xref linkend="guc-timezone"> configuration

2137

parameter before being displayed to the client.

2138

</para>

2139

2140

<para>

2141

The <xref linkend="guc-timezone"> configuration parameter can

2142

be set in the file <filename>postgresql.conf</>, or in any of the

2143

other standard ways described in <xref linkend="runtime-config">.

2144

There are also several special ways to set it:

2145

2146

2147

2148

<para>

2149

If <varname>timezone</> is not specified in

2150

<filename>postgresql.conf</> nor as a postmaster command-line switch,

2151

the server attempts to use the value of the <envar>TZ</envar>

2152

environment variable as the default time zone. If <envar>TZ</envar>

2153

is not defined or is not any of the time zone names known to

2154

<productname>PostgreSQL</productname>, the server attempts to

2155

determine the operating system's default time zone by checking the

2156

behavior of the C library function <literal>localtime()</>. The

2157

default time zone is selected as the closest match among

2158

<productname>PostgreSQL</productname>'s known time zones.

2159

</para>

2160

</listitem>

2161

2162

2163

<para>

2164

The <acronym>SQL</acronym> command <command>SET TIME ZONE</command>

2165

sets the time zone for the session. This is an alternative spelling

2166

of <command>SET TIMEZONE TO</> with a more SQL-spec-compatible syntax.

2167

</para>

2168

</listitem>

2169

2170

2171

<para>

2172

The <envar>PGTZ</envar> environment variable, if set at the

2173

client, is used by <application>libpq</application>

2174

applications to send a <command>SET TIME ZONE</command>

2175

command to the server upon connection.

2176

</para>

2177

</listitem>

2178

</itemizedlist>

2179

</para>

2180

2181

<para>

2182

Refer to <xref linkend="datetime-appendix"> for a list of

2183

available time zones.

2184

</para>

2185

2186

</sect2>

2187

2188

2189

<title>Internals</title>

2190

2191

<para>

2192

<productname>PostgreSQL</productname> uses Julian dates

2193

for all date/time calculations. They have the nice property of correctly

2194

predicting/calculating any date more recent than 4713 BC

2195

to far into the future, using the assumption that the length of the

2196

year is 365.2425 days.

2197

</para>

2198

2199

<para>

2200

Date conventions before the 19th century make for interesting reading,

2201

but are not consistent enough to warrant coding into a date/time handler.

2202

</para>

2203

</sect2>

2204

2205

</sect1>

2206

2207

2208

<title>Boolean Type</title>

2209

2210

2211

<primary>Boolean</primary>

2212

2213

</indexterm>

2214

2215

2216

2217

</indexterm>

2218

2219

2220

<primary>false</primary>

2221

</indexterm>

2222

2223

<para>

2224

<productname>PostgreSQL</productname> provides the

2225

standard <acronym>SQL</acronym> type <type>boolean</type>.

2226

<type>boolean</type> can have one of only two states:

2227

<quote>true</quote> or <quote>false</quote>. A third state,

2228

<quote>unknown</quote>, is represented by the

2229

<acronym>SQL</acronym> null value.

2230

</para>

2231

2232

<para>

2233

Valid literal values for the <quote>true</quote> state are:

2234

2235

2236

2237

2238

2239

2240

2241

</simplelist>

2242

For the <quote>false</quote> state, the following values can be

2243

used:

2244

2245

<member><literal>FALSE</literal></member>

2246

2247

<member><literal>'false'</literal></member>

2248

2249

2250

2251

</simplelist>

2252

Using the key words <literal>TRUE</literal> and

2253

<literal>FALSE</literal> is preferred (and

2254

<acronym>SQL</acronym>-compliant).

2255

</para>

2256

2257

2258

<title>Using the <type>boolean</type> type</title>

2259

2260

2261

CREATE TABLE test1 (a boolean, b text);

2262

INSERT INTO test1 VALUES (TRUE, 'sic est');

2263

INSERT INTO test1 VALUES (FALSE, 'non est');

2264

SELECT * FROM test1;

2265

a | b

2266

---+---------

2267

t | sic est

2268

f | non est

2269

2270

SELECT * FROM test1 WHERE a;

2271

a | b

2272

---+---------

2273

t | sic est

2274

</programlisting>

2275

</example>

2276

2277

<para>

2278

<xref linkend="datatype-boolean-example"> shows that

2279

<type>boolean</type> values are output using the letters

2280

<literal>t</literal> and <literal>f</literal>.

2281

</para>

2282

2283

<tip>

2284

<para>

2285

Values of the <type>boolean</type> type cannot be cast directly

2286

to other types (e.g., <literal>CAST

2287

(<replaceable>boolval</replaceable> AS integer)</literal> does

2288

not work). This can be accomplished using the

2289

<literal>CASE</literal> expression: <literal>CASE WHEN

2290

<replaceable>boolval</replaceable> THEN 'value if true' ELSE

2291

'value if false' END</literal>. See <xref

2292

linkend="functions-conditional">.

2293

</para>

2294

</tip>

2295

2296

<para>

2297

<type>boolean</type> uses 1 byte of storage.

2298

</para>

2299

</sect1>

2300

2301

2302

<title>Geometric Types</title>

2303

2304

<para>

2305

Geometric data types represent two-dimensional spatial

2306

objects. <xref linkend="datatype-geo-table"> shows the geometric

2307

types available in <productname>PostgreSQL</productname>. The

2308

most fundamental type, the point, forms the basis for all of the

2309

other types.

2310

</para>

2311

2312

2313

<title>Geometric Types</title>

2314

2315

<thead>

2316

<row>

2317

2318

<entry>Storage Size</entry>

2319

<entry>Representation</entry>

2320

<entry>Description</entry>

2321

</row>

2322

</thead>

2323

<tbody>

2324

<row>

2325

<entry><type>point</type></entry>

2326

<entry>16 bytes</entry>

2327

<entry>Point on the plane</entry>

2328

2329

</row>

2330

<row>

2331

2332

<entry>32 bytes</entry>

2333

<entry>Infinite line (not fully implemented)</entry>

2334

2335

</row>

2336

<row>

2337

2338

<entry>32 bytes</entry>

2339

<entry>Finite line segment</entry>

2340

2341

</row>

2342

<row>

2343

2344

<entry>32 bytes</entry>

2345

<entry>Rectangular box</entry>

2346

2347

</row>

2348

<row>

2349

2350

<entry>16+16n bytes</entry>

2351

<entry>Closed path (similar to polygon)</entry>

2352

2353

</row>

2354

<row>

2355

2356

<entry>16+16n bytes</entry>

2357

2358

2359

</row>

2360

<row>

2361

<entry><type>polygon</type></entry>

2362

<entry>40+16n bytes</entry>

2363

<entry>Polygon (similar to closed path)</entry>

2364

2365

</row>

2366

<row>

2367

<entry><type>circle</type></entry>

2368

<entry>24 bytes</entry>

2369

<entry>Circle</entry>

2370

<entry><(x,y),r> (center and radius)</entry>

2371

</row>

2372

</tbody>

2373

</tgroup>

2374

</table>

2375

2376

<para>

2377

A rich set of functions and operators is available to perform various geometric

2378

operations such as scaling, translation, rotation, and determining

2379

intersections. They are explained in <xref linkend="functions-geometry">.

2380

</para>

2381

2382

<sect2>

2383

<title>Points</title>

2384

2385

2386

<primary>point</primary>

2387

</indexterm>

2388

2389

<para>

2390

Points are the fundamental two-dimensional building block for geometric types.

2391

Values of type <type>point</type> are specified using the following syntax:

2392

2393

2394

( <replaceable>x</replaceable> , <replaceable>y</replaceable> )

2395

2396

</synopsis>

2397

2398

where <replaceable>x</> and <replaceable>y</> are the respective

2399

coordinates as floating-point numbers.

2400

</para>

2401

</sect2>

2402

2403

<sect2>

2404

<title>Line Segments</title>

2405

2406

2407

2408

</indexterm>

2409

2410

2411

<primary>line segment</primary>

2412

</indexterm>

2413

2414

<para>

2415

Line segments (<type>lseg</type>) are represented by pairs of points.

2416

Values of type <type>lseg</type> are specified using the following syntax:

2417

2418

2419

( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )

2420

( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )

2421

2422

</synopsis>

2423

2424

where

2425

2426

and

2427

2428

are the end points of the line segment.

2429

</para>

2430

</sect2>

2431

2432

<sect2>

2433

<title>Boxes</title>

2434

2435

2436

2437

</indexterm>

2438

2439

2440

<primary>rectangle</primary>

2441

</indexterm>

2442

2443

<para>

2444

Boxes are represented by pairs of points that are opposite

2445

corners of the box.

2446

Values of type <type>box</type> are specified using the following syntax:

2447

2448

2449

( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> ) )

2450

( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ( <replaceable>x2</replaceable> , <replaceable>y2</replaceable> )

2451

2452

</synopsis>

2453

2454

where

2455

2456

and

2457

2458

are any two opposite corners of the box.

2459

</para>

2460

2461

<para>

2462

Boxes are output using the first syntax.

2463

The corners are reordered on input to store

2464

the upper right corner, then the lower left corner.

2465

Other corners of the box can be entered, but the lower

2466

left and upper right corners are determined from the input and stored.

2467

</para>

2468

</sect2>

2469

2470

<sect2>

2471

<title>Paths</title>

2472

2473

2474

2475

</indexterm>

2476

2477

<para>

2478

Paths are represented by lists of connected points. Paths can be

2479

<firstterm>open</firstterm>, where

2480

the first and last points in the list are not considered connected, or

2481

<firstterm>closed</firstterm>,

2482

where the first and last points are considered connected.

2483

</para>

2484

2485

<para>

2486

Values of type <type>path</type> are specified using the following syntax:

2487

2488

2489

( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )

2490

[ ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) ]

2491

( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )

2492

( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )

2493

2494

</synopsis>

2495

2496

where the points are the end points of the line segments

2497

comprising the path. Square brackets (<literal>[]</>) indicate

2498

an open path, while parentheses (<literal>()</>) indicate a

2499

closed path.

2500

</para>

2501

2502

<para>

2503

Paths are output using the first syntax.

2504

</para>

2505

</sect2>

2506

2507

<sect2>

2508

<title>Polygons</title>

2509

2510

2511

<primary>polygon</primary>

2512

</indexterm>

2513

2514

<para>

2515

Polygons are represented by lists of points (the vertexes of the

2516

polygon). Polygons should probably be

2517

considered equivalent to closed paths, but are stored differently

2518

and have their own set of support routines.

2519

</para>

2520

2521

<para>

2522

Values of type <type>polygon</type> are specified using the following syntax:

2523

2524

2525

( ( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> ) )

2526

( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> ) , ... , ( <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )

2527

( <replaceable>x1</replaceable> , <replaceable>y1</replaceable> , ... , <replaceable>xn</replaceable> , <replaceable>yn</replaceable> )

2528

2529

</synopsis>

2530

2531

where the points are the end points of the line segments

2532

comprising the boundary of the polygon.

2533

</para>

2534

2535

<para>

2536

Polygons are output using the first syntax.

2537

</para>

2538

</sect2>

2539

2540

<sect2>

2541

<title>Circles</title>

2542

2543

2544

<primary>circle</primary>

2545

</indexterm>

2546

2547

<para>

2548

Circles are represented by a center point and a radius.

2549

Values of type <type>circle</type> are specified using the following syntax:

2550

2551

2552

< ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> >

2553

( ( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable> )

2554

( <replaceable>x</replaceable> , <replaceable>y</replaceable> ) , <replaceable>r</replaceable>

2555

2556

</synopsis>

2557

2558

where

2559

2560

is the center and <replaceable>r</replaceable> is the radius of the circle.

2561

</para>

2562

2563

<para>

2564

Circles are output using the first syntax.

2565

</para>

2566

</sect2>

2567

2568

</sect1>

2569

2570

2571

<title>Network Address Types</title>

2572

2573

2574

<primary>network</primary>

2575

<secondary>data types</secondary>

2576

</indexterm>

2577

2578

<para>

2579

<productname>PostgreSQL</> offers data types to store IPv4, IPv6, and MAC

2580

addresses, as shown in <xref linkend="datatype-net-types-table">. It

2581

is preferable to use these types instead of plain text types to store

2582

network addresses, because

2583

these types offer input error checking and several specialized

2584

operators and functions (see <xref linkend="functions-net">).

2585

</para>

2586

2587

2588

<title>Network Address Types</title>

2589

2590

<thead>

2591

<row>

2592

2593

<entry>Storage Size</entry>

2594

<entry>Description</entry>

2595

</row>

2596

</thead>

2597

<tbody>

2598

2599

<row>

2600

2601

<entry>12 or 24 bytes</entry>

2602

<entry>IPv4 and IPv6 networks</entry>

2603

</row>

2604

2605

<row>

2606

2607

<entry>12 or 24 bytes</entry>

2608

<entry>IPv4 and IPv6 hosts and networks</entry>

2609

</row>

2610

2611

<row>

2612

<entry><type>macaddr</type></entry>

2613

<entry>6 bytes</entry>

2614

<entry>MAC addresses</entry>

2615

</row>

2616

2617

</tbody>

2618

</tgroup>

2619

</table>

2620

2621

<para>

2622

When sorting <type>inet</type> or <type>cidr</type> data types,

2623

IPv4 addresses will always sort before IPv6 addresses, including

2624

IPv4 addresses encapsulated or mapped into IPv6 addresses, such as

2625

::10.2.3.4 or ::ffff::10.4.3.2.

2626

</para>

2627

2628

2629

2630

2631

2632

2633

2634

</indexterm>

2635

2636

<para>

2637

The <type>inet</type> type holds an IPv4 or IPv6 host address, and

2638

optionally the identity of the subnet it is in, all in one field.

2639

The subnet identity is represented by stating how many bits of

2640

the host address represent the network address (the

2641

<quote>netmask</quote>). If the netmask is 32 and the address is IPv4,

2642

then the value does not indicate a subnet, only a single host.

2643

In IPv6, the address length is 128 bits, so 128 bits specify a

2644

unique host address. Note that if you

2645

want to accept networks only, you should use the

2646

<type>cidr</type> type rather than <type>inet</type>.

2647

</para>

2648

2649

<para>

2650

The input format for this type is

2651

<replaceable class="parameter">address/y</replaceable>

2652

where

2653

<replaceable class="parameter">address</replaceable>

2654

is an IPv4 or IPv6 address and

2655

2656

is the number of bits in the netmask. If the

2657

2658

part is left off, then the

2659

netmask is 32 for IPv4 and 128 for IPv6, so the value represents

2660

just a single host. On display, the

2661

2662

portion is suppressed if the netmask specifies a single host.

2663

</para>

2664

</sect2>

2665

2666

2667

2668

2669

2670

2671

</indexterm>

2672

2673

<para>

2674

The <type>cidr</type> type holds an IPv4 or IPv6 network specification.

2675

Input and output formats follow Classless Internet Domain Routing

2676

conventions.

2677

The format for specifying networks is <replaceable

2678

class="parameter">address/y</> where <replaceable

2679

class="parameter">address</> is the network represented as an

2680

IPv4 or IPv6 address, and <replaceable

2681

class="parameter">y</> is the number of bits in the netmask. If

2682

<replaceable class="parameter">y</> is omitted, it is calculated

2683

using assumptions from the older classful network numbering system, except

2684

that it will be at least large enough to include all of the octets

2685

written in the input. It is an error to specify a network address

2686

that has bits set to the right of the specified netmask.

2687

</para>

2688

2689

<para>

2690

<xref linkend="datatype-net-cidr-table"> shows some examples.

2691

</para>

2692

2693

2694

<title><type>cidr</> Type Input Examples</title>

2695

2696

<thead>

2697

<row>

2698

<entry><type>cidr</type> Input</entry>

2699

<entry><type>cidr</type> Output</entry>

2700

<entry><literal><function>abbrev</function>(<type>cidr</type>)</literal></entry>

2701

</row>

2702

</thead>

2703

<tbody>

2704

<row>

2705

2706

2707

2708

</row>

2709

<row>

2710

2711

2712

2713

</row>

2714

<row>

2715

2716

2717

2718

</row>

2719

<row>

2720

2721

2722

2723

</row>

2724

<row>

2725

2726

2727

2728

</row>

2729

<row>

2730

2731

2732

2733

</row>

2734

<row>

2735

2736

2737

2738

</row>

2739

<row>

2740

2741

2742

2743

</row>

2744

<row>

2745

2746

2747

2748

</row>

2749

<row>

2750

2751

2752

2753

</row>

2754

<row>

2755

2756

2757

2758

</row>

2759

<row>

2760

2761

2762

2763

</row>

2764

<row>

2765

2766

2767

2768

</row>

2769

<row>

2770

2771

2772

2773

</row>

2774

<row>

2775

2776

2777

2778

</row>

2779

<row>

2780

2781

2782

2783

</row>

2784

</tbody>

2785

</tgroup>

2786

</table>

2787

</sect2>

2788

2789

2790

2791

2792

<para>

2793

The essential difference between <type>inet</type> and <type>cidr</type>

2794

data types is that <type>inet</type> accepts values with nonzero bits to

2795

the right of the netmask, whereas <type>cidr</type> does not.

2796

</para>

2797

2798

<tip>

2799

<para>

2800

If you do not like the output format for <type>inet</type> or

2801

<type>cidr</type> values, try the functions <function>host</>,

2802

<function>text</>, and <function>abbrev</>.

2803

</para>

2804

</tip>

2805

</sect2>

2806

2807

2808

<title><type>macaddr</></>

2809

2810

2811

<primary>macaddr (data type)</primary>

2812

</indexterm>

2813

2814

2815

<primary>MAC address</primary>

2816

<see>macaddr</see>

2817

</indexterm>

2818

2819

<para>

2820

The <type>macaddr</> type stores MAC addresses, i.e., Ethernet

2821

card hardware addresses (although MAC addresses are used for

2822

other purposes as well). Input is accepted in various customary

2823

formats, including

2824

2825

2826

2827

2828

2829

2830

2831

</simplelist>

2832

2833

which would all specify the same

2834

address. Upper and lower case is accepted for the digits

2835

<literal>a</> through <literal>f</>. Output is always in the

2836

last of the forms shown.

2837

</para>

2838

2839

<para>

2840

The directory <filename class="directory">contrib/mac</filename>

2841

in the <productname>PostgreSQL</productname> source distribution

2842

contains tools that can be used to map MAC addresses to hardware

2843

manufacturer names.

2844

</para>

2845

</sect2>

2846

2847

</sect1>

2848

2849

2850

<title>Bit String Types</title>

2851

2852

2853

<primary>bit string</primary>

2854

2855

</indexterm>

2856

2857

<para>

2858

Bit strings are strings of 1's and 0's. They can be used to store

2859

or visualize bit masks. There are two SQL bit types:

2860

<type>bit(<replaceable>n</replaceable>)</type> and <type>bit

2861

varying(<replaceable>n</replaceable>)</type>, where

2862

<replaceable>n</replaceable> is a positive integer.

2863

</para>

2864

2865

<para>

2866

<type>bit</type> type data must match the length

2867

<replaceable>n</replaceable> exactly; it is an error to attempt to

2868

store shorter or longer bit strings. <type>bit varying</type> data is

2869

of variable length up to the maximum length

2870

<replaceable>n</replaceable>; longer strings will be rejected.

2871

Writing <type>bit</type> without a length is equivalent to

2872

<literal>bit(1)</literal>, while <type>bit varying</type> without a length

2873

specification means unlimited length.

2874

</para>

2875

2876

<note>

2877

<para>

2878

If one explicitly casts a bit-string value to

2879

<type>bit(<replaceable>n</>)</type>, it will be truncated or

2880

zero-padded on the right to be exactly <replaceable>n</> bits,

2881

without raising an error. Similarly,

2882

if one explicitly casts a bit-string value to

2883

<type>bit varying(<replaceable>n</>)</type>, it will be truncated

2884

on the right if it is more than <replaceable>n</> bits.

2885

</para>

2886

</note>

2887

2888

<note>

2889

<para>

2890

Prior to <productname>PostgreSQL</> 7.2, <type>bit</type> data

2891

was always silently truncated or zero-padded on the right, with

2892

or without an explicit cast. This was changed to comply with the

2893

<acronym>SQL</acronym> standard.

2894

</para>

2895

</note>

2896

2897

<para>

2898

Refer to <xref

2899

linkend="sql-syntax-bit-strings"> for information about the syntax

2900

of bit string constants. Bit-logical operators and string

2901

manipulation functions are available; see <xref

2902

linkend="functions-bitstring">.

2903

</para>

2904

2905

2906

<title>Using the bit string types</title>

2907

2908

2909

CREATE TABLE test (a BIT(3), b BIT VARYING(5));

2910

INSERT INTO test VALUES (B'101', B'00');

2911

INSERT INTO test VALUES (B'10', B'101');

2912

2913

ERROR: bit string length 2 does not match type bit(3)

2914

</computeroutput>

2915

INSERT INTO test VALUES (B'10'::bit(3), B'101');

2916

SELECT * FROM test;

2917

2918

a | b

2919

-----+-----

2920

101 | 00

2921

100 | 101

2922

</computeroutput>

2923

</programlisting>

2924

</example>

2925

2926

</sect1>

2927

2928

&array;

2929

2930

&rowtypes;

2931

2932

2933

<title>Object Identifier Types</title>

2934

2935

2936

<primary>object identifier</primary>

2937

2938

</indexterm>

2939

2940

2941

2942

</indexterm>

2943

2944

2945

<primary>regproc</primary>

2946

</indexterm>

2947

2948

2949

<primary>regprocedure</primary>

2950

</indexterm>

2951

2952

2953

<primary>regoper</primary>

2954

</indexterm>

2955

2956

2957

<primary>regoperator</primary>

2958

</indexterm>

2959

2960

2961

<primary>regclass</primary>

2962

</indexterm>

2963

2964

2965

<primary>regtype</primary>

2966

</indexterm>

2967

2968

2969

2970

</indexterm>

2971

2972

2973

2974

</indexterm>

2975

2976

2977

2978

</indexterm>

2979

2980

<para>

2981

Object identifiers (OIDs) are used internally by

2982

<productname>PostgreSQL</productname> as primary keys for various

2983

system tables. An OID system column is also added to user-created

2984

tables, unless <literal>WITHOUT OIDS</literal> is specified when

2985

the table is created, or the <xref linkend="guc-default-with-oids">

2986

configuration variable is set to false. Type <type>oid</>

2987

represents an object identifier. There are also several alias

2988

types for <type>oid</>: <type>regproc</>, <type>regprocedure</>,

2989

<type>regoper</>, <type>regoperator</>, <type>regclass</>, and

2990

<type>regtype</>. <xref linkend="datatype-oid-table"> shows an

2991

overview.

2992

</para>

2993

2994

<para>

2995

The <type>oid</> type is currently implemented as an unsigned

2996

four-byte integer. Therefore, it is not large enough to provide

2997

database-wide uniqueness in large databases, or even in large

2998

individual tables. So, using a user-created table's OID column as

2999

a primary key is discouraged. OIDs are best used only for

3000

references to system tables.

3001

</para>

3002

3003

<note>

3004

<para>

3005

OIDs are included by default in user-created tables in

3006

<productname>PostgreSQL</productname> &version;. However, this

3007

behavior is likely to change in a future version of

3008

<productname>PostgreSQL</productname>. Eventually, user-created

3009

tables will not include an OID system column unless <literal>WITH

3010

OIDS</literal> is specified when the table is created, or the

3011

<varname>default_with_oids</varname> configuration variable is set

3012

to true. If your application requires the presence of an OID

3013

system column in a table, it should specify <literal>WITH

3014

OIDS</literal> when that table is created to ensure compatibility

3015

with future releases of <productname>PostgreSQL</productname>.

3016

</para>

3017

</note>

3018

3019

<para>

3020

The <type>oid</> type itself has few operations beyond comparison.

3021

It can be cast to

3022

integer, however, and then manipulated using the standard integer

3023

operators. (Beware of possible signed-versus-unsigned confusion

3024

if you do this.)

3025

</para>

3026

3027

<para>

3028

The OID alias types have no operations of their own except

3029

for specialized input and output routines. These routines are able

3030

to accept and display symbolic names for system objects, rather than

3031

the raw numeric value that type <type>oid</> would use. The alias

3032

types allow simplified lookup of OID values for objects. For example,

3033

to examine the <structname>pg_attribute</> rows related to a table

3034

<literal>mytable</>, one could write

3035

3036

SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;

3037

</programlisting>

3038

rather than

3039

3040

SELECT * FROM pg_attribute

3041

WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');

3042

</programlisting>

3043

While that doesn't look all that bad by itself, it's still oversimplified.

3044

A far more complicated sub-select would be needed to

3045

select the right OID if there are multiple tables named

3046

<literal>mytable</> in different schemas.

3047

The <type>regclass</> input converter handles the table lookup according

3048

to the schema path setting, and so it does the <quote>right thing</>

3049

automatically. Similarly, casting a table's OID to

3050

<type>regclass</> is handy for symbolic display of a numeric OID.

3051

</para>

3052

3053

3054

<title>Object Identifier Types</title>

3055

3056

<thead>

3057

<row>

3058

3059

<entry>References</entry>

3060

<entry>Description</entry>

3061

<entry>Value Example</entry>

3062

</row>

3063

</thead>

3064

3065

<tbody>

3066

3067

<row>

3068

3069

3070

<entry>numeric object identifier</entry>

3071

3072

</row>

3073

3074

<row>

3075

<entry><type>regproc</></entry>

3076

3077

<entry>function name</entry>

3078

3079

</row>

3080

3081

<row>

3082

<entry><type>regprocedure</></entry>

3083

3084

<entry>function with argument types</entry>

3085

3086

</row>

3087

3088

<row>

3089

<entry><type>regoper</></entry>

3090

<entry><structname>pg_operator</></entry>

3091

<entry>operator name</entry>

3092

3093

</row>

3094

3095

<row>

3096

<entry><type>regoperator</></entry>

3097

<entry><structname>pg_operator</></entry>

3098

<entry>operator with argument types</entry>

3099

<entry><literal>*(integer,integer)</> or <literal>-(NONE,integer)</></entry>

3100

</row>

3101

3102

<row>

3103

<entry><type>regclass</></entry>

3104

<entry><structname>pg_class</></entry>

3105

<entry>relation name</entry>

3106

3107

</row>

3108

3109

<row>

3110

<entry><type>regtype</></entry>

3111

3112

3113

<entry><literal>integer</></entry>

3114

</row>

3115

</tbody>

3116

</tgroup>

3117

</table>

3118

3119

<para>

3120

All of the OID alias types accept schema-qualified names, and will

3121

display schema-qualified names on output if the object would not

3122

be found in the current search path without being qualified.

3123

The <type>regproc</> and <type>regoper</> alias types will only

3124

accept input names that are unique (not overloaded), so they are

3125

of limited use; for most uses <type>regprocedure</> or

3126

<type>regoperator</> is more appropriate. For <type>regoperator</>,

3127

unary operators are identified by writing <literal>NONE</> for the unused

3128

operand.

3129

</para>

3130

3131

<para>

3132

Another identifier type used by the system is <type>xid</>, or transaction

3133

(abbreviated <abbrev>xact</>) identifier. This is the data type of the system columns

3134

<structfield>xmin</> and <structfield>xmax</>. Transaction identifiers are 32-bit quantities.

3135

</para>

3136

3137

<para>

3138

A third identifier type used by the system is <type>cid</>, or

3139

command identifier. This is the data type of the system columns

3140

<structfield>cmin</> and <structfield>cmax</>. Command identifiers are also 32-bit quantities.

3141

</para>

3142

3143

<para>

3144

A final identifier type used by the system is <type>tid</>, or tuple

3145

identifier (row identifier). This is the data type of the system column

3146

<structfield>ctid</>. A tuple ID is a pair

3147

(block number, tuple index within block) that identifies the

3148

physical location of the row within its table.

3149

</para>

3150

3151

<para>

3152

(The system columns are further explained in <xref

3153

linkend="ddl-system-columns">.)

3154

</para>

3155

</sect1>

3156

3157

3158

<title>Pseudo-Types</title>

3159

3160

3161

<primary>record</primary>

3162

</indexterm>

3163

3164

3165

3166

</indexterm>

3167

3168

3169

<primary>anyarray</primary>

3170

</indexterm>

3171

3172

3173

<primary>anyelement</primary>

3174

</indexterm>

3175

3176

3177

3178

</indexterm>

3179

3180

3181

<primary>trigger</primary>

3182

</indexterm>

3183

3184

3185

<primary>language_handler</primary>

3186

</indexterm>

3187

3188

3189

<primary>cstring</primary>

3190

</indexterm>

3191

3192

3193

<primary>internal</primary>

3194

</indexterm>

3195

3196

3197

<primary>opaque</primary>

3198

</indexterm>

3199

3200

<para>

3201

The <productname>PostgreSQL</productname> type system contains a

3202

number of special-purpose entries that are collectively called

3203

<firstterm>pseudo-types</>. A pseudo-type cannot be used as a

3204

column data type, but it can be used to declare a function's

3205

argument or result type. Each of the available pseudo-types is

3206

useful in situations where a function's behavior does not

3207

correspond to simply taking or returning a value of a specific

3208

<acronym>SQL</acronym> data type. <xref

3209

linkend="datatype-pseudotypes-table"> lists the existing

3210

pseudo-types.

3211

</para>

3212

3213

3214

<title>Pseudo-Types</title>

3215

3216

<thead>

3217

<row>

3218

3219

<entry>Description</entry>

3220

</row>

3221

</thead>

3222

3223

<tbody>

3224

<row>

3225

3226

<entry>Indicates that a function accepts any input data type whatever.</entry>

3227

</row>

3228

3229

<row>

3230

<entry><type>anyarray</></entry>

3231

<entry>Indicates that a function accepts any array data type

3232

(see <xref linkend="extend-types-polymorphic">).</entry>

3233

</row>

3234

3235

<row>

3236

<entry><type>anyelement</></entry>

3237

<entry>Indicates that a function accepts any data type

3238

(see <xref linkend="extend-types-polymorphic">).</entry>

3239

</row>

3240

3241

<row>

3242

<entry><type>cstring</></entry>

3243

<entry>Indicates that a function accepts or returns a null-terminated C string.</entry>

3244

</row>

3245

3246

<row>

3247

<entry><type>internal</></entry>

3248

<entry>Indicates that a function accepts or returns a server-internal

3249

data type.</entry>

3250

</row>

3251

3252

<row>

3253

<entry><type>language_handler</></entry>

3254

<entry>A procedural language call handler is declared to return <type>language_handler</>.</entry>

3255

</row>

3256

3257

<row>

3258

<entry><type>record</></entry>

3259

<entry>Identifies a function returning an unspecified row type.</entry>

3260

</row>

3261

3262

<row>

3263

<entry><type>trigger</></entry>

3264

<entry>A trigger function is declared to return <type>trigger.</></entry>

3265

</row>

3266

3267

<row>

3268

3269

<entry>Indicates that a function returns no value.</entry>

3270

</row>

3271

3272

<row>

3273

<entry><type>opaque</></entry>

3274

<entry>An obsolete type name that formerly served all the above purposes.</entry>

3275

</row>

3276

</tbody>

3277

</tgroup>

3278

</table>

3279

3280

<para>

3281

Functions coded in C (whether built-in or dynamically loaded) may be

3282

declared to accept or return any of these pseudo data types. It is up to

3283

the function author to ensure that the function will behave safely

3284

when a pseudo-type is used as an argument type.

3285

</para>

3286

3287

<para>

3288

Functions coded in procedural languages may use pseudo-types only as

3289

allowed by their implementation languages. At present the procedural

3290

languages all forbid use of a pseudo-type as argument type, and allow

3291

only <type>void</> and <type>record</> as a result type (plus

3292

<type>trigger</> when the function is used as a trigger). Some also

3293

support polymorphic functions using the types <type>anyarray</> and

3294

<type>anyelement</>.

3295

</para>

3296

3297

<para>

3298

The <type>internal</> pseudo-type is used to declare functions

3299

that are meant only to be called internally by the database

3300

system, and not by direct invocation in a <acronym>SQL</acronym>

3301

query. If a function has at least one <type>internal</>-type

3302

argument then it cannot be called from <acronym>SQL</acronym>. To

3303

preserve the type safety of this restriction it is important to

3304

follow this coding rule: do not create any function that is

3305

declared to return <type>internal</> unless it has at least one

3306

<type>internal</> argument.

3307

</para>

3308

3309

</sect1>

3310

3311

</chapter>

3312

3313

<!-- Keep this comment at the end of the file

3314

Local variables:

3315

mode:sgml

3316

sgml-omittag:nil

3317

sgml-shorttag:t

3318

sgml-minimize-attributes:nil

3319

sgml-always-quote-attributes:t

3320

sgml-indent-step:1

3321

sgml-indent-tabs-mode:nil

3322

sgml-indent-data:t

3323

sgml-parent-document:nil

3324

sgml-default-dtd-file:"./reference.ced"

3325

sgml-exposed-tags:nil

3326

sgml-local-catalogs:("/usr/share/sgml/catalog")

3327

sgml-local-ecat-files:nil

3328

End:

3329

-->

Older »