Re: put text list into table form

Hi Sean
thanks for the reply.

I ultimatly want to count the number of users per city, for the cities with
no users I want to show a 0.
The list of cities that I want a count for are stored in flat file.
(this is something I inherited, rather bad system/DB design I know, will
have to push for a rework at a later date)

So I need to produce a city table from flat file temporarily then LEFT JOIN
that table to the user table.
giving somthing like:
city | count
------------
city1 | 3
city2 | 0
city3 | 1
........ etc

Since the user table may/may not have all the cities in the file. I can't
just do a group by on the user table.
This is the query that I am generating to get the above effect.

SELECT count(uid) FROM (SELECT 'city1' AS city UNION SELECT 'city2' AS city
UNOIN........) AS c
LEFT JOIN "user" ON (c.city="user".city) GROUP BY c.city;

So I am asking is there a way in postgres that will let me create a
temporary table from a delimited flat file/string??
Something that will have the same effect as the
(SELECT........UNION........) query above??

Alex

----- Original Message -----
From: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
To: "Web2cad" <alex(at)web2cad(dot)co(dot)jp>; <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, January 30, 2006 8:43 PM
Subject: Re: [NOVICE] put text list into table form

>
>
>
> On 1/25/06 9:23 PM, "Web2cad" <alex(at)web2cad(dot)co(dot)jp> wrote:
>
> > Hi
> > I have a list of cities stored in flat file that I would like to left
join
> > with another table.
>
> Why not just do:
>
> Select * from table2 where table2.city in ('city1','city2','city3');
>
> Is that what you ultimately want to do?
>
>
> > The file looks like this:
> > city1,city2,city3
>
> Sean
>