psqlException：错误：列“order_id”中的null值违反not-null约束

Question

我在postgresql中创建了一个存储过程，如下所示：

 INSERT INTO ABC

     (order_id,order_dt, customer_id, route_id, routenum, ordertype, create_station_id, create_stationtype, create_time,create_user_id,tran_time, tran_user_id,station_id)

   values 

     (1,$1, $2, $3, $4, $5, $6, $7, LOCALTIMESTAMP, $8, 

    default, default,$9) 

   returning  order_id;

order_id是serial->主键的类型

我在插入时得到如下错误：

PSQLException: ERROR: null value in column "order_id" violates not-null constraint Where: SQL function "insert_ABC" statement 1.

Answer 1

您可以跳过插入到中的order_id:

CREATE TABLE ABC(order_id SERIAL PRIMARY KEY, order_dt INT -- rest of cols
                );

INSERT INTO ABC(order_dt)  -- rest of cols
VALUES (2)                 -- rest of values
RETURNING order_id;

或使用default:

INSERT INTO ABC(order_id, order_dt)
VALUES (default, 2)
RETURNING order_id;

编辑：

CREATE TABLE ABC(order_id SERIAL, order_dt INT);

INSERT INTO ABC(order_dt)  -- rest of cols
VALUES (2) ;  

INSERT INTO ABC(order_id, order_dt)
VALUES (default, 2) ; 

╔═══════════╦══════════╗
║ order_id  ║ order_dt ║
╠═══════════╬══════════╣
║        1  ║        2 ║
║        2  ║        2 ║
╚═══════════╩══════════╝

CREATE TABLE ABC(order_id SERIAL NULL, order_dt INT);
-- ERROR: conflicting NULL/NOT NULL declarations for 
-- column "order_id" of table "abc"