Table Of Content

PostgreSQL 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

ebook img

postgresql-9.3-A4.pdf (PDFy mirror) PDF

74.1 MB·English
by  
#additional_collections #pdfymirrors
Save to my drive
Quick download
Download
Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview postgresql-9.3-A4.pdf (PDFy mirror)

PostgreSQL 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

See more

The list of books you might like

Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.
DMCA & Copyright: Dear all, most of the website is community built, users are uploading hundred of books everyday, which makes really hard for us to identify copyrighted material, please contact us if you want any material removed.
Copyright @ PDFdrive.to, 2022 | We love our users