create table as problem

On Fri, Sep 15, 2006 at 09:42:35PM +0100, garry saddington wrote:

I am getting a syntax error at or near 'as' in this method, can anyone
help?

create table iclasses
(classid serial,
subject text,
year text,
groups text,
teacher text,
set text
)
as select distinct subject,year,groups,teacher,set from interimclasses

With CREATE TABLE AS you can specify column names but not column
types. If you want to create more columns than the query returns
then try an ordinary CREATE TABLE followed by INSERT INTO ... SELECT.

CREATE TABLE iclasses (
classid serial,
subject text,
year text,
groups text,
teacher text,
set text
);

INSERT INTO iclasses (subject, year, groups, teacher, set)
SELECT DISTINCT subject, year, groups, teacher, set
FROM interimclasses;

--
Michael Fuhr