PostgreSQL DBA(80) - Object Identifier Types

husthxd發表於2019-08-15

本節介紹了PostgreSQL的OID型別,官方文件的定義是:
>Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. Type >oid repesents an object identifier.

OID定義為無符號整型數字,PG內建了轉換函式從*regproc, regprocedure, regoper, regoperator, regclass, regtype, regrole, regnamespace, regconfig, and regdictionary*這些型別轉換為OID.

TODO

下面是一些例子:
查詢某張表的屬性
```
[local]:5432 pg12@testdb=# SELECT * FROM pg_attribute
  WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'tbl') limit 1;
-[ RECORD 1 ]-+---------
attrelid      | 32774
attname       | tableoid
atttypid      | 26
attstattarget | 0
attlen        | 4
attnum        | -6
attndims      | 0
attcacheoff   | -1
atttypmod     | -1
attbyval      | t
attstorage    | p
attalign      | i
attnotnull    | t
atthasdef     | f
atthasmissing | f
attidentity   | 
attgenerated  | 
attisdropped  | f
attislocal    | t
attinhcount   | 0
attcollation  | 0
attacl        | 
attoptions    | 
attfdwoptions | 
attmissingval |

Time: 5.209 ms

-->

[local]:5432 pg12@testdb=# SELECT * FROM pg_attribute where attrelid = 'tbl'::regclass limit 1;
-[ RECORD 1 ]-+---------
attrelid      | 32774
attname       | tableoid
atttypid      | 26
attstattarget | 0
attlen        | 4
attnum        | -6
attndims      | 0
attcacheoff   | -1
atttypmod     | -1
attbyval      | t
attstorage    | p
attalign      | i
attnotnull    | t
atthasdef     | f
atthasmissing | f
attidentity   | 
attgenerated  | 
attisdropped  | f
attislocal    | t
attinhcount   | 0
attcollation  | 0
attacl        | 
attoptions    | 
attfdwoptions | 
attmissingval |

Time: 1.205 ms

```

查詢運算子+的相關資訊
```
[local]:5432 pg12@testdb=# select * from pg_operator where oid='+(int,float)'::regoperator;
ERROR:  operator does not exist: +(int,float)
LINE 1: select * from pg_operator where oid='+(int,float)'::regopera...
                                            ^
Time: 1.502 ms
[local]:5432 pg12@testdb=# select * from pg_operator where oid='+(float,float)'::regoperator;
-[ RECORD 1 ]+---------
oid          | 591
oprname      | +
oprnamespace | 11
oprowner     | 10
oprkind      | b
oprcanmerge  | f
oprcanhash   | f
oprleft      | 701
oprright     | 701
oprresult    | 701
oprcom       | 591
oprnegate    | 0
oprcode      | float8pl
oprrest      | -
oprjoin      | -

Time: 3.446 ms
```
注意:運算子需指定引數.

**參考資料**
[Object Identifier Types](https://www.postgresql.org/docs/12/datatype-oid.html)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2653813/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

TypeScript 之 Object Types
2021-11-23
TypeScriptObject
PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQL
2019-06-27
SQLIndex
CATPROC - Packages and Types Invalid in Dba_registry-457861.1
2013-06-07
Package
PostgreSQL DBA(35) - CTE
2019-06-03
SQL
PostgreSQL DBA(42) - locale
2019-06-25
SQL
object_id and data_object_id in dba_objects
2009-05-18
Object
PostgreSQL DBA(133) - Extension(postgresql_anonymizer)
2019-11-19
SQL
PostgreSQL DBA(59) - Index(Bloom)
2019-07-18
SQLIndexOOM
PostgreSQL DBA(53) - Index(BRIN)
2019-07-11
SQLIndex
PostgreSQL DBA(58) - DBLink
2019-07-18
SQL
PostgreSQL DBA(48) - Index(GiST)
2019-07-04
SQLIndex
PostgreSQL DBA(51) - Index(GIN)
2019-07-09
SQLIndex
PostgreSQL DBA(52) - Index(RUM)
2019-07-10
SQLIndex
PostgreSQL DBA(47) - Index(Btree)
2019-07-02
SQLIndex
PostgreSQL DBA(43) - Index(Hash)
2019-06-26
SQLIndex
PostgreSQL DBA(191) - Collation
2022-04-30
SQL
PostgreSQL DBA(182) - HOT
2021-05-24
SQL
PostgreSQL DBA(185) - watch
2021-09-17
SQL
PostgreSQL DBA(181) - Using PostgreSQL as a Data Warehouse
2021-05-20
SQL
PostgreSQL DBA(175) - Cost EST（SeqScan）
2020-06-17
SQL
PostgreSQL DBA(150) - Extension(pgmetrics)
2019-12-19
SQL
PostgreSQL DBA(91) - PG upgrade
2019-09-12
SQL
PostgreSQL DBA(73) - dump函式
2019-08-12
SQL函式
PostgreSQL DBA(86) - Table Parameter(fillfactor)
2019-08-27
SQL
PostgreSQL DBA(87) - benchmarksql簡介
2019-08-28
SQL
PostgreSQL DBA(41) - PG Index Properties
2019-06-25
SQLIndex
PostgreSQL DBA(184) - Extension(hypoPG)
2021-06-17
SQL
PostgreSQL DBA(10) - 統計資訊
2018-10-06
SQL
PostgreSQL DBA(8) - sysbench簡介
2018-09-28
SQL
PostgreSQL DBA(186) - SQL Group By
2022-02-11
SQL
PostgreSQL DBA(142) - PG 12（Monitoring PostgreSQL VACUUM processes）
2019-12-10
SQL
PostgreSQL DBA(158) - Locks(tuple locks)
2020-01-14
SQL
PostgreSQL DBA(134) - Extension(auto_explain)
2019-11-20
SQLAI
Oracle vs PostgreSQL DBA（21）- Oracle VPD
2019-11-25
OracleSQL
PostgreSQL DBA(125) - PG 12（TPCC）
2019-11-11
SQL
PostgreSQL DBA(53) - PG 12 Generated columns
2019-07-12
SQL
PostgreSQL DBA(72) - Extension(pgplsql_check)
2019-08-09
SQL
PostgreSQL DBA(77) - Locks(Lock Monitoring)
2019-08-14
SQL