[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] Selectively Importing Data
From: David Blomstrom <david.blomstrom () gmail ! com>
Date: 2015-10-31 22:02:57
Message-ID: CAA54Z0hD9t0WVHnLya2kBeZhABL5GOnroOGU1P-H3DUks1t-uA () mail ! gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<div dir="ltr">Thanks for the tips.</div><div class="gmail_extra"><br><div \
class="gmail_quote">On Sat, Oct 31, 2015 at 7:12 AM, Charles Clavadetscher <span \
dir="ltr"><<a href="mailto:clavadetscher@swisspug.org" \
target="_blank">clavadetscher@swisspug.org</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">Hello<br> <br>
This should work:<br>
<br>
\copy taxon (descr) from <filename> ;<br>
<br>
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.<br> <br>
Bye<br>
Charles<br>
<div class="HOEnZb"><div class="h5"><br>
> -----Original Message-----<br>
> From: <a href="mailto:pgsql-general-owner@postgresql.org">pgsql-general-owner@postgresql.org</a> \
[mailto:<a href="mailto:pgsql-general-owner@postgresql.org">pgsql-general-owner@postgresql.org</a>] \
On Behalf Of Raymond O'Donnell<br> > Sent: Samstag, 31. Oktober 2015 12:42<br>
> To: David Blomstrom <<a \
href="mailto:david.blomstrom@gmail.com">david.blomstrom@gmail.com</a>>; <a \
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a><br> > \
Subject: Re: [GENERAL] Selectively Importing Data<br> ><br>
> On 31/10/2015 00:24, David Blomstrom wrote:<br>
> > First consider the following table:<br>
> ><br>
> > create table taxon (<br>
> > taxonid serial,<br>
> > descr text<br>
> > );<br>
> ><br>
> > As I understand it, "serial" means that column will \
automatically<br> > > populate with a numerical key.<br>
> ><br>
> > If I want to fill the field 'descr' with a list of scientific \
names<br> > > stored in a spreadsheet, then how would I proceed?<br>
> ><br>
> > I have a spreadsheet that has about a dozen columns. I copied the field<br>
> > with scientific names into a new spreadsheet. Then I created an empty<br>
> > field on the left. So I have a spreadsheet with two columns. I saved it<br>
> > as a CSV file.<br>
> ><br>
> > When I try to import it with pgAdmin III, I get the error message<br>
> ><br>
> > WARNING: null value in column "taxonid" violates non-null \
constraint<br> > ><br>
> > How can I import that single column into the second column in this<br>
> > table? Thanks.<br>
><br>
> Serial columns will only get populated if there's no value (or no NULL<br>
> either) inserted into that column. "Serial" [1] is not a real data \
type;<br> > it's just a handy shorthand for creating a sequence, setting a \
DEFAULT<br> > of nextval([sequence name]) on the column, and making that column \
NOT NULL.<br> ><br>
> At a guess, I think that - by creating the blank column in the<br>
> spreadsheet and then importing from it - you were actually inserting<br>
> NULL into the taxonid column, hence violating the NOT NULL constraint.<br>
><br>
> You need to find some way of excluding the taxonid column from the<br>
> import, so that the DEFAULT mechanism will populate it for you. I don't<br>
> know how you do that with pgAdmin; I know it can be done with COPY from<br>
> the command-line. You could try posting to the pgAdmin mailing list [2]<br>
> about it.<br>
><br>
> I hope that helps,<br>
><br>
> Ray.<br>
><br>
><br>
> [1]<br>
> <a href="http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL" \
rel="noreferrer" target="_blank">http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-SERIAL</a><br>
><br>
> [2] <a href="http://www.pgadmin.org/support/list.php" rel="noreferrer" \
target="_blank">http://www.pgadmin.org/support/list.php</a><br> ><br>
><br>
> --<br>
> Raymond O'Donnell :: Galway :: Ireland<br>
> <a href="mailto:rod@iol.ie">rod@iol.ie</a><br>
><br>
><br>
</div></div><span class="HOEnZb"><font color="#888888">> --<br>
> Sent via pgsql-general mailing list (<a \
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>)<br> > \
To make changes to your subscription:<br> > <a \
href="http://www.postgresql.org/mailpref/pgsql-general" rel="noreferrer" \
target="_blank">http://www.postgresql.org/mailpref/pgsql-general</a><br> <br>
<br>
<br>
--<br>
Sent via pgsql-general mailing list (<a \
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>)<br> To \
make changes to your subscription:<br> <a \
href="http://www.postgresql.org/mailpref/pgsql-general" rel="noreferrer" \
target="_blank">http://www.postgresql.org/mailpref/pgsql-general</a><br> \
</font></span></blockquote></div><br><br clear="all"><div><br></div>-- <br><div \
class="gmail_signature">David Blomstrom<br>Writer & Web Designer (Mac, M$ & \
Linux)<br><a href="http://www.geobop.org" target="_blank">www.geobop.org</a></div> \
</div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic