codekingpro/portable-devtools

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>5.1. Table Basics</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="ddl.html" title="Chapter 5. Data Definition" /><link rel="next" href="ddl-default.html" title="5.2. Default Values" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">5.1. Table Basics</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl.html" title="Chapter 5. Data Definition">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</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="ddl-default.html" title="5.2. Default Values">Next</a></td></tr></table><hr /></div><div class="sect1" id="DDL-BASICS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.1. Table Basics <a href="#DDL-BASICS" class="id_link">#</a></h2></div></div></div><a id="id-1.5.4.3.2" class="indexterm"></a><a id="id-1.5.4.3.3" class="indexterm"></a><a id="id-1.5.4.3.4" class="indexterm"></a><p>3   A table in a relational database is much like a table on paper: It4   consists of rows and columns.  The number and order of the columns5   is fixed, and each column has a name.  The number of rows is6   variable — it reflects how much data is stored at a given moment.7   SQL does not make any guarantees about the order of the rows in a8   table.  When a table is read, the rows will appear in an unspecified order,9   unless sorting is explicitly requested.  This is covered in <a class="xref" href="queries.html" title="Chapter 7. Queries">Chapter 7</a>.  Furthermore, SQL does not assign unique10   identifiers to rows, so it is possible to have several completely11   identical rows in a table.  This is a consequence of the12   mathematical model that underlies SQL but is usually not desirable.13   Later in this chapter we will see how to deal with this issue.14  </p><p>15   Each column has a data type.  The data type constrains the set of16   possible values that can be assigned to a column and assigns17   semantics to the data stored in the column so that it can be used18   for computations.  For instance, a column declared to be of a19   numerical type will not accept arbitrary text strings, and the data20   stored in such a column can be used for mathematical computations.21   By contrast, a column declared to be of a character string type22   will accept almost any kind of data but it does not lend itself to23   mathematical calculations, although other operations such as string24   concatenation are available.25  </p><p>26   <span class="productname">PostgreSQL</span> includes a sizable set of27   built-in data types that fit many applications.  Users can also28   define their own data types.  Most built-in data types have obvious29   names and semantics, so we defer a detailed explanation to <a class="xref" href="datatype.html" title="Chapter 8. Data Types">Chapter 8</a>.  Some of the frequently used data types are30   <code class="type">integer</code> for whole numbers, <code class="type">numeric</code> for31   possibly fractional numbers, <code class="type">text</code> for character32   strings, <code class="type">date</code> for dates, <code class="type">time</code> for33   time-of-day values, and <code class="type">timestamp</code> for values34   containing both date and time.35  </p><a id="id-1.5.4.3.8" class="indexterm"></a><p>36   To create a table, you use the aptly named <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> command.37   In this command you specify at least a name for the new table, the38   names of the columns and the data type of each column.  For39   example:40</p><pre class="programlisting">41CREATE TABLE my_first_table (42    first_column text,43    second_column integer44);45</pre><p>46   This creates a table named <code class="literal">my_first_table</code> with47   two columns.  The first column is named48   <code class="literal">first_column</code> and has a data type of49   <code class="type">text</code>; the second column has the name50   <code class="literal">second_column</code> and the type <code class="type">integer</code>.51   The table and column names follow the identifier syntax explained52   in <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS" title="4.1.1. Identifiers and Key Words">Section 4.1.1</a>.  The type names are53   usually also identifiers, but there are some exceptions.  Note that the54   column list is comma-separated and surrounded by parentheses.55  </p><p>56   Of course, the previous example was heavily contrived.  Normally,57   you would give names to your tables and columns that convey what58   kind of data they store.  So let's look at a more realistic59   example:60</p><pre class="programlisting">61CREATE TABLE products (62    product_no integer,63    name text,64    price numeric65);66</pre><p>67   (The <code class="type">numeric</code> type can store fractional components, as68   would be typical of monetary amounts.)69  </p><div class="tip"><h3 class="title">Tip</h3><p>70    When you create many interrelated tables it is wise to choose a71    consistent naming pattern for the tables and columns.  For72    instance, there is a choice of using singular or plural nouns for73    table names, both of which are favored by some theorist or other.74   </p></div><p>75   There is a limit on how many columns a table can contain.76   Depending on the column types, it is between 250 and 1600.77   However, defining a table with anywhere near this many columns is78   highly unusual and often a questionable design.79  </p><a id="id-1.5.4.3.13" class="indexterm"></a><p>80   If you no longer need a table, you can remove it using the <a class="xref" href="sql-droptable.html" title="DROP TABLE"><span class="refentrytitle">DROP TABLE</span></a> command.81   For example:82</p><pre class="programlisting">83DROP TABLE my_first_table;84DROP TABLE products;85</pre><p>86   Attempting to drop a table that does not exist is an error.87   Nevertheless, it is common in SQL script files to unconditionally88   try to drop each table before creating it, ignoring any error89   messages, so that the script works whether or not the table exists.90   (If you like, you can use the <code class="literal">DROP TABLE IF EXISTS</code> variant91   to avoid the error messages, but this is not standard SQL.)92  </p><p>93   If you need to modify a table that already exists, see <a class="xref" href="ddl-alter.html" title="5.6. Modifying Tables">Section 5.6</a> later in this chapter.94  </p><p>95   With the tools discussed so far you can create fully functional96   tables.  The remainder of this chapter is concerned with adding97   features to the table definition to ensure data integrity,98   security, or convenience.  If you are eager to fill your tables with99   data now you can skip ahead to <a class="xref" href="dml.html" title="Chapter 6. Data Manipulation">Chapter 6</a> and read the100   rest of this chapter later.101  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl.html" title="Chapter 5. Data Definition">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-default.html" title="5.2. Default Values">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 5. Data Definition </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"> 5.2. Default Values</td></tr></table></div></body></html>