Thread: create table as problem

create table as problem

From

garry saddington

Date:

15 September 2006, 20:47:57

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

I need to make a new table with a classid. Any comments are more than
welcome.
regards
garry

Re: create table as problem

From

Michael Fuhr

Date:

15 September 2006, 21:01:06

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