2

I am using the COPY FROM command to load data from a file. The table is defined with identity column, which is not part of the file. 

CREATE TABLE APP2DBMAP ( 
    FIELD_ID             integer  NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    FIELD_NAME           varchar(128)   ,
    TABLE_NAME           varchar(128)   ,
    COLUMN_NAME          varchar(128)   ,
    CONSTRAINT PK_APP2DBMAP PRIMARY KEY ( FIELD_ID )
);

I executed the following COPY FROM command, the file contains 3 values in 1 row.

copy app2dbmap (field_name, table_name, column_name) from '/opt/NetMgr/data/templ_db.txt' DELIMITER ',' ;

And I got the following error:

ERROR:  null value in column "field_id" violates not-null constraint
DETAIL:  Failing row contains (null, 'aaa', 'bbb', 'ccc').
CONTEXT:  COPY app2dbmap, line 1: "'aaa','bbb','ccc'"

I tried to change the column description of field_id to serial, and it did work fine.

I don't understand why it doesn't work with the original table definition.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Ilanit Nulman
  • 21
  • 2
  • 1
    The problem is that when inserting rows, it sets value of all not on the list to `NULL`... and in `GENERATED BY DEFAULT AS IDENTITY` field, it should be `DEFAULT`, not `NULL` ... so you basically can not do this that way. – Flash Thunder Aug 23 '18 at 10:38

1 Answers1

-1

The problem is you have specified the field_id to be a not null value and hence when the file is passing null as a value, your error is there. If you want an auto increment id, Use,

 CREATE TABLE APP2DBMAP ( 
FIELD_ID             smallserial  NOT NULL,
FIELD_NAME           varchar(128)   ,
TABLE_NAME           varchar(128)   ,
COLUMN_NAME          varchar(128)   ,
CONSTRAINT PK_APP2DBMAP PRIMARY KEY ( FIELD_ID )
);

You can also use bigserial(int4) instead of smallint(int8)

or you can give a default value,

CREATE TABLE APP2DBMAP ( 
FIELD_ID             integer NOT NULL default 0,
FIELD_NAME           varchar(128)   ,
TABLE_NAME           varchar(128)   ,
COLUMN_NAME          varchar(128)   ,
CONSTRAINT PK_APP2DBMAP PRIMARY KEY ( FIELD_ID )
);

You will have to pass something, you cannot pass null in a not null column

Shivam Batra
  • 68
  • 7
  • `CONTEXT: COPY app2dbmap, line 1: "'aaa','bbb','ccc'"` The file is not passing a null value. There are only three columns in the file: *field_name*, *table_name*, and *column_name*. – Adrian Martin Sep 12 '18 at 15:23
  • When you don't pass a value, it by default takes null , there are 4 columns and if 3 of them are specified, the fourth one will automatically be null, and not null will prevent null values to be inserted – Shivam Batra Sep 13 '18 at 20:16