Thread: why don't this create table work?
here is the sql: 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. here it is without the trailing null on always_never: nnm=> create table nnm_event_limits ( nnm-> nodename varchar(256) not null, nnm-> event_oid varchar(256) not null, nnm-> always_never varchar(1) , nnm-> limit int4); ERROR: parser: parse error at or near "limit" limit is not a reserved word as far as I can tell, any ideas? I am useing 6.5, got it from PG_VERSION file. I am new to DB programming in general and Postgres in particular. Thanks Marc ps would useing text instead of varchar be a good thing to do? Marc
On Tue, Nov 02, 1999 at 05:59:15PM -0500, User & allegedly wrote: > > here is the sql: > > 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, ^^^^^^^^^^ There is a 'not' missing... > nnm-> limit int4); > ERROR: parser: parse error at or near "null" > > This is converted from openviews table scheema. here it is without the > trailing null on always_never: > nnm=> create table nnm_event_limits ( > nnm-> nodename varchar(256) not null, > nnm-> event_oid varchar(256) not null, > nnm-> always_never varchar(1) , > nnm-> limit int4); > ERROR: parser: parse error at or near "limit" > > limit is not a reserved word as far as I can tell, any ideas? Actually, it is. You can do something like the following: select username from users limit 10; > I am useing 6.5, got it from PG_VERSION file. You're probably running 6.5.1 or 6.5.2. You can easily check this by starting psql and checking the first few lines. It will tell you the exact versionnumber. > ps would useing text instead of varchar be a good thing to do? That is my understanding, but I'm not completely sure. Mathijs
On Wed, Nov 03, 1999 at 05:00:26AM +0100, Mathijs Brands wrote: > On Tue, Nov 02, 1999 at 05:59:15PM -0500, User & allegedly wrote: > > > > here is the sql: > > > > 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, > ^^^^^^^^^^ > There is a 'not' missing... Not from my reading, null is the assumed default it does not have to be put in but it can be if you choose to. > > nnm-> limit int4); > > ERROR: parser: parse error at or near "null" > > > > This is converted from openviews table scheema. here it is without the > > trailing null on always_never: > > nnm=> create table nnm_event_limits ( > > nnm-> nodename varchar(256) not null, > > nnm-> event_oid varchar(256) not null, > > nnm-> always_never varchar(1) , > > nnm-> limit int4); > > ERROR: parser: parse error at or near "limit" > > > > limit is not a reserved word as far as I can tell, any ideas? > > Actually, it is. You can do something like the following: > > select username from users limit 10; This will not help me create the table, selecting is not the problem the table will not get created so I cannot select on it. > > > I am useing 6.5, got it from PG_VERSION file. > > You're probably running 6.5.1 or 6.5.2. You can easily check this > by starting psql and checking the first few lines. It will tell > you the exact versionnumber. 6.5.2 from digging around in /usr/ports marc > > > ps would useing text instead of varchar be a good thing to do? > > That is my understanding, but I'm not completely sure. > > Mathijs > > ************ >
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.
> 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.
> 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
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
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