pdf viewer in asp net c# : Add image field to pdf form control application system web page html azure console postgresql-9.4-A461-part2985

Chapter 21. Managing Databases
EveryinstanceofarunningPostgreSQLservermanagesoneormoredatabases.Databasesarethere-
forethetopmosthierarchicallevelfororganizingSQL objects(“databaseobjects”).Thischapter
describesthepropertiesofdatabases,andhowtocreate,manage,anddestroythem.
21.1. Overview
AdatabaseisanamedcollectionofSQL objects (“databaseobjects”).Generally,everydatabase
object(tables,functions,etc.)belongstooneandonlyonedatabase.(Howeverthereareafewsystem
catalogs,forexample
pg_database
,thatbelongtoawholeclusterandareaccessiblefromeach
databasewithinthecluster.)Moreaccurately,adatabaseisacollectionofschemasandtheschemas
containthetables,functions,etc.Sothefullhierarchyis:server,database,schema,table(orsome
otherkindofobject,suchasafunction).
Whenconnectingtothedatabaseserver,aclientmustspecifyinitsconnectionrequestthenameof
thedatabaseitwantstoconnectto.Itisnotpossibletoaccessmorethanonedatabaseperconnection.
However,anapplicationisnotrestrictedinthenumberofconnectionsitopenstothesameorother
databases.Databasesarephysicallyseparatedandaccesscontrolismanagedattheconnectionlevel.If
onePostgreSQLserverinstanceistohouseprojectsorusersthatshouldbeseparateandforthemost
partunawareofeachother,itisthereforerecommendedtoputthemintoseparatedatabases.Ifthe
projectsorusersareinterrelatedandshouldbeabletouseeachother’sresources,theyshouldbeput
inthesamedatabasebutpossiblyintoseparateschemas.Schemasareapurelylogicalstructureand
whocanaccesswhatismanagedbytheprivilegesystem.Moreinformationaboutmanagingschemas
isinSection5.7.
Databasesarecreatedwiththe
CREATE DATABASE
command(seeSection21.2)anddestroyedwith
the
DROP DATABASE
command(seeSection21.5).Todeterminethesetofexistingdatabases,exam-
inethe
pg_database
systemcatalog,forexample
SELECT datname FROM pg_database;
Thepsqlprogram’s
\l
meta-commandand
-l
command-lineoptionarealsousefulforlistingthe
existingdatabases.
Note:TheSQLstandardcallsdatabases“catalogs”,butthereisnodifferenceinpractice.
21.2. Creating a Database
Inordertocreateadatabase,thePostgreSQLservermustbeupandrunning(seeSection17.3).
DatabasesarecreatedwiththeSQLcommandCREATEDATABASE:
CREATE DATABASE
name
;
where
name
followstheusualrulesforSQLidentifiers.Thecurrentroleautomaticallybecomesthe
ownerofthenewdatabase.Itistheprivilegeoftheownerofadatabasetoremoveitlater(whichalso
removesalltheobjectsinit,eveniftheyhaveadifferentowner).
Thecreationofdatabasesisarestrictedoperation.SeeSection20.2forhowtograntpermission.
538
Add image field to pdf form - insert images into PDF in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Sample C# code to add image, picture, logo or digital photo into PDF document page using PDF page editor control
adding an image to a pdf; add image pdf
Add image field to pdf form - VB.NET PDF insert image library: insert images into PDF in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Guide VB.NET Programmers How to Add Images in PDF Document
add image to pdf preview; add picture to pdf document
Chapter21.ManagingDatabases
Sinceyouneedtobeconnectedtothedatabaseserverinordertoexecutethe
CREATE DATABASE
command,thequestionremainshowthefirstdatabaseatanygivensitecanbecreated. Thefirst
databaseisalwayscreatedbythe
initdb
commandwhenthedatastorageareaisinitialized.(See
Section17.2.)Thisdatabaseiscalled
postgres
.Sotocreatethefirst“ordinary”databaseyoucan
connectto
postgres
.
Aseconddatabase,
template1
,isalsocreatedduringdatabaseclusterinitialization.Wheneveranew
databaseiscreatedwithinthecluster,
template1
isessentiallycloned.Thismeansthatanychanges
youmakein
template1
arepropagatedtoallsubsequentlycreateddatabases.Becauseofthis,avoid
creatingobjectsin
template1
unlessyouwantthempropagatedtoeverynewlycreateddatabase.
MoredetailsappearinSection21.3.
As a convenience, there is a program youcan execute from the shell tocreate new databases,
createdb
.
createdb
dbname
createdb
doesnomagic.Itconnectstothe
postgres
databaseandissuesthe
CREATE DATABASE
command,exactlyasdescribedabove.Thecreatedbreferencepagecontainstheinvocationdetails.
Notethat
createdb
withoutanyargumentswillcreateadatabasewiththecurrentusername.
Note:Chapter19containsinformationabouthowtorestrictwhocanconnecttoagivendatabase.
Sometimesyouwanttocreateadatabaseforsomeoneelse,andhavehimbecometheownerofthe
newdatabase,sohecanconfigureandmanageithimself.Toachievethat,useoneofthefollowing
commands:
CREATE DATABASE
dbname
OWNER
rolename
;
fromtheSQLenvironment,or:
createdb -O
rolename dbname
fromtheshell.Onlythesuperuserisallowedtocreateadatabaseforsomeoneelse(thatis,forarole
youarenotamemberof).
21.3. Template Databases
CREATE DATABASE
actuallyworksbycopyinganexistingdatabase.Bydefault,itcopiesthestandard
systemdatabasenamed
template1
.Thusthatdatabaseisthe“template”fromwhichnewdatabases
aremade.Ifyouaddobjectsto
template1
, theseobjectswillbecopiedintosubsequentlycre-
ateduserdatabases.Thisbehaviorallowssite-localmodificationstothestandardsetofobjectsin
databases.Forexample,ifyouinstalltheprocedurallanguagePL/Perlin
template1
,itwillauto-
maticallybeavailableinuserdatabaseswithoutanyextraactionbeingtakenwhenthosedatabases
arecreated.
Thereisasecondstandardsystemdatabasenamed
template0
.Thisdatabasecontainsthesamedata
astheinitialcontentsof
template1
,thatis,onlythestandardobjectspredefinedbyyourversionof
PostgreSQL.
template0
shouldneverbechangedafterthedatabaseclusterhasbeeninitialized.By
instructing
CREATE DATABASE
tocopy
template0
insteadof
template1
,youcancreatea“virgin”
userdatabasethatcontainsnoneofthesite-localadditionsin
template1
.Thisisparticularlyhandy
whenrestoringa
pg_dump
dump:thedumpscriptshouldberestoredinavirgindatabasetoensure
539
VB.NET PDF Form Data Read library: extract form data from PDF in
a full-featured PDF software, it should have functions for processing text, image as well DLLs: Read and Extract Field Data in VB.NET. Add necessary references:
add picture to pdf; add an image to a pdf with acrobat
C# PDF Form Data Read Library: extract form data from PDF in C#.
PDF software, it should have functions for processing text, image as well as C#.NET Project DLLs: Read and Extract Field Data in C#. Add necessary references:
adding image to pdf file; add an image to a pdf form
Chapter21.ManagingDatabases
thatonerecreatesthecorrectcontentsofthedumpeddatabase,withoutconflictingwithobjectsthat
mighthavebeenaddedto
template1
lateron.
Anothercommonreasonforcopying
template0
insteadof
template1
isthatnewencodingand
localesettingscanbespecifiedwhencopying
template0
,whereasacopyof
template1
mustuse
thesamesettingsitdoes.Thisisbecause
template1
mightcontainencoding-specificorlocale-
specificdata,while
template0
isknownnotto.
Tocreateadatabasebycopying
template0
,use:
CREATE DATABASE
dbname
TEMPLATE template0;
fromtheSQLenvironment,or:
createdb -T template0
dbname
fromtheshell.
Itis possibletocreateadditionaltemplatedatabases, andindeedonecancopyanydatabaseina
clusterbyspecifyingitsnameasthetemplatefor
CREATE DATABASE
.Itisimportanttounderstand,
however,thatthisisnot(yet)intendedasageneral-purpose“
COPY DATABASE
”facility.Theprincipal
limitationisthatnoothersessionscanbeconnectedtothesourcedatabasewhileitisbeingcopied.
CREATE DATABASE
willfailifanyotherconnectionexistswhenitstarts;duringthecopyoperation,
newconnectionstothesourcedatabaseareprevented.
Two useful flags exist in
pg_database
for each database: the columns
datistemplate
and
datallowconn
.
datistemplate
can be set to indicate that a database is intended as a
templatefor
CREATE DATABASE
.Ifthisflagisset, thedatabasecanbeclonedbyanyuserwith
CREATEDB
privileges;ifitisnotset,onlysuperusersandtheownerofthedatabasecancloneit.If
datallowconn
isfalse, then nonewconnections tothatdatabasewillbeallowed (butexisting
sessionsarenotterminatedsimplybysettingtheflagfalse).The
template0
databaseisnormally
marked
datallowconn = false
topreventitsmodification. Both
template0
and
template1
shouldalwaysbemarkedwith
datistemplate = true
.
Note:
template1
and
template0
donothaveanyspecialstatusbeyondthefactthatthename
template1
isthedefaultsourcedatabasenamefor
CREATE DATABASE
.Forexample,onecould
drop
template1
andrecreateitfrom
template0
withoutanyilleffects.Thiscourseofactionmight
beadvisableifonehascarelesslyaddedabunchofjunkin
template1
.(Todelete
template1
,it
musthave
pg_database.datistemplate = false
.)
The
postgres
databaseisalso created whena databaseclusterisinitialized.Thisdatabase
ismeantasadefaultdatabaseforusersand applicationstoconnectto.Itissimplya copyof
template1
andcanbedroppedandrecreatedifnecessary.
21.4. Database Configuration
RecallfromChapter18thatthePostgreSQLserverprovidesalargenumberofrun-timeconfiguration
variables.Youcansetdatabase-specificdefaultvaluesformanyofthesesettings.
Forexample,ifforsomereasonyouwanttodisabletheGEQOoptimizerforagivendatabase,you’d
ordinarilyhavetoeitherdisableitforalldatabasesormakesurethateveryconnectingclientiscareful
toissue
SET geqo TO off
.Tomakethissettingthedefaultwithinaparticulardatabase,youcan
executethecommand:
540
VB.NET PDF Field Edit library: insert, delete, update pdf form
By using RaterEdge .NET PDF package, you can add form fields to existing pdf files, delete or remove form field in PDF page and update PDF field in VB.NET
adding an image to a pdf in acrobat; add jpg to pdf form
C# PDF Field Edit Library: insert, delete, update pdf form field
By using RaterEdge .NET PDF package, you can add form fields to existing pdf files, delete or remove form field in PDF page and update PDF field in C#
how to add image to pdf in acrobat; how to add an image to a pdf in reader
Chapter21.ManagingDatabases
ALTER DATABASE mydb SET geqo TO off;
Thiswillsavethesetting(butnotsetitimmediately).Insubsequentconnectionstothisdatabaseit
willappearasthough
SET geqo TO off;
hadbeenexecutedjustbeforethesessionstarted.Note
thatuserscanstillalterthissettingduringtheirsessions;itwillonlybethedefault.Toundoanysuch
setting,use
ALTER DATABASE
dbname
RESET
varname
.
21.5. Destroying a Database
DatabasesaredestroyedwiththecommandDROPDATABASE:
DROP DATABASE
name
;
Onlytheownerofthedatabase,orasuperuser,candropadatabase.Droppingadatabaseremovesall
objectsthatwerecontainedwithinthedatabase.Thedestructionofadatabasecannotbeundone.
Youcannotexecutethe
DROP DATABASE
commandwhileconnectedtothevictimdatabase.Youcan,
however,beconnectedtoanyotherdatabase,includingthe
template1
database.
template1
would
betheonlyoptionfordroppingthelastuserdatabaseofagivencluster.
Forconvenience,thereisalsoashellprogramtodropdatabases,dropdb:
dropdb
dbname
(Unlike
createdb
,itisnotthedefaultactiontodropthedatabasewiththecurrentusername.)
21.6. Tablespaces
TablespacesinPostgreSQLallowdatabaseadministratorstodefinelocationsinthefilesystemwhere
thefilesrepresentingdatabaseobjectscanbestored.Oncecreated,atablespacecanbereferredtoby
namewhencreatingdatabaseobjects.
Byusingtablespaces,anadministratorcancontrolthedisklayoutofaPostgreSQLinstallation.This
isusefulinatleasttwoways.First,ifthepartitionorvolumeonwhichtheclusterwasinitializedruns
outofspaceandcannotbeextended,atablespacecanbecreatedonadifferentpartitionanduseduntil
thesystemcanbereconfigured.
Second,tablespacesallowanadministratortouseknowledgeoftheusagepatternofdatabaseobjects
tooptimizeperformance.Forexample,anindexwhichisveryheavilyusedcanbeplacedonavery
fast,highlyavailabledisk,suchasanexpensivesolidstatedevice.Atthesametimeatablestoring
archiveddatawhichisrarelyusedornotperformancecriticalcouldbestoredonalessexpensive,
slowerdisksystem.
Warning
EventhoughlocatedoutsidethemainPostgreSQLdatadirectory,tablespaces
areanintegralpartofthedatabaseclusterandcannot betreatedasanau-
tonomouscollectionofdatafiles.Theyaredependentonmetadatacontained
in the e main data directory, , and therefore cannot be attached to o a different
databaseclusterorbackedupindividually.Similarly,ifyouloseatablespace
(file deletion, , diskfailure, etc), the database clustermight t become unread-
ableorunabletostart.Placingatablespace ona temporaryfilesystemlike
aramdiskrisksthereliabilityoftheentirecluster.
541
VB.NET PDF Form Data fill-in library: auto fill-in PDF form data
PDF Page. Image: Copy, Paste, Cut Image in Page. Fill-in Field Data. Field: Insert, Delete, Update Field. Images. Redact Pages. Annotation & Drawing. Add Sticky Note
add a picture to a pdf file; add an image to a pdf in preview
C# PDF Form Data fill-in Library: auto fill-in PDF form data in C#
Following C# sample code can help you have a quick evaluation of it. C#.NET Demo Code: Auto Fill-in Field Data to PDF in C#.NET. Add necessary references:
how to add an image to a pdf file; how to add image to pdf acrobat
Chapter21.ManagingDatabases
Todefineatablespace,usetheCREATETABLESPACEcommand,forexample::
CREATE TABLESPACE fastspace LOCATION ’/ssd1/postgresql/data’;
Thelocationmustbeanexisting,emptydirectorythatisownedbythePostgreSQLoperatingsystem
user.Allobjectssubsequentlycreatedwithinthetablespacewillbestoredinfilesunderneaththis
directory. Thelocationmustnotbeonremovableortransientstorage,astheclustermightfailto
functionifthetablespaceismissingorlost.
Note:Thereisusuallynotmuchpointinmakingmorethanonetablespaceperlogicalfilesys-
tem,sinceyoucannotcontrolthelocationofindividualfileswithinalogicalfilesystem.However,
PostgreSQLdoesnotenforceanysuchlimitation,andindeeditisnotdirectlyawareofthefile
systemboundariesonyoursystem.Itjuststoresfilesinthedirectoriesyoutellittouse.
Creationofthetablespaceitselfmustbedoneasadatabasesuperuser,butafterthatyoucanallow
ordinarydatabaseuserstouseit.Todothat,grantthemthe
CREATE
privilegeonit.
Tables,indexes,andentiredatabasescanbeassignedtoparticulartablespaces.Todoso,auserwith
the
CREATE
privilegeonagiventablespacemustpassthetablespacenameasaparametertothe
relevantcommand.Forexample,thefollowingcreatesatableinthetablespace
space1
:
CREATE TABLE foo(i int) TABLESPACE space1;
Alternatively,usethedefault_tablespaceparameter:
SET default_tablespace = space1;
CREATE TABLE foo(i int);
When
default_tablespace
is set to anything but an empty string, it supplies an implicit
TABLESPACE
clausefor
CREATE TABLE
and
CREATE INDEX
commandsthatdonothaveanexplicit
one.
Thereisalsoatemp_tablespacesparameter,whichdeterminestheplacementoftemporarytablesand
indexes,aswellastemporaryfilesthatareusedforpurposessuchassortinglargedatasets.Thiscan
bealistoftablespacenames,ratherthanonlyone,sothattheloadassociatedwithtemporaryobjects
canbespreadovermultipletablespaces.Arandommemberofthelistispickedeachtimeatemporary
objectistobecreated.
The tablespace associatedwithadatabase is usedtostorethesystem catalogs ofthatdatabase.
Furthermore, it is the default tablespace used for tables, indexes, and temporary files created
within the database, ifno
TABLESPACE
clause is given and no other selection is specified by
default_tablespace
or
temp_tablespaces
(asappropriate).Ifadatabaseiscreatedwithout
specifyingatablespaceforit,itusesthesametablespaceasthetemplatedatabaseitiscopiedfrom.
Twotablespacesareautomaticallycreatedwhenthedatabaseclusterisinitialized.The
pg_global
tablespaceisusedforsharedsystemcatalogs.The
pg_default
tablespaceisthedefaulttablespace
ofthe
template1
and
template0
databases(and,therefore,willbethedefaulttablespaceforother
databasesaswell,unlessoverriddenbya
TABLESPACE
clausein
CREATE DATABASE
).
Oncecreated,atablespacecanbeusedfromanydatabase,providedtherequestinguserhassufficient
privilege.Thismeansthatatablespacecannotbedroppeduntilallobjectsinalldatabasesusingthe
tablespacehavebeenremoved.
Toremoveanemptytablespace,usetheDROPTABLESPACEcommand.
542
C# PDF Image Extract Library: Select, copy, paste PDF images in C#
Scan image to PDF, tiff and various image formats. Get image information, such as its location, zonal information Able to edit, add, delete, move, and output PDF
add image field to pdf form; acrobat insert image in pdf
VB.NET PDF Image Extract Library: Select, copy, paste PDF images
multiple types of image from PDF file in VB.NET, like XObject Image, XObject Form, Inline Image DLLs for PDF Image Extraction in VB.NET. Add necessary references
add picture pdf; how to add image to pdf form
Chapter21.ManagingDatabases
Todeterminethesetofexistingtablespaces,examinethe
pg_tablespace
systemcatalog,forex-
ample
SELECT spcname FROM pg_tablespace;
Thepsqlprogram’s
\db
meta-commandisalsousefulforlistingtheexistingtablespaces.
PostgreSQLmakesuseofsymboliclinkstosimplifytheimplementationoftablespaces.Thismeans
thattablespacescanbeusedonlyonsystemsthatsupportsymboliclinks.
Thedirectory
$PGDATA/pg_tblspc
containssymboliclinksthatpointtoeachofthenon-built-inta-
blespacesdefinedinthecluster.Althoughnotrecommended,itispossibletoadjustthetablespacelay-
outbyhandbyredefiningtheselinks.Undernocircumstancesperformthisoperationwhiletheserver
isrunning.NotethatinPostgreSQL9.1andearlieryouwillalsoneedtoupdatethe
pg_tablespace
catalogwiththenewlocations.(Ifyoudonot,
pg_dump
willcontinuetooutputtheoldtablespace
locations.)
543
Chapter 22. Localization
Thischapterdescribestheavailablelocalizationfeaturesfromthepointofviewoftheadministrator.
PostgreSQLsupportstwolocalizationfacilities:
Usingthelocalefeaturesoftheoperatingsystemtoprovidelocale-specificcollationorder,number
formatting,translatedmessages,andotheraspects. ThisiscoveredinSection22.1andSection
22.2.
Providinganumberofdifferentcharactersetstosupportstoringtextinallkindsoflanguages,and
providingcharactersettranslationbetweenclientandserver.ThisiscoveredinSection22.3.
22.1. Locale Support
Localesupportreferstoanapplicationrespectingculturalpreferencesregardingalphabets,sorting,
numberformatting,etc.PostgreSQLusesthestandardISOCandPOSIXlocalefacilitiesprovidedby
theserveroperatingsystem.Foradditionalinformationrefertothedocumentationofyoursystem.
22.1.1.Overview
Localesupportisautomaticallyinitializedwhenadatabaseclusteriscreatedusing
initdb
.
initdb
willinitializethedatabaseclusterwiththelocalesettingofitsexecutionenvironmentbydefault,soif
yoursystemisalreadysettousethelocalethatyouwantinyourdatabaseclusterthenthereisnothing
elseyouneedtodo.Ifyouwanttouseadifferentlocale(oryouarenotsurewhichlocaleyoursystem
issetto),youcaninstruct
initdb
exactlywhichlocaletousebyspecifyingthe
--locale
option.
Forexample:
initdb --locale=sv_SE
This example for Unix systems sets the locale to Swedish (
sv
) as spoken in Sweden (
SE
).
Other possibilities might include
en_US
(U.S. English) and
fr_CA
(French Canadian). If
more thanone characterset canbe used fora locale thenthe specifications cantake the form
language_territory.codeset
.Forexample,
fr_BE.UTF-8
representstheFrenchlanguage(fr)
asspokeninBelgium(BE),withaUTF-8charactersetencoding.
Whatlocalesareavailableonyoursystemunderwhatnamesdependsonwhatwasprovidedbythe
operatingsystem vendorand whatwas installed. OnmostUnixsystems, thecommand
locale
-a
will provide a list of available locales. Windows uses more verbose locale names, such as
German_Germany
or
Swedish_Sweden.1252
,buttheprinciplesarethesame.
Occasionallyitisusefultomixrulesfromseverallocales,e.g.,useEnglishcollationrulesbutSpanish
messages.Tosupportthat,asetoflocalesubcategoriesexistthatcontrolonlycertainaspectsofthe
localizationrules:
LC_COLLATE
Stringsortorder
LC_CTYPE
Characterclassification(Whatisaletter?Its
upper-caseequivalent?)
544
Chapter22.Localization
LC_MESSAGES
Languageofmessages
LC_MONETARY
Formattingofcurrencyamounts
LC_NUMERIC
Formattingofnumbers
LC_TIME
Formattingofdatesandtimes
Thecategorynamestranslateintonamesof
initdb
optionstooverridethelocalechoiceforaspecific
category.Forinstance,tosetthelocaletoFrenchCanadian,butuseU.S.rulesforformattingcurrency,
use
initdb --locale=fr_CA --lc-monetary=en_US
.
Ifyouwantthesystemtobehaveasifithadnolocalesupport,usethespeciallocalename
C
,or
equivalently
POSIX
.
Somelocalecategoriesmusthavetheirvaluesfixedwhenthedatabaseiscreated.Youcanusediffer-
entsettingsfordifferentdatabases,butonceadatabaseiscreated,youcannotchangethemforthat
databaseanymore.
LC_COLLATE
and
LC_CTYPE
arethesecategories.Theyaffectthesortorderof
indexes,sotheymustbekeptfixed,orindexesontextcolumnswouldbecomecorrupt.(Butyoucan
alleviatethisrestrictionusingcollations,asdiscussedinSection22.2.)Thedefaultvaluesforthese
categoriesaredeterminedwhen
initdb
isrun,andthosevaluesareusedwhennewdatabasesare
created,unlessspecifiedotherwiseinthe
CREATE DATABASE
command.
Theotherlocalecategoriescanbechangedwheneverdesiredbysettingtheserverconfigurationpa-
rametersthathavethesamenameasthelocalecategories(seeSection18.11.2fordetails).Thevalues
thatarechosenby
initdb
areactuallyonlywrittenintotheconfigurationfile
postgresql.conf
to
serveasdefaultswhentheserverisstarted.Ifyouremovetheseassignmentsfrom
postgresql.conf
thentheserverwillinheritthesettingsfromitsexecutionenvironment.
Notethatthelocalebehavioroftheserverisdeterminedbytheenvironmentvariablesseenbythe
server,notbytheenvironmentofanyclient.Therefore, becarefultoconfigurethecorrectlocale
settingsbeforestartingtheserver.Aconsequenceofthis isthatifclientandserveraresetupin
differentlocales,messagesmightappearindifferentlanguagesdependingonwheretheyoriginated.
Note:Whenwespeakofinheritingthelocalefromtheexecutionenvironment,thismeansthe
followingonmostoperatingsystems:Foragivenlocalecategory,saythecollation,thefollowing
environmentvariablesareconsultedinthisorderuntiloneisfoundtobeset:
LC_ALL
,
LC_COLLATE
(orthevariablecorresponding totherespectivecategory),
LANG
.Ifnoneofthese environment
variablesaresetthenthelocaledefaultsto
C
.
Somemessagelocalizationlibrariesalsolookattheenvironmentvariable
LANGUAGE
whichover-
ridesallotherlocalesettingsforthepurposeofsettingthelanguageofmessages.Ifindoubt,
pleaserefertothedocumentationofyouroperatingsystem,inparticularthedocumentationabout
gettext.
Toenablemessagestobetranslatedtotheuser’spreferredlanguage,NLSmusthavebeenselectedat
buildtime(
configure --enable-nls
).Allotherlocalesupportisbuiltinautomatically.
22.1.2.Behavior
ThelocalesettingsinfluencethefollowingSQLfeatures:
Sortorderinqueriesusing
ORDER BY
orthestandardcomparisonoperatorsontextualdata
The
upper
,
lower
,and
initcap
functions
545
Chapter22.Localization
Patternmatchingoperators(
LIKE
,
SIMILAR TO
, andPOSIX-styleregularexpressions);locales
affectbothcaseinsensitivematchingandtheclassificationofcharactersbycharacter-classregular
expressions
The
to_char
familyoffunctions
Theabilitytouseindexeswith
LIKE
clauses
Thedrawbackofusinglocalesotherthan
C
or
POSIX
inPostgreSQLisitsperformanceimpact.It
slowscharacterhandlingandpreventsordinaryindexesfrombeingusedby
LIKE
.Forthisreasonuse
localesonlyifyouactuallyneedthem.
AsaworkaroundtoallowPostgreSQLtouseindexeswith
LIKE
clausesunderanon-Clocale,several
customoperatorclassesexist.Theseallowthecreationofanindexthatperformsastrictcharacter-by-
charactercomparison,ignoringlocalecomparisonrules.RefertoSection11.9formoreinformation.
Anotherapproachistocreateindexesusingthe
C
collation,asdiscussedinSection22.2.
22.1.3.Problems
Iflocalesupportdoesn’tworkaccordingtotheexplanationabove,checkthatthelocalesupportin
youroperatingsystemiscorrectlyconfigured.Tocheckwhatlocalesareinstalledonyoursystem,
youcanusethecommand
locale -a
ifyouroperatingsystemprovidesit.
CheckthatPostgreSQL is actuallyusingthe locale thatyouthink it is. The
LC_COLLATE
and
LC_CTYPE
settings aredeterminedwhenadatabaseis created,andcannotbechangedexceptby
creating a new database. . Other locale settings including
LC_MESSAGES
and
LC_MONETARY
are
initiallydeterminedbytheenvironmenttheserverisstartedin,butcanbechangedon-the-fly.You
canchecktheactivelocalesettingsusingthe
SHOW
command.
Thedirectory
src/test/locale
inthesourcedistributioncontainsatestsuiteforPostgreSQL’s
localesupport.
Clientapplicationsthathandleserver-sideerrorsbyparsingthetextoftheerrormessagewillobvi-
ouslyhaveproblemswhentheserver’smessagesareinadifferentlanguage.Authorsofsuchapplica-
tionsareadvisedtomakeuseoftheerrorcodeschemeinstead.
Maintainingcatalogsofmessagetranslationsrequirestheon-goingeffortsofmanyvolunteersthat
wanttoseePostgreSQLspeaktheirpreferredlanguagewell.Ifmessagesinyourlanguagearecur-
rentlynotavailableornotfullytranslated,yourassistancewouldbeappreciated.Ifyouwanttohelp,
refertoChapter51orwritetothedevelopers’mailinglist.
22.2. Collation Support
Thecollationfeatureallowsspecifyingthesortorderandcharacterclassificationbehaviorofdata
per-column,orevenper-operation.Thisalleviatestherestrictionthatthe
LC_COLLATE
and
LC_CTYPE
settingsofadatabasecannotbechangedafteritscreation.
22.2.1.Concepts
Conceptually,everyexpressionofacollatabledatatypehasacollation.(Thebuilt-incollatabledata
typesare
text
,
varchar
,and
char
.User-definedbasetypescanalsobemarkedcollatable,andof
546
Chapter22.Localization
courseadomainoveracollatabledatatypeiscollatable.)Iftheexpressionisacolumnreference,the
collationoftheexpressionisthedefinedcollationofthecolumn.Iftheexpressionisaconstant,the
collationisthedefaultcollationofthedatatypeoftheconstant.Thecollationofamorecomplex
expressionisderivedfromthecollationsofitsinputs,asdescribedbelow.
Thecollationofanexpressioncanbethe“default”collation,whichmeansthelocalesettingsdefined
forthedatabase.Itisalsopossibleforanexpression’scollationtobeindeterminate.Insuchcases,
orderingoperationsandotheroperationsthatneedtoknowthecollationwillfail.
Whenthedatabasesystemhastoperformanorderingoracharacterclassification,itusesthecollation
oftheinputexpression.Thishappens,forexample,with
ORDER BY
clausesandfunctionoroperator
callssuchas
<
.Thecollationtoapplyforan
ORDER BY
clauseissimplythecollationofthesort
key.Thecollationtoapplyforafunctionoroperatorcallisderivedfromthearguments,asdescribed
below.Inadditiontocomparisonoperators,collationsaretakenintoaccountbyfunctionsthatconvert
betweenloweranduppercaseletters,suchas
lower
,
upper
,and
initcap
;bypatternmatching
operators;andby
to_char
andrelatedfunctions.
Forafunctionoroperatorcall,thecollationthatisderivedbyexaminingtheargumentcollationsis
usedatruntimeforperformingthespecifiedoperation.Iftheresultofthefunctionoroperatorcall
isofacollatabledatatype,thecollationisalsousedatparsetimeasthedefinedcollationofthe
functionoroperatorexpression,incasethereisasurroundingexpressionthatrequiresknowledgeof
itscollation.
Thecollationderivationofanexpressioncanbeimplicitorexplicit.Thisdistinctionaffectshowcol-
lationsarecombinedwhenmultipledifferentcollationsappearinanexpression.Anexplicitcollation
derivationoccurswhena
COLLATE
clauseisused;allothercollationderivationsareimplicit.When
multiplecollationsneedtobecombined,forexampleinafunctioncall,thefollowingrulesareused:
1.Ifanyinputexpressionhasanexplicitcollationderivation,thenallexplicitlyderivedcollations
amongtheinputexpressionsmustbethesame,otherwiseanerrorisraised.Ifanyexplicitly
derivedcollationispresent,thatistheresultofthecollationcombination.
2.Otherwise,allinputexpressionsmusthavethesameimplicitcollationderivationorthedefault
collation.Ifanynon-defaultcollationispresent,thatistheresultofthecollationcombination.
Otherwise,theresultisthedefaultcollation.
3.Ifthereareconflictingnon-defaultimplicitcollationsamongtheinputexpressions,thenthecom-
binationisdeemedtohaveindeterminatecollation.Thisisnotanerrorconditionunless the
particularfunctionbeinginvokedrequiresknowledgeofthecollationitshouldapply.Ifitdoes,
anerrorwillberaisedatrun-time.
Forexample,considerthistabledefinition:
CREATE TABLE test1 (
a text COLLATE "de_DE",
b text COLLATE "es_ES",
...
);
Thenin
SELECT a < ’foo’ FROM test1;
the
<
comparisonisperformedaccordingto
de_DE
rules,becausetheexpressioncombinesanim-
plicitlyderivedcollationwiththedefaultcollation.Butin
SELECT a < (’foo’ COLLATE "fr_FR") FROM test1;
547
Documents you may be interested
Documents you may be interested