Sybase IQ supports loading and inserting column default values using the following statements:
INSERT...VALUES
INSERT...SELECT
INSERT...LOCATION
LOAD TABLE
UPDATE
SELECT...FROM...FOR UPDATE
Sybase IQ handles defining and inserting column default values with the following requirements:
Sybase IQ permits you to specify DEFAULT values that cannot be evaluated by Sybase IQ. An error is reported when an INSERT, LOAD, or ALTER ADD operation is performed on a table that has an unsupported DEFAULT value.
Sybase IQ generates an error or warning when the server attempts to insert a DEFAULT value that is not compatible with the data type of the column. For example, if you define a default expression of ‘N/A’ to an integer column, then any insert or load that does not specify the column value generates an error or warning, depending on the setting of the CONVERSION_ERROR database option. See Table 7-4 for information on supported implicit data type conversions.
If a DEFAULT value is too long for a CHARACTER type column, Sybase IQeither truncates the string or generates an exception, depending on the setting of the STRING_RTRUNCATION database option.
If the DEFAULT value for a VARCHAR or LONG VARCHAR column is the zero-length string, Sybase IQ either inserts a NULL or zero-length string, depending on the setting of the NON_ANSI_NULL_VARCHAR database option.
If the DEFAULT value for a VARCHAR, CHAR, or LONG VARCHAR column is a string that contains a partial multi-byte character, then Sybase IQ may trim the partial multi-byte character before inserting the value, depending on the setting of the TRIM_PARTIAL_MBC database option.
Sybase IQ generates an error message every time the server attempts to insert the DEFAULT value of a column, if that default value violates the check constraint of either the table or the column.
All constraint violations that occur during a LOAD TABLE operation as a result of inserting DEFAULT values apply towards any user specified IGNORE CONSTRAINT and MESSAGE LOG/ROW LOG option.
Column default values of UTC TIMESTAMP and CURRENT UTC TIMESTMAP are not supported by Sybase IQ. An error is reported every time an attempt is made to insert or update the default value of a column of this type.
Column DEFAULT values defined on base tables are not propagated to joins in which these tables participate.
Column DEFAULT values are not permitted on tables that participate in join indexes and Sybase IQ generates an error, if you attempt to define a DEFAULT value on such a table. This rule is similar to support for the AUTOINCREMENT default value.
If a column on which a default value is defined is added to a table, then all rows of the new column are populated with that default value.
Changing the default value of an existing column in a table does not change any existing values in the table.
The LOAD TABLE DEFAULTS option must be ON in order to use the default value specified in the LOAD TABLE statement DEFAULT option. If the DEFAULTS option is OFF, the specified load default value is not used and a NULL value is inserted into the column instead.
The LOAD TABLE DEFAULT specification must contain at least one column that needs to be loaded from the file specified in the LOAD TABLE command.
The LOAD TABLE DEFAULT default-value must be of the same character set as that of the database and must conform to the supported default values for columns and default value restrictions. The LOAD TABLE DEFAULT option does not support AUTOINCREMENT, IDENTITY, or GLOBAL AUTOINCREMENT as a load default value.
Encryption of the default value is not supported for the load default values specified in the LOAD TABLE DEFAULT clause.
See the individual sections for specific default value types later in this section for more information on defining and inserting column default values. Also see “Special values” in Chapter 2, “SQL Language Elements,” in Reference: Building Blocks, Tables, and Procedures for more information on the special values that can be used in default column value expressions.
