From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-general@postgresql.org
Cc: Markova, Nina <nmarkova@nrcan.gc.ca>
Subject: Re: FW: How to upload data to postgres
Date: Tue, 9 Sep 2008 19:43:13 -0700
Message-ID: <200809091943.13745.aklaver@comcast.net> (raw)
In-Reply-To: <6D8C7E015447D0428D80E9DBABA06A1E03F4AA5C@S0-OTT-X2.nrn.nrcan.gc.ca>
References: <6D8C7E015447D0428D80E9DBABA06A1E03F4AA5C@S0-OTT-X2.nrn.nrcan.gc.ca>
On Tuesday 09 September 2008 1:54:12 pm Markova, Nina wrote:
> So far I tried;
>
> 1) I have copied data from Ingres in ASCII (using Ingres copydb
> command).
> 2) created a table in a Postgres database
> 3) tried loading data into Potgres table - encounter problems.
>
> For 1) (the Ingres part)
> =====================
> Ingres used the following copy commands:
>
> copy site(
> sta= varchar(0)tab,
> ondate= varchar(0)tab,
> offdate= varchar(0)tab,
> lat= c0tab,
> lon= c0tab,
> elev= c0tab,
> regist_code= varchar(0)tab,
> vault_cond= varchar(0)tab,
> geology= varchar(0)tab,
> comment= varchar(0)tab,
> initials= varchar(0)tab,
> lddate= c0nl,
> nl= d0nl)
> into '/tmp/site.dba'
>
> Normally Ingres will use this command to copy data from a file:
> copy site(
> sta= varchar(0)tab,
> ondate= varchar(0)tab,
> offdate= varchar(0)tab,
> lat= c0tab,
> lon= c0tab,
> elev= c0tab,
> regist_code= varchar(0)tab,
> vault_cond= varchar(0)tab,
> geology= varchar(0)tab,
> comment= varchar(0)tab,
> initials= varchar(0)tab,
> lddate= c0nl,
> nl= d0nl)
> from '/vm04-0/home/postgres/test/site.dba'
>
> For 3)
> =====
> - I got error when I tried to copy with Ingres-like copy command.
> - Then I tried to copy with simple 'copy site from
> '/vm04-0/home/postgres/test/site-c.dba' - ERROR: value too long for
> type character varying(5)
The ERROR explains it. The value you are bringing over from the Ingres
database is to long for a varchar(5) field.
Instead of rehashing the documentation I will point you to the relevant
section that pertains to Postgres COPY:
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html>
> - I had no luck either when used binary copying - postgres complained
> about signature:
> copy site from '/vm04-0/home/postgres/test/site.dba' with binary
>
> ERROR: COPY file signature not recognized
>
> ========================
> I have couple of questions as well.
> ========================
> Q1: is there an equivalent of copydb in postgres (in Ingres copydb
> creates copy statements for all database tables in a single file)
See pg_dump:
http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html> Q2: how to say in postgres that a field has no default values (in
> Ingres 'not default' is used - and this produced an error in postgres
> CREATE TABLE command)
The CREATE TABLE only takes a DEFAULT clause. If you want no default don't
specify anything:
lat float not null,
Since you specified NOT NULL you will have to specify some value on INSERT.
>
> Create table site (
> sta varchar(5) not null,
> ondate varchar(8) not null,
> offdate varchar(8) not null,
> lat float not null not default, ----->
> lon float not null not default
> )
>
> Q3: How to specify storage structure of a table (again in Ingres
> 'modify' statement is used to specify btree, isam or hash structure). In
> the Postgres documentation I only saw how to create an index with a
> specific structure.
As far as I know this cannot be done in Postgres. The only way you can modify
the storage parameters is :
"Storage Parameters
The WITH clause can specify storage parameters for tables, and for indexes
associated with a UNIQUE or PRIMARY KEY constraint. Storage parameters for
indexes are documented in CREATE INDEX. The only storage parameter currently
available for tables is:
FILLFACTOR
The fillfactor for a table is a percentage between 10 and 100. 100
(complete packing) is the default. When a smaller fillfactor is specified,
INSERT operations pack table pages only to the indicated percentage; the
remaining space on each page is reserved for updating rows on that page. This
gives UPDATE a chance to place the updated copy of a row on the same page as
the original, which is more efficient than placing it on a different page.
For a table whose entries are never updated, complete packing is the best
choice, but in heavily updated tables smaller fillfactors are appropriate. "
This only applies to later versions of Postgres.
>
> In Ingres: modify site to isam unique on sta, ondate (means structure
> isam, primary key is on 2 fields - sta and ondate)
>
> Thanks in advance,
> Nina
>
> > ______________________________________________
> > From: Markova, Nina
> > Sent: September 9, 2008 14:32
> > To: pgsql-general@postgresql.org
> > Subject: How to upload data to postgres
> >
> > Hi again,
> >
> > I need to load data from Ingres database to Postgres database. What's
> > the easiest way?
> >
> > Thanks,
> > Nina
--
Adrian Klaver
aklaver@comcast.net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: aklaver@comcast.net, pgsql-general@postgresql.org, nmarkova@nrcan.gc.ca
Subject: Re: FW: How to upload data to postgres
In-Reply-To: <200809091943.13745.aklaver@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox