Default Timestamp

[Ivy]

Hi,

I used the export_objects function and found a table with a column defaulted to a timestamptz(6) like the following.

Create table test (
updated_on timestamp DEFAULT '2013-03-25 15:04:18.070037-04'::timestamptz(6)
);

What does it mean? How is it different from using the data type timestampz such as:

Create table test (
updated_on timestamptz DEFAULT current_timestamp
);

[id10t]

HI!

>> How is it different... ?
Same as function differs from constant value.

CURRENT_TIMESTAMP
Returns a value of type TIMESTAMP WITH TIME ZONE representing the start of the current transaction.

'2013-03-25 15:04:18.070037-04'::timestamptz(6)

- and this is a constant that consists of a date and a time with a time zone and 6 fractional digits retained in the seconds field (-04 its a time zone offset).

Code: Select all

sampler=> create table peng (dummy int, ts timestamptz default current_timestamp);
CREATE TABLE
sampler=> copy peng(dummy) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> \.
sampler=> copy peng(dummy) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 3
>> 4
>> \.

Code: Select all

sampler=> select * from peng;
 dummy |              ts               
-------+-------------------------------
     1 | 2013-05-24 20:33:44.26812+03
     2 | 2013-05-24 20:33:44.26812+03
     3 | 2013-05-24 20:34:02.390964+03
     4 | 2013-05-24 20:34:02.390964+03
(4 rows)

Or to end a current transaction with commit:

Code: Select all

sampler=> insert into peng(dummy) values (5);
 OUTPUT 
--------
      1
(1 row)

sampler=> insert into peng(dummy) values (6);
 OUTPUT 
--------
      1
(1 row)

sampler=> commit;
COMMIT
sampler=> insert into peng(dummy) values (7);
 OUTPUT 
--------
      1
(1 row)

sampler=> select * from peng;
 dummy |              ts               
-------+-------------------------------
     1 | 2013-05-24 20:33:44.26812+03
     2 | 2013-05-24 20:33:44.26812+03
     3 | 2013-05-24 20:34:02.390964+03
     4 | 2013-05-24 20:34:02.390964+03
     5 | 2013-05-24 20:34:13.056333+03
     6 | 2013-05-24 20:34:13.056333+03
     7 | 2013-05-24 20:36:26.259107+03
(7 rows)

[Ivy]

Thanks for the reply. I don't think I asked the question correctly. My question should have been:

Why would we use a constant date in the default when it is supposed to show the current datetime. Since the table was created by a senior Vertica DBA, I was wondering if there was a good reason behind it.

[id10t]

Hi!

Who knows? Ask your senior DBA. Perhaps he has chosen it as default constant value.

[Ivy]

Hi sKwa, the Senior DBA is no longer with the company. So I should just use my own best judgement moving forward. Thank you again for your assistance!