DEFAULT

You may use the DEFAULT keyword in the following contexts:

The default value (literal or expression) that you specify in a CREATE TABLE or ALTER TABLE statement must meet the following criteria:

Also, in INSERT and UPDATE statements, you do not have to specify values for columns that have a DEFAULT value defined. In such a case, Pervasive PSQL computes the DEFAULT expression and uses the result value as the value of the column.

Syntax

See the syntax for the following statements:

Remarks

You can specify expressions as DEFAULT values for columns of all data types as shown in the following table.

Examples

The following statement creates a table named Tab1. The default value of the col1 column is the DATE part of timestamp returned by NOW ().

CREATE TABLE Tab1 
( 
    col1 DATE DEFAULT NOW() 
) 

The following statement creates a table named Tab5. The default value of the col5 column is 200.

CREATE TABLE Tab5 
( 
    col5 INT DEFAULT 200 
) 

The following statement creates a table named Tab8. The default value of the col8 column is the timestamp at the time of the INSERT or UPDATE.

CREATE TABLE Tab8 
( 
    col8 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
) 

The following statement creates a table named Tab6. The default value of the col6 column is the name of user, after executing an INSERT or UPDATE statement. Using a default user name is practical only when security is enabled. Otherwise, USER is always NULL.

CREATE TABLE Tab6 
( 
    col6 VARCHAR(20) DEFAULT USER 
) 

The following statement shows an invalid example. The statement results in a parse-time error because SQL_TIME is not an allowed data type for a DATE column.

CREATE TABLE Tab 
( 
    col DATE DEFAULT CURTIME() 
) 

The following statement shows an invalid example. The statement results in a parse-time error. Although '3.1' is convertible to a number, it is not a valid integer.

CREATE TABLE Tab 
( 
    col SMALLINT DEFAULT '3.1' 
) 

The following statement shows an invalid example. The CREATE TABLE statement succeeds but the INSERT statement fails because -60000 is outside of the range supported by SMALLINT.

CREATE TABLE Tab 
( 
    col SMALLINT DEFAULT 3 * -20000 
) 
INSERT INTO Tab values(DEFAULT) 

The following statements show valid examples of setting a default value of zero for an IDENTITY and a SMALLIDENTITY data type.

CREAT TABLE t1 ( c1 IDENTITY DEFAULT '0' ) 
ALTER TABLE t1 ALTER c1 SMALLIDENTITY DEFAULT 0 

The following statements show invalid examples of setting a default value for an IDENTITY and a SMALLIDENTITY data type.

CREATE TABLE t1 ( c1 IDENTITY DEFAULT 3 ) 
ALTER TABLE t1 ALTER c1 SMALLIDENTITY DEFAULT 1 

Expressions as Default Values for Date or Time Columns

Defaults specify the values that are used in a column if you do not specify a value for the column when inserting a row.

To assign an expression as a default on a column, define a default value using the DEFAULT keyword in the CREATE TABLE statement. For more details on creating a table, see CREATE TABLE .

In addition to literals and NULL values, Pervasive PSQL also allows you to specify date and time scalar functions and expressions as default values for columns of the appropriate data types.

CURRENT_USER() is allowed as a default value for string columns CHAR and VARCHAR.

The following table lists the specific date and time scalar functions that you can invoke to provide the default value for a column of the appropriate data type:

When you specify a scalar function as the default value for a column in INSERT and UPDATE statements, Pervasive PSQL invokes the specified scalar function to set the value of such a column to the result of the function call.

See Also

ALTER TABLE

CREATE TABLE

INSERT

UPDATE