Re: Selectively Importing Data

Thanks for the tips.

On Sat, Oct 31, 2015 at 7:12 AM, Charles Clavadetscher <
clavadetscher(at)swisspug(dot)org> wrote:

> 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
>
>
>
> --
> 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
>

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org