我在PostgreSQL中有一个表,其中一列有默认值
该表的DDL为:
CREATE TABLE public.my_table_name
(int_column_1 character varying(6) NOT NULL,
text_column_1 character varying(20) NOT NULL,
text_column_2 character varying(15) NOT NULL,
default_column numeric(10,7) NOT NULL DEFAULT 0.1,
time_stamp_column date NOT NULL);
我正试图在一个查询中插入多行。在这些行中,我有default_column
的值,还有一些行没有default_column
的值,希望Postgres使用这些行的默认值。
以下是我尝试过的:
INSERT INTO "my_table_name"(int_column_1, text_column_1, text_column_2, default_column, time_stamp_column)
VALUES
(91,'text_row_11','text_row_21',8,current_timestamp),
(91,'text_row_12','text_row_22',,current_timestamp),
(91,'text_row_13','text_row_23',19,current_timestamp),
(91,'text_row_14','text_row_24',,current_timestamp),
(91,'text_row_15','text_row_25',27,current_timestamp);
这给了我一个错误。所以,当我尝试插入:
INSERT INTO "my_table_name"(int_column_1, text_column_1, text_column_2, default_column, time_stamp_column)
VALUES (91,'text_row_12','text_row_22',,current_timestamp), -- i want null to be appended here, so i left it empty.
--error from this query is: ERROR: syntax error at or near ","
和
INSERT INTO "my_table_name"(int_column_1, text_column_1, text_column_2, default_column, time_stamp_column)
VALUES (91,'text_row_14','text_row_24',NULL,current_timestamp),
-- error from this query is: ERROR: new row for relation "glycemicindxdir" violates check constraint "food_item_check"
那么,我该如何解决这个问题;当我有值的时候插入值,或者当我没有值的时候让Postgres插入默认值?
使用default
关键字:
INSERT INTO my_table_name
(int_column_1, text_column_1, text_column_2, default_column, time_stamp_column)
VALUES
(91, 'text_row_11', 'text_row_21', 8 , current_timestamp),
(91, 'text_row_12', 'text_row_22', default, current_timestamp),
(91, 'text_row_13', 'text_row_23', 19 , current_timestamp),
(91, 'text_row_14', 'text_row_24', default, current_timestamp),
(91, 'text_row_15', 'text_row_25', 27 , current_timestamp);