Re: How to store in hours:minutes:seconds where hours may be bigger than 24

[Celia McInnis]

Whoops - I hadn't changed the type of the column in the table that I was
inserting into - it was of type "TIME WITHOUT TIMEZONE". Now that I have
set the column type to INTERVAL, I can insert the string '25:17:07' into
the column without even needing to do any casting.

Thank goodness and thanks!
Celia

On Tue, Mar 19, 2024 at 11:01 PM Christophe Pettus  wrote:

>
>
> > On Mar 19, 2024, at 19:56, Celia McInnis 
> wrote:
> >
> > Thanks for the suggestion, Steve, but No - when I insert
> 25:17:07::interval into my table I get 01:17:07 into the table - i.e., it
> replaces 25 hours by (25 mod 24) hours or 1 hour, and this is not what I
> want. I really need the number of hours rather than the number of hours mod
> 24. Do I have to make a composite type to get what I want???
>
> I'm not seeing that result:
>
> xof=# create table t (i interval);
> CREATE TABLE
> xof=# insert into t values('25:17:07'::interval);
> INSERT 0 1
> xof=# select * from t;
> i
> --
>  25:17:07
> (1 row)
>
> Can you show what you are doing that gets the result you describe?

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

[Tom Lane]

Celia McInnis  writes:
> Thanks for the suggestion, Steve, but No - when I insert 25:17:07::interval
> into my table I get 01:17:07 into the table - i.e., it replaces 25 hours by
> (25 mod 24) hours or 1 hour, and this is not what I want.

There is definitely something you are not telling us, because it
works in isolation:

regression=# create table t (f1 interval);
CREATE TABLE
regression=# insert into t values ('25:17:07'::interval);
INSERT 0 1
regression=# select * from t;
f1
--
 25:17:07
(1 row)

What's the full context of your problem?

regards, tom lane

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

[Christophe Pettus]

> On Mar 19, 2024, at 19:56, Celia McInnis  wrote:
> 
> Thanks for the suggestion, Steve, but No - when I insert 25:17:07::interval 
> into my table I get 01:17:07 into the table - i.e., it replaces 25 hours by 
> (25 mod 24) hours or 1 hour, and this is not what I want. I really need the 
> number of hours rather than the number of hours mod 24. Do I have to make a 
> composite type to get what I want???

I'm not seeing that result:

xof=# create table t (i interval);
CREATE TABLE
xof=# insert into t values('25:17:07'::interval);
INSERT 0 1
xof=# select * from t;
i 
--
 25:17:07
(1 row)

Can you show what you are doing that gets the result you describe?

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

[Celia McInnis]

Thanks for the suggestion, Steve, but No - when I insert 25:17:07::interval
into my table I get 01:17:07 into the table - i.e., it replaces 25 hours by
(25 mod 24) hours or 1 hour, and this is not what I want. I really need the
number of hours rather than the number of hours mod 24. Do I have to make a
composite type to get what I want???

Thanks,
Celia McInnis

On Tue, Mar 19, 2024 at 10:44 PM Steve Baldwin 
wrote:

> Could you use an interval data type? For example:
>
> b2bcreditonline=# create table interval_example (i interval);
> CREATE TABLE
> b2bcreditonline=# insert into interval_example values
> ('26:15:32'::interval);
> INSERT 0 1
> b2bcreditonline=# select * from interval_example;
> i
> --
>  26:15:32
> (1 row)
>
> b2bcreditonline=# select i, i + interval '45 minutes' as plus from
> interval_example;
> i |   plus
> --+--
>  26:15:32 | 27:00:32
> (1 row)
>
> Steve
>
> On Wed, Mar 20, 2024 at 1:05 PM Celia McInnis 
> wrote:
>
>> Hi:
>>
>> I want to store times in a database as hours:minutes:seconds where hours
>> can be greater than 24. How do I do this? I will want to be able to add
>> such times.
>>
>> Thanks,
>> Celia McInnis
>>
>

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

[Steve Baldwin]

Could you use an interval data type? For example:

b2bcreditonline=# create table interval_example (i interval);
CREATE TABLE
b2bcreditonline=# insert into interval_example values
('26:15:32'::interval);
INSERT 0 1
b2bcreditonline=# select * from interval_example;
i
--
 26:15:32
(1 row)

b2bcreditonline=# select i, i + interval '45 minutes' as plus from
interval_example;
i |   plus
--+--
 26:15:32 | 27:00:32
(1 row)

Steve

On Wed, Mar 20, 2024 at 1:05 PM Celia McInnis 
wrote:

> Hi:
>
> I want to store times in a database as hours:minutes:seconds where hours
> can be greater than 24. How do I do this? I will want to be able to add
> such times.
>
> Thanks,
> Celia McInnis
>

Re: How to store in hours:minutes:seconds where hours may be bigger than 24

[Ron Johnson]

On Tue, Mar 19, 2024 at 10:05 PM Celia McInnis 
wrote:

> Hi:
>
> I want to store times in a database as hours:minutes:seconds where hours
> can be greater than 24. How do I do this? I will want to be able to add
> such times.
>

Try the INTERVAL data type.

How to store in hours:minutes:seconds where hours may be bigger than 24

[Celia McInnis]

Hi:

I want to store times in a database as hours:minutes:seconds where hours
can be greater than 24. How do I do this? I will want to be able to add
such times.

Thanks,
Celia McInnis

RE: Postgres database encryption

[Zahir Lalani]

>On Tue, Mar 19, 2024 at 9:22 AM Johnathan Tiamoh 
> wrote:
>Hello,  
>
>
>Good morning. Please I wish to find out the best way of encrypting a Postgres 
>database. 
>
>This could either be encrypting a whole database ( I don’t if this is 
>possible), a specific table and a specific tablespace. 
>
>Look at PgSodium.  It might meet your requirements.

We use PgSodium - works well and we can control down to field level 

Re: Postgres database encryption

[Ron Johnson]

On Tue, Mar 19, 2024 at 9:22 AM Johnathan Tiamoh 
wrote:

> Hello,
>
>
> Good morning. Please I wish to find out the best way of encrypting a
> Postgres database.
>
> This could either be encrypting a whole database ( I don’t if this is
> possible), a specific table and a specific tablespace.
>

Look at PgSodium.  It might meet your requirements.

Re: Postgres database encryption

[Kai Wagner]

Hey,

On 19/03/2024 14:22, Johnathan Tiamoh wrote:

Hello,


Good morning. Please I wish to find out the best way of encrypting a 
Postgres database.


This could either be encrypting a whole database ( I don’t if this is 
possible), a specific table and a specific tablespace.


There is no default incore mechanism to do that. Depending on your treat 
vector, OS-Level could be fine or you need to go with any of the incore 
rebuilds available by some vendors.


We're currently working on another mechanism, which enables you to 
encrypt on a table level. Of course, completely open source project, 
available on GitHub https://github.com/Percona-Lab/pg_tde


Would be happy, if you can give it a shot, and share your thoughts and 
feedback.





King regards
Johnathan T.


--
Kai Wagner
Sr. Engineering Manager, Percona

e: kai.wag...@percona.com
w: www.percona.com
Databases Run Better with Percona.

Re: Query on Postgres SQL transaction

[Adrian Klaver]

On 3/19/24 02:18, Bandi, Venkataramana - Dell Team wrote:

Hi Greg,

We are using hibernate framework to persist the data into Postgres SQL 
DB and data is persisting and committing for all the clients but one of 
the client data is not inserted into DB.


What is different about that client?

Are all the clients passing data through the same instance of the framework?

Are you sure that the client is pointed at the correct database?

Is the log entry below from that client?



Not getting any error/exception for this case. Could you please let us 
know how we can trace out this scenario on transaction level whether 
transaction is committing or not?


We have enabled below properties in postgresql.conf file and verified 
but didn't get any findings about the transaction and below log 
statements are writing in our data store logs.


log_statement = 'all'

logging_collector = on

log_min_messages = debug5

log_min_error_statement = debug5

2024-02-19 15:21:54.850 +08 [1876] LOG:  execute S_48: insert into 
xxx 
(f_schedule_name,f_id,f_totaldataredtn,f_invalidationtime,f_statuscode,f_module,f_app_type,f_dbbackuptype,f_is_compressed,f_proxy,f_size,f_sizeprotected,f_groupjobid,f_status,f_bytesmodifiednotsent,f_sizetransferredboffset,f_bytesmodifiedsent,f_errcode,f_jobid2,f_media_server,f_starttime,f_storageid,f_pool,f_queuestart,f_sizescannedboffset,f_errorcodesummary,f_ncopies,f_sizeprotectedboffset,f_snap_target_platform,f_backup_servername,f_nfiles,f_expiry,f_owner,f_policy_id,f_parentjobid,f_sub_name,f_completion_status,f_endtime,f_filesscanned,f_idle_wait,f_storage_unit,f_group_id,f_backup_set,f_ntries,f_job_name,f_level,f_agent_name,f_failed_copies,f_restarted_job,f_success_copies,f_domain_id,f_snap_target,f_jobid,f_request_id,f_pluginname,f_sizetransferred,f_is_snap,f_node_id,f_workflow_id,f_action_name,f_agent_id,f_instancename,f_session,f_totalobjdedup,f_changedbytes,f_sizeboffset,f_dedupredtn,f_statuscodesummary,f_workflow_jobid,f_snap_policy,f_size_copies,f_sizescanned,f_sub_id,f_archive_flag,f_nfilesnot,f_media_wait,f_snap_creation,f_effective_path) values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78)


2024-02-19 15:21:54.851 +08 [10928] DEBUG:  bind  to 

2024-02-19 15:21:54.852 +08 [10928] DEBUG:  CommitTransaction(1) name: 
unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0


Regards,

Venkat

*
*

*

Internal Use - Confidential

From:*Greg Sabino Mullane 
*Sent:* Saturday, March 16, 2024 12:07 AM
*To:* Bandi, Venkataramana - Dell Team 
*Cc:* pgsql-general@lists.postgresql.org; Kishore, Nanda - Dell Team 
; Alampalli, Kishore 


*Subject:* Re: Query on Postgres SQL transaction

[EXTERNAL EMAIL]

That's a very vague question, but you can trace exactly what is 
happening by issuing


SET log_statement = 'all';

Ideally at the session level by your application, but can also set it at 
the database and user level. If all else fails, set it globally (i.e. 
postgresql.conf). Turn it off again as soon as possible, it will make 
your logs extremely verbose. But you can track exactly what your 
application is doing.


Cheers,

Greg



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

Re: operator is only a shell - Error

[Adrian Klaver]

On 3/19/24 00:02, Rajesh S wrote:
Sorry Adrian, my bad. I'd mentioned "deposit_sub_no='1'" by mistake, 
actually it was "deposit_sub_no=cast(1 as varchar)". This was throwing 


1) Maybe you could explain the logic of taking a number and casting it 
to a string to compare it to a number?


2) select 1::varchar = 1::varchar;
 ?column?
--
 t

So:

deposit_sub_no::varchar = 1::varchar


error "SQL Error [42883]: ERROR: operator does not exist: numeric = 
character varying  Hint: No operator matches the given name and argument 
types. You might need to add explicit type casts.  Position: 19".  Then 
realized that "numeric=text" works but "numeric=varchar" does not.  I 
could resolve the problem by creating the following function and 
operator by commenting "CUMMUTATOR" and "NEGATOR".  Also the error 
"operator is only a shell" also vanished.  I'm just sharing the script 
for your reference.  Thank you very much for your valuable support.


CREATE OR REPLACE FUNCTION public.num_eq_varchar(
     numeric,
     varchar)
     RETURNS boolean
AS 'select case when $2 ~ ''^[0-9\.]+$'' then $1 operator(pg_catalog.=) 
cast($2 as numeric) else $1::varchar = $2 end;'

LANGUAGE SQL IMMUTABLE;

-- Operator: =;

-- DROP OPERATOR IF EXISTS public.= (numeric , varchar);

CREATE OPERATOR public.= (
     FUNCTION = num_eq_varchar,
     LEFTARG = numeric,
     RIGHTARG = varchar,
-- COMMUTATOR = =,
-- NEGATOR = <>,
     RESTRICT = eqsel,
     JOIN = eqjoinsel,
     HASHES, MERGES
);


Thanks,

Rajesh S




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

RE: Query on Postgres SQL transaction

[Bandi, Venkataramana - Dell Team]

Hi Greg,

We are using hibernate framework to persist the data into Postgres SQL DB and 
data is persisting and committing for all the clients but one of the client 
data is not inserted into DB.
Not getting any error/exception for this case. Could you please let us know how 
we can trace out this scenario on transaction level whether transaction is 
committing or not?
We have enabled below properties in postgresql.conf file and verified but 
didn't get any findings about the transaction and below log statements are 
writing in our data store logs.

log_statement = 'all'
logging_collector = on
log_min_messages = debug5
log_min_error_statement = debug5

2024-02-19 15:21:54.850 +08 [1876] LOG:  execute S_48: insert into xxx 
(f_schedule_name,f_id,f_totaldataredtn,f_invalidationtime,f_statuscode,f_module,f_app_type,f_dbbackuptype,f_is_compressed,f_proxy,f_size,f_sizeprotected,f_groupjobid,f_status,f_bytesmodifiednotsent,f_sizetransferredboffset,f_bytesmodifiedsent,f_errcode,f_jobid2,f_media_server,f_starttime,f_storageid,f_pool,f_queuestart,f_sizescannedboffset,f_errorcodesummary,f_ncopies,f_sizeprotectedboffset,f_snap_target_platform,f_backup_servername,f_nfiles,f_expiry,f_owner,f_policy_id,f_parentjobid,f_sub_name,f_completion_status,f_endtime,f_filesscanned,f_idle_wait,f_storage_unit,f_group_id,f_backup_set,f_ntries,f_job_name,f_level,f_agent_name,f_failed_copies,f_restarted_job,f_success_copies,f_domain_id,f_snap_target,f_jobid,f_request_id,f_pluginname,f_sizetransferred,f_is_snap,f_node_id,f_workflow_id,f_action_name,f_agent_id,f_instancename,f_session,f_totalobjdedup,f_changedbytes,f_sizeboffset,f_dedupredtn,f_statuscodesummary,f_workflow_jobid,f_snap_policy,f_size_copies,f_sizescanned,f_sub_id,f_archive_flag,f_nfilesnot,f_media_wait,f_snap_creation,f_effective_path)
 values 
($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78)
2024-02-19 15:21:54.851 +08 [10928] DEBUG:  bind  to 
2024-02-19 15:21:54.852 +08 [10928] DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0


Regards,
Venkat



Internal Use - Confidential

From: Greg Sabino Mullane 
Sent: Saturday, March 16, 2024 12:07 AM
To: Bandi, Venkataramana - Dell Team 
Cc: pgsql-general@lists.postgresql.org; Kishore, Nanda - Dell Team 
; Alampalli, Kishore 

Subject: Re: Query on Postgres SQL transaction


[EXTERNAL EMAIL]
That's a very vague question, but you can trace exactly what is happening by 
issuing

SET log_statement = 'all';

Ideally at the session level by your application, but can also set it at the 
database and user level. If all else fails, set it globally (i.e. 
postgresql.conf). Turn it off again as soon as possible, it will make your logs 
extremely verbose. But you can track exactly what your application is doing.

Cheers,
Greg

Re: Postgres database encryption

[Greg Sabino Mullane]

What is your threat model? That will determine the best answer.

The easiest thing is to use OS-level encryption.

Cheers,
Greg

Re: set local statement_timeout within a stored procedure

[Tom Lane]

"Abraham, Danny"  writes:
> Is there a way to integrate " set local statement_timeout" in a stored 
> procedure?

Not usefully.  statement_timeout bounds the time spent for a single
command sent by the client.  So by the time you're inside a procedure,
the countdown is already running (or not) for the current command, and
it's too late to change it with effect for that command.

regards, tom lane

Postgres database encryption

[Johnathan Tiamoh]

Hello,


Good morning. Please I wish to find out the best way of encrypting a
Postgres database.

This could either be encrypting a whole database ( I don’t if this is
possible), a specific table and a specific tablespace.


King regards
Johnathan T.

set local statement_timeout within a stored procedure

[Abraham, Danny]

Hi,

Is there a way to integrate " set local statement_timeout" in a stored 
procedure?

Something like 

create or replace procedure call_sp()
language plpgsql
as $$
begin
   set local statement_timeout = 1;
call sp();
end; $$

Re: operator is only a shell - Error

[Rajesh S]

Sorry Adrian, my bad. I'd mentioned "deposit_sub_no='1'" by mistake, 
actually it was "deposit_sub_no=cast(1 as varchar)". This was throwing 
error "SQL Error [42883]: ERROR: operator does not exist: numeric = 
character varying  Hint: No operator matches the given name and argument 
types. You might need to add explicit type casts.  Position: 19".  Then 
realized that "numeric=text" works but "numeric=varchar" does not.  I 
could resolve the problem by creating the following function and 
operator by commenting "CUMMUTATOR" and "NEGATOR".  Also the error 
"operator is only a shell" also vanished.  I'm just sharing the script 
for your reference.  Thank you very much for your valuable support.


CREATE OR REPLACE FUNCTION public.num_eq_varchar(
    numeric,
    varchar)
    RETURNS boolean
AS 'select case when $2 ~ ''^[0-9\.]+$'' then $1 operator(pg_catalog.=) 
cast($2 as numeric) else $1::varchar = $2 end;'

LANGUAGE SQL IMMUTABLE;

-- Operator: =;

-- DROP OPERATOR IF EXISTS public.= (numeric , varchar);

CREATE OPERATOR public.= (
    FUNCTION = num_eq_varchar,
    LEFTARG = numeric,
    RIGHTARG = varchar,
-- COMMUTATOR = =,
-- NEGATOR = <>,
    RESTRICT = eqsel,
    JOIN = eqjoinsel,
    HASHES, MERGES
);


Thanks,

Rajesh S

On 18-03-2024 21:20, Adrian Klaver wrote:

On 3/18/24 00:05, Rajesh S wrote:
Thank you for your response.  Actually, I was trying to address the 
following query.


select LIEN_AC_NO from deposit_lien where deposit_no='0002114029832' 
and deposit_sub_no='1' and unlien_dt is null and unlien_remarks is null;


In the above query "deposit_sub_no" column is "numeric" type and 
passing '1' (as varchar).  To address this I'd created the function 
and operator as I'd mentioned in the earlier mail. Even the following 
query throws error after creating the function and operator.


select * from deposit_lien where deposit_no='0002114029832';

ERROR: operator is only a shell: character varying = numeric LINE 1: 
select * from deposit_lien where deposit_no='0002114029832' ^ SQL 
state: 42883 Character: 44


In the above query "deposit_no" column is having "varchar" data 
type.  But before creating the function and operator it was working 
fine.  Tried dropping the same, even though the same error.  How to 
proceed now?


Not clear to me what the problem is you are trying to solve?

On a stock Postgres install:

select 1::numeric = '1';
 ?column?
--
 t

select '0002114029832'::varchar = '0002114029832';
 ?column?
--
 t





Thanks,

Rajesh S

On 15-03-2024 19:10, Greg Sabino Mullane wrote:

On Fri, Mar 15, 2024 at 6:26 AM Rajesh S  wrote:

    I wanted to implement a new "=" (equal) operator with LEFTARG as
    numeric and RIGHTARG as varchar.  But after creating the function
    and operator, psql shows the error "operator is only a shell:
    character varying = numeric

Your operator has numeric on the left and varchar on the right. But 
your query is doing numeric on the RIGHT. Probably want to make a 
matching one to cover both cases.


Cheers,
Greg