timestamps

"s" == strawman <strawman@plexi.com> writes:

s> I'm trying to create a column that defaults to the current time and date. I
s> tried the SQLServer like syntax below but potgresql choked:

s> CREATE TABLE clicks (
s> avo_userid varchar (10) NOT NULL ,
s> link_id int NOT NULL ,
s> the_time timestamp NOT NULL CONSTRAINT df_now DEFAULT (timestamp('now'))
s> );
s> NOTICE: there is more than one function named "timestamp"
s> NOTICE: that satisfies the given argument types. you will have to
s> NOTICE: retype your query using explicit typecasts.
s> ERROR: function timestamp(unknown) does not exist

s> Is "timestamp" not both a type and a function? How can I look it
up?

There are data type named 'timestamp' and more than one function for
converting 'timestamp' -> other data type. B.e.

timestamp(data)
timestamp(datetime)
... etc

All of them have the same name - 'timestamp', but different type of
argument(s).

In your case parser dont know - what type of argument you use. You
must 'retype your query using explicit typecasts':

the_time timestamp NOT NULL CONSTRAINT df_now DEFAULT (timestamp('now'::datetime))

s> And one more question: How does one construct a foreign key relationship in
s> postgres?

In 6.4 there are no really 'primary ney - foreign key' constraint. If
you wont have this feature you must use 'refint' from postgres
contribs.

--
Anatoly K. Lasareff Email: tolik@icomm.ru
Senior programmer

strawman@plexi.com ha scritto:

I'm trying to create a column that defaults to the current time and date. I
tried the SQLServer like syntax below but potgresql choked:

CREATE TABLE clicks (
avo_userid varchar (10) NOT NULL ,
link_id int NOT NULL ,
the_time timestamp NOT NULL CONSTRAINT df_now DEFAULT (timestamp('now'))
);
NOTICE: there is more than one function named "timestamp"
NOTICE: that satisfies the given argument types. you will have to
NOTICE: retype your query using explicit typecasts.
ERROR: function timestamp(unknown) does not exist

Is "timestamp" not both a type and a function? How can I look it up?

You can create table like:

CREATE TABLE clicks (
userid varchar(10) not null,
links int not null,
df_time timestamp constraint df_now DEFAULT current_timestamp
);

but remember in such case you can insert a value to df_time column different
than current timestamp.
If you want to avoid this you have to create a trigger (see attached example).

--

And one more question: How does one construct a foreign key relationship in
postgres?

Thanks for any help.

Foreign key is not yet implemented but you may use triggers (See attached
example).

NB: You need v6.4.? to use examples.

--
- Jose' -

"No other success in life can compensate for failure in the home" (David O.
McKay)