CODEWITHSUNDEEP
postgresqlnull
Malik A. Rumi
Malik A. Rumi

Reputation: 2015

Can't get past Postgres not null constraint

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:

  1. shorttitle is blank=True. I understand this is != null, but I thought it might be relevant.
  2. 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.

Upvotes: 0

Views: 864

Answers (3)

klin
klin

Reputation: 121474

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

alter table lili_code alter shorttitle set default '';

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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

Upvotes: 1

Thilo
Thilo

Reputation: 262464

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;

Upvotes: 1

Related Questions