Copying a column into a temp. table

Hi Alex,

Of course "select ... into" doesn't work, but how should I do it?
Can't find in http://www.postgresql.org/docs/8.1/static/

You might want to look at the CREATE TABLE AS command. You might even
use the temporary definer.

Ciao,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

On Friday 01 October 2010 6:54:09 am Alexander Farber wrote:

How do you copy a column from table please?
I'm trying with PostgreSQL 8.1.21/CentOS 5.5:

# psql -a -f clean-forum.sql
start transaction;
START TRANSACTION
create temp table old_topics (topic_id integer) on commit delete rows;
CREATE TABLE
create temp table old_posts (post_id integer) on commit delete rows;
CREATE TABLE
select topic_id from phpbb_topics where forum_id=5 and topic_poster=1
and age(to_timestamp(topic_time))>interval '14 days' into old_topics;
psql:clean-forum.sql:6: ERROR: syntax error at or near "into" at character
124 psql:clean-forum.sql:6: LINE 1: ...
age(to_timestamp(topic_time))>interval '14 days' into old_t...
psql:clean-forum.sql:6:
^
commit
ROLLBACK

Of course "select ... into" doesn't work, but how should I do it?
Can't find in http://www.postgresql.org/docs/8.1/static/

Thank you
Alex

http://www.postgresql.org/docs/8.1/interactive/sql-insert.html

--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Oct 1, 2010 at 10:02 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On Friday 01 October 2010 6:54:09 am Alexander Farber wrote:

How do you copy a column from table please?
I'm trying with PostgreSQL 8.1.21/CentOS 5.5:

select topic_id from phpbb_topics where forum_id=5 and topic_poster=1
and age(to_timestamp(topic_time))>interval '14 days' into old_topics;
psql:clean-forum.sql:6: ERROR: syntax error at or near "into" at

character

124 psql:clean-forum.sql:6: LINE 1: ...
age(to_timestamp(topic_time))>interval '14 days' into old_t...

Of course "select ... into" doesn't work, but how should I do it?
Can't find in http://www.postgresql.org/docs/8.1/static/

Thank you
Alex

http://www.postgresql.org/docs/8.1/interactive/sql-insert.html

Short answer is
insert into my_temp_table select a,b from mytable;

Or as Gabriele said, CREATE TEMP TABLE mytable AS SELECT should also work.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

On Fri, Oct 1, 2010 at 10:52 AM, Alexander Farber <
alexander.farber@gmail.com> wrote:

Thank you, I've created the following script which seems to work ok.

I just hope, that it won't be hit by the max_stack_depth-problem
which actually forced me to look for a custom solution -

SQL ERROR [ postgres ]
ERROR: stack depth limit exceeded HINT: Increase the configuration
parameter "max_stack_depth". []

SQL
DELETE FROM phpbb_posts WHERE post_id IN (334767, ..skipped.., 382871)

# psql -a -f clean-phpbb-forum.sql
start transaction;
START TRANSACTION
create temp table old_topics (topic_id integer) on commit delete rows;
CREATE TABLE
create temp table old_posts (post_id integer) on commit delete rows;
CREATE TABLE
insert into old_topics select topic_id from phpbb_topics
where forum_id=5 and topic_poster=1 and
age(to_timestamp(topic_time))>interval '14 days';
INSERT 0 14788
-- select count(topic_id) as "old topics:" from old_topics;
insert into old_posts select p.post_id from phpbb_posts p, old_topics t
where p.forum_id=5 and p.poster_id=1 and p.topic_id=t.topic_id;
INSERT 0 73718
-- select count(post_id) as "old posts:" from old_posts;
delete from phpbb_posts where post_id in (select post_id from old_posts);
DELETE 73718
delete from phpbb_topics where topic_id in (select topic_id from
old_topics);
DELETE 14788
update phpbb_config set
config_value = (select count(topic_id) from phpbb_topics)
where config_name = 'num_topics';
UPDATE 1
update phpbb_config set
config_value = (select count(post_id) from phpbb_posts)
where config_name = 'num_posts';
UPDATE 1
update phpbb_users set
user_posts = (select count(post_id) from phpbb_posts where poster_id=1)
where user_id = 1;
UPDATE 1
update phpbb_forums set
forum_posts = (select count(post_id) from phpbb_posts),
forum_topics = (select count(topic_id) from phpbb_topics),
forum_topics_real = (select count(topic_id) from phpbb_topics)
where forum_id = 5;
UPDATE 1
commit
COMMIT

Please stay on the list.

With temporary tables you shouldn't be hitting that limit. BTW, what
Postgres version are you using? and any rough estimation of how many
elements that IN list has in the query that fails?

You could have also done:

DELETE FROM phpbb_posts WHERE post_id IN (select xyz from mytable where
...)

assuming that inner select would return the list: 334767, ..skipped..,
382871, and avoided creating temp tables altogether.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

[sorry, I didn't intend to send my prev. reply off-list]

Hello Gurjeet,

thanks for you interest! I've just summarized my problem at the

http://www.phpbb.com/community/viewtopic.php?uid=221190&amp;f=46&amp;t=2105913&amp;start=0

I log protocols of card games into forum_id=5 as Anonymous user_id=1
at my site http://preferans.de and there are currently:

phpbb=> select count(*) from phpbb_posts where poster_id=1;
count
--------
115431
(1 row)

I use phpBB 3.0.7-PL1 with postgresql-server-8.1.21-1.el5_5.1 with
CentOS Linux 5.5, both under 32-bit (development VM) and 64-bit
(production server).

Regards
Alex