Thread: Selectively Importing Data

Selectively Importing Data

From
David Blomstrom
Date:
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.

Re: Selectively Importing Data

From
Andy Colson
Date:
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


Re: Selectively Importing Data

From
Raymond O'Donnell
Date:
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


Re: Selectively Importing Data

From
"Charles Clavadetscher"
Date:
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



Re: Selectively Importing Data

From
David Blomstrom
Date:
Thanks for the tips.

On Sat, Oct 31, 2015 at 7:12 AM, Charles Clavadetscher <clavadetscher@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-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



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