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
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
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:


         [ ( 
 [, ...] ) ]
         VALUES ( 
 [, ...] )


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.


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

INSERT INTO books (id, title, author_id, subject_id)

       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

INSERT INTO books (subject_id, author_id, id, title)

       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