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

Try using just one column?

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

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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]http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL 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]http://www.pgadmin.org/support/list.php
about it.

I hope that helps,

Ray.

[1]: http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL
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

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

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