Undeliverable Message

To: ISMTP(at)SFRA0046@Servers[<pgsql-sql-digest(at)hub(dot)org>]
Cc:
Subject: pgsql-sql-digest V1 #178

Message not delivered to recipients below. Press F1 for help with VNM
error codes.

VNM3043: DE_VOLDER Fabrice(at)ATR_EXPL_LYON1@SFR_DO_CNTR_EST

VNM3043 -- MAILBOX IS FULL

The message cannot be delivered because the
recipient's mailbox contains the maximum number of
messages, as set by the system administrator. The
recipient must delete some messages before any
other messages can be delivered.
The maximum message limit for a user's mailbox is
10,000. The default message limit is 1000 messages.
Administrators can set message limits using the
Mailbox Settings function available in the
Manage User menu (MUSER).

When a user's mailbox reaches the limit, the
user must delete some of the messages before
the mailbox can accept any more incoming messages.

---------------------- Original Message Follows ----------------------

pgsql-sql-digest Wednesday, March 24 1999 Volume 01 : Number 178

Index:

Re: [SQL] DEFAULT TEXT 'now'
Re: [SQL] DEFAULT TEXT 'now'
Re: [SQL] transaction-lock?
Re: [SQL] DEFAULT TEXT 'now'
Comparing Dates to Current Time
Comparing Dates to Current Time
Use of 'default TEXT now()'
RE: [SQL] Comparing Dates to Current Time
RE: [SQL] Use of 'default TEXT now()'
RE: [SQL] Comparing Dates to Current Time
RE: [SQL] Use of 'default TEXT now()'
Re: [SQL] Use of 'default TEXT now()'
RE: [SQL] Use of 'default TEXT now()'
types and subtypes
RE: [SQL] types and subtypes
Help with query

----------------------------------------------------------------------

Date: Wed, 24 Mar 1999 02:09:38 -0500 (EST)
From: Howie <caffeine(at)toodarkpark(dot)org>
Subject: Re: [SQL] DEFAULT TEXT 'now'

On Tue, 23 Mar 1999, Roberto Joao Lopes Garcia wrote:

> Hi
>
> I+m have a table like bellow:
>
> CREATE TABLE conex (
> id int4 PRIMARY KEY DEFAULT NEXTVAL('seq_conex'),
>
> inicio datetime DEFAULT TEXT 'now',
> ultimo datetime DEFAULT TEXT
> 'now',
> .
> .
> .
> )
>
> insert into conex (ip) values(+200.200.200.200+)
>
> Will result every time the same data and time for +inicio+ and +ultimo+!
>
> I want to insert the current date and time. Is that code correct and the
> result a bug???
> Is there an way to make the DEFAULT work???

try:

...
inicido DATETIME DEFAULT NOW(),
ultimo DATETIME DEFAULT NOW(),
...

- ---
Howie <caffeine(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
"You can change the world with a bullet in the right place"

------------------------------

Date: 24 Mar 1999 10:10:42 +0300
From: tolik(at)icomm(dot)ru (Anatoly K. Lasareff)
Subject: Re: [SQL] DEFAULT TEXT 'now'

>>>>> "RJLG"
RJLG> Hi
RJLG> I+m have a table like bellow:

RJLG> CREATE TABLE conex (
RJLG> id int4 PRIMARY KEY DEFAULT NEXTVAL('seq_conex'),

RJLG> inicio datetime DEFAULT TEXT 'now',
RJLG> ultimo datetime DEFAULT TEXT
RJLG> 'now',
RJLG> .
RJLG> .
RJLG> .
RJLG> )

RJLG> insert into conex (ip) values(+200.200.200.200+)

RJLG> Will result every time the same data and time for +inicio+ and +ultimo+!

You must type

inicio datetime DEFAULT TEXT now(),
^^^^^

This is the function which calls every time you insert new record. In
your case 'now' is the constant, which calculates once, when you
create the table.

- --
Anatoly K. Lasareff Email: tolik(at)icomm(dot)ru
Senior programmer

------------------------------

Date: Wed, 24 Mar 1999 14:42:30 +0200
From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
Subject: Re: [SQL] transaction-lock?

At 20:06 +0200 on 23/03/1999, Forgacs Tamas wrote:

>
> I am a novice pgsql user. I used Oracle, but it is unbelievable what
> bugs they have...
>
> So, is it possible, that if one transaction is pending on for eg. 'test'
> table in one psql session, then in another session this 'test' table is
> locked until the transaction is not finished on another????
>
> I have been tested a lot of ways, but the most easier problem is the
> next:
> - there is a table named 'test', with only 1 field.
> - opening a psql session on tty1, and on tty2 into the same database
>
> - on tty1 'begin' a transaction
> - on tty1 'insert' 1 row into the 'test' table
> - on tty2 'select' from the 'test' table
> > transaction not finished

Yes, that's the documented behaviour. Oracle has bugs, but Postgres will
have a better locking resolution only starting 6.5. At the moment it locks
on tables.

Herouth

- --
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

------------------------------

Date: Wed, 24 Mar 1999 10:26:55 -0600
From: "Ross J. Reedstrom" <reedstrm(at)rice(dot)edu>
Subject: Re: [SQL] DEFAULT TEXT 'now'

Anatoly K. Lasareff wrote:
>
> >>>>> "RJLG" >
>> <snipped problem with using default 'now'>

> <snipped description of using now() instead>

What version of postgresql are you all using? I tried RJLG's example
code with 6.4.2, and it worked - I got different times at each insert,
so the default 'now' was being calculated at insertion time, not table
creation time. Here's what it looks like:

test2
Table +----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| id | int4 not null default nextval (
| 4 |
| inicio | datetime default text 'now'
| 8 |
| ultimo | datetime default text 'now'
| 8 |
| ip | inet
| var |
+----------------------------------+----------------------------------+-------+
Index: conex_pkey
test2id|inicio |ultimo
| ip
- --+----------------------------+----------------------------+---------------
1|Tue Mar 23 13:40:24 1999 CST|Tue Mar 23 13:40:24 1999
CST|200.200.200.200
2|Tue Mar 23 13:40:28 1999 CST|Tue Mar 23 13:40:28 1999
CST|200.200.200.202
3|Tue Mar 23 13:40:30 1999 CST|Tue Mar 23 13:40:30 1999
CST|200.200.200.203
4|Tue Mar 23 13:41:06 1999 CST|Tue Mar 23 13:41:06 1999
CST|200.200.200.203
5|Tue Mar 23 13:41:21 1999 CST|Tue Mar 23 13:41:21 1999
CST|200.200.200.205
(5 rows)

test2
???

Ross

- --
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

------------------------------

Date: Wed, 24 Mar 1999 14:49:37 -0400
From: Jenny DiBartolomeo <jdibartolomeo(at)agency(dot)com>
Subject: Comparing Dates to Current Time

OK... this is probably a trivial one. In an Oracle query, I might do the
following select statement:

SELECT title

FROM articles

WHERE expires > SYSDATE

How would I do the same thing in Postgres if the column "expires" is of the
data type "DATE"? I've tried everything and read the man pages and can't
seem to get it working! Any help would be VERY APPRECIATED!!!

Thanks sooooo much for any help you can give me!!!! Please respond via
email to jdibartolomeo(at)agency(dot)com(dot)

- -Jenny

- ------------------------------
Jennifer DiBartolomeo
jdibartolomeo(at)agency(dot)com
- ------------------------------

------------------------------

Date: Wed, 24 Mar 1999 15:08:48 -0400
From: Jenny DiBartolomeo <jdibartolomeo(at)agency(dot)com>
Subject: Comparing Dates to Current Time

OK... this is probably a trivial one. In an Oracle query, I might do the
following select statement:

SELECT title

FROM articles

WHERE expires > SYSDATE

How would I do the same thing in Postgres if the column "expires" is of the
data type "DATE"? I've tried everything and read the man pages and can't
seem to get it working! Any help would be VERY APPRECIATED!!!

Thanks sooooo much for any help you can give me!!!! Please respond via
email to jdibartolomeo(at)agency(dot)com(dot)

- -Jenny

- ------------------------------
Jennifer DiBartolomeo
jdibartolomeo(at)agency(dot)com
- ------------------------------

------------------------------

Date: Wed, 24 Mar 1999 14:19:59 -0600 (CST)
From: "Ing. Roberto Andrade" <randrade(at)campus(dot)iztacala(dot)unam(dot)mx>
Subject: Use of 'default TEXT now()'

Hi:

I'm writing an application where I have to use a datetime field. In this
field we'll keep the date and time of the coments about some tasks.

I am not sure about the right use of 'default', should I use:

fecha_obs datetime default TEXT now()

or

fecha_obs datetime default now() ?

When I must use TEXT ?

I want to retrieve the fields in datetime order.

TIA

Roberto Andrade Fonseca
Universidad Nacional Aut<noma de M0xico

Tel0fonos: 623-1154, 623-1119
e-mail: randrade(at)campus(dot)iztacala(dot)unam(dot)mx

------------------------------

Date: Wed, 24 Mar 1999 15:12:19 -0600
From: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com>
Subject: RE: [SQL] Comparing Dates to Current Time

try:
SELECT title
FROM articles
WHERE expires > now()::datetime::date
-DEJ

> -----Original Message-----
> From: Jenny DiBartolomeo [mailto:jdibartolomeo(at)agency(dot)com]
> Sent: Wednesday, March 24, 1999 1:09 PM
> To: pgsql-sql(at)postgreSQL(dot)org
> Subject: [SQL] Comparing Dates to Current Time
>
>
> OK... this is probably a trivial one. In an Oracle query, I
> might do the
> following select statement:
>
> SELECT title
>
> FROM articles
>
> WHERE expires > SYSDATE
>
> How would I do the same thing in Postgres if the column
> "expires" is of the
> data type "DATE"? I've tried everything and read the man
> pages and can't
> seem to get it working! Any help would be VERY APPRECIATED!!!
>
> Thanks sooooo much for any help you can give me!!!! Please
> respond via
> email to jdibartolomeo(at)agency(dot)com(dot)
>
> -Jenny
>
> ------------------------------
> Jennifer DiBartolomeo
> jdibartolomeo(at)agency(dot)com
> ------------------------------
>

------------------------------

Date: Wed, 24 Mar 1999 15:14:55 -0600
From: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com>
Subject: RE: [SQL] Use of 'default TEXT now()'

Use:
fecha_obs datetime default datetime(now())
The use of TEXT was for:
fecha_obs datetime default text 'now'
which will also do what you want (I believe, haven't tried it).
-DEJ

> -----Original Message-----
> From: Ing. Roberto Andrade [mailto:randrade(at)campus(dot)iztacala(dot)unam(dot)mx]
> Sent: Wednesday, March 24, 1999 2:20 PM
> To: sql
> Subject: [SQL] Use of 'default TEXT now()'
>
>
> Hi:
>
> I'm writing an application where I have to use a datetime
> field. In this
> field we'll keep the date and time of the coments about some tasks.
>
> I am not sure about the right use of 'default', should I use:
>
> fecha_obs datetime default TEXT now()
>
> or
>
> fecha_obs datetime default now() ?
>
> When I must use TEXT ?
>
> I want to retrieve the fields in datetime order.
>
> TIA
>
> Roberto Andrade Fonseca
> Universidad Nacional Aut<noma de M0xico
>
> Tel0fonos: 623-1154, 623-1119
> e-mail: randrade(at)campus(dot)iztacala(dot)unam(dot)mx
>
>

------------------------------

Date: Wed, 24 Mar 1999 15:18:16 -0600
From: Michael Davis <michael(dot)davis(at)prevuenet(dot)com>
Subject: RE: [SQL] Comparing Dates to Current Time

Try select title from articles where expires > now()::date;

-----Original Message-----
From: Jenny DiBartolomeo [SMTP:jdibartolomeo(at)agency(dot)com]
Sent: Wednesday, March 24, 1999 12:09 PM
To: pgsql-sql(at)postgreSQL(dot)org
Subject: [SQL] Comparing Dates to Current Time

OK... this is probably a trivial one. In an Oracle query, I might
do the
following select statement:

SELECT title

FROM articles

WHERE expires > SYSDATE

How would I do the same thing in Postgres if the column "expires" is
of the
data type "DATE"? I've tried everything and read the man pages and
can't
seem to get it working! Any help would be VERY APPRECIATED!!!

Thanks sooooo much for any help you can give me!!!! Please respond
via
email to jdibartolomeo(at)agency(dot)com(dot)

-Jenny

------------------------------
Jennifer DiBartolomeo
jdibartolomeo(at)agency(dot)com
------------------------------

------------------------------

Date: Wed, 24 Mar 1999 15:31:37 -0600 (CST)
From: "Ing. Roberto Andrade" <randrade(at)campus(dot)iztacala(dot)unam(dot)mx>
Subject: RE: [SQL] Use of 'default TEXT now()'

Hi Jackson:

On Wed, 24 Mar 1999, Jackson, DeJuan wrote:

> Use:
> fecha_obs datetime default datetime(now())
> The use of TEXT was for:
> fecha_obs datetime default text 'now'
> which will also do what you want (I believe, haven't tried it).
> -DEJ

Where can I get some help about this subject?

TIA,

Roberto Andrade Fonseca

------------------------------

Date: Wed, 24 Mar 1999 05:55:37 -0800 (PST)
From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
Subject: Re: [SQL] Use of 'default TEXT now()'

On Wed, 24 Mar 1999, Ing. Roberto Andrade wrote:

> I'm writing an application where I have to use a datetime field. In this
> field we'll keep the date and time of the coments about some tasks.
>
> I am not sure about the right use of 'default', should I use:
>
> fecha_obs datetime default TEXT now()
>
> or
>
> fecha_obs datetime default now() ?
>
> When I must use TEXT ?

Unless I've missed something important, you want to use the latter form.
Text is a field data type for a variable length field without limit. Like
the memo field in dBASE and Paradox.

The variable length field with a limit to the number of characters it can
contain is varchar(n). The data type, char(n), holds a fixed length string,
padded with blanks, to the maximum of 'n'.

These are most of the character data types. What you want is one of the
date/time data types.

Hope this helps,

Rich

Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc.
2404 SW 22nd Street
Troutdale, OR 97060-1247 U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax)
rshepard(at)appl-ecosys(dot)com

------------------------------

Date: Wed, 24 Mar 1999 15:59:52 -0600
From: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com>
Subject: RE: [SQL] Use of 'default TEXT now()'

You might take a look at the tutorial on the PostgreSQL web-site
(http://www.postgreSQL.ORG/docs/tutorial/index.html)
There are other references under the "Getting Help/Documentation" section in
the web-site as well

> -----Original Message-----
> From: Ing. Roberto Andrade [mailto:randrade(at)campus(dot)iztacala(dot)unam(dot)mx]
> Sent: Wednesday, March 24, 1999 3:32 PM
> To: sql
> Subject: RE: [SQL] Use of 'default TEXT now()'
>
>
> Hi Jackson:
>
> On Wed, 24 Mar 1999, Jackson, DeJuan wrote:
>
> > Use:
> > fecha_obs datetime default datetime(now())
> > The use of TEXT was for:
> > fecha_obs datetime default text 'now'
> > which will also do what you want (I believe, haven't tried it).
> > -DEJ
>
> Where can I get some help about this subject?
>
> TIA,
>
> Roberto Andrade Fonseca
>

------------------------------

Date: Wed, 24 Mar 1999 16:59:23 -0500 (GMT+5)
From: Ingrith Andrea Correa Vargas <i-correa(at)uniandes(dot)edu(dot)co>
Subject: types and subtypes

hello all!

I want to simulate objects with Postgres and I would like to know if it's
possible to create a type (like address: t_address) and then to create
another type (like customer) with an attribute of type t_address.

And I have a second question:
Can I make a "reference" to an object of an existing type?!

These are features provided by the SQL3 standar, I want to know if it's
possible to implement it with Postgres.

Can somebody help to me?!

Thank you in advance,

//Ingrith Correa :)

------------------------------

Date: Wed, 24 Mar 1999 16:27:02 -0600
From: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com>
Subject: RE: [SQL] types and subtypes

> hello all!
>
> I want to simulate objects with Postgres and I would like to
> know if it's
> possible to create a type (like address: t_address) and then to create
> another type (like customer) with an attribute of type t_address.
Yes, you can.

> And I have a second question:
> Can I make a "reference" to an object of an existing type?!
If what you mean here is a c-style typedef no not really but you can use
inheritance.
If what you mean is another name by which to refer to a table or row then
the answer depend complete upon the context. In rules tables are already
referenced as new, old, current. In pl/pgsql you can create references
until your hearts content.

> These are features provided by the SQL3 standar, I want to
> know if it's
> possible to implement it with Postgres.
PostgreSQL isn't fully SQL3 compliant, but it's getting there.

> Can somebody help to me?!
That depends upon your problem, and you must remember that we are not
psychiatrist, plastic-surgeons, or magicians but we'll do our best.

> Thank you in advance,
> //Ingrith Correa :)
You're welcome in behind,
-DEJ

------------------------------

Date: Thu, 25 Mar 1999 06:13:06 +0300 (MSK)
From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
Subject: Help with query

Hi,

I have query
select source,max(up_date),count(*) from flats group by source
which gets me
source | max|count
- --------------------------+----------+-----

where source is a company name, max(up_date) - the last update,
count - total number of items for giver company

but I need

source | max| count_updates
- --------------------------+----------+-----

where count_updates is a number of *last updated* items for given company ?

How I can do this with SQL ?

Best regards,

Oleg

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

------------------------------

End of pgsql-sql-digest V1 #178
*******************************