Re: How can I do this? - Mailing list pgsql-general

From Chris Boget
Subject Re: How can I do this?
Date
Msg-id 006f01c2b7e0$2c290bc0$8c01a8c0@ENTROPY
Whole thread Raw
In response to How can I do this?  ("Boget, Chris" <chris@wild.net>)
Responses Re: How can I do this?
List pgsql-general
> >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


pgsql-general by date:

Previous
From: Neil Conway
Date:
Subject: Re: dropping template1
Next
From: Jochem van Dieten
Date:
Subject: Re: dropping template1