Thanks for the tips. On Sat, Oct 31, 2015 at 7:12 AM, Charles Clavadetscher < clavadetsc...@swisspug.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-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Raymond O'Donnell > > Sent: Samstag, 31. Oktober 2015 12:42 > > To: David Blomstrom <david.blomst...@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 > > r...@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 > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.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
