Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create table error

Create table error

From
Kumar S
Date:
Dear group,
Could you please suggest if my sql syntax is wrong for
creating the table:
my sql statements:
create table EXPERIMENT
(
        exp_id          serial,
        exp_name        varchar(32)     not null,
        con_id          serial  references
contacts(con_id),
        exp_type        varchar(32)     not null,
        exp_desc        varchar(64),
        exp_pmid        integer(30)     not null,
        exp_rawdata_url varchar(32),
        constraint      experiment_pk primary
key(exp_id)
);


When I compile this I get the following error:
psql:/home/sk/temp/postgres/exp.sql:11: ERROR:  syntax
error at or near "(" at character 200

What is wrong with my code.

Please help.

SK

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: Create table error

From
Stephan Szabo
Date:
On Tue, 14 Sep 2004, Kumar S wrote:

> Dear group,
> Could you please suggest if my sql syntax is wrong for
> creating the table:
> my sql statements:
> create table EXPERIMENT
> (
>         exp_id          serial,
>         exp_name        varchar(32)     not null,
>         con_id          serial  references
> contacts(con_id),
>         exp_type        varchar(32)     not null,
>         exp_desc        varchar(64),
>         exp_pmid        integer(30)     not null,
>         exp_rawdata_url varchar(32),
>         constraint      experiment_pk primary
> key(exp_id)
> );
>
>
> When I compile this I get the following error:
> psql:/home/sk/temp/postgres/exp.sql:11: ERROR:  syntax
> error at or near "(" at character 200
>
> What is wrong with my code.

The type INTEGER doesn't take additional information in parens.
What range of values were you trying to hold?

Re: Create table error

From
Ennio-Sr
Date:
* Kumar S <ps_postgres@yahoo.com> [140904, 13:38]:
> Dear group,
> Could you please suggest if my sql syntax is wrong for
> creating the table:
> my sql statements:
> create table EXPERIMENT
> (
>         exp_id          serial,
> [ ... ]
>         exp_rawdata_url varchar(32),
                                    ^^^
>         constraint      experiment_pk primary
> key(exp_id)
> );
>
>
> When I compile this I get the following error:
> psql:/home/sk/temp/postgres/exp.sql:11: ERROR:  syntax
> error at or near "(" at character 200
>
> What is wrong with my code.

May be you should drop the underlined comma?
Regards,
     Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.     \\?//
 Fa' qualche cosa di cui non sei capace!"   (diceva Henry Miller) ]     (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that.             )=(
 Do something you aren't good at!" (used to say Henry Miller) ]

Re: Create table error

From
Kumar S
Date:
Thank you Stephan Szabo,
 I removed additional information from parenthesis and
it worked.
What I wanted to store is a number (e.g:15099404)
which is a publication ID from PubMed (medical litt.
database).  This number I wanted to use further to
build queries to extract the publication linked to
that number.

My collegue suggested that I could store that number
as a string too because I will not be doing any
calculations on that number.

Do you have any comments.

Thanks
Kumar.

--- Stephan Szabo <sszabo@megazone.bigpanda.com>
wrote:

> On Tue, 14 Sep 2004, Kumar S wrote:
>
> > Dear group,
> > Could you please suggest if my sql syntax is wrong
> for
> > creating the table:
> > my sql statements:
> > create table EXPERIMENT
> > (
> >         exp_id          serial,
> >         exp_name        varchar(32)     not null,
> >         con_id          serial  references
> > contacts(con_id),
> >         exp_type        varchar(32)     not null,
> >         exp_desc        varchar(64),
> >         exp_pmid        integer(30)     not null,
> >         exp_rawdata_url varchar(32),
> >         constraint      experiment_pk primary
> > key(exp_id)
> > );
> >
> >
> > When I compile this I get the following error:
> > psql:/home/sk/temp/postgres/exp.sql:11: ERROR:
> syntax
> > error at or near "(" at character 200
> >
> > What is wrong with my code.
>
> The type INTEGER doesn't take additional information
> in parens.
> What range of values were you trying to hold?
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: Create table error

From
Stephan Szabo
Date:
On Tue, 14 Sep 2004, Kumar S wrote:

> Thank you Stephan Szabo,
>  I removed additional information from parenthesis and
> it worked.
> What I wanted to store is a number (e.g:15099404)
> which is a publication ID from PubMed (medical litt.
> database).  This number I wanted to use further to
> build queries to extract the publication linked to
> that number.
>
> My collegue suggested that I could store that number
> as a string too because I will not be doing any
> calculations on that number.

Well, it all depends on how it's used.  For example,
would you want to sort on the field, and would you
want 9000 to come before or after 150000? Does the
value ever have leading zeros that it would be bad
to lose?

If you're really just using it as a value that's only ever compared to be
= (or <>), a string is fine (*). If it's got to have leading zeros, a
string is nearly necessary.  If you want numeric sorting on the field
you'd either need to cast in your queries or use a number. If you're
comparing like ids after 15099403 where you want a numeric comparison
rather than a textual one, you'll need to cast or use a numer.

(*) - You probably would want a constraint to keep the valid values
constrained appropriately.