Can't get past Postgres not null constraint

Question

I am trying to move data from one table to another:

zuri=# INSERT INTO lili_code (uuid, arrow, popularname, slug, effdate, codetext)           
SELECT uuid, arrow, popularname, slug, effdate, codetext
FROM newarts;
ERROR:  null value in column "shorttitle" violates not-null constraint

My Question: shorttitle is NOT one of the columns I was trying to fill, so why does it matter if it is null or not?

Please note:

shorttitle is blank=True. I understand this is != null, but I thought it might be relevant.
There are a lot of additional columns in lili_code besides shorttitle that weren't in newarts.

At this point it looks to me like my only options are

a. inserting by hand (yuck!)

b. making a csv and importing that

c. adding all the missing columns from lili_code to newarts and making sure they are NOT NULL and have at least a default value in them.

Have I missed an option? What's the best solution here? I'm using django 1.9.1, python 2.7.11, Ubuntu 15.10, and Postgresql 9.4.

Thanks as always.

Thilo · Accepted Answer · 2016-02-05 00:14:20Z

1

Since the column is non-null, you need to provide a value.

If there is no real data for it, you can calculate it on the fly in the SELECT part of your INSERT statement, it does not have to be an actual column in the source table.

If you are fine with an empty string, you can do

INSERT INTO lili_code 
(uuid, arrow, popularname, slug, effdate, codetext, shorttitle)           
SELECT uuid, arrow, popularname, slug, effdate, codetext, ''
FROM newarts;

Or maybe you want to use popularname for this column as well:

INSERT INTO lili_code 
(uuid, arrow, popularname, slug, effdate, codetext, shorttitle)           
SELECT uuid, arrow, popularname, slug, effdate, codetext, popularname
FROM newarts;

answered Feb 5, 2016 at 0:14

Thilo

264k107 gold badges527 silver badges674 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2016-02-05 00:20:09Z

1

If you not define the value on the INSERT the db will insert NULL or the default value.

If column doesnt allow NULL you will get an error. So either provide a dummy value or change the field to allow NULL

answered Feb 5, 2016 at 0:20

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Comments

klin · Accepted Answer · 2016-02-05 00:21:00Z

1

You can define a default value for the column, e.g.:

alter table lili_code alter shorttitle set default '';

answered Feb 5, 2016 at 0:21

klin

123k15 gold badges241 silver badges263 bronze badges

2 Comments

Haleemur Ali Over a year ago

this defeats the purpose of putting a non null constraint.

klin Over a year ago

I don't know what you have in mind. One does not prevent the other. They rather work well together.

Collectives™ on Stack Overflow

Can't get past Postgres not null constraint

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related