Postgres Pro
Downloads
Home   >   mailing lists

Thread: timestamp precision

timestamp precision

From
A Gilmore
Date:
Hello,

Im using the default precision for my timestamps, 6.  Is it safe to
declare this column unique?  Because you can define the level of
precision I assume you could theoretically have a duplicate.  Or does a
precision of 6 give you an accuracy at the point where pgsql could never
do 2 transactions in the given timestamp time span?

I likely could have written this better, I hope its understandable.

Thank you in advance.

A Gilmore

Re: timestamp precision

From
Michael Glaesemann
Date:
On Sep 13, 2004, at 4:19 PM, A Gilmore wrote:

> Hello,
>
> Im using the default precision for my timestamps, 6.  Is it safe to
> declare this column unique?

The *only* way to ensure uniqueness in a column is to explicitly
declare the column UNIQUE or PRIMARY (which implies UNIQUE NOT NULL
iirc). So if you declare it unique, of course it's safe.

If you are assuming it's unique because of the high precision, well,
you might get lucky, and you might not. (Some might even argue that
it's for all intents and purposes unique). However, if you want to
guarantee uniqueness, declare it UNIQUE.

Michael Glaesemann
grzm myrealbox com

Re: create table error

From
Kumar S
Date:
Dear group,
 I am getting a constant error that says Parse error
at "(".

I am using 7.4 and I never had this kind of problem in
previous versions.

lines of my code:

create table contacts
(
     con_id    serial   ,
     exp_id    serial   REFERENCES experiment,
     con_lname  varchar(32) ,
     con_fname  varchar(32) ,
     con_addressline  varchar (64),
     con_zip    varchar(16)  ,
);
Is there some problem with these sql statements.
please help.

Thank you.

psk






__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail

Re: create table error

From
Devrim GUNDUZ
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Mon, 13 Sep 2004, Kumar S wrote:

> I am getting a constant error that says Parse error
> at "(".
>
> I am using 7.4 and I never had this kind of problem in
> previous versions.

The lines below would never ever work in any PostgreSQL server...

> lines of my code:
>
> create table contacts
> (
>     con_id    serial   ,
>     exp_id    serial   REFERENCES experiment,
>     con_lname  varchar(32) ,
>     con_fname  varchar(32) ,
>     con_addressline  varchar (64),
>     con_zip    varchar(16)  ,
> );

      con_zip    varchar(16)  ,

would be

      con_zip    varchar(16)

You cannot use a comma after the last column definition.

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org                devrim.gunduz~linux.org.tr
             http://www.tdmsoft.com
             http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBRaQLtl86P3SPfQ4RAnPnAJ41BEsJvrz+CJGKckByVHag0pJ9xQCgjZIK
w0A+rtK/XRljlbjOV+Dxfsc=
=SJ6k
-----END PGP SIGNATURE-----

Re: create table error

From
Michael Glaesemann
Date:
On Sep 13, 2004, at 10:35 PM, Kumar S wrote:
> create table contacts
> (
>      con_id    serial   ,
>      exp_id    serial   REFERENCES experiment,
>      con_lname  varchar(32) ,
>      con_fname  varchar(32) ,
>      con_addressline  varchar (64),
>      con_zip    varchar(16)  ,
> );
> Is there some problem with these sql statements.

Yes. You have an extra comma following the line beginning con_zip. The
last line of the table definition should not be followed by a comma.

I suspect you're also going to have trouble with the exp_id serial
references experiment line. You probably don't want a default on a
column that needs to match a value in another table.

Hope that helps.

Also, please do not start a new thread by replying to a different
message. Create a new message instead.

Michael Glaesemann
grzm myrealbox com

Re: timestamp precision

From
Tom Lane
Date:
Michael Glaesemann <grzm@myrealbox.com> writes:
> On Sep 13, 2004, at 4:19 PM, A Gilmore wrote:
>> Im using the default precision for my timestamps, 6.  Is it safe to
>> declare this column unique?

> If you are assuming it's unique because of the high precision, well,
> you might get lucky, and you might not. (Some might even argue that
> it's for all intents and purposes unique).

I think what he's wondering is whether every two transactions will get
distinguishable values of now(), so that putting a UNIQUE constraint on
timestamps inserted by distinct transactions could never fail.

I think this is an unsafe assumption, because:

1.  The amount of precision that is actually in the now() value is
unspecified, and varies depending on the hardware and OS.  On older
machines it's quite possible that now() only advances once per clock
tick interrupt (60 or 100 times per second).

2.  Even if the now() quantum is less than the minimum time to complete
a transaction, what if two clients launch transactions concurrently?

            regards, tom lane

Re: timestamp precision

From
A Gilmore
Date:
Tom Lane wrote:
> Michael Glaesemann <grzm@myrealbox.com> writes:
>
>>On Sep 13, 2004, at 4:19 PM, A Gilmore wrote:
>>
>>>Im using the default precision for my timestamps, 6.  Is it safe to
>>>declare this column unique?
>
>
>>If you are assuming it's unique because of the high precision, well,
>>you might get lucky, and you might not. (Some might even argue that
>>it's for all intents and purposes unique).
>
>
> I think what he's wondering is whether every two transactions will get
> distinguishable values of now(), so that putting a UNIQUE constraint on
> timestamps inserted by distinct transactions could never fail.
>
> I think this is an unsafe assumption, because:
>

Yeah, thats what I was meaning.  I didn't think it would work (by work,
I mean no chance of failure due to duplicate) but was hoping to be suprised.

Thank you for the insight.

A Gilmore