Обсуждение: ALTER TABLE some table ADD PARTITION partition_name VALUES less than (TO_DATE('variablex', 'DD/MM/YYYY')) ;

I am writing a plsql code
DECLARE
    sys_date             character varying(500);
variablex            character varying(500);
sys_date:='select to_char(now,''YYYYMMDD'')';
    execute immediate sys_date into variablex;
    dbms_output.put_line(variablex);
begin
ALTER TABLE some table  ADD PARTITION partition_name VALUES less than
(TO_DATE('variablex', 'DD/MM/YYYY')) ;

I want to add a partition based on sysdate and each day a table would be
created. So i am writing the code as above

It throws an error
ERROR:  date format not recognized
CONTEXT:  SQL statement "ALTER TABLE some table ADD PARTITION partition_name
VALUES less than (TO_DATE('variablex', 'DD/MM/YYYY'))"
edb-spl function inline_code_block line 44 at SQL statement




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ALTER-TABLE-some-table-ADD-PARTITION-partition-name-VALUES-less-than-TO-DATE-variablex-DD-MM-YYYY-tp5801241.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

jagadishk wrote:
> I am writing a plsql code
> DECLARE
>     sys_date             character varying(500);
> variablex            character varying(500);
> sys_date:='select to_char(now,''YYYYMMDD'')';
>     execute immediate sys_date into variablex;
>     dbms_output.put_line(variablex);
> begin
> ALTER TABLE some table  ADD PARTITION partition_name VALUES less than
> (TO_DATE('variablex', 'DD/MM/YYYY')) ;
> 
> I want to add a partition based on sysdate and each day a table would be
> created. So i am writing the code as above
> 
> It throws an error
> ERROR:  date format not recognized
> CONTEXT:  SQL statement "ALTER TABLE some table ADD PARTITION partition_name
> VALUES less than (TO_DATE('variablex', 'DD/MM/YYYY'))"
> edb-spl function inline_code_block line 44 at SQL statement

Remove the single quotes around "variablex".

I believe that you need dynamic SQL to perform an ALTER TABLE.

Yours,
Laurenz Albe

jagadishk, 23.04.2014 13:13:
> I am writing a plsql code
> DECLARE
>     sys_date             character varying(500);
> variablex            character varying(500);
> sys_date:='select to_char(now,''YYYYMMDD'')';
>     execute immediate sys_date into variablex;
>     dbms_output.put_line(variablex);
> begin
> ALTER TABLE some table  ADD PARTITION partition_name VALUES less than
> (TO_DATE('variablex', 'DD/MM/YYYY')) ;
>
> I want to add a partition based on sysdate and each day a table would be
> created. So i am writing the code as above
>
> It throws an error
> ERROR:  date format not recognized
> CONTEXT:  SQL statement "ALTER TABLE some table ADD PARTITION partition_name
> VALUES less than (TO_DATE('variablex', 'DD/MM/YYYY'))"
> edb-spl function inline_code_block line 44 at SQL statement

Since when does Postgres support ALTER TABLE ... ADD PARTITION ?

we are using enterprise db database.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/ALTER-TABLE-some-table-ADD-PARTITION-partition-name-VALUES-less-than-TO-DATE-variablex-DD-MM-YYYY-tp5801241p5801254.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On 23 April 2014 13:32, jagadishk <jagadishkantubugata@yahoo.com> wrote:

> we are using enterprise db database.

You'll probably want the MongoDB mailing list then.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services