From: | "George Pavlov" <gpavlov(at)mynewplace(dot)com> |
---|---|
To: | "Marco Lechner" <marco(dot)lechner(at)geographie(dot)uni-freiburg(dot)de>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: create table with rownames as values in column of seciond table |
Date: | 2008-12-16 00:06:19 |
Message-ID: | 8C5B026B51B6854CBE88121DBF097A8603350905@ehost010-33.exch010.intermedia.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
your problem is a little unorthodox, but i will spare you the "why the
heck do you want to do this?" discussion and assume you have good
reasons... so here's a "dynamic SQL" approach:
select 'create table test (id bigint, '|| array_to_string(array(select
a||' text' from foo),', ')||');';
not pretty, but you get the idea.
> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)org] On Behalf Of Marco Lechner
> Sent: Monday, December 15, 2008 1:42 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] create table with rownames as values in column of
> seciond table
>
> Hi list,
>
> I try to create a table using plpgsql or plpythonu. I'm starting with
> programming in postgresql therfore I'm a little bit confused. I know a
> little bit of python and a little bit of SQL. May be some hints could
> help me gettin into it:
>
> My problem is:
> I'm having a table with a column a and certain values in it.
> _a_|_b_
> ab | v1
> de | v2
> fc | v3
> wd | v4
> ed | v5
> ...|...
>
> And I need a script to make the first column the rownames and the
other
> columns the values of the newly created table:
>
> CREATE TABLE test(
> ID BIGINT,
> ab TEXT,
> de TEXT,
> fc TEXT,
> wd TEXT,
> ed TEXT,
> ...
> PRIMARY KEY(ID));
>
> and: INSERT INTO test VALUES('v1', 'v2', 'v3', 'v4', 'v5', ...);
>
> The number of rows and the names of the columns vary.
>
> I get a list of the values in the first table by SELECT a FROM table1;
> But how to put this into a CREATE TABLE script - and fill in the other
> columns as values?
>
> Marco
From | Date | Subject | |
---|---|---|---|
Next Message | Asko Oja | 2008-12-16 17:00:18 | How can this be legal syntax |
Previous Message | Marco Lechner | 2008-12-15 21:42:15 | create table with rownames as values in column of seciond table |