Обсуждение: Selectively Importing Data

On 10/30/2015 07:24 PM, 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.
ThenI 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.

Try using just one column?

Or, create taxonid as integer, then fill the spreadsheet column with int's, and import both columns.

-Andy

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

Hello

This should work:

\copy taxon (descr) from <filename> ;

This is true if your sheet has only one column, so you should delete that column. However if you happen to have another
tablewith a foreign key on taxonid you will probably screw up the references. But from your description I take it, that
thisis not the case. 

Bye
Charles

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Raymond O'Donnell
> Sent: Samstag, 31. Oktober 2015 12:42
> To: David Blomstrom <david.blomstrom@gmail.com>; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Selectively Importing Data
>
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general