Обсуждение: trobles in importing dbf file

Поиск
Список
Период
Сортировка

trobles in importing dbf file

От
giuseppe.derossi@email.it
Дата:
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



Re: trobles in importing dbf file

От
"Andy Shellam"
Дата:
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!
>



Re: trobles in importing dbf file

От
"Lane Van Ingen"
Дата:
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



Re: trobles in importing dbf file

От
"Jim C. Nasby"
Дата:
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?"

Re: trobles in importing dbf file

От
giuseppe.derossi@email.it
Дата:
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