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
);
Default Timestamp
Moderator: NorbertKrupa
Re: Default Timestamp
HI!
>> How is it different... ?
Same as function differs from constant value.
Or to end a current transaction with commit:
>> 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.
- 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).'2013-03-25 15:04:18.070037-04'::timestamptz(6)
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)
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)
Re: Default Timestamp
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.
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.
Re: Default Timestamp
Hi!
Who knows? Ask your senior DBA. Perhaps he has chosen it as default constant value.
Who knows? Ask your senior DBA. Perhaps he has chosen it as default constant value.
Re: Default Timestamp
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!