Обсуждение: How can I do this?

I'm still in the process of converting my MySQL DB
to PG.  I've redone the table schemas so that they
are normal form.
In trying to move data from one table to another, I
need the corresponding relevant data from 2 other
tables.  The table that I'm moving the data INTO has
the following schema

trader_id   | integer  | not null default 0
card_name   | integer  | not null default 0
total_have  | integer  | not null default 0
is_tradable | smallint | default 0
record_num  | bigint   | not null default nextval('public.trader_haves_record_num_seq'::text)
Indexes: trader_haves_pkey primary key btree (record_num),
         trader_haves_card_name_key btree (card_name),
         trader_haves_trader_id_key btree (trader_id)
Foreign Key constraints:
$1 FOREIGN KEY (trader_id) REFERENCES logins(record_num) ON UPDATE CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (card_name) REFERENCES cards_type(record_num) ON UPDATE CASCADE ON DELETE CASCADE

In the old table, trader_id had the person's name and
card_name had the name of the card.  However, now, those
fields are referencing the relevant fields in the logins
and cards_type tables, respectively.  As you can see
above. :p
Since I can't just copy the data straight over, I need
to get the appropriate record nums for the data in the
old columns.  This is the query that I tried to build:

SELECT ( SELECT cards_type.record_num FROM cards_type WHERE cards_type.card_name LIKE ( substr( trader_haves_old.card_name, 1, 9 ) || '%' )), trader_haves_old.total_have, ( SELECT logins.record_num FROM logins WHERE logins.name = trader_haves_old.trader ), trader_haves_old.available from trader_haves_old;

However, that's not working because the subselects are
returning more than one row.  Ok, that's fine I guess.
But I don't know any other way I can do this?  How can
I create a query to return the data I need?  I'm still
relatively unfamiliar and am still getting used to sub
selects because MySQL doesn't have them.

Any help would be very much appreciated!

thnx,
Chris

On Wed, 8 Jan 2003 18:37:13 -0600 , "Boget, Chris" <chris@wild.net>
wrote:
>I'm still in the process of converting my MySQL DB
>to PG.  I've redone the table schemas so that they
>are normal form.
>
>SELECT ( SELECT cards_type.record_num FROM cards_type WHERE
>cards_type.card_name LIKE ( substr( trader_haves_old.card_name, 1, 9 ) ||
>'%' )), trader_haves_old.total_have, ( SELECT logins.record_num FROM logins
>WHERE logins.name = trader_haves_old.trader ), trader_haves_old.available
>from trader_haves_old;

Chris, why do you want to match on only the first nine characters of
card_name?  A typical normalization job looks like:

-- record_num is supplied by DEFAULT clause
INSERT INTO cards_type(card_name)
SELECT DISTINCT card_name
  FROM trader_haves_old;

-- record_num is supplied by DEFAULT clause
INSERT INTO logins(name)
SELECT DISTINCT trader
  FROM trader_haves_old;

INSERT INTO trader_haves(card_id, total_have, trader_id, available)
SELECT c.record_num, o.total_have, l.record_num, o.available
  FROM trader_haves_old o
       INNER JOIN cards_type c ON c.card_name = o.card_name
       INNER JOIN logins l ON l.name = o.trader;

HTH.
Servus
 Manfred

> >SELECT ( SELECT cards_type.record_num FROM cards_type WHERE
> >cards_type.card_name LIKE ( substr( trader_haves_old.card_name, 1, 9 ) ||
> >'%' )), trader_haves_old.total_have, ( SELECT logins.record_num FROM logins
> >WHERE logins.name = trader_haves_old.trader ), trader_haves_old.available
> >from trader_haves_old;
> Chris, why do you want to match on only the first nine characters of card_name?

I'm doing that because of the kinds of problems that crop up when you don't
use normalization - the values in the card_name field in the 2 seperate tables
don't always match up.  Sometimes there is, say (as an analogy), a first and
last name and sometimes there is only a first name.  So by grabbing the first
9 characters of the column for this transfer from the old to the new table, I'll
be able to get the proper record number (from cards_type) for 99+% of the
cards.
Sadly, the MySQL database wasn't normalized and I've been running into a
lot of roadblocks (mainly from the fact that I'm still learning PG) in converting
it over.

> A typical normalization job looks like:

Right.  If all the names matched.

> INSERT INTO trader_haves(card_id, total_have, trader_id, available)
> SELECT c.record_num, o.total_have, l.record_num, o.available
>   FROM trader_haves_old o
>        INNER JOIN cards_type c ON c.card_name = o.card_name
>        INNER JOIN logins l ON l.name = o.trader;

I'll try (a slight modified version of) this when I get home.
Thanks!

Chris

On Thu, 9 Jan 2003 07:08:15 -0600, "Chris Boget" <chris@wild.net>
wrote:
>> Chris, why do you want to match on only the first nine characters of card_name?
>
>I'm doing that because of the kinds of problems that crop up when you don't
>use normalization - the values in the card_name field in the 2 seperate tables
>don't always match up.  Sometimes there is, say (as an analogy), a first and
>last name and sometimes there is only a first name.  So by grabbing the first
>9 characters of the column for this transfer from the old to the new table, I'll
>be able to get the proper record number (from cards_type) for 99+% of the
>cards.

So if you have

card_id | card_name
--------+--------------
    277 | 123456789abc
    727 | 123456789bbc
    772 | 123456789bca

which card_id do you want to select for trader_haves_old.card_name =
'123456789bbc'?

>Sadly, the MySQL database wasn't normalized and I've been running into a
>lot of roadblocks (mainly from the fact that I'm still learning PG) in converting
>it over.

While you are still unfamiliar with the new DB, don't make several
steps at once.  Strictly separate the tasks of
a) importing data,
b) cleaning up structures (normalization),
c) cleaning up data.

You already have done (a), because there is a table trader_haves_old.
If you do step (b) like suggested, you end up with data that are not
worse than now.  You can always look for similar names later:

SELECT count(*), substring(card_name, 1, 9)
  FROM cards_type
 GROUP BY substring(card_name, 1, 9)
HAVING count(*) > 1;

... and then eliminate unwanted card_names one by one (if there are
only a few):

SELECT card_id, card_name
  FROM cards_type
 WHERE card_name LIKE '123456789%';

UPDATE trader_haves
   SET card_id = 277
 WHERE card_id IN (727, 772);

DELETE FROM cards_type
 WHERE card_id IN (727, 772);

Good luck!
Servus
 Manfred

> So if you have
> card_id | card_name
> --------+--------------
>     277 | 123456789abc
>     727 | 123456789bbc
>     772 | 123456789bca
> which card_id do you want to select for trader_haves_old.card_name =
> '123456789bbc'?

That's a perfectly valid point.  And as I said, I'd be able to get the proper
record number for 99+%.  Happily, there aren't situations like the one you
describe.  The names of the cards are almost all distinguishable within the
first 9 characters. That's why I chose it as my aribitrary value. :p

> >Sadly, the MySQL database wasn't normalized and I've been running into a
> >lot of roadblocks (mainly from the fact that I'm still learning PG) in converting
> >it over.
> While you are still unfamiliar with the new DB, don't make several steps at once.
> Strictly separate the tasks of
> a) importing data,

Already done in temporary holding tables.

> b) cleaning up structures (normalization),

Done.  These are the tables I'm trying to move the data to.

> c) cleaning up data.

Haven't done this.  Perhaps it would be a good idea to update the temp table with
the proper values before moving over the the actual table.  This would certainly
make things a heck of a lot easier!

> Good luck!

Thank you very much for your help and your advice!

Chris