codekingpro/portable-devtools
114k
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>