Re: Storing and comparing columns of cryptographic hashes?

On Mon, Apr 8, 2024 at 10:08 AM Josh Triplett wrote: > > > - Is there a way to tell postgresql "this column contains cryptographic > hashes, so you can do hash joins using any subset of the bits, without > having to hash them again"? If not, should there be? > if you know the specific

Re: Issue with date/timezone conversion function

On 4/9/24 11:24, Lok P wrote: On Tue, Apr 9, 2024 at 10:33 PM Tom Lane > wrote: 'EST' is going to rotate to UTC-5, but that's probably not what you want in the summer.  I'd suggest AT TIME ZONE 'America/New_York' or the like.  See

Re: Issue with date/timezone conversion function

Below should work... date_trunc('hour', timestamp_column *AT TIME ZONE '*America/New_York') + (((date_part('minute', timestamp_column *AT TIME ZONE '*America/New_York')::int / 15)::int) * interval '15 min') On Tue, Apr 9, 2024 at 11:54 PM Lok P wrote: > > On Tue, Apr 9, 2024 at 10:33 PM Tom

Re: Issue with date/timezone conversion function

On Tue, Apr 9, 2024 at 10:33 PM Tom Lane wrote: > Lok P writes: > > These tables are INSERT only tables and the data in the create_timestamp > > column is populated using the now() function from the application, which > > means it will always be incremental, and the historical day transaction >

Re: PL/pgSQL techniques better than bash for dynamic DO?

út 9. 4. 2024 v 18:33 odesílatel Ron Johnson napsal: > PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now. > > I must purge the oldest X period of records from 70 tables, every Sunday. > The field name, interval (X days or months) and date (CURRENT_DATE or > CURRENT_TIMESTAMP) varies for

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Adrian Klaver writes: > On 4/9/24 08:12, Thiemo Kellner wrote: >>> PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader'; >>> >>> Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME. No, it's being seen as DB_NAME.SCHEMA_NAME.TYPE_NAME. > Because you did not do?: > PG_CATALOG.PG_ROLES.ROLNAME%type

Re: Issue with date/timezone conversion function

Lok P writes: > These tables are INSERT only tables and the data in the create_timestamp > column is populated using the now() function from the application, which > means it will always be incremental, and the historical day transaction > count is going to be the same. However surprisingly the

Re: Issue with date/timezone conversion function

On 4/9/24 9:16 AM, Lok P wrote: On Tue, Apr 9, 2024 at 9:26 PM Adrian Klaver > wrote: On 4/9/24 08:43, Lok P wrote: > Hi All, > It's version 15.4 of postgresql database. Every "date/time" data type > attribute gets stored in the

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Am 09.04.2024 um 17:18 schrieb Adrian Klaver: Because you did not do?: PG_CATALOG.PG_ROLES.ROLNAME%type Thanks

Re: prepared statement "cu1" already exists (but it does not)

Ok thanks Tom, My understanding from your last comment: Since prepared statements and server cursors use different name spaces, it's save to use the same name as in PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... ) Seb From: Tom Lane Sent:

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Am 09.04.2024 um 17:18 schrieb Adrian Klaver: Because you did not do?: PG_CATALOG.PG_ROLES.ROLNAME%type Oh, right. Sorry. What an oversight.

Re: prepared statement "cu1" already exists (but it does not)

Sebastien Flaesch writes: >>> Is it an issue if I use the same name for a prepared statement and the >>> server cursor? I mean: >> From memory, I think those share the same "portal" namespace. > Can you please elaborate? > Is it supported to do: >PQprepare(pgConn, "cu1", "declare cu1

Re: Moving delta data faster

On Sun, Apr 7, 2024 at 2:25 AM Adrian Klaver wrote: > > I have no idea how this works in the code, but my suspicion is it is due > to the following: > > https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT > > "The optional ON CONFLICT clause specifies an alternative action to

PL/pgSQL techniques better than bash for dynamic DO?

PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now. I must purge the oldest X period of records from 70 tables, every Sunday. The field name, interval (X days or months) and date (CURRENT_DATE or CURRENT_TIMESTAMP) varies for each table. Thus, I put all the relevant data in a tab-separated

Re: Issue with date/timezone conversion function

On Tue, Apr 9, 2024 at 9:26 PM Adrian Klaver wrote: > On 4/9/24 08:43, Lok P wrote: > > Hi All, > > It's version 15.4 of postgresql database. Every "date/time" data type > > attribute gets stored in the database in UTC timezone only. One of the > > support persons local timezone is

Re: Issue with date/timezone conversion function

On 4/9/24 08:43, Lok P wrote: Hi All, It's version 15.4 of postgresql database. Every "date/time" data type attribute gets stored in the database in UTC timezone only. One of the support persons local timezone is "asia/kolkata" and  that support person needs to fetch the count of transactions

Issue with date/timezone conversion function

Hi All, It's version 15.4 of postgresql database. Every "date/time" data type attribute gets stored in the database in UTC timezone only. One of the support persons local timezone is "asia/kolkata" and that support person needs to fetch the count of transactions from a table- transaction_tab and

Re: Tracing libpq client: Only with PQtrace()?

Yep I got it, thanks for the suggestion! Seb From: Greg Sabino Mullane Sent: Tuesday, April 9, 2024 4:50 PM To: Sebastien Flaesch Cc: pgsql-general Subject: Re: Tracing libpq client: Only with PQtrace()? EXTERNAL: Do not click links or open attachments if you

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

On 4/9/24 08:12, Thiemo Kellner wrote: Thanks for taking this up. Am 09.04.2024 um 17:09 schrieb Adrian Klaver: On 4/9/24 07:59, Thiemo Kellner wrote: [Code: 0, SQL State: 0A000] ERROR: References to other databases are not implemented: pg_catalog.pg_roles.rolname     Position: 298 [Script

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Thanks for taking this up. Am 09.04.2024 um 17:09 schrieb Adrian Klaver: On 4/9/24 07:59, Thiemo Kellner wrote: [Code: 0, SQL State: 0A000] ERROR: References to other databases are not implemented: pg_catalog.pg_roles.rolname    Position: 298 [Script position: 334 - 361]

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

On 4/9/24 07:59, Thiemo Kellner wrote: Hi I have the following function code. When trying to install, it gives me [Code: 0, SQL State: 0A000]  FEHLER: Verweise auf andere Datenbanken sind nicht implementiert: pg_catalog.pg_roles.rolname   Position: 298  [Script position: 334 - 361] [Code:

Re: Tracing libpq client: Only with PQtrace()?

On 2024-Apr-09, Greg Sabino Mullane wrote: > On Tue, Apr 9, 2024 at 8:51 AM Sebastien Flaesch > wrote: > > > Is the PQtrace() API the only way to enable libpq client tracing? > > > > I thought about some environment variable of client configuration > > setting... > > That's generally the job

[Code: 0, SQL State: 0A000] when "typing" from pg_catalog

Hi I have the following function code. When trying to install, it gives me [Code: 0, SQL State: 0A000] FEHLER: Verweise auf andere Datenbanken sind nicht implementiert: pg_catalog.pg_roles.rolname Position: 298 [Script position: 334 - 361] To the best of my knowledge, pg_catalog is a

Re: Tracing libpq client: Only with PQtrace()?

On Tue, Apr 9, 2024 at 8:51 AM Sebastien Flaesch wrote: > Is the PQtrace() API the only way to enable libpq client tracing? > > I thought about some environment variable of client configuration > setting... > That's generally the job of the client, or more specifically, the driver providing the

Re: Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

Hi team . In the https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION doc, it mentions that Immutable Functions and Hi can be pushed down using `extensions` option for foreign server. But it does not mention TYPE. In the shippable.c

Re: prepared statement "cu1" already exists (but it does not)

Tom, > > Is it an issue if I use the same name for a prepared statement and the > > server cursor? I mean: > From memory, I think those share the same "portal" namespace. Can you please elaborate? Is it supported to do: PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )

Re: Regarding: Replication of TRUNCATE commands is not working

On 4/9/24 05:07, Arvind Raghuwanshi wrote: Hi Laurenz, Thanks for the response Question: What PostgreSQL version are you using?  The feature was introduced in v11. Answer: I am using the 16.0 Postgresql version. db1=> SELECT version();                                                version

Re: Tracing libpq client: Only with PQtrace()?

Hello, On 2024-Apr-09, Sebastien Flaesch wrote: > Is the PQtrace() API the only way to enable libpq client tracing? Yes. Regards -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Tracing libpq client: Only with PQtrace()?

Hello Sorry if I missed that in the doc: Is the PQtrace() API the only way to enable libpq client tracing? I thought about some environment variable of client configuration setting... Seb

Re: Regarding: Replication of TRUNCATE commands is not working

Hi Laurenz, Thanks for the response Question: What PostgreSQL version are you using? The feature was introduced in v11. Answer: I am using the 16.0 Postgresql version. db1=> SELECT version(); version

Re: Regarding: Replication of TRUNCATE commands is not working

On Tue, 2024-04-09 at 14:50 +0530, Arvind Raghuwanshi wrote: > I have tried to run the TRUNCATE command  and found out that it's not getting > replicated using logical replication for pgsql. > I have also checked the schema change using pg_dump command but the schema > change also not getting

Re: Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

On Tue, 2024-04-09 at 15:49 +0530, Rajan Pandey wrote: > I was reading  > https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION > and found that it mentions that Immutable Functions and Operators can > be pushed down using `extensions` option for foreign

Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

Hi, I was reading https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION and found that it mentions that Immutable Functions and Operators can be pushed down using `extensions` option for foreign server. But it does not mention about TYPE. In the

Regarding: Replication of TRUNCATE commands is not working

Hi, I have tried to run the TRUNCATE command and found out that it's not getting replicated using logical replication for pgsql. I have also checked the schema change using pg_dump command but the schema change also not getting detected for TRUNCATE command. However on pgsql logical replication