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@xxxxxxxxxxxxxx > > 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@xxxxxxxxxxx
