Re: Selectively Importing Data

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 table with a foreign key on taxonid you will probably screw up the references. But from your description I take it, that this is not the case.

Bye
Charles

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