Postgres Pro
Downloads
Home   >   mailing lists

Thread: put text list into table form

put text list into table form

From
"Web2cad"
Date:
Hi
I have a list of cities stored in flat file that I would like to left join with another table.
 
The file looks like this:
city1,city2,city3
 
I want to put them into a table like this:
 city
--------
city1
city2
city3
 
I don't actually want to store them into the data base. I just want to get the above table with a select statement.
 
At the moment I'm using this statement which I feel is rather ugly:
select 'city1' union select 'city2' union select 'city3'
 
Is there a way of putting a text list into table form without using a bunch of unions? Like a row separator  (RS)?
So I can do something like
select 'city1'(RS)'city2'(RS)'city3'
 
Thanks for any help

Re: put text list into table form

From
Sean Davis
Date:


On 1/25/06 9:23 PM, "Web2cad" <alex@web2cad.co.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

Re: put text list into table form

From
Sean Davis
Date:


On 1/30/06 7:24 PM, "Web2cad" <alex@web2cad.co.jp> wrote:

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

Create temporary table city_table (
...
);

In psql, you can then do:

\copy city_table from 'flat.file.txt'

To populate the table.  Note that the temporary table is only visible to the
session that created it and will be dropped after that session is closed.

Sean

Re: put text list into table form

From
"Web2cad"
Date:
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@mail.nih.gov>
To: "Web2cad" <alex@web2cad.co.jp>; <pgsql-novice@postgresql.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@web2cad.co.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
>

Re: put text list into table form

From
Bruno Wolff III
Date:
On Tue, Jan 31, 2006 at 09:24:30 +0900,
  Web2cad <alex@web2cad.co.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?