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