在Postgresql中计算列类型的大小 [英] Calculating the size of a column type in Postgresql

问题描述

我试图弄清楚如何确定数据库中特定列的大小，例如，我有两个列分别称为sourceip和destinationip，它们都是16字节字段。

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.

我以为这将是在information_schema或\d +中的某个地方，但是我找不到用于隔离每种列类型大小的特定命令。

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.

您可以计算数据库中的列类型大小吗？还是只需要在Postgresql文档中引用每种类型的字节大小？

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?

推荐答案

pg中只有少数类型具有固定长度-几乎所有类型都是varlena类型-具有动态长度。您可以检查类似

only few types in pg has fixed length - almost all types are varlena type - it has dynamic length. You can check queries like

 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)

结果不是-1时，类型不是固定长度

When result is not -1, then type has not fixed length

对于varlena类型，请使用pg_column_size函数：

for varlena types use pg_column_size function:

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

这篇关于在Postgresql中计算列类型的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！