Обсуждение: trobles in importing dbf file
Hi Guys, I'm a newbie in Postgres and hope my mail is not off topic. Well, I'm migrating from Mysql to PostGree in order to use the postgis features. I've some dbf files I've to import, so I've used the utility dbf2pg.exe ( I've installed the version 8.1 in win xp env). Now is there a way to add automatically a primary key if no, how can I can add it after importing the table in postgres ? In mYsql an index (the number or row) is automatically added as first column. Sorry if the question is trivial, I didn't suspect this gap. Is there a standard proceure to import a database mapped in a lot of dbf files. Thanks in advantage -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Partecipa al concorso Best Western, ogni giorno puoi vincere un fantastico week end da sogno! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5096&d=20060511
You can create a column in your destination table with a data type of "serial" which will be an auto-incrementing number with every inserted row. Or if you create the table with "WITH OIDS", PostgreSQL will add an internal object ID. The above will take care of your unique primary key. Andy > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of giuseppe.derossi@email.it > Sent: 11 May 2006 8:25 am > To: pgsql-admin@postgresql.org > Subject: [ADMIN] trobles in importing dbf file > > Hi Guys, > I'm a newbie in Postgres and hope my mail is not off topic. Well, I'm > migrating from Mysql to PostGree in order to use the postgis features. > I've > some dbf files I've to import, so I've used the utility dbf2pg.exe ( I've > installed the version 8.1 in win xp env). Now is there a way to add > automatically a primary key if no, how > can I can add it after importing the table in postgres ? In mYsql an index > (the number or row) is automatically added as first column. > Sorry if the question is trivial, I didn't suspect this gap. > Is there a standard proceure to import a database mapped in a lot of dbf > files. > > > > > Thanks in advantage > > -- > Email.it, the professional e-mail, gratis per te: http://www.email.it/f > > Sponsor: > Partecipa al concorso Best Western, ogni giorno puoi vincere un > fantastico > week end da sogno! > Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5096&d=20060511 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > !DSPAM:14,4462e6f834491668112969! >
I use 8.0.7; PostgreSQL will create tables with the number of a row (called
an OID) automatically if the config parameter called default_with_oids in
postgres.conf file is set to true. If not, it will do what your CREATE TABLE
statement says (WITH OIDS or WITHOUT OIDS); here is a sample:
CREATE TABLE alarm_active (
alarmtypeid integer NOT NULL default '0',
alarmsource integer NOT NULL default '0',
alarmstateid integer NOT NULL default '0',
messageid integer NOT NULL default '0',
ackby varchar(30) default NULL,
silencedby varchar(30) default NULL,
updatedtime timestamp(3) NOT NULL default current_timestamp(3),
alarmrfid integer NOT NULL default '0',
CONSTRAINT alarm_active_pk PRIMARY KEY (alarmtypeid,alarmsource)
) WITH OIDS;
You asked about PRIMARY KEYS. There is an example of that above, too. You
can also use ALTER TABLE to add a primary key.
I have never used dbf2pg.exe so I don't know what it adds or substracts from
the conversion process. But if it doesn't take care of the above for you, I
suppose what you could do is:
- do your import;
- set up the new table with another name, the way you want it;
- INSERT into the new table from the table that got set up by db2pg.exe
- drop the old table
- rename the new table to the old table name by using
ALTER TABLE <new table> RENAME TO <old table>
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of
giuseppe.derossi@email.it
Sent: Thursday, May 11, 2006 3:25 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] trobles in importing dbf file
Hi Guys,
I'm a newbie in Postgres and hope my mail is not off topic. Well, I'm
migrating from Mysql to PostGree in order to use the postgis features. I've
some dbf files I've to import, so I've used the utility dbf2pg.exe ( I've
installed the version 8.1 in win xp env). Now is there a way to add
automatically a primary key if no, how
can I can add it after importing the table in postgres ? In mYsql an index
(the number or row) is automatically added as first column.
Sorry if the question is trivial, I didn't suspect this gap.
Is there a standard proceure to import a database mapped in a lot of dbf
files.
Thanks in advantage
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f
Sponsor:
Partecipa al concorso Best Western, ogni giorno puoi vincere un fantastico
week end da sogno!
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5096&d=20060511
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
BTW, your email almost got marked as spam:
X-Spam-Status: No, score=4.1 required=5.0 tests=BAYES_00,DNS_FROM_RFC_ABUSE,
NO_REAL_NAME,RCVD_HELO_IP_MISMATCH,RCVD_NUMERIC_HELO
autolearn=no version=3.1.0
On Thu, May 11, 2006 at 09:24:35AM +0200, giuseppe.derossi@email.it wrote:
> Hi Guys,
> I'm a newbie in Postgres and hope my mail is not off topic. Well, I'm
> migrating from Mysql to PostGree in order to use the postgis features. I've
It's Postgres or PostgreSQL, not Postgree.
> some dbf files I've to import, so I've used the utility dbf2pg.exe ( I've
> installed the version 8.1 in win xp env). Now is there a way to add
> automatically a primary key if no, how
> can I can add it after importing the table in postgres ? In mYsql an index
> (the number or row) is automatically added as first column.
> Sorry if the question is trivial, I didn't suspect this gap.
ALTER TABLE tablename ADD PRIMARY KEY (field1, field2);
or
ALTER TABLE tablename ADD UNIQUE (field1, field2);
--
Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
Hi Lane,
when I've imported my first dbf table by using db2pg.exe, I tought to obtain
a sql modifiable script which I could use to import the table. The table is
automatically imported with the feature "without OIDS". For this reason
I've to use your solution. Thanks a lot.
Peraphs, because of I've to import a large database, I ought to write a
procedure which performs this elaboration.
My best regards
Giu
--------- Original Message --------
Da: Lane Van Ingen <lvaningen@esncc.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Oggetto: RE: [ADMIN] trobles in importing dbf file
Data: 11/05/06 13:57
>
>
>
> I use 8.0.7; PostgreSQL will create tables with the number of a row
(called
> an OID) automatically if the config parameter called default_with_oids in
> postgres.conf file is set to true. If not, it will do what your CREATE
TABLE
> statement says (WITH OIDS or WITHOUT OIDS); here is a sample:
> CREATE TABLE alarm_active (
> alarmtypeid integer NOT NULL default '0',
> alarmsource integer NOT NULL default '0',
> alarmstateid integer NOT NULL default '0',
> messageid integer NOT NULL default '0',
> ackby varchar(30) default NULL,
> silencedby varchar(30) default NULL,
> updatedtime timestamp(3) NOT NULL default current_timestamp(3),
> alarmrfid integer NOT NULL default '0',
> CONSTRAINT alarm_active_pk PRIMARY KEY (alarmtypeid,alarmsource)
> ) WITH OIDS;
>
> You asked about PRIMARY KEYS. There is an example of that above, too. You
> can also use ALTER TABLE to add a primary key.
>
> I have never used dbf2pg.exe so I don't know what it adds or substracts
from
> the conversion process. But if it doesn't take care of the above for you,
I
> suppose what you could do is:
> - do your import;
> - set up the new table with another name, the way you want it;
> - INSERT into the new table from the table that got set up by db2pg.exe
> - drop the old table
> - rename the new table to the old table name by using
> ALTER TABLE <new table> RENAME TO <old table>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of
> giuseppe.derossi@email.it
> Sent: Thursday, May 11, 2006 3:25 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] trobles in importing dbf file
>
>
> Hi Guys,
> I'm a newbie in Postgres and hope my mail is not off topic. Well, I'm
> migrating from Mysql to PostGree in order to use the postgis features.
I've
> some dbf files I've to import, so I've used the utility dbf2pg.exe ( I've
> installed the version 8.1 in win xp env). Now is there a way to add
> automatically a primary key if no, how
> can I can add it after importing the table in postgres ? In mYsql an index
> (the number or row) is automatically added as first column.
> Sorry if the question is trivial, I didn't suspect this gap.
> Is there a standard proceure to import a database mapped in a lot of dbf
> files.
>
>
>
>
> Thanks in advantage
>
> --
> Email.it, the professional e-mail, gratis per te: http://www.email.it/f
>
> Sponsor:
> Partecipa al concorso Best Western, ogni giorno puoi vincere un
fantastico
> week end da sogno!
> Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5096&d=20060511
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
>
>
>
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f
Sponsor:
Tutta la musica che vuoi a portata di click!
Entra in www.radiosnj.com
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=5180&d=20060512