Postgres Pro
Downloads
Home   >   mailing lists

Thread: CURRENTE_DATE

CURRENTE_DATE

From
"Rossana Ocampos"
Date:

Hello ,

I am new with PostgreSQL and I have a bug. I have created a function that has an input variable of type date , in case it does not receive value , it has to assume by default the current date.

I have defined it as follows variable  DATE DEFAULT CURRENT_DATE, but I get the following error.

 

El error

ERROR: invalid input syntax for type date: “CURRENT_DATE” LINE 1: ...extsupplydate ('1085018'::bigint, '5278'::bigint, 'CURRENT_D... ^ ERROR: invalid input syntax for type date: “CURRENT_DATE” SQL status: 22007 Characters: 78

 

Please could you help me, thank you very much.

Rossana

Re: CURRENTE_DATE

From
Rich Shepard
Date:
On Tue, 22 Oct 2024, Rossana Ocampos wrote:

> I am new with PostgreSQL and I have a bug. I have created a function that
> has an input variable of type date , in case it does not receive value ,
> it has to assume by default the current date.

> ERROR: invalid input syntax for type date: "CURRENT_DATE" LINE 1:
> ...extsupplydate ('1085018'::bigint, '5278'::bigint, 'CURRENT_D... ^ ERROR:
> invalid input syntax for type date: "CURRENT_DATE" SQL status: 22007
> Characters: 78

Rossana,

I have several tables with default values, including dates. Change your
input's values to `default' (without the quotes.)

Regards,

Rich

Re: CURRENTE_DATE

From
Rich Shepard
Date:
On Tue, 22 Oct 2024, Rossana Ocampos wrote:

> ERROR: invalid input syntax for type date: "CURRENT_DATE" LINE 1:
> ...extsupplydate ('1085018'::bigint, '5278'::bigint, 'CURRENT_D... ^ ERROR:
> invalid input syntax for type date: "CURRENT_DATE" SQL status: 22007
> Characters: 78

Rossana,

Also, I suggest you put the column names (not their data types) separate
from the values. Here's an example of a script I use (Slackware64 linux):

\d enforcement
                    Table "public.enforcement"
      Column     |  Type   | Collation | Nullable |   Default 
----------------+---------+-----------+----------+--------------
  company_nbr    | integer |           |          |
  action_date    | date    |           | not null | CURRENT_DATE
  penalty_amount | real    |           |          |
  reason         | text    |           |          |
  comment        | text    |           |          | 
Foreign-key constraints:
     "enforcement_company_nbr_fkey" FOREIGN KEY (company_nbr) REFERENCES companies(company_nbr) ON UPDATE CASCADE ON
DELETERESTRICT
 

insert into enforcement (company_nbr, action_date, penalty_amount, reason, comment) values (123, '2024-10-23', default,
'Dida wrong thing.', null);
 

HTH,

Rich

Re: CURRENTE_DATE

From
Ray O'Donnell
Date:
On 22/10/2024 12:31, Rossana Ocampos wrote:
@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-language:EN-US;}a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:purple; text-decoration:underline;}span.E-MailFormatvorlage17 {mso-style-type:personal-compose; font-family:"Calibri","sans-serif"; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri","sans-serif"; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}

Hello ,

I am new with PostgreSQL and I have a bug. I have created a function that has an input variable of type date , in case it does not receive value , it has to assume by default the current date.

I have defined it as follows variable  DATE DEFAULT CURRENT_DATE, but I get the following error.

 

El error

ERROR: invalid input syntax for type date: “CURRENT_DATE” LINE 1: ...extsupplydate ('1085018'::bigint, '5278'::bigint, 'CURRENT_D... ^ ERROR: invalid input syntax for type date: “CURRENT_DATE” SQL status: 22007 Characters: 78


I think you just need to leave off the quotes, as current_date is a function:

  insert into .... values ( ... , current_date, ....);

Also, you don't need to quote the bigint values.


HTH,

Ray.


 

Please could you help me, thank you very much.

Rossana 



-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: CURRENTE_DATE

From
Muhammad Ikram
Date:
Hi,

I am just thinking, when DEFAULT CURRENT_DATE is being used in table definition then why the function is again being used in INSERT statement why not use
default. Here is sample


edb=# create table date_test (id int, hiredate date default current_date);
CREATE TABLE
edb=# insert into date_test values (1, default);
INSERT 0 1
edb=# select * from date_test;
 id |      hiredate
----+--------------------
  1 | 24-OCT-24 00:00:00
(1 row)

Regards,
Ikram


On Wed, Oct 23, 2024 at 6:18 PM Ray O'Donnell <ray@rodonnell.ie> wrote:
On 22/10/2024 12:31, Rossana Ocampos wrote:

Hello ,

I am new with PostgreSQL and I have a bug. I have created a function that has an input variable of type date , in case it does not receive value , it has to assume by default the current date.

I have defined it as follows variable  DATE DEFAULT CURRENT_DATE, but I get the following error.

 

El error

ERROR: invalid input syntax for type date: “CURRENT_DATE” LINE 1: ...extsupplydate ('1085018'::bigint, '5278'::bigint, 'CURRENT_D... ^ ERROR: invalid input syntax for type date: “CURRENT_DATE” SQL status: 22007 Characters: 78


I think you just need to leave off the quotes, as current_date is a function:

  insert into .... values ( ... , current_date, ....);

Also, you don't need to quote the bigint values.


HTH,

Ray.


 

Please could you help me, thank you very much.

Rossana 



-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie