Received: from cyclone.stortek.com (cyclone.stortek.com [129.80.22.130]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f29GLlx78172 for ; Fri, 9 Mar 2001 11:21:47 -0500 (EST) (envelope-from CreagRS@LOUISVILLE.STORTEK.COM) Received: from cyclone.stortek.com (localhost [127.0.0.1]) by cyclone.stortek.com (8.9.3/8.9.3) with ESMTP id JAA10509 for ; Fri, 9 Mar 2001 09:21:41 -0700 (MST) Received: from vinson-atm6.stortek.com (root@vinson.stortek.com [129.80.40.134] (may be forged)) by cyclone.stortek.com (8.9.3/8.9.3) with ESMTP id JAA10441 for ; Fri, 9 Mar 2001 09:21:35 -0700 (MST) Received: from lsv-bridge.stortek.com (lsv-bridge.stortek.com [129.80.5.131]) by vinson-atm6.stortek.com (8.9.3/8.9.3) with ESMTP id JAA20163 for ; Fri, 9 Mar 2001 09:21:34 -0700 (MST) Received: by lsv-bridge.stortek.com with Internet Mail Service (5.5.2653.19) id ; Fri, 9 Mar 2001 09:21:33 -0700 Message-ID: <10FE17AD5F7ED31188CE002048406DE8514CDD@lsv-msg06.stortek.com> From: "Creager, Robert S" To: "'SQL - PGSQL'" Subject: cannot get CREATE TABLE AS to work Date: Fri, 9 Mar 2001 09:21:32 -0700 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200103/192 X-Sequence-Number: 1845 I'm sure I'm doing something wrong, and I'm hoping someone can show me the way of things. Running 7.1beta5 on an Ultra 5, Solaris 2.6 w/256Mb mem. If I remove the AS, the table creates correctly and I can do the INSERT INTO with the SELECT clause psql -d tassiv -c "\ create table observationsII ( \ ra float8 not null, \ decl float8 not null, \ mag float8 not null, \ smag float8 not null, \ obs_id serial, \ file_id int4 references files on delete cascade, \ star_id int4 references comp_loc on delete set null default null ) \ AS select o.ra, o.decl, o.mag, o.smag, o.file_id from observations o" ERROR: parser: parse error at or near "AS" And the next question, should this really be taking 3 hours to insert 315446 records? I noticed the disk is basically idle during the few times when I watched. Would this be because of the index created on obs_id? time psql -d tassiv -c "insert into observationsII( ra, decl, mag, smag, file_id ) select ra, decl, mag, smag, file_id from observations" INSERT 0 315446 0.01u 0.01s 3:13:22.39 0.0% Robert Creager Senior Software Engineer Client Server Library 303.673.2365 V 303.661.5379 F 888.912.4458 P StorageTek INFORMATION made POWERFUL