From: | Vincent Michel <vincent(dot)michel(at)logilab(dot)fr> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Fast identifiers table creation |
Date: | 2011-06-30 16:33:09 |
Message-ID: | 201106301833.10021.vincent.michel@logilab.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi all,
I have a simple problem, but don't know any simple solution for it.
I have a list of (millions) of couple (url1, url2), where url1 and url2 are
strings. I have imported this list in a table "urls" by COPY FROM.
In the same time, I have a table "idurl" (id integer, url string) that gives
me a correspondence between an url and an identifier (also millions of
different urls).
I would like to create a new table (id1, id2), each row of this table being
the couple of identifiers corresponding to a couple of urls from the table
"urls".
I have tried OUTER JOIN:
CREATE tmp1 (id integer);
INSERT INTO tmp1 (id) SELECT idurl.id FROM urls LEFT OUTER JOIN idurl
ON (url.url1 = idurl.url);
and I have done the same thing with another table tmp2.
But this takes a while, and I have the problem of combining the resulting
tables tmp1 and tmp2.
Another solution that I have tried, is to create a index on url in the table
idurl, but this take lots of space in memory.
I know that there is no miracle solution, but if someone has a better
solution...
Thanks !
-Vincent
From | Date | Subject | |
---|---|---|---|
Next Message | Ognjen Blagojevic | 2011-06-30 20:02:51 | Re: [NOVICE] change data type 'money' to '€' |
Previous Message | Jean-Yves F. Barbier | 2011-06-30 15:44:05 | Re: Locking out a user after several failed login attempts |