[SQL] insert into table from list or array ?

[Andreas]

 Hi,

is it possible to insert into a table from list or an array ?

Suppose there is a set of numbers that might be IDs of tables within the DB.
To work with them I'd need a temporary table that just holds a single 
column with those numbers.

Something like

create temporary table tmptable as select id from
( 2, 3, 5, 7, 11, 13, 17, 19, 23, 31,
  37, ... );

would be great.

I get those numbers as textfile with 10 numbers per line and devided by 
comma+space as in the sample above, though the comma+space is negotiable 
if this were an issue.


:)

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] insert into table from list or array ?

[Viktor Bojović]

i think that you can try to
create temporary table tmptable(id integer, numbers integer[]);
and
insert into table values (1,array[2, 3, 5, 7, 11, 13, 17, 19, 23, 31, 37,
...]);

On Mon, Oct 18, 2010 at 8:00 PM, Andreas  wrote:

>  Hi,
>
> is it possible to insert into a table from list or an array ?
>
> Suppose there is a set of numbers that might be IDs of tables within the
> DB.
> To work with them I'd need a temporary table that just holds a single
> column with those numbers.
> Something like
>
> create temporary table tmptable as select id from
> ( 2, 3, 5, 7, 11, 13, 17, 19, 23, 31,
>  37, ... );
>
> would be great.
>
> I get those numbers as textfile with 10 numbers per line and devided by
> comma+space as in the sample above, though the comma+space is negotiable if
> this were an issue.
>
> :)
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
---
Viktor Bojović
---
Wherever I go, Murphy goes with me

Re: [SQL] insert into table from list or array ?

[Pavel Stehule]

Hello

2010/10/18 Andreas :
>  Hi,
>
> is it possible to insert into a table from list or an array ?
>

yes, it's possible

INSERT INTO tmptab
   SELECT v
 FROM unnest(string_to_array('1,2,4,2,1',',')) g(v)

Regards

Pavel Stehule

> Suppose there is a set of numbers that might be IDs of tables within the DB.
> To work with them I'd need a temporary table that just holds a single column
> with those numbers.
> Something like
>
> create temporary table tmptable as select id from
> ( 2, 3, 5, 7, 11, 13, 17, 19, 23, 31,
>  37, ... );
>
> would be great.
>
> I get those numbers as textfile with 10 numbers per line and devided by
> comma+space as in the sample above, though the comma+space is negotiable if
> this were an issue.
>
> :)
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] insert into table from list or array ?

[Andreas]

 Am 18.10.2010 20:14, schrieb Pavel Stehule:

2010/10/18 Andreas:

is it possible to insert into a table from list or an array ?

yes, it's possible

INSERT INTO tmptab
SELECT v
  FROM unnest(string_to_array('1,2,4,2,1',',')) g(v)



Thanks Pavel,
though I'm just not yet 100% aware how it works but it works.   :)

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql