Re: [SQL] why don't this create table work?

On Wed, Nov 03, 1999 at 12:35:52AM -0500, Tom Lane wrote:
> User & <marc@oscar.noc.cv.net> writes:
> > nnm=> create table nnm_event_limits (
> > nnm-> nodename varchar(256) not null,
> > nnm-> event_oid  varchar(256) not null,
> > nnm-> always_never varchar(1) null,
> > nnm-> limit int4);
> > ERROR:  parser: parse error at or near "null"
> 
> > This is converted from openviews table scheema.
> 
> Openviews is allowing stuff that is not in the SQL92 spec --- AFAICS
> there is nothing in the spec about a "NULL" column qualification.
> You can say "NOT NULL" or you can leave it out.

I am sorry I should have said the oracle table scheema for the 
openview data wharehouse.  

> 
> > limit is not a reserved word as far as I can tell, any ideas?
> 
> Yes it is.  Probably we could allow it as a column name anyway,
> but it's not listed as a "safe" column ID in the 6.5 grammar.
> If you're determined to use it as a column name even though it's
> reserved, put double quotes around it, eg "limit" int4.  But you'll
> have to do that every time you refer to it in a query, so choosing
> another name is probably the path of least resistance.

Thanks it worked.  The purpose for this exersize is to get a demo server up
for web based reporting on the openview datawharehouse we are collecting
here.  The final version will connect over odbc to solid or oracle, probably
solid, to get live data and do reports on it.  Since HP has already fixed the
colum names I am just going to have to deal with it.  
> 
> > ps would useing text instead of varchar be a good thing to do?
> 
> Use varchar if you have an application-defined reason to want to
> enforce a specific upper limit on the length of the string in
> a column.  If you don't have any particular upper limit in mind,
> use text --- it's the same thing as varchar except for the limit.
> 
> In the above example, I'll bet a nickel that you have no clear reason
> for specifying an upper limit of 256 on nodename and event_oid, so they

If I was to take that bet you would owe me a shinny new nickle, my reason
for the use of varchar instead of text is very good: I don't realy know
what I am doing so while I am learning I change as little as possable
and I have not figured out why they did what they did so I will not
'improve' it.  And this has to be good enough to get me the go ahead
to get the production version started and no better, that last part 
is very important to me as features tend to stop creaping and start 
running into my code when I don't watch out.  

<some what off topic>  The webserver I am useing is roxen (www.roxen.com)
it is open source and very nice, and comes with postgres support out of 
the box.  It has a tag based language called RXML for doing neat stuff
and it runs as a non forking deamon which makes it very nice for an
embeded type server, we have had problems recently with netscape proxy
killing some very important boxes here recently, and a real convenient 
admin gui.  All in all I am very impressed with the product, just like
postgres very very nice job and much thanks to the developers.

<off topic/>

Thanks Marc


> should probably be text.  If always_never can legitimately be either 0
> or 1 chars long, but never more, then varchar(1) is the right
> declaration.  (Perhaps it should always be 1 char long --- in that case
> you should've said char(1).  Note that NULL is by no means the same
> thing as a zero-character string.)
> 
>             regards, tom lane