DEFAULT

Updated on 2025-01-09 GMT+08:00

View PDF

In MySQL, when using ALTER TABLE to add a column with the not null constraint, if no value is specified, a default value is inserted. However, an error will be reported a value is inserted into a non-empty table in GaussDB(DWS). Therefore, for common data types, Default values (see table 1.) are specified for ALTER TABLE ADD COLUMN with NOT NULL constraint.

Input

   
    
      
      DROP TABLE IF EXISTS default_test_1;
CREATE TABLE default_test_1
(
    c1 tinyint,
    c2 smallint,
    c3 MEDIUMINT,
    c4 int,
    c5 bigint
);
INSERT INTO default_test_1
VALUES (1, 1, 1, 1, 1);
SELECT *
FROM default_test_1;
ALTER TABLE default_test_1 add COLUMN (c1_1 tinyint not null, c2_1 SMALLINT not null, c3_1 MEDIUMINT not null, c4_1 int not null, c5_1 BIGINT not null);
SELECT *
FROM default_test_1;

     

   
  

Output

   
    
      
      DROP TABLE IF EXISTS "public"."default_test_1";
CREATE TABLE "public"."default_test_1" (
  "c1" SMALLINT,
  "c2" SMALLINT,
  "c3" INTEGER,
  "c4" INTEGER,
  "c5" BIGINT
) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("c1");
INSERT INTO
  "public"."default_test_1"
VALUES
  (1, 1, 1, 1, 1);
SELECT
  *
FROM
  default_test_1;
ALTER TABLE
  "public"."default_test_1"
ADD
  COLUMN "c1_1" SMALLINT NOT NULL DEFAULT 0,
ADD
  COLUMN "c2_1" SMALLINT NOT NULL DEFAULT 0,
ADD
  COLUMN "c3_1" INTEGER NOT NULL DEFAULT 0,
ADD
  COLUMN "c4_1" INTEGER NOT NULL DEFAULT 0,
ADD
  COLUMN "c5_1" BIGINT NOT NULL DEFAULT 0;
SELECT
  *
FROM
  default_test_1;

     

   
  

**Table 1**
Data Type	MYSQL	GaussDB(DWS) Data Type	Conversion Result
Signed Integer	TINYINT	SMALLINT	0
	SMALLINT	SMALLINT	0
	MEDIUMINT	INT/INTEGER	0
	INT/INTEGER	INT/INTEGER	0
	BIGINT	BIGINT	0
Unsigned Integer	TINYINT UNSIGNED	TINYINT	0
	SMALLINT UNSIGNED	INT	0
	MEDIUMINT UNSIGNED	INT	0
	INT UNSIGNED	BIGINT	0
	BIGINT UNSIGNED	NUMERIC	0
Float	FLOAT	REAL	0
	FLOAT UNSIGNED	REAL	0
	DOUBLE	DOUBLE PRECISION	0
	DOUBLE UNSIGNED	DOUBLE PRECISION	0
	DOUBLE PRECISION (DOUBLE)	DOUBLE PRECISION	0
	DOUBLE PRECISION [UNSIGNED] (DOUBLE)	DOUBLE PRECISION	0
	REAL (Processed as double by default, when set to REAL_AS_FLOAT, it is processed as float.)	REAL/DOUBLE PRECISION	0
	REAL [UNSIGNED] (Processed as double by default, when set to REAL_AS_FLOAT, it is processed as float.)	REAL/DOUBLE PRECISION	0
	NUMERIC/DECIMAL	NUMERIC/DECIMAL	0
Boolean	BOOLEAN/BOOL	BOOLEAN	0
Character Type	CHAR/CHARACTER	CHAR/CHARACTER * 4	An empty string will be returned.
	NCHAR	CHAR/CHARACTER * 4	An empty string will be returned.
	VARCHAR	VARCHAR * 4	An empty string will be returned.
Text	TINYTEXT	TEXT	An empty string will be returned.
	TEXT	TEXT	An empty string will be returned.
	MEDIUMTEXT	TEXT	An empty string will be returned.
	LONGTEXT	TEXT	An empty string will be returned.
Binary	BINARY	BYTEA	An empty string will be returned.
	VARBINARY	BYTEA	An empty string will be returned.
	TINYBLOB	BYTEA (column storage)/BLOB (non-column storage)	An empty string will be returned.
	BLOB	BYTEA (column storage)/BLOB (non-column storage)	An empty string will be returned.
	MEDIUMBLOB	BYTEA (column storage)/BLOB (non-column storage)	An empty string will be returned.
	LONGBLOB	BYTEA (column storage)/BLOB (non-column storage)	An empty string will be returned.
	CHAR BYTE (BINARY)	BYTEA	An empty string will be returned.
Date	DATE	DATE	1970-01-01
	TIME	TIME(P) WITHOUT TIME ZONE	00:00:00
	DATETIME	TIMESTAMP WITHOUT TIME ZONE	1970-01-01 00:00:00
	TIMESTAMP	TIMESTAMP(P) WITH TIME ZONE	1970-01-01 00:00:00
	datetime/datetime(0)	datetime->timestamp with out time zone datetime(0)->timestamp(0) with out time zone	1970-01-01 00:00:00.xx (The number of x depends on the precision.)
	YEAR	SMALLINT	0000
Bit String	BIT	BIT	DEFAULT 0:: BIT(x)
Set	ENUM	CHARACTER VARYING()	Return the first element by default.
Set	SET	CHARACTER VARYING()	An empty string will be returned.
Auto Increment	SERIAL ( BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE)	CREATE SEQUENCE serial_name INCREMENT 2 START 1	No processing is required.
JSON	JSON	jsonb	No processing is required.
Spatial	point	point	No processing is required.
	polygon	polygon	No processing is required.
	geometry	Not supported. The input is returned as it was received	No processing is required.
	linestring	polygon	No processing is required.
	geometrycollection	Not supported. The input is returned as it was received	No processing is required.
	multipoint	box	No processing is required.
	multilinestring	box	No processing is required.
	multipolygon	polygon	No processing is required.