Postgres date value dynamic inserting

Question

I am scratching my head trying to fix the following:

create or replace procedure my_schema.Test()
as $$

declare
    today date = now();

begin   
    drop table if exists my_schema.tst_table;
    create table  my_schema.tst_table (
        todays_date varchar     );
    
    execute('
        insert into my_schema.tst_table 
        values (' || today || ')    
    ');

end;
$$
language plpgsql;

Basically I am trying to dynamically insert the current date into a table which will be used in later steps.

The issue I am facing is that due to the today variable looking like '2022-02-11', and because I am inserting the record dynamically, postgres is interpreting the "-" as a minus sign and inserting the value 2009 into my table.

Does anyone have a workaround for this?

Never store date values in a varchar column.

user330315
– user330315

2022-02-11 16:38:55 +00:00
Commented Feb 11, 2022 at 16:38 — user330315
– user330315, Commented Feb 11, 2022 at 16:38

user330315 · Accepted Answer · 2022-02-11 16:42:05Z

1

Don't concatenate input values into dynamic SQL. And never store date values in a varchar column:

create or replace procedure my_schema.Test()
as $$
declare
    today date := current_date;
begin   
    drop table if exists my_schema.tst_table;
    create table  my_schema.tst_table 
    (
      todays_date date
    );
    
    execute('
        insert into my_schema.tst_table 
        values ($1)    
    ') using today;
end;
$$
language plpgsql;

But creating a table just to store the value of current_date seems a bit of an overkill.

answered Feb 11, 2022 at 16:42

user330315

Sign up to request clarification or add additional context in comments.

Comments

8ns · Accepted Answer · 2022-02-11 16:32:14Z

0

You can use the cast operator to force the value conversion to VARCHAR datatype:

execute('
    insert into my_schema.tst_table 
    values ('today'::VARCHAR)    
');

edited Feb 11, 2022 at 16:32

answered Feb 11, 2022 at 16:07

8ns

1298 bronze badges

Collectives™ on Stack Overflow

Postgres date value dynamic inserting

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related