Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes14kdownloads
infoschema-columns.html337 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>37.17. columns</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="infoschema-column-udt-usage.html" title="37.16. column_udt_usage" /><link rel="next" href="infoschema-constraint-column-usage.html" title="37.18. constraint_column_usage" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">37.17. <code class="literal">columns</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="infoschema-column-udt-usage.html" title="37.16. column_udt_usage">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="information-schema.html" title="Chapter 37. The Information Schema">Up</a></td><th width="60%" align="center">Chapter 37. The Information Schema</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="infoschema-constraint-column-usage.html" title="37.18. constraint_column_usage">Next</a></td></tr></table><hr /></div><div class="sect1" id="INFOSCHEMA-COLUMNS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">37.17. <code class="literal">columns</code> <a href="#INFOSCHEMA-COLUMNS" class="id_link">#</a></h2></div></div></div><p>3   The view <code class="literal">columns</code> contains information about all4   table columns (or view columns) in the database.  System columns5   (<code class="literal">ctid</code>, etc.) are not included.  Only those columns are6   shown that the current user has access to (by way of being the7   owner or having some privilege).8  </p><div class="table" id="id-1.7.6.21.3"><p class="title"><strong>Table 37.15. <code class="structname">columns</code> Columns</strong></p><div class="table-contents"><table class="table" summary="columns Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">9       Column Type10      </p>11      <p>12       Description13      </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">14       <code class="structfield">table_catalog</code> <code class="type">sql_identifier</code>15      </p>16      <p>17       Name of the database containing the table (always the current database)18      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">19       <code class="structfield">table_schema</code> <code class="type">sql_identifier</code>20      </p>21      <p>22       Name of the schema containing the table23      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">24       <code class="structfield">table_name</code> <code class="type">sql_identifier</code>25      </p>26      <p>27       Name of the table28      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">29       <code class="structfield">column_name</code> <code class="type">sql_identifier</code>30      </p>31      <p>32       Name of the column33      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">34       <code class="structfield">ordinal_position</code> <code class="type">cardinal_number</code>35      </p>36      <p>37       Ordinal position of the column within the table (count starts at 1)38      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">39       <code class="structfield">column_default</code> <code class="type">character_data</code>40      </p>41      <p>42       Default expression of the column43      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">44       <code class="structfield">is_nullable</code> <code class="type">yes_or_no</code>45      </p>46      <p>47       <code class="literal">YES</code> if the column is possibly nullable,48       <code class="literal">NO</code> if it is known not nullable.  A not-null49       constraint is one way a column can be known not nullable, but50       there can be others.51      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">52       <code class="structfield">data_type</code> <code class="type">character_data</code>53      </p>54      <p>55       Data type of the column, if it is a built-in type, or56       <code class="literal">ARRAY</code> if it is some array (in that case, see57       the view <code class="literal">element_types</code>), else58       <code class="literal">USER-DEFINED</code> (in that case, the type is59       identified in <code class="literal">udt_name</code> and associated60       columns).  If the column is based on a domain, this column61       refers to the type underlying the domain (and the domain is62       identified in <code class="literal">domain_name</code> and associated63       columns).64      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">65       <code class="structfield">character_maximum_length</code> <code class="type">cardinal_number</code>66      </p>67      <p>68       If <code class="literal">data_type</code> identifies a character or bit69       string type, the declared maximum length; null for all other70       data types or if no maximum length was declared.71      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">72       <code class="structfield">character_octet_length</code> <code class="type">cardinal_number</code>73      </p>74      <p>75       If <code class="literal">data_type</code> identifies a character type,76       the maximum possible length in octets (bytes) of a datum; null77       for all other data types.  The maximum octet length depends on78       the declared character maximum length (see above) and the79       server encoding.80      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">81       <code class="structfield">numeric_precision</code> <code class="type">cardinal_number</code>82      </p>83      <p>84       If <code class="literal">data_type</code> identifies a numeric type, this85       column contains the (declared or implicit) precision of the86       type for this column.  The precision indicates the number of87       significant digits.  It can be expressed in decimal (base 10)88       or binary (base 2) terms, as specified in the column89       <code class="literal">numeric_precision_radix</code>.  For all other data90       types, this column is null.91      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">92       <code class="structfield">numeric_precision_radix</code> <code class="type">cardinal_number</code>93      </p>94      <p>95       If <code class="literal">data_type</code> identifies a numeric type, this96       column indicates in which base the values in the columns97       <code class="literal">numeric_precision</code> and98       <code class="literal">numeric_scale</code> are expressed.  The value is99       either 2 or 10.  For all other data types, this column is null.100      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">101       <code class="structfield">numeric_scale</code> <code class="type">cardinal_number</code>102      </p>103      <p>104       If <code class="literal">data_type</code> identifies an exact numeric105       type, this column contains the (declared or implicit) scale of106       the type for this column.  The scale indicates the number of107       significant digits to the right of the decimal point.  It can108       be expressed in decimal (base 10) or binary (base 2) terms, as109       specified in the column110       <code class="literal">numeric_precision_radix</code>.  For all other data111       types, this column is null.112      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">113       <code class="structfield">datetime_precision</code> <code class="type">cardinal_number</code>114      </p>115      <p>116       If <code class="literal">data_type</code> identifies a date, time,117       timestamp, or interval type, this column contains the (declared118       or implicit) fractional seconds precision of the type for this119       column, that is, the number of decimal digits maintained120       following the decimal point in the seconds value.  For all121       other data types, this column is null.122      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">123       <code class="structfield">interval_type</code> <code class="type">character_data</code>124      </p>125      <p>126       If <code class="literal">data_type</code> identifies an interval type,127       this column contains the specification which fields the128       intervals include for this column, e.g., <code class="literal">YEAR TO129       MONTH</code>, <code class="literal">DAY TO SECOND</code>, etc.  If no130       field restrictions were specified (that is, the interval131       accepts all fields), and for all other data types, this field132       is null.133      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">134       <code class="structfield">interval_precision</code> <code class="type">cardinal_number</code>135      </p>136      <p>137       Applies to a feature not available138       in <span class="productname">PostgreSQL</span>139       (see <code class="literal">datetime_precision</code> for the fractional140       seconds precision of interval type columns)141      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">142       <code class="structfield">character_set_catalog</code> <code class="type">sql_identifier</code>143      </p>144      <p>145       Applies to a feature not available in <span class="productname">PostgreSQL</span>146      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">147       <code class="structfield">character_set_schema</code> <code class="type">sql_identifier</code>148      </p>149      <p>150       Applies to a feature not available in <span class="productname">PostgreSQL</span>151      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">152       <code class="structfield">character_set_name</code> <code class="type">sql_identifier</code>153      </p>154      <p>155       Applies to a feature not available in <span class="productname">PostgreSQL</span>156      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">157       <code class="structfield">collation_catalog</code> <code class="type">sql_identifier</code>158      </p>159      <p>160       Name of the database containing the collation of the column161       (always the current database), null if default or the data type162       of the column is not collatable163      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">164       <code class="structfield">collation_schema</code> <code class="type">sql_identifier</code>165      </p>166      <p>167       Name of the schema containing the collation of the column, null168       if default or the data type of the column is not collatable169      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">170       <code class="structfield">collation_name</code> <code class="type">sql_identifier</code>171      </p>172      <p>173       Name of the collation of the column, null if default or the174       data type of the column is not collatable175      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">176       <code class="structfield">domain_catalog</code> <code class="type">sql_identifier</code>177      </p>178      <p>179       If the column has a domain type, the name of the database that180       the domain is defined in (always the current database), else181       null.182      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">183       <code class="structfield">domain_schema</code> <code class="type">sql_identifier</code>184      </p>185      <p>186       If the column has a domain type, the name of the schema that187       the domain is defined in, else null.188      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">189       <code class="structfield">domain_name</code> <code class="type">sql_identifier</code>190      </p>191      <p>192       If the column has a domain type, the name of the domain, else null.193      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">194       <code class="structfield">udt_catalog</code> <code class="type">sql_identifier</code>195      </p>196      <p>197       Name of the database that the column data type (the underlying198       type of the domain, if applicable) is defined in (always the199       current database)200      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">201       <code class="structfield">udt_schema</code> <code class="type">sql_identifier</code>202      </p>203      <p>204       Name of the schema that the column data type (the underlying205       type of the domain, if applicable) is defined in206      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">207       <code class="structfield">udt_name</code> <code class="type">sql_identifier</code>208      </p>209      <p>210       Name of the column data type (the underlying type of the211       domain, if applicable)212      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">213       <code class="structfield">scope_catalog</code> <code class="type">sql_identifier</code>214      </p>215      <p>216       Applies to a feature not available in <span class="productname">PostgreSQL</span>217      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">218       <code class="structfield">scope_schema</code> <code class="type">sql_identifier</code>219      </p>220      <p>221       Applies to a feature not available in <span class="productname">PostgreSQL</span>222      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">223       <code class="structfield">scope_name</code> <code class="type">sql_identifier</code>224      </p>225      <p>226       Applies to a feature not available in <span class="productname">PostgreSQL</span>227      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">228       <code class="structfield">maximum_cardinality</code> <code class="type">cardinal_number</code>229      </p>230      <p>231       Always null, because arrays always have unlimited maximum cardinality in <span class="productname">PostgreSQL</span>232      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">233       <code class="structfield">dtd_identifier</code> <code class="type">sql_identifier</code>234      </p>235      <p>236       An identifier of the data type descriptor of the column, unique237       among the data type descriptors pertaining to the table.  This238       is mainly useful for joining with other instances of such239       identifiers.  (The specific format of the identifier is not240       defined and not guaranteed to remain the same in future241       versions.)242      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">243       <code class="structfield">is_self_referencing</code> <code class="type">yes_or_no</code>244      </p>245      <p>246       Applies to a feature not available in <span class="productname">PostgreSQL</span>247      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">248       <code class="structfield">is_identity</code> <code class="type">yes_or_no</code>249      </p>250      <p>251       If the column is an identity column, then <code class="literal">YES</code>,252       else <code class="literal">NO</code>.253      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">254       <code class="structfield">identity_generation</code> <code class="type">character_data</code>255      </p>256      <p>257       If the column is an identity column, then <code class="literal">ALWAYS</code>258       or <code class="literal">BY DEFAULT</code>, reflecting the definition of the259       column.260      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">261       <code class="structfield">identity_start</code> <code class="type">character_data</code>262      </p>263      <p>264       If the column is an identity column, then the start value of the265       internal sequence, else null.266      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">267       <code class="structfield">identity_increment</code> <code class="type">character_data</code>268      </p>269      <p>270       If the column is an identity column, then the increment of the internal271       sequence, else null.272      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">273       <code class="structfield">identity_maximum</code> <code class="type">character_data</code>274      </p>275      <p>276       If the column is an identity column, then the maximum value of the277       internal sequence, else null.278      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">279       <code class="structfield">identity_minimum</code> <code class="type">character_data</code>280      </p>281      <p>282       If the column is an identity column, then the minimum value of the283       internal sequence, else null.284      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">285       <code class="structfield">identity_cycle</code> <code class="type">yes_or_no</code>286      </p>287      <p>288       If the column is an identity column, then <code class="literal">YES</code> if the289       internal sequence cycles or <code class="literal">NO</code> if it does not;290       otherwise null.291      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">292       <code class="structfield">is_generated</code> <code class="type">character_data</code>293      </p>294      <p>295       If the column is a generated column, then <code class="literal">ALWAYS</code>,296       else <code class="literal">NEVER</code>.297      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">298       <code class="structfield">generation_expression</code> <code class="type">character_data</code>299      </p>300      <p>301       If the column is a generated column, then the generation expression,302       else null.303      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">304       <code class="structfield">is_updatable</code> <code class="type">yes_or_no</code>305      </p>306      <p>307       <code class="literal">YES</code> if the column is updatable,308       <code class="literal">NO</code> if not (Columns in base tables are always309       updatable, columns in views not necessarily)310      </p></td></tr></tbody></table></div></div><br class="table-break" /><p>311   Since data types can be defined in a variety of ways in SQL, and312   <span class="productname">PostgreSQL</span> contains additional ways to313   define data types, their representation in the information schema314   can be somewhat difficult.  The column <code class="literal">data_type</code>315   is supposed to identify the underlying built-in type of the column.316   In <span class="productname">PostgreSQL</span>, this means that the type317   is defined in the system catalog schema318   <code class="literal">pg_catalog</code>.  This column might be useful if the319   application can handle the well-known built-in types specially (for320   example, format the numeric types differently or use the data in321   the precision columns).  The columns <code class="literal">udt_name</code>,322   <code class="literal">udt_schema</code>, and <code class="literal">udt_catalog</code>323   always identify the underlying data type of the column, even if the324   column is based on a domain.  (Since325   <span class="productname">PostgreSQL</span> treats built-in types like326   user-defined types, built-in types appear here as well.  This is an327   extension of the SQL standard.)  These columns should be used if an328   application wants to process data differently according to the329   type, because in that case it wouldn't matter if the column is330   really based on a domain.  If the column is based on a domain, the331   identity of the domain is stored in the columns332   <code class="literal">domain_name</code>, <code class="literal">domain_schema</code>,333   and <code class="literal">domain_catalog</code>.  If you want to pair up334   columns with their associated data types and treat domains as335   separate types, you could write <code class="literal">coalesce(domain_name,336   udt_name)</code>, etc.337  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="infoschema-column-udt-usage.html" title="37.16. column_udt_usage">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="information-schema.html" title="Chapter 37. The Information Schema">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="infoschema-constraint-column-usage.html" title="37.18. constraint_column_usage">Next</a></td></tr><tr><td width="40%" align="left" valign="top">37.16. <code class="literal">column_udt_usage</code> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 37.18. <code class="literal">constraint_column_usage</code></td></tr></table></div></body></html>