On 31/10/2015 00:24, David Blomstrom wrote:
> First consider the following table:
>
> create table taxon (
> taxonid serial,
> descr text
> );
>
> As I understand it, "serial" means that column will automatically
> populate with a numerical key.
>
> If I want to fill the field 'descr' with a list of scientific names
> stored in a spreadsheet, then how would I proceed?
>
> I have a spreadsheet that has about a dozen columns. I copied the field
> with scientific names into a new spreadsheet. Then I created an empty
> field on the left. So I have a spreadsheet with two columns. I saved it
> as a CSV file.
>
> When I try to import it with pgAdmin III, I get the error message
>
> WARNING: null value in column "taxonid" violates non-null constraint
>
> How can I import that single column into the second column in this
> table? Thanks.
Serial columns will only get populated if there's no value (or no NULL
either) inserted into that column. "Serial" [1] is not a real data type;
it's just a handy shorthand for creating a sequence, setting a DEFAULT
of nextval([sequence name]) on the column, and making that column NOT NULL.
At a guess, I think that - by creating the blank column in the
spreadsheet and then importing from it - you were actually inserting
NULL into the taxonid column, hence violating the NOT NULL constraint.
You need to find some way of excluding the taxonid column from the
import, so that the DEFAULT mechanism will populate it for you. I don't
know how you do that with pgAdmin; I know it can be done with COPY from
the command-line. You could try posting to the pgAdmin mailing list [2]
about it.
I hope that helps,
Ray.
[1]
http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL
[2] http://www.pgadmin.org/support/list.php
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
