-
Notifications
You must be signed in to change notification settings - Fork 16
/
postgres.database.txt
14333 lines (11531 loc) · 787 KB
/
postgres.database.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
POSTGRES
TODO:
- continue going through this doc, refreshing it. Stopped at `psql`
- go through all `???` in this doc
- read full official docs, and compare with this doc
/=+===============================+=\
/ : : \
)==: VERSION :==(
\ :_______________________________: /
\=+===============================+=/
VERSION ==> #15.3
ARCHITECTURE ==> #RDBMS with focus on standard compliance and extensibility.
#Conform to SQL:2016 for most of it
#Client (psql, pgadmin, etc.) / server (postgres) architecture
pg_config|postgres --version
ICONF.server_version[_num]
version()->STR #Postgres full version, including OS
DATADIR|DBDIR/PG_VERSION #Postgres version used by a CLUSTER|DATABASE
/=+===============================+=\
/ : : \
)==: SUMMARY :==(
\ :_______________________________: /
\=+===============================+=/
DATABASE DESIGN ==> #Modelling (pgModeler)
# - create tables with right columns, constraints (pkey, fkey, default, check, not null, unique, exclude) and properties (inherits)
# - using:
# - normal types (NUM, BOOL, STR, BSTR, BYTEA, DATE|TIME, ENUM) and arrays, ranges
# - special types (net, dictionary, xml, json, hstore, ltree, geometry) and created types (ctype, domain)
# - id vs uuid
# - views for encapsulation, or security restriction per column
# - [event] triggers
# - sequences
# - schemas
# - foreign tables (including file_fdw for CSV files)
# - listen|notify for clients communication
#Security:
# - roles and privileges
# - authentication
# - PCONF.unix_socket_directories
# - FUNC definition (leakproof, security definer)
#Multithread-safety (transactions, locks)
#Watch out for:
# - null possibility in queries
# - SQL injection when concatenating 'SQL' (use quote_*() or format())
PERFORMANCE ==> # - on design, check using:
# - materialized views instead of views
# - rules instead of triggers
# - index
# - cursors
# - partitions
# - prepared statements
# - large objects
# - tablespaces
# - CONF tunning:
# - disabling durability, decreasing checkpoints frequency, using RAM disks
# - setting right resources needed for SCONF.work_mem, SCONF.maintenance_work_mem, SCONF.effective_cache_size,
# PCONF.wal_buffers, ZSCONF.max_stack_depth, ZSCONF.temp_file_limit,
# PCONF.max_files_per_processes, SCONF.effective_io_concurrency, PCONF.shared_buffers,
# PCONF.max_connections, SCONF.statement_timeout
# - using pgtune
# - optimizing queries with explain
# - using pgbench
# - for big data write, see below best practices
# - use connection pooling (pgBouncer)
# - upgrading hardware
# - FUNC definition (volatility, cost, rows)
# - TABLE fillfactor, fastupdate
# - autovacuum tunning
SETUP FOR END USERS # - create [A|W]FUNC (possibily from PL/* languages), prepared statements, comments
AND FUTURE MAINTENANCE ==> # - logging
# - [hot] standby with [a]sync. streaming replication
# - use pgagent for regular tasks:
# - pgbadger and pgcluu reports creation
# - check_postgres
# - good idea to merge pgbadger, pgcluu and check_postgres into one HTML file with a script
# - pg_dumpall
# - if durability, check proper cache usage (HCONF.wal_sync_method, HDD|filesystem cache)
TESTING ==> # - random filling (datafiller.py)
# - unit testing (pgTap)
# - load testing (Tsung)
MAINTENANCE ==> # - monitoring:
# - use pgadmin, with Server status window, and opening a psql within pgadmin (create proper .psqlrc), or use teamPostgreSQL
# - resource (should not exceed PCONF.max_connections and SCONF.work_mem), space usage or other problems:
# - pgbadger and pgcluu
# - pg_top
# - check_postgres
# - data update (should create functions):
# - partitionning
# - copy "TABLE" from
# - cluster/reindex (ask for exclusive lock)
# - check PostgreSQL upgrades, and use pg_upgrade
# - create restore points with pg_create_restore_point(STR) after critical operations
/=+===============================+=\
/ : : \
)==: UPGRADE :==(
\ :_______________________________: /
\=+===============================+=/
pg_upgrade #Upgrade a CLUSTER to a new major release of Postgres
#Should make a backup first
#Cannot be done on a log shipping standby
#Both servers must be down
#DATABASEs cannot use TYPE reg*, except regclass|regrole|regtype
ENVVAR PGBINOLD
--old-bindir|-b OLD_BINDIR
ENVVAR PGBINNEW
--new-bindir|-B NEW_BINDIR
ENVVAR PGDATAOLD
--old-datadir|-d OLD_DATADIR #NEW*DIR must be the new install, with similar settings
ENVVAR PGDATANEW #OLD*DIR must be erased afterwards
--new-datadir|-D NEW_DATADIR #Must use NEW_BINDIR/pg_upgrade
ENVVAR PGPORTOLD
-p OLD_PORT
ENVVAR PGPORTNEW
-P NEW_PORT #
PGSOCKETDIR
--socketdir|-s DIR #DIR with Unix sockets
--old-options|-o STR
--new-options|-O STR #`postgres` CLI flags
ENVVAR PGUSER
--username|-u ROLE #Initial session ROLE
--link|-k #Use hard links instead of copies. Faster.
#Must be on the same filesystem
#Cannot access old server afterwards
--clone #Same but using reflinks. Even faster.
#Must be on the same filesystem
--jobs|-j NUM #Use multiple processes
--no-sync|-N #Like initdb
--retain|-r #Do not cleanup logs
--check|-c #Dry run
/=+===============================+=\
/ : : \
)==: SYNTAX :==(
\ :_______________________________: /
\=+===============================+=/
COMMAND; #"Statement"
CLAUSE ==> #Part of a COMMAND attached to keywords
#E.g. WHERE BOOL
WHITESPACES ==> #Ignored
CASE-SENSITIVITY ==> #Case-insensitive for:
# - keywords (usually uppercase)
# - VARs (usually lowercase)
"VAR"
U&"VAR2" #Make VAR case-sensitive
-- COMMENT ...
/* COMMENT */ #
(VAR [= VAL], ...) #OPTS. Used in many SQL statements for named parameters.
#VAL is only optional if either:
# - it has a default value
# - it has no value, i.e. it is implicitly a BOOL
#Some commands allow specifying OPTS both in (...) and as keywords
# - prefer (...) as keywords tend to be deprecated then
(VAR [VAL], ...) #ZOPTS. Same without = sign
pg_options_to_table
('VAR=VAL'_ARR)->ROW_SET #Parses as ROW: option_name 'VAR', option_value 'VAL'
DDL #"Data Definition Language"
#Statements operating on entities themselves
#E.g. create, alter, drop, truncate, comment, etc.
DML #"Data Manipulation Language"
#Statements operating on entities contents
#E.g. select, insert, update, delete, explain, etc.
DCL #"Data Control Language"
#Statements operating on authorization
#E.g. grant, revoke, etc.
TCL #"Transaction Control Language"
#Statements operating on transactions
#E.g. commit, rollback, savepoint, etc.
OUTPUT ==> #Some statements return the NUM of ROWs manipulated.
#This is separate from the ROW_SET returned by a SUBQUERY, and is not printed by default
#For: select|insert|update|delete|merge|move|fetch|copy|create table as
.sql #File extension for SQL
application/sql #MIME type for SQL
/=+===============================+=\
/ : : \
)==: NAME :==(
\ :_______________________________: /
\=+===============================+=/
name #TYPE of a "VAR", used internally
ICONF.max_identifier_length #63. Max size of a "VAR"
NAME[NUM] #'CHAR'
VAR #Any identifier
#[[:alnum:]_]+
#Max 63 chars
"VAR" #Allows reserved keywords, case-sensitive, and do not trim whitespaces
#"" to escape "
as VAR #Some COMMANDs allows optional `as`.
#This allows reserved keywords, but remains case-insensitive unless quoted
SCONF.quote_all_identifiers #BOOL (def: false). Quote all "VAR" in output
"..." #Notation to means a VAR (with|without quoting)
#Including "TABLE", "COL", etc.
TEXT <-> NAME
VARCHAR|BPCHAR <=-> NAME #Type casting
pg_get_keywords()->ROW_SET #All SQL keywords
ROW.word #'KEYWORD'
ROW.catcode #CHAR among:
# - 'U': can be used by any NAME
# - due to being used only in unambiguous places
# - e.g. any ENTITY
# - 'C': cannot be used as TYPE|FUNC NAME
# - due to already being a TYPE|FUNC
# - 'T': cannot be used as COL NAME
# - due to ambiguity in SELECT query
# - 'R': cannot be used as COL|TYPE|FUNC NAME
ROW.catdesc #Like catcode, but as longer STR
ROW.barelabel #BOOL. If false, requires `as VAR`
ROW.baredesc #Like barecode, but as longer STR
/=+===============================+=\
/ : : \
)==: SCHEMA :==(
\ :_______________________________: /
\=+===============================+=/
create schema "SCHEMA" #Creates a SCHEMA, i.e. namespace inside a DATABASE
#Dependency parent of the ENTITYs it contains
create schema "SCHEMA" #Combine create schema + create ENTITYs within that SCHEMA
create ENTITY ... [,...] #ENTITY: table, view, index, sequence, trigger
#Can also use `grant ...` instead of `create ENTITY`
alter ENTITY "ENTITY"
set schema "SCHEMA" #For all ENTITYs that can have a SCHEMA
[SCHEMA.]"NAME" #"NAME"s are namespaced by SCHEMA
SCONF.search_path #'SCHEMA,...' used as default. Leftmost has priority.
#Def: '"$user", public'
current_schemas(BOOL) #Show SCONF.search_path
->'SCHEMA'_ARR #If false, do not include pg_catalog and pg_temp*
SEARCH PATH ATTACK ==> #Creating ENTITYs in a non-rightmost SCHEMA to in order to shadow ENTITYs in other SCHEMAs
#Can prevent by either:
# - not specifying shared SCHEMAs in SCONF.search_path
# - i.e. using only ROLE-specific ("$user") or session-specific (pg_temp) SCHEMAs
# - put one shared SCHEMA as rightmost
# - restrict `create` PRIVILEGE on shared SCHEMAs
set schema 'SCHEMA,...' #Same as: set SCONF.search_path to 'SCHEMA,...'
DEFAULT CREATE SCHEMA ==> #When creating a new ENTITY, use the leftmost SCHEMA of SCONF.search_path, except "$user"
current_schema
current_schema()->'SCHEMA' #Default write schema. null if none
pg_ENTITY_is_visible(OID)->BOOL #True if ENTITY's SCHEMA is within SCONF.search_path
"$user" #Can be used in SCONF.search_path, replaced by current "ROLE" name
#This SCHEMA must be manually created
#Meant for user-specific data
public #SCHEMA automatically created, initially empty
#Meant for non-user-specific data
#`public` ROLE has `usage` PRIVILEGEs
pg_namespace #TABLE with all SCHEMAs
pg_namespace.oid #OID
pg_namespace.nspname #NAME of "SCHEMA"
regnamespace #TYPE to cast pg_namespace.oid as "SCHEMA" name
pg_type.typnamespace #REGNAMESPACE of TYPE
pg_class.relnamespace #REGNAMESPACE of RELATION
pg_constraint.connamespace #REGNAMESPACE of CONSTRAINT
pg_ts_parser.prsnamespace #REGNAMESPACE of PARSER
pg_ts_template.tmplnamespace #REGNAMESPACE of TEMPLATE
pg_ts_dict.dictnamespace #REGNAMESPACE of DICTIONARY
pg_ts_config.cfgnamespace #REGNAMESPACE of REGCONF
pg_conversion.connamespace #REGNAMESPACE of CONVERSION
pg_collation.collnamespace #REGNAMESPACE of COLLATION
pg_opfamily.opfnamespace #REGNAMESPACE of OPFAMILY
pg_opclass.opcnamespace #REGNAMESPACE of OPCLASS
pg_seclabels.objnamespace #REGNAMESPACE|null of SECURITY_LABEL
pg_proc.pronamespace #REGNAMESPACE of FUNC
pg_operator.oprnamespace #REGNAMESPACE of OP
pg_statistic_ext.stxnamespace #REGNAMESPACE of STATISTICS
pg_tables.schemaname #"SCHEMA" name of TABLE
pg_indexes.schemaname #"SCHEMA" name of INDEX
pg_views.schemaname #"SCHEMA" name of VIEW
pg_matviews.schemaname #"SCHEMA" name of MVIEW
pg_sequences.schemaname #"SCHEMA" name of SEQUENCE
pg_stats.schemaname #"SCHEMA" name of analyzed TABLE
pg_policies.schemaname #"SCHEMA" name of POLICY
pg_rules.schemaname #"SCHEMA" name of RULE
pg_publication_tables.schemaname #"SCHEMA" name of PUB's TABLE
pg_stats_ext[_exprs]
.statistics_schemaname #"SCHEMA" name of STATISTICS
pg_stats_ext.schemaname #"SCHEMA" name of STATISTICS's TABLE
/=+===============================+=\
/ : : \
)==: PG_CATALOG :==(
\ :_______________________________: /
\=+===============================+=/
pg_catalog #SCHEMA for system data. "System catalogs"
#Contains all pg_* TABLE|VIEWs and builtins FUNC|TYPEs
#Prepended to SCONF.search_path
# - can be explicitly added to SCONF.search_path to override priority order
#Sometimes readonly. Should avoid modifying.
#DATABASE-specific unless on cluster-specific ENTITY
# - dependency child of DATABASE
PRIVILEGES ==> #Owned by CLUSTER|DATABASE owner
#Most TABLEs are read-only to `public`
#pg_settings is read-write
#A few TABLEs can only be seen by superuser
postgres -O
ZSCONF.allow_system_table_mods #BOOL (def: false). Allow modifying pg_catalog
pg_node_tree #Internal serialized information TYPE, specific to pg_catalog.*
#Is internally like a STR
pg_get_expr(PG_NODE_TREE, #Converts PG_NODE_TREE to STR
TABLE.oid[, BOOL])->STR #TABLE.oid can be 0 if PG_NODE_TREE does not contain variables
#BOOL (def: false) is prettify
/=+===============================+=\
/ : : \
)==: INFORMATION SCHEMA :==(
\ :_______________________________: /
\=+===============================+=/
information_schema #SCHEMA for system data
#Unlike pg_catalog, is standard SQL
#Does not contain Postgres-specific information
#Are all VIEWs over pg_catalog.*
#To document ???
DOMAIN TYPES ==> #The following are DOMAIN_TYPEs used only by information_schema
#All use not null
sql_identifier #NAME_TYPE, COLLATION "C"
yes_or_no #VARCHAR(3)_TYPE, 'YES|NO', COLLATION "C"
character_data #VARCHAR_TYPE, COLLATION "C"
cardinal_number #INT4_TYPE, >= 0
time_stamp #TIMESTAMPTZ_TYPE, default current_timestamp
/=+===============================+=\
/ : : \
)==: ENTITY :==(
\ :_______________________________: /
\=+===============================+=/
ENTITY #Entity that can be created with `create ENTITY ...`
#E.g. table, collation, function, etc.
"ENTITY" #Notation depends on entity. It can be one of the following.
VAR,... #For most ENTITYs
VAR #For access method, collation, conversion, database, event trigger, language,
#subscription, tablespace, text search *
VAR(...),... #For function|procedure|routine|aggregate
VAR(TYPE|none, TYPE2|none),... #For operator
(TYPE as TYPE2) #For cast
VAR on "TABLE" #For policy, trigger, rule
VAR using ACCESS_METHOD #For operator class|family
for TYPE language LANG #For transform
for RPROLE server SERVER #For user mapping
ENTITY_CATALOG #pg_* TABLE listing ENTITYs of a given kind
ENTITY ID ==> #The following three properties are often used to identify any ENTITY
class[o]id #REGCLASS of the ENTITY_CATALOG
obj[o]id #OID of the ENTITY within its ENTITY_CATALOG
objsubid #INT4. "COL" number. 0 if classid is not pg_class
pg_describe_object
(classid, objid, objsubid)->STR #'ENTITY NAME [of RELATION NAME2]'
pg_identify_object
(classid, objid, objsubid)->ROW #
ROW.type #'ENTITY'
ROW.schema #'SCHEMA'|null
ROW.name #'NAME'|null
ROW.identity #'SCHEMA.NAME[.COL]'
pg_identify_object_as_address
(classid, objid, objsubid)->ROW #
ROW.type #'ENTITY'
ROW.object_names #{SCHEMA,NAME[,COL]}
ROW.object_args #STR_ARR. Arguments passed to some ENTITYs
pg_get_object_address('ENTITY',
{SCHEMA,NAME[,COL]},STR_ARR)->ROW#ROW: classid, objid, objsubid
alter ENTITY ... "ENTITY" ... #Sets ENTITY options after creation
#Not for ENTITY: access method, cast, transform
#Several ENTITYs allow combining several `alter ...` into a single statement
# - faster than doing individual statements serially
# - for most actions of:
# - alter [foreign] table, materialized view, type "ROW"
# - alter function|procedure|routine
alter ENTITY "ENTITY" #Rename an ENTITY.
rename to "VAR" #Not for ENTITY: extension, operator, user mapping
create or replace ENTITY ... #If "ENTITY" already exists, drop it first.
#For ENTITY:
# - view, rule, trigger
# - function|procedure|aggregate
# - language, transform
create ENTITY if not exists ... #If "ENTITY" already exists, noop but no error
#For ENTITY: collation, extension, foreign table, index, materialized view, schema,
#sequence, server, statistics, table, user mapping
alter ENTITY if exists ... #Fail if "ENTITY" does not exist
#For ENTITY: foreign table, index, materialized view, sequence, table, view
create ENTITY ... with (OPTS) #Common syntax found in multiple ENTITYs
#If OPTS.VAR VAL optional, then also optional in alter ...
#When present the following are available too
alter ENTITY ... set (OPTS) #Sets OPTS after creation
alter ENTITY ... #Sets OPTS to default value
reset (OPTS.VAR,...) #Not with ENTITY: publication|subscription
drop ENTITY "ENTITY" #Delete an ENTITY
drop ENTITY if exists "ENTITY" #Unless set, fails if exists
/=+===============================+=\
/ : : \
)==: DEPENDENCIES :==(
\ :_______________________________: /
\=+===============================+=/
drop ENTITY ... restrict|cascade #If there are dependency child objects
# - 'restrict' (def): fail
# - 'cascade': drop them, recursively
# - not allowed for database|tablespace|role|user mapping
pg_[sh]depend #TABLE with child|parent dependencies between [cluster-wide] ENTITYs
pg_[sh]depend.classid
pg_[sh]depend.objid
pg_[sh]depend.objsubid #ENTITY
pg_[sh]depend.refclassid
pg_[sh]depend.refobjid
pg_[sh]depend.refobjsubid #ENTITY it depends on
pg_shdepend.dbid #pg_database.oid of DATABASE
pg_depend.deptype #'CHAR' for the type of dependence among:
# - 'n' ("normal"): delete child with `cascade`
# - 'a' ("auto"): delete child with `cascade|restrict`
# - 'i' ("internal"): like 'a', except if child is dropped due to another parent being
# deleted, its initial parent is deleted too
# - 'P|S' ("partition primary|secondary"): partition CHILD_TABLE
# - 'e' ("extension"): EXTENSION direct child
# - 'x' ("auto extension"): EXTENSION indirect child
pg_shdepend.deptype #'CHAR' for the type of dependence among:
# - 'o' ("owner"): ROLE owner child
# - 'a' ("ACL"): ROLE mentioned in ACL
# - 'r' ("policy"): ROLE mentioned in POLICY
# - 't' ("tablespace"): TABLESPACE parent of a TABLE
/=+===============================+=\
/ : : \
)==: COMMENT :==(
\ :_______________________________: /
\=+===============================+=/
comment on ENTITY "ENTITY" #Add a comment
is 'COMMENT' #Child auto-dependency of ENTITY
comment ... is null #Delete a comment
obj_description
(OID, 'ENTITY_CATALOG')
->STR|null #Get DATABASE-wide ENTITY's comment
shobj_description
(OID, 'ENTITY_CATALOG')
->STR|null #Get cluster-wide ENTITY's comment
col_description
(TABLE_OID, COL_NUM)->STR|null #Get COL's comment
\d* #Print comments under `description` column
pg_description #TABLE with all COMMENTs on DATABASE-wide ENTITYs
pg_description.classoid
pg_description.objoid
pg_description.objsubid #ENTITY
pg_description.description #'COMMENT'
pg_shdescription #TABLE with all COMMENTs on cluster-wide ENTITYs
#Same COLs except no objsubid
/=+===============================+=\
/ : : \
)==: TYPE DEFINITION :==(
\ :_______________________________: /
\=+===============================+=/
create type TYPE #Without YOPTS, creates a new "shell TYPE", i.e. TYPE declared but not implemented yet
#Can be used only to type a FUNC's arguments or return value
#Useful when creating a TYPE used in its own YOPTS.*
create type TYPE(YOPTS) #Creates a new "base TYPE", based on C functions
#Must be superuser
YOPTS.category #'CHAR' among:
# - 'B': BOOL
# - 'N': NUM (or OID-like)
# - 'S': STR|NAME
# - 'Z': char, pg_node_tree
# - 'V': BSTR
# - 'D': date|time[stamp][tz]
# - 'T': INTERVAL
# - 'A': ARR_TYPE
# - 'G': point|line|lseg|box|polygon|path|circle
# - 'I': inet|cidr
# - 'U': user-defined (including tid|cid|xid|xid8, bytea, EXTENSION TYPEs like json, etc.)
# - 'E': ENUM_TYPE
# - 'R': [MULTI]RANGE_TYPE
# - 'C': ROW_TYPE
# - 'P': pseudo-type
# - 'X': unknown
#Def: 'U'
YOPTS.default #VAL assigned as default value
YOPTS.like #TYPE2
YOPTS.input #FUNC(CSTR[, OID, TYPEMOD_INT4])->TYPE_VAL
#Converts 'UNKNOWN' to TYPE
#OID: of TYPE, or of TYPE[*] if ARR_TYPE
#Must be `strict`
YOPTS.output #FUNC(TYPE_VAL)->CSTR
#Converts TYPE to 'UNKNOWN'
YOPTS.receive #FUNC(INTERNAL[, OID, TYPEMOD_INT4])->TYPE_VAL
#Converts binary representation to TYPE
#INTERNAL is pointer towards bytes
#Must be `strict`
YOPTS.send #FUNC(TYPE_VAL)->BYTEA
#Converts TYPE to binary representation
YOPTS.typmod_in #FUNC(CSTR_ARR)->TYPEMOD_UINT
#"Type modifier", i.e. arbitrary arguments passed to TYPE
#Usually for variable-bounded length, e.g. bpchar(NUM)
#Arguments are converted to a single UINT
# - passed to YOPTS.input|receive and cast FUNCs
# - as TYPEMOD_INT4, -1 if no TYPEMOD
YOPTS.typmod_out #FUNC(TYPEMOD_UINT)->CSTR
#Def: returns '(UINT)'
pg_type #TABLE with all TYPEs
pg_type.oid #OID
pg_type.typname #"TYPE" name
pg_type.typtype #'CHAR' among:
# - 'b': base TYPE (typcategory 'B|N|S|Z|V|D|T|A|G|I|U')
# - 'e': ENUM_TYPE (typcategory 'E')
# - 'r': RANGE_TYPE (typcategory 'R')
# - 'm': MULTIRANGE_TYPE (typcategory 'R')
# - 'd': DOMAIN_TYPE (uses underlying TYPE's typcategory)
# - 'c': ROW_TYPE (typcategory 'C')
# - 'p': pseudo-type (typcategory 'P|X')
pg_type.typcategory #'CHAR' of YOPTS.category
pg_type.typdefault #STR of YOPTS.default. Can be:
# - null: none
# - VAL: if `create type` was used
# - 'EXPR': with DOMAIN_TYPE
pg_type.typinput #REGPROC of YOPTS.input
pg_type.typoutput #REGPROC of YOPTS.output
pg_type.typreceive #REGPROC of YOPTS.receive. 0 if none.
pg_type.typsend #REGPROC of YOPTS.send. 0 if none.
pg_type.typmodin #REGPROC of YOPTS.typemod_in. 0 if none.
pg_type.typmodout #REGPROC of YOPTS.typemod_out. 0 if none.
pg_type.typanalyze #REGPROC of YOPTS.analyze. 0 if none.
pg_type.typisdefined #BOOL. Temporarily set to false while `create type` is ongoing.
regtype #TYPE to cast pg_type.oid as "TYPE" name
format_type
(TYPE_OID, TYPEMOD_UINT|null)
->'TYPE' #
/=+===============================+=\
/ : : \
)==: DOMAIN :==(
\ :_______________________________: /
\=+===============================+=/
create domain "TYPE" [as] TYPE2 #Creates a TYPE based on TYPE2 but with additional CONSTRAINTs
create domain ... default VAL #Default VAL (def: null)
#Can be `default`
alter domain "TYPE"
set default VAL #
alter domain "TYPE" drop default #
create domain ... [not] null #
alter domain "TYPE"
set|drop not null #
create domain ...
check(BOOL_REXPR) #Can use `value` to refer to the new value
create domain ...
constraint "CONSTRAINT" ... #Set name of "CONSTRAINT" underlying [not] null or check()
alter domain "TYPE"
rename constraint
"CONSTRAINT" to "CONSTRAINT2" #
alter domain "TYPE"
drop constraint
[if exists] "CONSTRAINT"
[restrict|cascade] #
pg_type.typbasetype #REGTYPE of underlying TYPE of DOMAIN_TYPE. 0 if not DOMAIN_TYPE
pg_type.typtypmod #TYPEMOD_INT4 passed to underlying TYPE of DOMAIN_TYPE
#0 if not DOMAIN_TYPE, or if no TYPEMOD
pg_attribute.atttypmod #Same for COL
pg_type.typndims #INT4. Number of dimensions of underlying ARR_TYPE of DOMAIN_TYPE
#0 if not DOMAIN_TYPE, or if underlying TYPE is not ARR_TYPE
pg_attribute.attndims #Same for COL
pg_type.typdefaultbin #PG_NODE_TREE of the default value, if using DOMAIN_TYPE
pg_type.typnotnull #BOOL. `not null` on DOMAIN_TYPE. 0 if not DOMAIN_TYPE
pg_constraint.contypid #REGTYPRE of CONSTRAINT's DOMAIN.
#0 if CONSTRAINT is on a TABLE instead
/=+===============================+=\
/ : : \
)==: TYPE CASTING :==(
\ :_______________________________: /
\=+===============================+=/
pg_typeof(VAL)->REGTYPE #
cast(VAL as TYPE)
VAL::TYPE #Explicit casting
TYPE '...' #Explicit casting, but UNKNOWN '...' only
ASSIGNMENT CASTING ==> #Automatic casting when:
# - inserting a value in a COL
# - assigning|returning a value inside a FUNC
#Always implies explicit casting too
IMPLICIT CASTING ==> #Automatic casting when passing arguments to a FUNC
#Always implies explicit + assignment casting too
TYPE -> TYPE2 #Means can do implicit casting
TYPE => TYPE2 #Means can do assignment casting
TYPE ~> TYPE2 #Means can do explicit casting
TYPE <=-> TYPE2 #Means TYPE2 => TYPE, TYPE -> TYPE2
create cast (TYPE as TYPE2) #Creates a type casting FUNC
with function FUNC(...) #Must be FUNC(TYPE1_VAL[, TYPEMOD_INT4[, BOOL]])->TYPE2_VAL
#BOOL is true if explicit cast
#Current ROLE must own TYPE[2]
create cast (TYPE as TYPE2) #Create a type cast that does:
with inout # - TYPE1_VAL -> 'TYPE1_UNKNOWN'
# - 'TYPE1_UNKNOWN' -> TYPE2_VAL
#I.e. uses YOPTS.output|input
create cast (TYPE as TYPE2) #Create a type cast when TYPE[2] are binary-equivalent.
without function #I.e. uses YOPTS.send|receive
create cast ... as assignment
create cast ... as implicit #Assignment|implicit casting (def: explicit)
pg_cast #TABLE with implicit|explicit type casting
pg_cast.oid #OID
pg_cast.castsource|casttarget #REGTYPE of source|target TYPE
pg_cast.castfunc #REGPROC of FUNC used for conversion
pg_cast.castcontext #Casting:
# - 'e': explicit
# - 'a': assignment
# - 'i': implicit
pg_cast.castmethod #How to cast, among:
# - 'f': `with function`
# - 'i': `with inout`
# - 'b': `without function`
YOPTS.preferred #BOOL (def: false). Each YOPTS.category has a single true, which is the preferred type cast.
pg_type.typispreferred #Also used as the preferred TYPE in:
# - overloaded FUNCs
# - anycompatible*
#For:
# - NUM -> FLOAT8
# - STR -> TEXT
# - BSTR -> VARBIT
# - date|time[stamp][tz] -> TIMESTAMPTZ
# - REG* -> OID
# - inet|cidr -> INET
/=+===============================+=\
/ : : \
)==: LARGE TYPES :==(
\ :_______________________________: /
\=+===============================+=/
FIXED LENGTH TYPES ==> #Types that do not have a variable length
VARIABLE BOUNDED LENGTH TYPES #One of:
==> # - numeric(...)
# - varchar|bpchar(...)
# - bit(...)
# - time[stamp][tz]|interval(...)
VARIABLE UNBOUNDED LENGTH TYPES #One of:
==> # - numeric
# - text
# - bit varying, bytea
# - pg_node_tree, pg_snapshot
# - [MULTI]RANGE_TYPE
# - ARR_TYPE
# - ROW_TYPE
# - any other TYPE built from variable '...': json[b]|jsonpath, xml, hstore,
# ltree|l[txt]query, query_int, path|polygon, inet|cidr, tsvector|tsquery
PHYSICAL SIZE ==> #Additional 1|4|18 bytes, max 1GB (see TOAST)
SIZING CAST ==> #Type cast to same type
#Used to enforce size of variable bounded length TYPEs
# - failing if too large
# - padding or truncating
#Usually implicit casting
YOPTS.internallength #INT2. Number of bytes of a TYPE.
#Can be `variable` for variable bounded|unbounded TYPE
pg_type.typlen #INT2 of YOPTS.internallength. Can be:
# - -2: cstring|unknown
# - -1: variable bounded|unbounded TYPE
pg_attribute.attlen #Same for COL
pg_column_size(VAL)->INT4 #Size of VAL, in bytes, including:
# - additional 1|4|8 bytes
# - TOAST
# - compression
YOPTS.alignment #'CHAR' specifying how many bytes to align the values when stored, i.e. padding them if necessary.
pg_type.typalign #Can be:
# - 'c' (1 byte, i.e. no alignment)
# - 's' (2 bytes)
# - 'i' (4 bytes)
# - 'd' (8 bytes, usually)
#Usually same as YOPTS.internallength for fixed-length TYPEs except:
# - no alignment: cstring|unknown, name, uuid
# - 2 bytes: tidd
#For variable length TYPEs:
# - ARR|RANGE_TYPE: uses underlying TYPE's alignment
# - 8 bytes: ROW_TYPE, PATH|POLYGON
# - 4 bytes: all others
pg_attribute.attalign #Same for COL
YOPTS.passedbyvalue #BOOL. Whether the TYPE is passed by value or by reference.
pg_type.typbyval #Builtin TYPEs: true if fixed length of 1-8 bytes, except macaddr[8] and tid
pg_attribute.attbyval #Same for COL
/=+===============================+=\
/ : : \
)==: TOAST :==(
\ :_______________________________: /
\=+===============================+=/
TOAST ==> #Large variable length TYPE values are split them in several ~2KB chunks
#Goals:
# - fit in a single 8KB heap page, so that computation can happen in-memory only, which is faster
# - compression, to save space
#Not used by non-persisted data ("on-disk TOAST"), except in few specific cases ("in-memory TOAST").
IN-LINE TOAST ==> #Keeps data as is but possibly compress it
#Requires 1 (if <127 bytes) or 4 additional bytes
#Used if <2005 bytes
OUT-OF-LINE TOAST ==> #Replace data with 18 bytes pointer to pg_toast_OID TABLE
#Used if >=2005 bytes
pg_class.reltoastrelid #REGCLASS of the RELATION's pg_toast_OID "TABLE". 0 if none
pg_toast.* #SCHEMA with out-of-line data
pg_toast.pg_toast_OID #"TABLE" with TOAST out-of-line data
pg_toast.pg_toast_OID.chunk_id #Data ID
pg_toast.pg_toast_OID.chunk_seq #Data chunk serial NUM
pg_toast.pg_toast_OID.chunk_data #BYTEA with the data
pg_toast_temp_NUM.* #Same with TEMP TABLEs
#Same special behavior as pg_temp_NUM
ICONF.segment_size #1.3e6 (i.e. 1GB). Max amount of heap pages in a TOASTED type.
#I.e. max size of any TYPE value, especially variable unbounded length ones.
SCONF.default_toast_compression #Compress TOASTed values (in-line or out-of-line) among:
# - 'pglz' (def): Postgres-specific LZMA-like, optimized for speed (not space)
# - 'lz4': optimized for space
# - requires compiling with --with-lz4 (is the case with Ubuntu)
create table "TABLE"
("COL" TYPE compression ENUM,...)
alter table "TABLE"
alter [column] "COL"
set compression ENUM #Sets compression, like SCONF.default_toast_compression
pg_attribute.attcompression #'CHAR' of compression among '\0' (default_toast_compression), 'p' (pglz), 'l' (lz4)
pg_column_compression(VAL)->STR #Returns compression 'pglz|lz4' or null
alter [foreign] table "TABLE" #Sets TOAST behavior among:
alter [column] "COL" # - plain
set storage ENUM # - no out-of-line nor compression
# - only possible value for fixed length TYPEs
# - extended:
# - both out-of-line and compression
# - built-in TYPEs: for variable length TYPEs
# - external:
# - out-of-line but not compression
# - i.e. faster computation but more space
# - main:
# - no out-of-line but compression
# - might still be out-of-line, but only as last resort after extended|external ones
# - built-in TYPEs: numeric, inet|cidr
pg_attribute.attstorage #'CHAR' for TOAST behavior for the COL
#Can be 'p|x|e|m' for plain|extended|external|main
YOPTS.storage
pg_type.typstorage #Same for a TYPE, i.e. setting default value for COLs using it
TOPTS.toast_tuple_target #UINT. Do not use out-of-line nor compression for values < UINT bytes
#Min: 128, max: 8160, def: 2040 (which is usually good)
OPTS.process_toast #BOOL (def: true). Whether `vacuum` applies to TOAST_TABLEs too
#Always `on` if `vacuum full`
/=+===============================+=\
/ : : \
)==: LARGE OBJECTS :==(
\ :_______________________________: /
\=+===============================+=/
LARGEOBJ #VAL that is persisted as its own file
#Meant for large values
# - always on-file, not in-memory
# - conceptually like a file path or file descriptor
# - i.e. low memory but slow read|write
#Split into 2KB chunks
LARGE OBJECT #"ENTITY" name
OID #When specifying as argument of LARGEOBJ creation, allows specifying its OID
#Can be 0|-1 to ask for a new one
lo_import('PATH'[, OID])->OID #Creates a LARGEOBJ from a file
#Must be superuser
lo_export(OID, 'PATH') #Creates a file from a LARGEOBJ
#Must be superuser
lo_creat(OID)->OID #Creates an empty LARGEOBJ
lo_from_bytea(OID, BYTEA)->OID #Creates a LARGEOBJ from BYTEA
lo_unlink(OID) #Delete LARGEOBJ from database. Must be done after use
#Does not remove from files, if imported from files
lo_get(OID[, INT8, INT4])->BYTEA #Reads LARGEOBJ
#If INT8, only from that byte, with a length of INT4 bytes
lo_put(OID, INT8, BYTEA) #Writes LARGEOBJ
pg_largeobject_metadata #TABLE with all LARGEOBJ metadata
pg_largeobject_metadata.oid #OID of LARGEOBJ
pg_largeobject #TABLE with all LARGEOBJ data
#Visible only to superuser
pg_largeobject.loid #pg_largeobject_metadata.oid of LARGEOBJ
pg_largeobject.data #BYTEA with contents
pg_largeobject.pageno #INT 0-based index, if BYTEA split into several chunks
log_manage("COL") #Calls lo_unlink() on all LARGEOBJ OIDs that have been removed|changed since last call
#Should be done as a TFUNC: before update or delete, for each row
#Must `delete * from TABLE` before `drop|truncate table` to ensure TFUNC is called
#Trusted postgres extension 'lo'
lo #TYPE that abstracts LARGEOBJ
#Must be used as log_manage() "COL" TYPE
vacuumlo DATABASE... #CLI. Remove LARGEOBJs which OIDs does not exist in DATABASE anymore
--limit|-l NUM #Max of LARGEOBJs to remove (def: 1000). Meant for performance
#Can be 0
--dry-run|-n #
--host|-h HOST
--port|-p PORT
--username|-U USER
--no-password|-w
--password|-W #LIBPQ connection
/=+===============================+=\
/ : : \
)==: EQUAL :==(
\ :_______________________________: /
\=+===============================+=/
operator([SCHEMA.]OP) #Another way to write OP
#Default SCHEMA: pg_catalog
OPERATORS ==> #Operators shared by all TYPEs, except a few documented as such
OPERAND ORDER ==> #The evaluation order of FUNC|OPs arguments is not defined. It might be reordered by query planner.
#E.g. with select VALS,... or with or|and chains
(VAL) #Parenthesis to override operator order
VAL = VAL2
VAL != VAL2
VAL <> VAL2 #BOOL
VAL [not] in (VAL2,...) #BOOL. Whether VAL = <> any VAL2
case [LVAL]
when TVAL then RVAL
[...] #Switch statement. Substitutes to RVAL where LVAL = TVAL
[else RVAL] #Def LVAL is true, i.e. can use TVAL BOOLs, like an if statement
end #Def RVAL: null
/=+===============================+=\
/ : : \
)==: COMPARE :==(
\ :_______________________________: /
\=+===============================+=/
VAL > >= < <= VAL2 #BOOL
VAL [not] between [symmetric] #BOOL. Same as VAL >= VAL2 and VAL <= VAL3
VAL2 and VAL3 #If VAL3 < VAL2:
# - if symmetric: swap them
# - otherwise: returns false
greatest|least(VAL...)->BOOL #Using > <
min|max(SET)->VAL #AFUNC
/=+===============================+=\
/ : : \
)==: UNKNOWN :==(
\ :_______________________________: /
\=+===============================+=/
unknown #Unknown TYPE
#Has no operators, but is usually implicitly transtyped based on operators
#Cannot be used in an ARR_TYPE
#Also called "string"
unknown -> VAL #Possible with any TYPE, except any*
VAL ~> unknown #Never possible, except with unknown itself