Re: Timestamp conversion Error in dynamic sql script

[Laurenz Albe]

On Tue, 2024-04-02 at 11:08 +0530, sud wrote:
> Not able to figure out why it's giving below error during timezone conversion
> while defining the partitions even though I used the typecast? 
> 
> [...]
> DECLARE
> start_date TIMESTAMP := '2022-01-01';
> [...]
>     TO_CHAR(start_date + i, '_MM_DD')
> 
> ***
> SQL Error [42883]: ERROR: operator does not exist: timestamp without time 
> zone + integer
> Hint: No operator matches the given name and argument types. You might need 
> to add explicit type casts.

As the error states, you cannot add an integer to a timestamp.

What is the supposed meaning of that addition?

Yours,
Laurenz Albe

Re: Not able to purge partition

[Laurenz Albe]

On Tue, 2024-04-02 at 01:41 +0530, sud wrote:
> On Thu, Mar 21, 2024 at 6:18 PM Laurenz Albe  wrote:
> > > [create some partitions, then drop a partition of the referenced table]
> > >
> > > SQL Error [P0001]: ERROR: cannot drop table 
> > > schema1.test_part_drop_parent_p2024_02_01 because other objects depend on 
> > > it
> > > CONTEXT: SQL statement "DROP TABLE 
> > > schema1.test_part_drop_parent_p2024_02_01"
>
> However, out of curiosity, does this default foreign key setup i.e. foreign 
> keys
> between the table (but not between the partitions) also make the data load 
> into
> the child partitions slower ( as it must be then looking and validating the 
> presence
> of the keys across all the partitions of the parent table)?

The query checking the foreign key consistency should be somewhat more 
expensive,
since it has to undergo partition pruning to figure out which partition to 
query.

Yours,
Laurenz Albe

Getting wrong datetime in database using insert into table query.

[Saksham Joshi]

OS: Windows 10
Psycopg version: 2.9.9
Python version: 3.11.6
PostgreSQL version: 11
pip version : 24.0
1: what you did
We are using 'psycopg2-binary' library to connect to my postgresql hosted
on Azure.We have created a table named 'apilog' to store our api logs using
'Insert Into table' query.We have specifically added two columns named
create_date and update_date with 'timestamp with time zone' property
enabled.I only update create_date for each log locally using python and i
expected update_date column to automatically update the datetime when the
transaction is committed at the end in python.
2: what you expected to happen
I expected to see update_date column returning datetime values which are
similar to the time the transaction is committed in python however instead
the value seems to returning datetime which is more closer to the time db
connection is established.
3: what happened instead
The datetime value in update_date is coming earlier than the create_date
value of even the very first log which is creating discrepancy and making
it difficult to track the exact time logs are committed into database.

For example:
This query INSERT INTO api_log(log_detail,create_date)
VALUES('example log 1', datetime.datetime.utcnow'),('example log 2',
datetime.datetime.utcnow')
Should ideally return update_date which is older than 'example log 2'
create_date but it is returning a datetime which is even earlier than
'example log 1' create_date.

Re: Getting wrong datetime in database using insert into table query.

[Sándor Daku]

On Tue, 2 Apr 2024 at 10:59, Saksham Joshi  wrote:

> OS: Windows 10
> Psycopg version: 2.9.9
> Python version: 3.11.6
> PostgreSQL version: 11
> pip version : 24.0
> 1: what you did
> We are using 'psycopg2-binary' library to connect to my postgresql hosted
> on Azure.We have created a table named 'apilog' to store our api logs using
> 'Insert Into table' query.We have specifically added two columns named
> create_date and update_date with 'timestamp with time zone' property
> enabled.I only update create_date for each log locally using python and i
> expected update_date column to automatically update the datetime when the
> transaction is committed at the end in python.
> 2: what you expected to happen
> I expected to see update_date column returning datetime values which are
> similar to the time the transaction is committed in python however instead
> the value seems to returning datetime which is more closer to the time db
> connection is established.
> 3: what happened instead
> The datetime value in update_date is coming earlier than the create_date
> value of even the very first log which is creating discrepancy and making
> it difficult to track the exact time logs are committed into database.
>
> For example:
> This query INSERT INTO api_log(log_detail,create_date)
> VALUES('example log 1', datetime.datetime.utcnow'),('example log 2',
> datetime.datetime.utcnow')
> Should ideally return update_date which is older than 'example log 2'
> create_date but it is returning a datetime which is even earlier than
> 'example log 1' create_date.
>


Without seeing the definition of your table it's only a guess, but if you
using 'now' or now() as the default value for update_date then this might
be the source of your problem:

https://www.postgresql.org/docs/11/functions-datetime.html
now() timestamp with time zone Current date and time (start of current
transaction);I think clock_timestamp is what you need.

Regards,
Sándor

Re: Timestamp conversion Error in dynamic sql script

[Erik Wienhold]

On 2024-04-02 07:38 +0200, sud wrote:
> I am trying to create a block which will create a few partitions
> dynamically and also insert ~1million rows into each of those partitions.
> Not able to figure out why it's giving below error during timezone
> conversion while defining the partitions even though I used the typecast?
> 
> CREATE TABLE parent_table (
> id Numeric,
> col1 TEXT,
> col2 TEXT,
> partition_key TIMESTAMP,
> primary key (partition_key, id)
> )
> PARTITION BY RANGE (partition_key);
> 
> **
> 
> DO $$
> DECLARE
> start_date TIMESTAMP := '2022-01-01';
> begin
> FOR i IN 0..10 LOOP
> 
> EXECUTE format('
> CREATE TABLE parent_table_%s (
> CHECK (partition_key >= DATE ''%s'' AND partition_key < DATE ''%s''
> )
> ) INHERITS (parent_table);',
> TO_CHAR(start_date + i, '_MM_DD'),
> 
> TO_CHAR(start_date + i, '-MM-DD')::timestamp ,
> TO_CHAR(start_date + i + INTERVAL '1 day', '-MM-DD')::timestamp
> );
> EXECUTE format('
> ALTER TABLE parent_table ATTACH PARTITION parent_table_%s
> FOR VALUES FROM (''%s'') TO (''%s'');',
> TO_CHAR(start_date + i, '_MM_DD'),
> TO_CHAR(start_date + i, '-MM-DD')::timestamp,
> TO_CHAR(start_date + i + INTERVAL '1 day', '-MM-DD') ::timestamp
> );
> 
> END LOOP;
> 
>   FOR i IN 0..10 LOOP
> EXECUTE format('
> INSERT INTO parent_table_%s (id,col1, col2,  partition_key)
> SELECT
> generate_series(1, 100),
> md5(random()::text),
> md5(random()::text),
> TIMESTAMP ''%s'' + INTERVAL ''%s days''
> FROM generate_series(1, 100);',
> TO_CHAR(start_date + i, '_MM_DD'),
> start_date,
> i
> );
> END LOOP;
> END $$;
> 
> ***
> 
> 
> 
> 
> *SQL Error [42883]: ERROR: operator does not exist: timestamp without time
> zone + integerHint: No operator matches the given name and argument types.
> You might need to add explicit type casts.Where: PL/pgSQL function
> inline_code_block line 7 at EXECUTEError position:*

Two ways to fix it:

1. Declare start_date as DATE when you want to add days with date + int

2. Keep TIMESTAMP and use start_date + make_interval(days => i)

-- 
Erik

Re: Timestamp conversion Error in dynamic sql script

[Greg Sabino Mullane]

>
> 1. Declare start_date as DATE when you want to add days with date + int
> 2. Keep TIMESTAMP and use start_date + make_interval(days => i)
>

Also

0. Use TIMESTAMPTZ not TIMESTAMP

Cheers,
Greg

Re: Getting wrong datetime in database using insert into table query.

[Adrian Klaver]

On 4/2/24 01:58, Saksham Joshi wrote:

OS: Windows 10
Psycopg version: 2.9.9
Python version: 3.11.6
PostgreSQL version: 11
pip version : 24.0
1: what you did
We are using 'psycopg2-binary' library to connect to my postgresql 
hosted on Azure.We have created a table named 'apilog' to store our api 
logs using 'Insert Into table' query.We have specifically added two 
columns named create_date and update_date with 'timestamp with time 
zone' property enabled.I only update create_date for each log locally 
using python and i expected update_date column to automatically update 
the datetime when the transaction is committed at the end in python.

2: what you expected to happen
I expected to see update_date column returning datetime values which are 
similar to the time the transaction is committed in python however 
instead the value seems to returning datetime which is more closer to 
the time db connection is established.

3: what happened instead
The datetime value in update_date is coming earlier than the create_date 
value of even the very first log which is creating discrepancy and 
making it difficult to track the exact time logs are committed into 
database.


For example:
This query INSERT INTO api_log(log_detail,create_date)
VALUES('example log 1', datetime.datetime.utcnow'),('example log 2', 
datetime.datetime.utcnow')
Should ideally return update_date which is older than 'example log 2' 
create_date but it is returning a datetime which is even earlier than 
'example log 1' create_date.



Read:

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Timestamp conversion Error in dynamic sql script

[sud]

On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane 
wrote:

> 1. Declare start_date as DATE when you want to add days with date + int
>> 2. Keep TIMESTAMP and use start_date + make_interval(days => i)
>>
>
> Also
>
> 0. Use TIMESTAMPTZ not TIMESTAMP
>
>
> Thank you so much. That helped.

Now this block seems to be failing near the "LIKE" operator. Isn't it
allowed to add the check constraints along with the CREATE TABLE statement?


*SQL Error [42601]: ERROR: syntax error at or near "LIKE"Where: PL/pgSQL
function inline_code_block line 8 at EXECUTE*
*Error position*

CREATE TABLE parent_table (
id Numeric,
col1 TEXT,
col2 TEXT,
partition_key TIMESTAMP,
primary key (partition_key, id)
)
PARTITION BY RANGE (partition_key);

*

DO $$
DECLARE
start_date TIMESTAMPtz := '2022-01-01';
begin
FOR i IN 0..10 LOOP

EXECUTE format('
CREATE TABLE parent_table_%s (
CHECK (partition_key >=  ''%s'' AND partition_key <  ''%s'' )
   * ) LIKE (parent_table including all);',*
   TO_CHAR(start_date + make_interval(days=>i),'_MM_DD'),
(start_date + make_interval(days=>i))::timestamptz ,
(start_date + make_interval(days=>i))::timestamptz
);

EXECUTE format('
ALTER TABLE parent_table ATTACH PARTITION parent_table_%s
FOR VALUES FROM (''%s'') TO (''%s'');',
TO_CHAR(start_date + make_interval(days=>i),'_MM_DD'),
(start_date + make_interval(days=>i))::timestamptz ,
(start_date + make_interval(days=>i))::timestamptz
);

END LOOP;

  FOR i IN 0..10 LOOP
EXECUTE format('
INSERT INTO parent_table_%s (id,col1, col2,  partition_key)
SELECT
generate_series(1, 100),
md5(random()::text),
md5(random()::text),
''%s''
   FROM generate_series(1, 100);',TO_CHAR(start_date +
make_interval(days=>i),'_MM_DD'),

(start_date + make_interval(days=>i))::timestamptz);

END LOOP;

END $$;


*SQL Error [42601]: ERROR: syntax error at or near "LIKE"Where: PL/pgSQL
function inline_code_block line 8 at EXECUTE*
*Error position: *

Re: Timestamp conversion Error in dynamic sql script

[Erik Wienhold]

On 2024-04-02 22:08 +0200, sud wrote:
> On Tue, Apr 2, 2024 at 7:46 PM Greg Sabino Mullane 
> wrote:
> 
> Now this block seems to be failing near the "LIKE" operator. Isn't it
> allowed to add the check constraints along with the CREATE TABLE statement?
> 
> [...]
> 
> EXECUTE format('
> CREATE TABLE parent_table_%s (
> CHECK (partition_key >=  ''%s'' AND partition_key <  ''%s'' )
>* ) LIKE (parent_table including all);',*
>TO_CHAR(start_date + make_interval(days=>i),'_MM_DD'),
> (start_date + make_interval(days=>i))::timestamptz ,
> (start_date + make_interval(days=>i))::timestamptz
> );
> 
> [...]
> 
> *SQL Error [42601]: ERROR: syntax error at or near "LIKE"Where: PL/pgSQL
> function inline_code_block line 8 at EXECUTE*
> *Error position: *

The LIKE clause goes inside the parenthesis along with the column and
constraint definitions, i.e.:

CREATE TABLE parent_table__mm_dd (
LIKE parent_table,
CHECK (...)
);

-- 
Erik

What linux version to install ?

[David Gauthier]

This is what we are running...
Red Hat Enterprise Linux Server release 7.9 (Maipo)

In our pandora distrib, I see PG v15.3.  I was wondering if there is
something even better. Can't seem to pin this down using
https://www.postgresql.org/download/linux/redhat/.

Re: What linux version to install ?

[Adrian Klaver]

On 4/2/24 16:53, David Gauthier wrote:

This is what we are running...
Red Hat Enterprise Linux Server release 7.9 (Maipo)

In our pandora distrib, I see PG v15.3.  I was wondering if there is 
something even better. Can't seem to pin this down using 
https://www.postgresql.org/download/linux/redhat/ 
.


1) https://yum.postgresql.org/news/rhel7-postgresql-rpms-end-of-life/

"EOL announcement for RHEL 7

PostgreSQL RPM repo stopped adding new packages to the RHEL 7 repo as of 
Aug 2023, including PostgreSQL 16.


We will maintain older major releases until each major release is EOLed 
by PostgreSQL project. Please visit here for latest release dates for 
each major release.


If you have any questions, please either email to 
pgsql-pkg-...@lists.postgresql.org, or create a ticket at our redmine.

"

2) What is 'pandora distrib'?

--
Adrian Klaver
adrian.kla...@aklaver.com