Table Of ContentPostgreSQL 9.3.4 Documentation
The PostgreSQL Global Development Group
PostgreSQL9.3.4Documentation
byThePostgreSQLGlobalDevelopmentGroup
Copyright©1996-2014ThePostgreSQLGlobalDevelopmentGroup
LegalNotice
PostgreSQLisCopyright©1996-2014bythePostgreSQLGlobalDevelopmentGroup.
Postgres95isCopyright©1994-5bytheRegentsoftheUniversityofCalifornia.
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written
agreementisherebygranted,providedthattheabovecopyrightnoticeandthisparagraphandthefollowingtwoparagraphsappearinall
copies.
INNOEVENTSHALLTHEUNIVERSITYOFCALIFORNIABELIABLETOANYPARTYFORDIRECT,INDIRECT,SPECIAL,
INCIDENTAL,ORCONSEQUENTIALDAMAGES,INCLUDINGLOSTPROFITS,ARISINGOUTOFTHEUSEOFTHISSOFTWARE
ANDITSDOCUMENTATION,EVENIFTHEUNIVERSITYOFCALIFORNIAHASBEENADVISEDOFTHEPOSSIBILITYOF
SUCHDAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
THEIMPLIEDWARRANTIESOFMERCHANTABILITYANDFITNESSFORAPARTICULARPURPOSE.THESOFTWAREPRO-
VIDEDHEREUNDERISONAN“AS-IS”BASIS,ANDTHEUNIVERSITYOFCALIFORNIAHASNOOBLIGATIONSTOPROVIDE
MAINTENANCE,SUPPORT,UPDATES,ENHANCEMENTS,ORMODIFICATIONS.
Table of Contents
Preface................................................................................................................................................lx
1.WhatisPostgreSQL?.............................................................................................................lx
2.ABriefHistoryofPostgreSQL.............................................................................................lxi
2.1.TheBerkeleyPOSTGRESProject...........................................................................lxi
2.2.Postgres95.................................................................................................................lxi
2.3.PostgreSQL..............................................................................................................lxii
3.Conventions..........................................................................................................................lxii
4.FurtherInformation.............................................................................................................lxiii
5.BugReportingGuidelines...................................................................................................lxiii
5.1.IdentifyingBugs.....................................................................................................lxiv
5.2.WhattoReport.......................................................................................................lxiv
5.3.WheretoReportBugs............................................................................................lxvi
I.Tutorial.............................................................................................................................................1
1.GettingStarted........................................................................................................................1
1.1.Installation..................................................................................................................1
1.2.ArchitecturalFundamentals........................................................................................1
1.3.CreatingaDatabase....................................................................................................2
1.4.AccessingaDatabase.................................................................................................3
2.TheSQLLanguage.................................................................................................................5
2.1.Introduction................................................................................................................5
2.2.Concepts.....................................................................................................................5
2.3.CreatingaNewTable.................................................................................................5
2.4.PopulatingaTableWithRows...................................................................................6
2.5.QueryingaTable........................................................................................................7
2.6.JoinsBetweenTables..................................................................................................9
2.7.AggregateFunctions.................................................................................................11
2.8.Updates.....................................................................................................................12
2.9.Deletions...................................................................................................................13
3.AdvancedFeatures................................................................................................................14
3.1.Introduction..............................................................................................................14
3.2.Views........................................................................................................................14
3.3.ForeignKeys.............................................................................................................14
3.4.Transactions..............................................................................................................15
3.5.WindowFunctions....................................................................................................17
3.6.Inheritance................................................................................................................20
3.7.Conclusion................................................................................................................21
II.TheSQLLanguage......................................................................................................................22
4.SQLSyntax...........................................................................................................................24
4.1.LexicalStructure.......................................................................................................24
4.1.1.IdentifiersandKeyWords............................................................................24
4.1.2.Constants......................................................................................................26
4.1.2.1.StringConstants..............................................................................26
4.1.2.2.StringConstantswithC-styleEscapes............................................26
4.1.2.3.StringConstantswithUnicodeEscapes..........................................28
4.1.2.4.Dollar-quotedStringConstants.......................................................28
4.1.2.5.Bit-stringConstants.........................................................................29
4.1.2.6.NumericConstants..........................................................................29
4.1.2.7.ConstantsofOtherTypes................................................................30
iii
4.1.3.Operators......................................................................................................31
4.1.4.SpecialCharacters........................................................................................31
4.1.5.Comments....................................................................................................32
4.1.6.OperatorPrecedence....................................................................................32
4.2.ValueExpressions.....................................................................................................33
4.2.1.ColumnReferences......................................................................................34
4.2.2.PositionalParameters...................................................................................34
4.2.3.Subscripts.....................................................................................................35
4.2.4.FieldSelection.............................................................................................35
4.2.5.OperatorInvocations....................................................................................36
4.2.6.FunctionCalls..............................................................................................36
4.2.7.AggregateExpressions.................................................................................36
4.2.8.WindowFunctionCalls................................................................................38
4.2.9.TypeCasts....................................................................................................39
4.2.10.CollationExpressions................................................................................40
4.2.11.ScalarSubqueries.......................................................................................41
4.2.12.ArrayConstructors.....................................................................................41
4.2.13.RowConstructors.......................................................................................43
4.2.14.ExpressionEvaluationRules.....................................................................44
4.3.CallingFunctions......................................................................................................45
4.3.1.UsingPositionalNotation............................................................................45
4.3.2.UsingNamedNotation................................................................................46
4.3.3.UsingMixedNotation..................................................................................46
5.DataDefinition......................................................................................................................48
5.1.TableBasics..............................................................................................................48
5.2.DefaultValues..........................................................................................................49
5.3.Constraints................................................................................................................50
5.3.1.CheckConstraints........................................................................................50
5.3.2.Not-NullConstraints....................................................................................52
5.3.3.UniqueConstraints.......................................................................................53
5.3.4.PrimaryKeys................................................................................................53
5.3.5.ForeignKeys................................................................................................54
5.3.6.ExclusionConstraints..................................................................................57
5.4.SystemColumns.......................................................................................................57
5.5.ModifyingTables......................................................................................................59
5.5.1.AddingaColumn.........................................................................................59
5.5.2.RemovingaColumn....................................................................................59
5.5.3.AddingaConstraint.....................................................................................60
5.5.4.RemovingaConstraint................................................................................60
5.5.5.ChangingaColumn’sDefaultValue............................................................60
5.5.6.ChangingaColumn’sDataType.................................................................61
5.5.7.RenamingaColumn....................................................................................61
5.5.8.RenamingaTable........................................................................................61
5.6.Privileges..................................................................................................................61
5.7.Schemas....................................................................................................................62
5.7.1.CreatingaSchema.......................................................................................63
5.7.2.ThePublicSchema......................................................................................64
5.7.3.TheSchemaSearchPath..............................................................................64
5.7.4.SchemasandPrivileges................................................................................65
5.7.5.TheSystemCatalogSchema.......................................................................65
5.7.6.UsagePatterns..............................................................................................66
5.7.7.Portability.....................................................................................................66
iv
5.8.Inheritance................................................................................................................67
5.8.1.Caveats.........................................................................................................69
5.9.Partitioning...............................................................................................................70
5.9.1.Overview......................................................................................................70
5.9.2.ImplementingPartitioning...........................................................................71
5.9.3.ManagingPartitions.....................................................................................74
5.9.4.PartitioningandConstraintExclusion.........................................................74
5.9.5.AlternativePartitioningMethods.................................................................76
5.9.6.Caveats.........................................................................................................76
5.10.ForeignData...........................................................................................................77
5.11.OtherDatabaseObjects..........................................................................................78
5.12.DependencyTracking.............................................................................................78
6.DataManipulation.................................................................................................................80
6.1.InsertingData...........................................................................................................80
6.2.UpdatingData...........................................................................................................81
6.3.DeletingData............................................................................................................82
7.Queries..................................................................................................................................83
7.1.Overview..................................................................................................................83
7.2.TableExpressions.....................................................................................................83
7.2.1.TheFROMClause..........................................................................................84
7.2.1.1.JoinedTables...................................................................................84
7.2.1.2.TableandColumnAliases...............................................................87
7.2.1.3.Subqueries.......................................................................................88
7.2.1.4.TableFunctions...............................................................................89
7.2.1.5.LATERALSubqueries.......................................................................89
7.2.2.TheWHEREClause........................................................................................91
7.2.3.TheGROUP BYandHAVINGClauses...........................................................91
7.2.4.WindowFunctionProcessing......................................................................94
7.3.SelectLists................................................................................................................94
7.3.1.Select-ListItems..........................................................................................94
7.3.2.ColumnLabels.............................................................................................95
7.3.3.DISTINCT....................................................................................................95
7.4.CombiningQueries...................................................................................................96
7.5.SortingRows............................................................................................................96
7.6.LIMITandOFFSET...................................................................................................97
7.7.VALUESLists............................................................................................................98
7.8.WITHQueries(CommonTableExpressions)...........................................................99
7.8.1.SELECTinWITH...........................................................................................99
7.8.2.Data-ModifyingStatementsinWITH.........................................................102
8.DataTypes...........................................................................................................................105
8.1.NumericTypes........................................................................................................106
8.1.1.IntegerTypes..............................................................................................107
8.1.2.ArbitraryPrecisionNumbers.....................................................................107
8.1.3.Floating-PointTypes..................................................................................109
8.1.4.SerialTypes................................................................................................110
8.2.MonetaryTypes......................................................................................................111
8.3.CharacterTypes......................................................................................................111
8.4.BinaryDataTypes..................................................................................................113
8.4.1.byteaHexFormat.....................................................................................114
8.4.2.byteaEscapeFormat................................................................................114
8.5.Date/TimeTypes.....................................................................................................116
8.5.1.Date/TimeInput.........................................................................................117
v
8.5.1.1.Dates..............................................................................................118
8.5.1.2.Times.............................................................................................118
8.5.1.3.TimeStamps..................................................................................119
8.5.1.4.SpecialValues...............................................................................120
8.5.2.Date/TimeOutput......................................................................................121
8.5.3.TimeZones................................................................................................122
8.5.4.IntervalInput..............................................................................................123
8.5.5.IntervalOutput...........................................................................................125
8.6.BooleanType..........................................................................................................126
8.7.EnumeratedTypes..................................................................................................127
8.7.1.DeclarationofEnumeratedTypes..............................................................127
8.7.2.Ordering.....................................................................................................127
8.7.3.TypeSafety................................................................................................128
8.7.4.ImplementationDetails..............................................................................128
8.8.GeometricTypes.....................................................................................................129
8.8.1.Points.........................................................................................................129
8.8.2.LineSegments............................................................................................129
8.8.3.Boxes..........................................................................................................130
8.8.4.Paths...........................................................................................................130
8.8.5.Polygons.....................................................................................................130
8.8.6.Circles........................................................................................................131
8.9.NetworkAddressTypes..........................................................................................131
8.9.1.inet...........................................................................................................131
8.9.2.cidr...........................................................................................................132
8.9.3.inetvs.cidr............................................................................................132
8.9.4.macaddr....................................................................................................132
8.10.BitStringTypes....................................................................................................133
8.11.TextSearchTypes.................................................................................................134
8.11.1.tsvector................................................................................................134
8.11.2.tsquery..................................................................................................135
8.12.UUIDType...........................................................................................................136
8.13.XMLType............................................................................................................137
8.13.1.CreatingXMLValues..............................................................................137
8.13.2.EncodingHandling..................................................................................138
8.13.3.AccessingXMLValues............................................................................139
8.14.JSONType............................................................................................................139
8.15.Arrays...................................................................................................................139
8.15.1.DeclarationofArrayTypes......................................................................139
8.15.2.ArrayValueInput.....................................................................................140
8.15.3.AccessingArrays.....................................................................................142
8.15.4.ModifyingArrays.....................................................................................143
8.15.5.SearchinginArrays..................................................................................146
8.15.6.ArrayInputandOutputSyntax................................................................146
8.16.CompositeTypes..................................................................................................148
8.16.1.DeclarationofCompositeTypes..............................................................148
8.16.2.CompositeValueInput.............................................................................149
8.16.3.AccessingCompositeTypes....................................................................150
8.16.4.ModifyingCompositeTypes....................................................................150
8.16.5.CompositeTypeInputandOutputSyntax...............................................151
8.17.RangeTypes.........................................................................................................152
8.17.1.Built-inRangeTypes...............................................................................152
8.17.2.Examples..................................................................................................152
vi
8.17.3.InclusiveandExclusiveBounds..............................................................153
8.17.4.Infinite(Unbounded)Ranges...................................................................153
8.17.5.RangeInput/Output..................................................................................153
8.17.6.ConstructingRanges................................................................................154
8.17.7.DiscreteRangeTypes..............................................................................155
8.17.8.DefiningNewRangeTypes.....................................................................155
8.17.9.Indexing...................................................................................................156
8.17.10.ConstraintsonRanges............................................................................156
8.18.ObjectIdentifierTypes.........................................................................................157
8.19.Pseudo-Types........................................................................................................159
9.FunctionsandOperators.....................................................................................................161
9.1.LogicalOperators...................................................................................................161
9.2.ComparisonOperators............................................................................................161
9.3.MathematicalFunctionsandOperators..................................................................163
9.4.StringFunctionsandOperators..............................................................................166
9.4.1.format.......................................................................................................180
9.5.BinaryStringFunctionsandOperators..................................................................182
9.6.BitStringFunctionsandOperators........................................................................184
9.7.PatternMatching....................................................................................................185
9.7.1.LIKE...........................................................................................................185
9.7.2.SIMILAR TORegularExpressions............................................................186
9.7.3.POSIXRegularExpressions......................................................................187
9.7.3.1.RegularExpressionDetails...........................................................190
9.7.3.2.BracketExpressions......................................................................192
9.7.3.3.RegularExpressionEscapes..........................................................193
9.7.3.4.RegularExpressionMetasyntax....................................................196
9.7.3.5.RegularExpressionMatchingRules.............................................197
9.7.3.6.LimitsandCompatibility..............................................................198
9.7.3.7.BasicRegularExpressions............................................................199
9.8.DataTypeFormattingFunctions............................................................................199
9.9.Date/TimeFunctionsandOperators.......................................................................206
9.9.1.EXTRACT,date_part...............................................................................210
9.9.2.date_trunc..............................................................................................213
9.9.3.AT TIME ZONE..........................................................................................214
9.9.4.CurrentDate/Time.....................................................................................215
9.9.5.DelayingExecution....................................................................................217
9.10.EnumSupportFunctions......................................................................................217
9.11.GeometricFunctionsandOperators.....................................................................218
9.12.NetworkAddressFunctionsandOperators..........................................................222
9.13.TextSearchFunctionsandOperators...................................................................224
9.14.XMLFunctions....................................................................................................228
9.14.1.ProducingXMLContent..........................................................................228
9.14.1.1.xmlcomment...............................................................................228
9.14.1.2.xmlconcat.................................................................................229
9.14.1.3.xmlelement...............................................................................229
9.14.1.4.xmlforest.................................................................................231
9.14.1.5.xmlpi..........................................................................................231
9.14.1.6.xmlroot......................................................................................232
9.14.1.7.xmlagg........................................................................................232
9.14.2.XMLPredicates.......................................................................................233
9.14.2.1.IS DOCUMENT.............................................................................233
9.14.2.2.XMLEXISTS.................................................................................233
vii
9.14.2.3.xml_is_well_formed..............................................................233
9.14.3.ProcessingXML......................................................................................234
9.14.4.MappingTablestoXML..........................................................................235
9.15.JSONFunctionsandOperators............................................................................239
9.16.SequenceManipulationFunctions.......................................................................242
9.17.ConditionalExpressions.......................................................................................245
9.17.1.CASE.........................................................................................................245
9.17.2.COALESCE................................................................................................246
9.17.3.NULLIF.....................................................................................................247
9.17.4.GREATESTandLEAST..............................................................................247
9.18.ArrayFunctionsandOperators............................................................................247
9.19.RangeFunctionsandOperators............................................................................250
9.20.AggregateFunctions.............................................................................................252
9.21.WindowFunctions................................................................................................255
9.22.SubqueryExpressions..........................................................................................257
9.22.1.EXISTS.....................................................................................................258
9.22.2.IN.............................................................................................................258
9.22.3.NOT IN.....................................................................................................259
9.22.4.ANY/SOME.................................................................................................259
9.22.5.ALL...........................................................................................................260
9.22.6.Row-wiseComparison.............................................................................260
9.23.RowandArrayComparisons...............................................................................260
9.23.1.IN.............................................................................................................261
9.23.2.NOT IN.....................................................................................................261
9.23.3.ANY/SOME(array).....................................................................................261
9.23.4.ALL(array)...............................................................................................262
9.23.5.Row-wiseComparison.............................................................................262
9.24.SetReturningFunctions.......................................................................................263
9.25.SystemInformationFunctions.............................................................................266
9.26.SystemAdministrationFunctions........................................................................276
9.26.1.ConfigurationSettingsFunctions.............................................................276
9.26.2.ServerSignalingFunctions......................................................................277
9.26.3.BackupControlFunctions.......................................................................278
9.26.4.RecoveryControlFunctions....................................................................280
9.26.5.SnapshotSynchronizationFunctions.......................................................282
9.26.6.DatabaseObjectManagementFunctions.................................................282
9.26.7.GenericFileAccessFunctions.................................................................284
9.26.8.AdvisoryLockFunctions.........................................................................285
9.27.TriggerFunctions.................................................................................................287
9.28.EventTriggerFunctions.......................................................................................288
10.TypeConversion................................................................................................................290
10.1.Overview..............................................................................................................290
10.2.Operators..............................................................................................................291
10.3.Functions..............................................................................................................294
10.4.ValueStorage........................................................................................................297
10.5.UNION,CASE,andRelatedConstructs..................................................................298
11.Indexes..............................................................................................................................300
11.1.Introduction..........................................................................................................300
11.2.IndexTypes...........................................................................................................301
11.3.MulticolumnIndexes............................................................................................303
11.4.IndexesandORDER BY.........................................................................................304
11.5.CombiningMultipleIndexes................................................................................304
viii
11.6.UniqueIndexes.....................................................................................................305
11.7.IndexesonExpressions........................................................................................306
11.8.PartialIndexes......................................................................................................306
11.9.OperatorClassesandOperatorFamilies..............................................................309
11.10.IndexesandCollations........................................................................................310
11.11.ExaminingIndexUsage......................................................................................310
12.FullTextSearch................................................................................................................312
12.1.Introduction..........................................................................................................312
12.1.1.WhatIsaDocument?...............................................................................313
12.1.2.BasicTextMatching................................................................................313
12.1.3.Configurations..........................................................................................314
12.2.TablesandIndexes................................................................................................315
12.2.1.SearchingaTable.....................................................................................315
12.2.2.CreatingIndexes......................................................................................316
12.3.ControllingTextSearch........................................................................................317
12.3.1.ParsingDocuments..................................................................................317
12.3.2.ParsingQueries........................................................................................318
12.3.3.RankingSearchResults...........................................................................320
12.3.4.HighlightingResults................................................................................322
12.4.AdditionalFeatures..............................................................................................323
12.4.1.ManipulatingDocuments.........................................................................323
12.4.2.ManipulatingQueries...............................................................................324
12.4.2.1.QueryRewriting..........................................................................325
12.4.3.TriggersforAutomaticUpdates..............................................................326
12.4.4.GatheringDocumentStatistics................................................................327
12.5.Parsers...................................................................................................................328
12.6.Dictionaries...........................................................................................................330
12.6.1.StopWords...............................................................................................331
12.6.2.SimpleDictionary....................................................................................331
12.6.3.SynonymDictionary................................................................................333
12.6.4.ThesaurusDictionary...............................................................................334
12.6.4.1.ThesaurusConfiguration.............................................................335
12.6.4.2.ThesaurusExample.....................................................................336
12.6.5.IspellDictionary.......................................................................................337
12.6.6.SnowballDictionary................................................................................338
12.7.ConfigurationExample.........................................................................................338
12.8.TestingandDebuggingTextSearch.....................................................................340
12.8.1.ConfigurationTesting...............................................................................340
12.8.2.ParserTesting...........................................................................................342
12.8.3.DictionaryTesting....................................................................................343
12.9.GiSTandGINIndexTypes..................................................................................344
12.10.psqlSupport........................................................................................................345
12.11.Limitations..........................................................................................................347
12.12.MigrationfromPre-8.3TextSearch...................................................................348
13.ConcurrencyControl.........................................................................................................349
13.1.Introduction..........................................................................................................349
13.2.TransactionIsolation............................................................................................349
13.2.1.ReadCommittedIsolationLevel.............................................................350
13.2.2.RepeatableReadIsolationLevel..............................................................351
13.2.3.SerializableIsolationLevel......................................................................352
13.3.ExplicitLocking...................................................................................................355
13.3.1.Table-levelLocks.....................................................................................355
ix
13.3.2.Row-levelLocks......................................................................................357
13.3.3.Deadlocks.................................................................................................358
13.3.4.AdvisoryLocks........................................................................................359
13.4.DataConsistencyChecksattheApplicationLevel..............................................360
13.4.1.EnforcingConsistencyWithSerializableTransactions...........................360
13.4.2.EnforcingConsistencyWithExplicitBlockingLocks............................361
13.5.LockingandIndexes.............................................................................................361
14.PerformanceTips..............................................................................................................363
14.1.UsingEXPLAIN....................................................................................................363
14.1.1.EXPLAINBasics.......................................................................................363
14.1.2.EXPLAIN ANALYZE.................................................................................369
14.1.3.Caveats.....................................................................................................372
14.2.StatisticsUsedbythePlanner..............................................................................373
14.3.ControllingthePlannerwithExplicitJOINClauses............................................374
14.4.PopulatingaDatabase..........................................................................................376
14.4.1.DisableAutocommit................................................................................376
14.4.2.UseCOPY..................................................................................................376
14.4.3.RemoveIndexes.......................................................................................377
14.4.4.RemoveForeignKeyConstraints............................................................377
14.4.5.Increasemaintenance_work_mem........................................................377
14.4.6.Increasecheckpoint_segments..........................................................377
14.4.7.DisableWALArchivalandStreamingReplication.................................378
14.4.8.RunANALYZEAfterwards........................................................................378
14.4.9.SomeNotesAboutpg_dump...................................................................378
14.5.Non-DurableSettings...........................................................................................379
III.ServerAdministration.............................................................................................................381
15.InstallationfromSourceCode..........................................................................................383
15.1.ShortVersion........................................................................................................383
15.2.Requirements........................................................................................................383
15.3.GettingTheSource...............................................................................................385
15.4.InstallationProcedure...........................................................................................385
15.5.Post-InstallationSetup..........................................................................................395
15.5.1.SharedLibraries.......................................................................................395
15.5.2.EnvironmentVariables.............................................................................396
15.6.SupportedPlatforms.............................................................................................396
15.7.Platform-specificNotes........................................................................................397
15.7.1.AIX..........................................................................................................397
15.7.1.1.GCCIssues..................................................................................398
15.7.1.2.Unix-DomainSocketsBroken.....................................................398
15.7.1.3.InternetAddressIssues................................................................398
15.7.1.4.MemoryManagement.................................................................399
ReferencesandResources................................................................400
15.7.2.Cygwin.....................................................................................................400
15.7.3.HP-UX.....................................................................................................401
15.7.4.IRIX.........................................................................................................402
15.7.5.MinGW/NativeWindows........................................................................402
15.7.5.1.CollectingCrashDumpsonWindows........................................403
15.7.6.SCOOpenServerandSCOUnixWare.....................................................403
15.7.6.1.Skunkware...................................................................................403
15.7.6.2.GNUMake..................................................................................403
15.7.6.3.Readline.......................................................................................403
x
