1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Data Manipulation</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="The SQL Language"
18 TITLE="Dependency Tracking"
19 HREF="ddl-depend.html"><LINK
22 HREF="dml-update.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="ddl-depend.html"
81 HREF="dml-update.html"
96 >Chapter 6. Data Manipulation</H1
102 >Table of Contents</B
106 HREF="dml.html#DML-INSERT"
111 HREF="dml-update.html"
116 HREF="dml-delete.html"
122 > The previous chapter discussed how to create tables and other
123 structures to hold your data. Now it is time to fill the tables
124 with data. This chapter covers how to insert, update, and delete
125 table data. We also introduce ways to effect automatic data changes
126 when certain events occur: triggers and rewrite rules. The chapter
127 after this will finally explain how to extract your long-lost data
128 back out of the database.
136 >6.1. Inserting Data</A
145 > When a table is created, it contains no data. The first thing to
146 do before a database can be of much use is to insert data. Data is
147 conceptually inserted one row at a time. Of course you can also
148 insert more than one row, but there is no way to insert less than
149 one row at a time. Even if you know only some column values, a
150 complete row must be created.
153 > To create a new row, use the <TT
157 The command requires the table name and a value for each of the
158 columns of the table. For example, consider the products table
164 CLASS="PROGRAMLISTING"
165 >CREATE TABLE products (
171 An example command to insert a row would be:
173 CLASS="PROGRAMLISTING"
174 >INSERT INTO products VALUES (1, 'Cheese', 9.99);</PRE
176 The data values are listed in the order in which the columns appear
177 in the table, separated by commas. Usually, the data values will
178 be literals (constants), but scalar expressions are also allowed.
181 > The above syntax has the drawback that you need to know the order
182 of the columns in the table. To avoid that you can also list the
183 columns explicitly. For example, both of the following commands
184 have the same effect as the one above:
186 CLASS="PROGRAMLISTING"
187 >INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
188 INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);</PRE
190 Many users consider it good practice to always list the column
194 > If you don't have values for all the columns, you can omit some of
195 them. In that case, the columns will be filled with their default
198 CLASS="PROGRAMLISTING"
199 >INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
200 INSERT INTO products VALUES (1, 'Cheese');</PRE
202 The second form is a <SPAN
206 extension. It fills the columns from the left with as many values
207 as are given, and the rest will be defaulted.
210 > For clarity, you can also request default values explicitly, for
211 individual columns or for the entire row:
213 CLASS="PROGRAMLISTING"
214 >INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
215 INSERT INTO products DEFAULT VALUES;</PRE
228 >, that is, inserting a lot of data,
229 take a look at the <A
234 > command. It is not as flexible as the
238 > command, but is more efficient.
249 SUMMARY="Footer navigation table"
260 HREF="ddl-depend.html"
278 HREF="dml-update.html"
288 >Dependency Tracking</TD