From: | "Web2cad" <alex(at)web2cad(dot)co(dot)jp> |
---|---|
To: | "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: put text list into table form |
Date: | 2006-01-31 00:24:30 |
Message-ID: | 000d01c625fc$b3d3b420$7dfda8c0@hpxw4100 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | operationsengineer1 | 2006-01-31 01:04:18 | Insert Text |
Previous Message | Murat Tasan | 2006-01-30 23:19:00 | function return type is a setof some column type |