Re: put text list into table form

On Tue, Jan 31, 2006 at 09:24:30 +0900,
Web2cad <alex(at)web2cad(dot)co(dot)jp> wrote:
>
> 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;

That looks like a nice way to do things.

> 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??

If you wanted you could create a temporary table and load it using \copy
in psql. Unless the list of cities is really large, it probably won't buy you
much over what you are already doing.

Is there some reason you can't load the file in to the database and then make
that the definitive city list?