[SQL] Inserting and incrementing with MAX aggregate

[Keith Perry]

I was wondering if this statement was still supported in 7.0.x versions
of pgSQL.  I upgraded from a 6.x version where say:

insert into events (eid,name) values (max(eid)+1,'server down');

works but not in the 7.x variants.

I know that I could use the serial type for that column but in the
interest not having to rewrite the code (or dump, drop and recreate the
tables/data), I wanted  to know if there was a more stand way to
incrementing a field automatically that would be fairly portable.  Any
help would be appreciated.

Keith Perry
VCSN Inc.
[EMAIL PROTECTED]
http://vcsn.com

[SQL] Re: Inserting and incrementing with MAX aggregate

[Keith Perry]

Ahhh, thank you that worked.  I don't know why but for some reason I didn't
think I could do a subquery in an insert *laff*- 'learn something new
everyday :)

Keith-

Ian Harding wrote:

> Could you not:
>
> insert into events (eid,name) values ((SELECT max(eid) FROM
> EVENTS)+1,'server down');
>
> It looks like it would work.  It's just a subquery...
>
> Ian
>
> Keith Perry wrote:
>
> > I was wondering if this statement was still supported in 7.0.x versions
> > of pgSQL.  I upgraded from a 6.x version where say:
> >
> > insert into events (eid,name) values (max(eid)+1,'server down');
> >
> > works but not in the 7.x variants.
> >
> > I know that I could use the serial type for that column but in the
> > interest not having to rewrite the code (or dump, drop and recreate the
> > tables/data), I wanted  to know if there was a more stand way to
> > incrementing a field automatically that would be fairly portable.  Any
> > help would be appreciated.
> >
> > Keith Perry
> > VCSN Inc.
> > [EMAIL PROTECTED]
> > http://vcsn.com

[SQL] Problem with Day of Week

[Keith Perry]

Greetings,

I notice some talk about date problems and interestingly enough planning
out an application in which I will need to be able to manipulate dates.
I notice however that there seems to be a discrepancy with the day or
week in 7.0.3

---

pmhcc=# select date_part('dow','now'::timestamp);
 date_part
---
 1
(1 row)

pmhcc=# select to_char('now'::timestamp,'D');
 to_char
-
 2
(1 row)

pmhcc=# select version();
   version
-
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)

pmhcc=# select now();
  now

 2001-01-29 12:57:46-05
(1 row)

---

Now as far as I know, Sunday is supposed to 1, so I would think that
date_part is doing something funky unless I am mis-understanding
something.  I was hoping that someone might be able to shead some light
on this.  Thanks.

Keith C. Perry
VCSN, Inc.
http://vcsn.com