create table with rownames as values in column of seciond table

From: Marco Lechner <marco(dot)lechner(at)geographie(dot)uni-freiburg(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: create table with rownames as values in column of seciond table
Date: 2008-12-15 21:42:15
Message-ID: 4946CF37.8010907@geographie.uni-freiburg.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Attachment Content-Type Size
marco_lechner.vcf text/x-vcard 325 bytes

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message George Pavlov 2008-12-16 00:06:19 Re: create table with rownames as values in column of seciond table
Previous Message Philippe Lang 2008-12-15 14:21:41 Re: "Subquery must return only one column" & query optimization