Re: create table as problem

[Michael Fuhr <mike@xxxxxxxx>]

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