Thread: difference between current_timestamp and now() in quotes
test2=# create table dupa(a timestamp, b serial);
NOTICE: CREATE TABLE will create implicit sequence "dupa_b_seq" for
serial column "dupa.b"
CREATE TABLE
test2=# insert into dupa(a) select current_timestamp from
generate_series(1,100);
INSERT 0 100
test2=# insert into dupa(a) select 'current_timestamp' from
generate_series(1,100);
ERROR: date/time value "current" is no longer supported
LINE 1: insert into dupa(a) select 'current_timestamp' from generate...
^
test2=# insert into dupa(a) select 'now()' from generate_series(1,100);
INSERT 0 100
Any ideas why the difference ?
--
GJ
On 22/01/2009 13:52, Grzegorz Jaśkiewicz wrote: > test2=# insert into dupa(a) select 'current_timestamp' from > generate_series(1,100); > ERROR: date/time value "current" is no longer supported This doesn't answer your question, but you use current_timestamp without the quotes, thus - insert into dupa(a) select current_timestamp ... Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Grzegorz Jaśkiewicz escribió: > test2=# insert into dupa(a) select 'current_timestamp' from > generate_series(1,100); > ERROR: date/time value "current" is no longer supported > LINE 1: insert into dupa(a) select 'current_timestamp' from generate... > ^ > test2=# insert into dupa(a) select 'now()' from generate_series(1,100); > INSERT 0 100 > > > Any ideas why the difference ? The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't recognize it in quotes. I don't know why 'now()' works; I think it is a literal of type unknown. I guess it's expanded to the actual value in later parsing stages. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thursday 22 January 2009 8:16:46 am Alvaro Herrera wrote: > Grzegorz Jaśkiewicz escribió: > > test2=# insert into dupa(a) select 'current_timestamp' from > > generate_series(1,100); > > ERROR: date/time value "current" is no longer supported > > LINE 1: insert into dupa(a) select 'current_timestamp' from generate... > > ^ > > test2=# insert into dupa(a) select 'now()' from generate_series(1,100); > > INSERT 0 100 > > > > > > Any ideas why the difference ? > > The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't > recognize it in quotes. I don't know why 'now()' works; I think it is a > literal of type unknown. I guess it's expanded to the actual value in > later parsing stages. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support At least on 8.2 'now()' does not work either at least not in the way I think you want. I get: test=# SELECT 'now()'; ?column? ---------- now() (1 row) -- Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote:
select now();
TryOn Thursday 22 January 2009 8:16:46 am Alvaro Herrera wrote:Grzegorz Jaśkiewicz escribió:test2=# insert into dupa(a) select 'current_timestamp' from generate_series(1,100); ERROR: date/time value "current" is no longer supported LINE 1: insert into dupa(a) select 'current_timestamp' from generate... ^ test2=# insert into dupa(a) select 'now()' from generate_series(1,100); INSERT 0 100 Any ideas why the difference ?The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't recognize it in quotes. I don't know why 'now()' works; I think it is a literal of type unknown. I guess it's expanded to the actual value in later parsing stages. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 supportAt least on 8.2 'now()' does not work either at least not in the way I think you want. I get: test=# SELECT 'now()';?column? ----------now() (1 row)
select now();
On Thursday 22 January 2009 9:07:37 am Jason Long wrote: > Adrian Klaver wrote: > > On Thursday 22 January 2009 8:16:46 am Alvaro Herrera wrote: > >> Grzegorz Jaśkiewicz escribió: > >>> test2=# insert into dupa(a) select 'current_timestamp' from > >>> generate_series(1,100); > >>> ERROR: date/time value "current" is no longer supported > >>> LINE 1: insert into dupa(a) select 'current_timestamp' from generate... > >>> ^ > >>> test2=# insert into dupa(a) select 'now()' from generate_series(1,100); > >>> INSERT 0 100 > >>> > >>> > >>> Any ideas why the difference ? > >> > >> The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't > >> recognize it in quotes. I don't know why 'now()' works; I think it is a > >> literal of type unknown. I guess it's expanded to the actual value in > >> later parsing stages. > >> > >> -- > >> Alvaro Herrera > >> http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom > >> Development, 24x7 support > > > > At least on 8.2 'now()' does not work either at least not in the way I > > think you want. I get: > > > > test=# SELECT 'now()'; > > ?column? > > ---------- > > now() > > (1 row) > > Try > select now(); Yes that is how I use it :) My example referred to the original message that said select 'now()' worked but 'current_timestamp' did not. -- Adrian Klaver aklaver@comcast.net
well, I am asking that - for pure curiosity reason. I got a function in C, that automagically puts all data in quotes, and that's how I came across that strange difference between current_time and now(). Funny enough, pg will translate first one to latter in domains, for instance.
2009/1/22, Adrian Klaver <aklaver@comcast.net>: > On Thursday 22 January 2009 8:16:46 am Alvaro Herrera wrote: >> Grzegorz Jaśkiewicz escribió: >> > test2=# insert into dupa(a) select 'current_timestamp' from >> > generate_series(1,100); >> > ERROR: date/time value "current" is no longer supported >> > LINE 1: insert into dupa(a) select 'current_timestamp' from generate... >> > ^ >> > test2=# insert into dupa(a) select 'now()' from generate_series(1,100); >> > INSERT 0 100 >> > >> > >> > Any ideas why the difference ? >> >> The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't >> recognize it in quotes. I don't know why 'now()' works; I think it is a >> literal of type unknown. I guess it's expanded to the actual value in >> later parsing stages. >> >> -- >> Alvaro Herrera >> http://www.CommandPrompt.com/ >> PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > At least on 8.2 'now()' does not work either at least not in the way I > think > you want. I get: > > test=# SELECT 'now()'; > ?column? > ---------- > now() > (1 row) > Table 8-13. Special Date/Time Inputs http://www.postgresql.org/docs/current/interactive/datatype-datetime.html bdteste=# SELECT 'now'::date, 'yesterday'::date, 'today'::date, 'tomorrow'::date; date | date | date | date ------------+------------+------------+------------ 2009-01-22 | 2009-01-21 | 2009-01-22 | 2009-01-23 (1 registro) Osvaldo
On Thursday 22 January 2009 18:57:16 Osvaldo Kussama wrote: > 2009/1/22, Adrian Klaver <aklaver@comcast.net>: > > On Thursday 22 January 2009 8:16:46 am Alvaro Herrera wrote: > >> Grzegorz Jaśkiewicz escribió: > >> > test2=# insert into dupa(a) select 'current_timestamp' from > >> > generate_series(1,100); > >> > ERROR: date/time value "current" is no longer supported > >> > LINE 1: insert into dupa(a) select 'current_timestamp' from > >> > generate... ^ > >> > test2=# insert into dupa(a) select 'now()' from > >> > generate_series(1,100); INSERT 0 100 > >> > > >> > > >> > Any ideas why the difference ? > >> > >> The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't > >> recognize it in quotes. I don't know why 'now()' works; I think it is a > >> literal of type unknown. I guess it's expanded to the actual value in > >> later parsing stages. > >> > >> -- > >> Alvaro Herrera > >> http://www.CommandPrompt.com/ > >> PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > > > At least on 8.2 'now()' does not work either at least not in the way I > > think > > you want. I get: > > > > test=# SELECT 'now()'; > > ?column? > > ---------- > > now() > > (1 row) > > Table 8-13. Special Date/Time Inputs > http://www.postgresql.org/docs/current/interactive/datatype-datetime.html > > bdteste=# SELECT 'now'::date, 'yesterday'::date, 'today'::date, > 'tomorrow'::date; > date | date | date | date > ------------+------------+------------+------------ > 2009-01-22 | 2009-01-21 | 2009-01-22 | 2009-01-23 > (1 registro) > > Osvaldo 'now' != 'now()' And also the cast matters, IMHO. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
Adrian Klaver escribió:
> At least on 8.2 'now()' does not work either at least not in the way I think
> you want. I get:
>
> test=# SELECT 'now()';
> ?column?
> ----------
> now()
> (1 row)
alvherre=# select 'now()'::unknown::timestamptz;
timestamptz
-------------------------------
2009-01-22 15:01:46.352979-03
(1 fila)
It even works if you use "text" instead of unknown.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thursday 22 January 2009 10:04:05 am Alvaro Herrera wrote: > Adrian Klaver escribió: > > At least on 8.2 'now()' does not work either at least not in the way I > > think you want. I get: > > > > test=# SELECT 'now()'; > > ?column? > > ---------- > > now() > > (1 row) > > alvherre=# select 'now()'::unknown::timestamptz; > timestamptz > ------------------------------- > 2009-01-22 15:01:46.352979-03 > (1 fila) > > > It even works if you use "text" instead of unknown. I see my problem I was just doing a select. When I did an insert into a timestamp field than the 'now()' form worked. Learn something new everyday :) -- Adrian Klaver aklaver@comcast.net
On 2009-01-22, Adrian Klaver <aklaver@comcast.net> wrote: > On Thursday 22 January 2009 8:16:46 am Alvaro Herrera wrote: >> Grzegorz Jaśkiewicz escribió: >> > test2=# insert into dupa(a) select 'current_timestamp' from >> > generate_series(1,100); >> > ERROR: date/time value "current" is no longer supported >> > LINE 1: insert into dupa(a) select 'current_timestamp' from generate... >> > ^ >> > test2=# insert into dupa(a) select 'now()' from generate_series(1,100); >> > INSERT 0 100 >> > >> > >> > Any ideas why the difference ? >> >> The parser handles CURRENT_TIMESTAMP (and others) specially, and doesn't >> recognize it in quotes. I don't know why 'now()' works; I think it is a >> literal of type unknown. I guess it's expanded to the actual value in >> later parsing stages. >> >> -- >> Alvaro Herrera http://www.CommandPrompt.com/ >> PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > At least on 8.2 'now()' does not work either at least not in the way I think > you want. I get: > > test=# SELECT 'now()'; > ?column? > ---------- > now() > (1 row) 'now' is a string which can be translated to timestamp now() is a function that returns the current timestamp current_timestamp is a constant that does the same
"current_timestamp" is the reserved keyword of postgreSQL. When you executes it within single quotation mark it treated as string & that is the only reason it thrown error.
"Now()" is an in-built function you can use it with/without single quotation mark.
For more information refere the below link.
--
Thanks & Regards
Dhaval Jaiswal
EnterpriseDB
From: Grzegorz Jaśkiewicz <gryzman@gmail.com>
To: GENERAL <pgsql-general@postgresql.org>
Sent: Thursday, January 22, 2009 7:22:21 PM
Subject: [GENERAL] difference between current_timestamp and now() in quotes
test2=# create table dupa(a timestamp, b serial);
NOTICE: CREATE TABLE will create implicit sequence "dupa_b_seq" for
serial column "dupa.b"
CREATE TABLE
test2=# insert into dupa(a) select current_timestamp from
generate_series(1,100);
INSERT 0 100
test2=# insert into dupa(a) select 'current_timestamp' from
generate_series(1,100);
ERROR: date/time value "current" is no longer supported
LINE 1: insert into dupa(a) select 'current_timestamp' from generate...
^
test2=# insert into dupa(a) select 'now()' from generate_series(1,100);
INSERT 0 100
Any ideas why the difference ?
--
GJ
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general