在 Postgresql 中计算列类型的大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12394538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating the size of a column type in Postgresql
提问by user7980
I am trying to figure out how to determine the size of a specific column in database for instance I have two columns called sourceip, destinationip that are both 16 byte fields.
我想弄清楚如何确定数据库中特定列的大小，例如我有两列名为 sourceip 和 destinationip，它们都是 16 字节字段。
I thought this would be somewhere in the information_schema or \d+ but I cannot find a specific command to isolate the size of each column type.
我认为这会在 information_schema 或 \d+ 中的某个地方，但我找不到特定的命令来隔离每个列类型的大小。
Can you calculate column type size in database or do you just have to reference the byte size for each type in the Postgresql documentation?
您可以计算数据库中的列类型大小，还是只需要在 Postgresql 文档中引用每种类型的字节大小？
回答by Pavel Stehule
only few types in pg has fixed length - almost all types are varlena type - it has dynamic length. You can check queries like
pg 中只有少数类型具有固定长度 - 几乎所有类型都是 varlena 类型 - 它具有动态长度。您可以检查查询，如
postgres=# select typlen from pg_type where oid = 'int'::regtype::oid;
typlen
--------
4
(1 row)
postgres=# select attlen from pg_attribute where attrelid = 'x'::regclass and attname = 'a';
attlen
--------
4
(1 row)
When result is not -1, then type has not fixed length
当结果不是 -1 时，则类型没有固定长度
for varlena types use pg_column_size function:
对于 varlena 类型，使用 pg_column_size 函数：
postgres=# \df *size*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------------------+------------------+---------------------+--------
pg_catalog | pg_column_size | integer | "any" | normal
pg_catalog | pg_database_size | bigint | name | normal
pg_catalog | pg_database_size | bigint | oid | normal
pg_catalog | pg_indexes_size | bigint | regclass | normal
pg_catalog | pg_relation_size | bigint | regclass | normal
pg_catalog | pg_relation_size | bigint | regclass, text | normal
pg_catalog | pg_size_pretty | text | bigint | normal
pg_catalog | pg_size_pretty | text | numeric | normal
pg_catalog | pg_table_size | bigint | regclass | normal
pg_catalog | pg_tablespace_size | bigint | name | normal
pg_catalog | pg_tablespace_size | bigint | oid | normal
pg_catalog | pg_total_relation_size | bigint | regclass | normal
(12 rows)
postgres=# select pg_column_size('Hello');
pg_column_size
----------------
6
(1 row)
postgres=# select pg_column_size(10);
pg_column_size
----------------
4
(1 row)
postgres=# select pg_column_size(now());
pg_column_size
----------------
8
