0

What I have:

ALTER TABLE countryb
ADD gnppercap real
;


INSERT INTO countryb (gnppercap)
    SELECT gnp/population
    FROM countryb
;

I successfully created the column "gnppercap", now I want to populate values in every row with the variable. The new variable is the product of gnp and 1/population, with variables gnp and population already in the table I'm altering, countryb.

Here's the error:

ERROR: null value in column "code" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, null, null, null, null, null, null, null, null, null, null, >null, null, null, null, 0.000263028).

I know that the table, countryb has a ton of non-null vars in it, so that's what those nulls are, I think. I thought that since I specified the column I am inserting values into, it wouldn't matter...?

I'm lost. Help appreciated!

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Jefftopia
  • 2,105
  • 1
  • 26
  • 45

1 Answers1

3

You want to update the table, not insert a new row.

update countryb set gnppercap = gnp/population

As an aside: You probably don't want to store a calculated value in a separate column. What happens if you update gnp or population? Your gnppercap column will be inaccurate.

CharlesC
  • 350
  • 2
  • 8
  • Wow yeah that was too easy, can't believe I missed it. Another question, sometimes the values are 0 in the numerator and denominator, I want to set these equal to null. How can I do that? – Jefftopia May 16 '13 at 17:37
  • That would give you a null gnppercap. Your new column is probably null by default so there is no need to re-update that value to null. I would do this instead: update countryb set gnppercap = gnp/population where gnp <> 0 and population <> 0 – CharlesC May 16 '13 at 17:45