Thread: Copying a column into a temp. table

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

  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

[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&f=46&t=2105913&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

On Fri, Oct 1, 2010 at 4:59 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
> 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.
>