Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Inserting New Values

The following is the syntax of the INSERT INTO command, when used to insert new values, which is subsequently described in detail:

  INSERT INTO 
table_name

         [ ( 
column_name
 [, ...] ) ]
         VALUES ( 
value
 [, ...] )

table_name

The INSERT SQL command initiates an insertion of data into the table called table_name .

( column_name [, ...] )

An optional grouped expression which describes the targeted columns for the insertion.

VALUES

The SQL clause which instructs PostgreSQL to expect a grouped expression of values to follow.

( value [, ...] )

The required grouped expression that describes the values to be inserted. There should be one value for each specified column, separated by commas. These values may be expressions themselves (e.g., an operation between two values), or constants.

Each value following the VALUES clause must be of the same data type as the column it is being inserted into. If the optional column-target expression is omitted, PostgreSQL will expect there to be one value for each column in the literal order of the table's structure. If there are fewer values to be inserted than columns, PostgreSQL will attempt to insert a default value (or the NULL value, if there is no default) for each omitted value.

To demonstrate, Example 4-16 illustrates the insertion of a new book into Book Town's books table.

Example 4-16. Inserting new values into the books table

booktown=# 
INSERT INTO books (id, title, author_id, subject_id)

booktown-# 
       VALUES (41472, 'Practical PostgreSQL', 1212, 4);

INSERT 3574037 1

The SQL statement in Example 4-16 inserts a new book with an id of 41472, a title of Practical PostgreSQL , an author identifier of 1212, and a subject identifier of 4. Note the feedback beginning with INSERT, which indicates that the insertion was successful. The first number following INSERT is the OID (object identifier) of the freshly inserted row. The second number following INSERT represents the number of rows inserted (in this case, 1).

Notice that the optional column target list is specified identically to the physical structure of the table, from left to right. In this case, omitting the grouped expression would have no effect on the statement since the INSERT statement assumes that you are inserting values in the natural order of the table's columns. You can re-arrange the names of the columns in the grouped column target list if you wish to specify the values in a different order following the VALUES clause, as demonstrated in Example 4-17.

Example 4-17. Changing the order of target columns

booktown=# 
INSERT INTO books (subject_id, author_id, id, title)

booktown-# 
       VALUES (4, 7805, 41473, 'Programming Python');

INSERT 3574041 1
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire