Re: How to select unique records in PostgreSQL

Honestly, you do it *in PostgreSQL* the same way you do it in all the other SQL RDBMSs. On 11/24/22 06:01, Rama Krishnan wrote: Hi All, I want to get the unique wallet_id from this table even it was repeated on multiple occasions I should calculate only once as well as if the wallet_id was

Practical maximum max_locks_per_transaction?

v9.6.24 (being retired next year) https://www.postgresql.org/docs/9.6/runtime-config-locks.html "The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g., query of a parent tab

Re: Practical maximum max_locks_per_transaction?

On 11/25/22 17:56, Tom Lane wrote: Ron writes: How "raised" is too raised?  I just had to increase max_locks_per_transaction from 640 to 1024 on an instance with many child tables and against which is run many large reports.  Another instance has zero children, but had to increase th

Re: Get table catalog from pg_indexes

On 11/27/22 11:22, Igor Korot wrote: Hi, ALL, Table pg_indexes does not contain a field for a catalog. So how do I get that? SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND schemaname = $3 You did not look hard enough, or Google "postgresql pg_indexes". test=# \d pg_inde

Re: Get table catalog from pg_indexes

On 11/27/22 15:55, Adrian Klaver wrote: On 11/27/22 13:31, Ron wrote: On 11/27/22 11:22, Igor Korot wrote: Hi, ALL, Table pg_indexes does not contain a field for a catalog. So how do I get that? SELECT 1 FROM pg_indexes WHERE indexname = $1 AND tablename = $2 AND schemaname = $3 You did

Re: Get table catalog from pg_indexes

On 11/28/22 00:04, Thomas Kellerer wrote: Igor Korot schrieb am 27.11.2022 um 23:13: I've never heard of a database referred to as a catalog. (That's always been where a database's metadata -- i.e. the pg_catalog schema -- is stored.) In the ODBC terminology the DB is usually referenced as cat

Re: delete statement returning too many results

On 11/28/22 07:29, Arlo Louis O'Keeffe wrote: Hello everyone, I am seeing weird behaviour of a delete statement that is returning more results than I am expecting. This is the query: DELETE FROM queue WHERE id IN ( SELECT id FROM queue O

Re: postgres large database backup

On 11/30/22 11:41, Vijaykumar Jain wrote: On Wed, Nov 30, 2022, 9:10 PM Atul Kumar wrote: Hi, I have a 10TB database running on postgres 11 version running on centos 7 "on premises", I need to schedule the backup of this database in a faster way. The scheduled backup will

Re: postgres large database backup

On 11/30/22 19:41, Michael Loftis wrote: On Wed, Nov 30, 2022 at 18:03 Mladen Gogala wrote: On 11/30/22 18:19, Hannes Erven wrote: You could also use a filesystem that can do atomic snapshots - like ZFS. Uh, oh. Not so sure about that. Here is a page from the world of the b

Re: how to secure pg_hba.conf

On 12/1/22 07:45, Rizwan Shaukat wrote: Hi, we hv requiremnt from security to secure pg_hba.conf file was encryption or password protected on server to protect ip visibilty because these server access by application n thy can amend as well. how we can achive it pls pg_hba.conf should only be

Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

On 12/4/22 11:05, Alban Hertroys wrote: On 3 Dec 2022, at 20:55, Karsten Hilbert wrote: You would need to wrap the function creation calls into some automation to generate and store those diffs, comparing it back, etc, but that may be doable. I would also generate new diffs right after major

Re: Inheritance pg_largeobject table

On 12/8/22 23:08, Zhao, Bing wrote: *Categorization: Unclassified * We are running PG11.11, and have more than 50T LO data about load into the pg_largeobject table. But 32T is the limitation. We have created couple child tables that using inheritance to pg_largeobject, and we have tried use

Re: Test if a database has any privilege granted to public

Off-topic, but you don't need all those text casts. On 12/14/22 23:44, Bryn Llewellyn wrote: I want to adopt a rule that no database in my cluster has any privilege granted to public. It suits me best to encapsulate the test as a boolean function thus: *function mgr.db_has_priv_granted_to_pub

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

If a hacker gets in with root access, they can copy /your/ (and my) Postgresql database files (or, more easily, the backup files) off site, restore them, and then have access to your database.  Not so much TDE-encrypted databases, since the backups are encrypted too and you need the key to decr

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

And encrypting a tar.gz file presumes a pretty small database.  (The --jobs= option was added to pg_dump/pg_restore for just that reason.) On 12/21/22 16:25, Benedict Holland wrote: What would you be missing? You can encrypt databases. You can encrypt the s3 buckets using kms. You can govern ac

Re: best practice to patch a postgresql version?

Just downgrade the packages if you need to revert to a previous version. Remove the 14*.5* package, and install the 14*.4* package (because no one's crazy enough to start with 14.0 in December 2022).  You'll have to explicitly specify the version number. On 12/26/22 03:29, qihua wu wrote: We

Re: best practice to patch a postgresql version?

23:33, qihua wu wrote: Thanks Ron, But on a critical production database, we need to cut down the downtime as much as possible. If just remove a version, and then install a new version, both of them need a downtime. If we can install several versions on different location, switching version will h

Re: Regd. the Query w.r.t Alternative functionalities from Oracle  PostgreSQL (Oracle to PostgreSQL database migration)

On 12/28/22 20:15, rob stone wrote: On Wed, 2022-12-28 at 09:45 -0800, Adrian Klaver wrote: On 12/23/22 01:37, Chetan Kosanam wrote: TCS Confidential See here: https://www.postgresql.org/support/professional_support/ Also there is ora2pg. See https://ora2pg.darold.net/ That does a gre

Re: Purging few months old data and vacuuming in production

On 12/30/22 00:39, Ranjith Paliyath wrote: Hi, We have a PostgreSQL (slightly old version, something like - PostgreSQL 11.2 You know, of course, that you should update to the latest version. It's quick and painless. on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8

Re: Inserts create new records in reporting table

200K *total* records, or 200K records per day/month/quarter/etc?  Because 200K records isn't that much on modern hardware.  Heck, it wasn't much 20 years ago on "Enterprise" hardware. And you don't mention the window for loading the data into "the" table, and then into the reporting table. A

Re: REINDEX vs VACUUM

I don't think VACUUM FULL (copy the table, create new indices and other metadata all in one command) actually vacuums tables.  It's a misleading name. Something like REBUILD TABLE would be a better name. On 1/4/23 07:25, Rébeli-Szabó Tamás wrote: Here is my understanding: REINDEX recreates th

Re: PG replicas and transactions atomicity

On 1/5/23 14:09, Christophe Pettus wrote: On Jan 5, 2023, at 12:07, Louis Laborde wrote: Are PG replicas updated atomically following the same transactions boundaries as the source DB ? Yes. The same transactional guarantees apply to the replica as do to the original transactions on the pri

Re: best practice to patch a postgresql version?

On 1/5/23 23:43, Laurenz Albe wrote: On Tue, 2022-12-27 at 00:48 -0600, Ron wrote: If it really is a critical production database, you will have a CAT/UAT (customer/user acceptance testing) server on which you rigorously run regression tests on a point release for a month before updating the

Re: Purging few months old data and vacuuming in production

On 1/6/23 02:44, Ranjith Paliyath wrote: Thank you for the details, experience shared and the suggestions. Apologies for the delay in collecting the response for the queries. (1)Are the tables tied together by FK? - Overall there are 9 tables (sorry not 6 as mentioned originally) that are be

Re: Purging few months old data and vacuuming in production

On 1/6/23 08:27, Ranjith Paliyath wrote: Thank you very much for the response. > Can you do online purging? > For example, get a list of the main table's primary keys to be deleted, and > then nibble away at them all day: in one transaction delete all the records > for one

Re: Purging few months old data and vacuuming in production

On 1/7/23 05:29, Peter J. Holzer wrote: [snip] If I understood correctly, you have to delete about 3 million records (worst case) from the main table each day. Including the other 8 tables those are 27 million DELETE queries each of which deletes only a few records. That's about 300 queries per s

Re: How do the Linux distributions create the Linux user/group "postgres"?

On 1/9/23 07:15, Joe Conway wrote: On 1/9/23 07:41, Matthias Apitz wrote: Please note: I'm talking about the user and group "postgres" in the Linux OS and not in the PostgreSQL server. We're compiling PostgreSQL from source (actually 14.1) and distribute that to our customers. They're asked to

Re: pg_multixact_member file limits

On 1/9/23 22:51, Martin Ritchie wrote: Are there any limits on the number of records in the postgresql/12/main/pg_multixact/members directory? We have a database that has grown to tens of thousands of files in this directory during an autovacuum after a large data purge. It shrank after the aut

Re: Use case for enabling log_duration other than benchmarking

On 1/10/23 07:14, Alicja Kucharczyk wrote: Do you know any use case for enabling log_duration? Like 3rd party tools for instance. I find this parameter pretty much useless (in opposite to log_min_duration_statement) as it does not show the query text, so besides having just the timing logged it

Re: Use case for enabling log_duration other than benchmarking

On 1/10/23 09:57, Alicja Kucharczyk wrote: wt., 10 sty 2023 o 14:57 Ron napisał(a): On 1/10/23 07:14, Alicja Kucharczyk wrote: Do you know any use case for enabling log_duration? Like 3rd party tools for instance. I find this parameter pretty much useless (in opposite to

Changing displayed time zone in RAISE NOTICE output?

How do I get clock_timestamp() to display the time in a different time zone? This is America/Chicago, but I'd like to display it in a different TZ. psql (12.12 (Ubuntu 12.12-1.pgdg18.04+1)) postgres=# DO $$ postgres$# BEGIN postgres$# RAISE NOTICE '%', clock_timestamp(); postgres$# END$$; NOTICE

Re: Changing displayed time zone in RAISE NOTICE output?

On 1/11/23 15:06, Adrian Klaver wrote: On 1/11/23 13:00, Ron wrote: How do I get clock_timestamp() to display the time in a different time zone? This is America/Chicago, but I'd like to display it in a different TZ. psql (12.12 (Ubuntu 12.12-1.pgdg18.04+1)) postgres=# DO $$ postgres$#

EXPLAIN and FK references?

Pg 12.11 Deletes are slow in one table with many indices and FK references. That's not surprising, but it's *VERY* slow, and I'm trying to figure out why. Is there any EXPLAIN option which shows what "query plans" Pg is using when checking FK references (index scan, seq scan, etc) during dele

Re: EXPLAIN and FK references?

On 1/12/23 00:07, Tom Lane wrote: Ron writes: Deletes are slow in one table with many indices and FK references. That's not surprising, but it's *VERY* slow, and I'm trying to figure out why. Is there any EXPLAIN option which shows what "query plans" Pg is using wh

Re: EXPLAIN and FK references?

On 1/12/23 01:11, Tom Lane wrote: Ron writes: On 1/12/23 00:07, Tom Lane wrote: No, not directly, but you could look at EXPLAIN ANALYZE to see which of the RI triggers is eating the time. Good to know, but even deleting one day of data (90,000 rows using an index scan on the date field

Re: AW: [Extern] Re: postgres restore & needed history files

On 1/12/23 10:50, Zwettler Markus (OIZ) wrote: [snip] What would you do in case of a disaster when all history files in pg_wal are gone and also deleted in the backup due to the backup retention? Yet another reason why you should not roll your own PITR backup solution.  Use something like pgB

gexec from command prompt?

Postgresql 12.11 This might be more of a bash question, or it might be a psql vs engine problem. I want to run this query using psql from a bash prompt: select format('SELECT ''%s'', MIN(part_date) FROM %s;', table_name, table_name) from dba.table_structure order by table_name\gexec Thus, I add

Directly embedding a psql SET variable inside another string?

Pg 12 I need to pass a \set variable (in this example named v_ssn) into a LIKE string.  A two-stage process (building v_like from v_ssn, and then using v_like in the LIKE string) works, and is fine when executing an sql script, but not so good  is there any way to *directly* embed v_ssn in an

Re: Directly embedding a psql SET variable inside another string?

On 1/13/23 11:13, David G. Johnston wrote: On Fri, Jan 13, 2023 at 9:12 AM Ron wrote: is there any way to *directly* embed v_ssn in another string? No As expected, this fails: postgres=# SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%'; ERROR:  syntax error

Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

We regularly use "history" databases.  Put them on slow media, and only take a backup when data is added to them (monthly, quarterly, etc). On 1/15/23 15:57, HECTOR INGERTO wrote: > But you cannot and should not rely on snapshots alone That’s only for non atomic (multiple pools) snapshots. Is

Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"

On 1/16/23 07:11, Laurenz Albe wrote: On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote: This is a puzzle I have not been able to crack yet. We have a single-page table with 28 rows that is purely read-only. There isn't a way in postgres to make a table RO, but I say this with confidence be

Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound"

On 1/16/23 15:46, Rob Sargent wrote: On 1/16/23 14:18, Ron wrote: On 1/16/23 07:11, Laurenz Albe wrote: On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote: This is a puzzle I have not been able to crack yet. We have a single-page table with 28 rows that is purely read-only. There isn

Maintaining blank lines in psql output?

White space can of course make things easy to read, but psql seems to ignore those blank lines.  Is there any way to retain them in psql output? $ cat spaces.sql insert into foo values(1); insert into foo values(2); insert into foo values(3); insert into foo values(4); insert into bar value

Re: Maintaining blank lines in psql output?

On 1/17/23 15:22, David G. Johnston wrote: On Tue, Jan 17, 2023 at 1:48 PM Ron wrote: White space can of course make things easy to read, but psql seems to ignore those blank lines.  Is there any way to retain them in psql output? Nope, there is no setting for psql to print all

Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools?

On 1/18/23 10:54, Rob Sargent wrote: On 1/18/23 09:38, HECTOR INGERTO wrote: I wanted to understand the underlying issue. I use ZFS snapshots instead of a “correct” backup because with only two machines it allows me to have backups in the main machine and in the secondary too that acts as ho

Re: Database size different on Primary and Standby?

On 1/18/23 17:09, Hilbert, Karin wrote: I manage some PostgreSQL clusters on Linux.  We have a Primary & two Standby servers & for Production, there is also a DR server.  We use repmgr for our HA solution & the Standbys are cloned from the Primary using the *repmgr standby clone* command. My

Re: [EXT] Re: DBeaver session populating pg_stat_activity.backend_xmin

On 1/25/23 16:21, Dirschel, Steve wrote: [snip] The problem is users will connect using DBeaver and their sessions will sit idle. Idle is not a problem, "idle in transaction" is. From my perspective "idle in transaction" isn't necessarily a problem (although I don't like seeing sessions sitti

Re: Sequence vs UUID

On 1/26/23 15:55, Erik Wienhold wrote: On 26/01/2023 20:17 CET veem v wrote: Hello, We were trying to understand whether we should use UUID or Sequence in general for primary keys. In many of the blogs (one is below) across multiple databases, I saw over the internet and all are mostly stating

Re: Indexes mysteriously change to ON ONLY

I cheat by using sed to remove "ONLY ON " from the CREATE statements. On 1/27/23 15:30, Rumpi Gravenstein wrote: Tom/Christophe  I now understand.  Thanks for the clear explanation. On Fri, Jan 27, 2023 at 4:16 PM Tom Lane wrote: Rumpi Gravenstein writes: > We are using the pg_indexe

Re: Sequence vs UUID

hanks, Ben On Sat, Jan 28, 2023, 3:39 PM Erik Wienhold wrote: > On 27/01/2023 01:48 CET Ron wrote: > > On 1/26/23 15:55, Erik Wienhold wrote: > > > > There are arguments against sequential PK, e.g. they give away too much info and > > allow a

Re: Sequence vs UUID

Then it's not a Type 4 UUID, which is perfectly fine; just not random. Also, should now() be replaced by clock_timestamp(), so that it can be called multiple times in the same transaction? On 1/28/23 21:28, Miles Elam wrote: On Sat, Jan 28, 2023 at 6:02 PM Ron wrote: Type 4 UUIDs ar

Re: How to control pg_catalog results for each users?

On 1/30/23 02:41, Laurenz Albe wrote: On Mon, 2023-01-30 at 14:00 +0900, hirose shigeo(廣瀬 繁雄 □SWC○ACT) wrote: all users can get the all of table name , table structure and other information from pg_catalog, which is considered a security problem. The belief that restricting that will improve se

Re: Sequence vs UUID

And populate that column with UUIDs generated by the gen_random_uuid() function. (Requires v13.) On 1/30/23 13:46, Adrian Klaver wrote: On 1/30/23 11:43, veem v wrote: Thank You So much for the details. I am a bit new to postgres. And these test results I picked were from a dev system. If I

"SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?

v12.13 https://www.postgresql.org/docs/12/sql-select.html The docs say that one of these are required in the SELECT list. [ * |/|expression|/ [ [ AS ]/|output_name|/ ] [, ...] ] However, *not* mentioning anything also works, though acts like COUNT(*). test=# select * from sales_detail;  cus

Re: Sequence vs UUID

On 2/2/23 17:11, Peter J. Holzer wrote: On 2023-02-02 10:22:09 -0500, Benedict Holland wrote: Well... until two processes generate an identical UUID. That happened to me several times. How did that happen? Pure software implementation with non-random seed? Hardware with insufficient entropy sou

Re: Switching identity column to serial

On 2/3/23 18:54, Erik Wienhold wrote: I was wondering if it's possible to drop a column identity (not the column itself) while keeping the attached sequence. This would avoid recreating an identical sequence (especially with a correct start value and owner). Why doesn't this work? BEGIN; DROP

Re: Switching identity column to serial

On 2/3/23 22:41, Ron wrote: On 2/3/23 18:54, Erik Wienhold wrote: I was wondering if it's possible to drop a column identity (not the column itself) while keeping the attached sequence. This would avoid recreating an identical sequence (especially with a correct start value and owner).

Re: Question regarding UTF-8 data and "C" collation on definition of field of table

Why are you specifying the collation to be "C" when the default db encoding is UTF8, and UTF-8 has Greek, Chinese and English encodings? On 2/5/23 17:08, Dionisis Kontominas wrote: Hello all,   I have a question regarding the definition of the type of a character field in a table and more spe

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

On 2/7/23 09:06, Adrian Klaver wrote: On 2/7/23 06:09, Philip Semanchuk wrote: On Feb 7, 2023, at 3:30 AM, Laurenz Albe wrote: On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote: I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like to change the {my_expressi

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

On 2/7/23 09:06, Adrian Klaver wrote: On 2/7/23 06:09, Philip Semanchuk wrote: On Feb 7, 2023, at 3:30 AM, Laurenz Albe wrote: On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote: I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like to change the {my_expressi

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

Ignore this... On 2/7/23 13:05, Ron wrote: On 2/7/23 09:06, Adrian Klaver wrote: On 2/7/23 06:09, Philip Semanchuk wrote: On Feb 7, 2023, at 3:30 AM, Laurenz Albe wrote: On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote: I have a column defined GENERATED ALWAYS AS {my_expression

Re: How to use the BRIN index properly?

Is the data in your tables stored in natural correlation with those *three* columns?  I'm dubious that can even happen. BRIN is best for *range queries* on tables who's data is added in the same order as the key in the BRIN index (for example, a BRIN index on a timestamp field in a log table w

Re: How to use the BRIN index properly?

1. The whole index does not need to fit in memory, just the parts of it you need at that time. 2. Partition the table by the primary key.  Each index will be *much* smaller, since each child will be smaller. On 2/8/23 16:14, Siddharth Jain wrote: OK so in that case we are left with the B-Tree

Re: psql "\d" no longer working

On 2/12/23 03:02, Rob Sargent wrote: Seems I've lost the table definition meta-command riftehr=> \d actual_and_inf_rel_clean_final ERROR:  column c.relhasoids does not exist LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi... while listing tables still works

Re: Query plan for "id IS NULL" on PK

On 2/14/23 18:21, David Rowley wrote: [snip] since it likely only applies to nearly zero real-world cases Are you sure? -- Born in Arizona, moved to Babylonia.

Re: Multi-column index: Which column order

On 2/15/23 02:46, Laurenz Albe wrote: [snip] Which one is best? CREATE UNIQUE INDEX ix1 ON art (code, etb) or CREATE UNIQUE INDEX ix1 ON art (etb, code) (or its PRIMARY KEY equivalent) Both are the same. There is an old myth that says that you should use the moew selective column first (whic

Re: Multi-column index: Which column order

On 2/15/23 21:45, Laurenz Albe wrote: On Wed, 2023-02-15 at 10:20 -0600, Ron wrote: On 2/15/23 02:46, Laurenz Albe wrote: [snip] Both are the same. There is an old myth that says that you should use the more selective column first (which would be "code"), but that is just a myth

Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

On 2/16/23 09:47, cen wrote: Hi, I am running the same application (identical codebase) as two separate instances to index (save) different sets of data. Both run PostgreSQL 13. The queries are the same but the content in actual databases is different. One database is around 1TB and the othe

Re: Repear operations on 50 tables of the same schema?

On 2/27/23 05:53, celati Laurent wrote: Good morning, I am new to Postgresql. I have 50 tables into a "ign" schema (schema other than public). I would like for each of these 50 tables: - Add a prefix to the name of the table: "IGN_bdTopo_" - Add a suffix to the table name: "_V1" - create a ne

pg_get_functiondef(), trailing spaces and + sign

Is there any direct way in Postgresql to get rid of the frankly anti-useful junk at the end of each line (which also infects pg_stat_activity.query), or must I resort to sed post-processing? test=# select pg_get_functiondef(oid) test-# from pg_proc test-# where proname = 'foo';    

Re: Quit currently running query

On 2/28/23 04:53, Albert Cornelius wrote: How can I quit a currently running query? I've issued a query and my server does not respond anymore. Is there another solution than using kill -9? What *specifically* do you mean by "my server does not respond anymore"?  Because if "the server" /reall

Re: Quit currently running query

On 2/28/23 11:31, Peter J. Holzer wrote: On 2023-02-28 07:42:08 -0600, Ron wrote: On 2/28/23 04:53, Albert Cornelius wrote: How can I quit a currently running query? I've issued a query and my server does not respond anymore. Is there another solution than using kill -9?

Converting row elements into a arrays?

Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.  f1 | f2_array +- 1 | {1,2,3}   2 | {1,2,3,4}   3 | {1,2} The ultimate goal is to somehow use pg_index.indkey to get col

Re: Converting row elements into a arrays?

On 3/2/23 15:34, David G. Johnston wrote: On Thu, Mar 2, 2023 at 1:58 PM Ron wrote: Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number. Group By crea

Re: Converting row elements into a arrays?

On 3/2/23 15:45, Rob Sargent wrote: On 3/2/23 13:58, Ron wrote: Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.  f1 | f2_array +- 1 | {1,2,3}   2 | {1,

Re: Converting row elements into a arrays?

On 3/2/23 15:01, Ray O'Donnell wrote: On 02/03/2023 20:58, Ron wrote: Postgresql 12.13 Given the sample below, I'm looking for how to generate this output.  It's like GROUP BY, but generating an array instead of an aggreate number.   f1 | f2_array +- 1 | {1,2,3}

Re: Dropping behavior for unique CONSTRAINTs

On 3/3/23 04:54, David Rowley wrote: On Fri, 3 Mar 2023 at 23:17, Conner Bean wrote: I wanted to avoid using a unique index since dropping them requires an exclusive lock and cannot be done concurrently. My thought was to then use a unique constraint, since I've read unofficial docs[0] that say

Re: Dropping behavior for unique CONSTRAINTs

On 3/4/23 02:03, Peter J. Holzer wrote: [snip] So your plan is to create a unique constraint (backed by a unique index) and then to drop the index and keep the constraint? That doesn't work. A unique constraint can't exist without a (unique) index. Think about it: With a unique constraint Postgr

Re: Dropping behavior for unique CONSTRAINTs

On 3/4/23 05:51, Peter J. Holzer wrote: On 2023-03-04 02:34:02 -0600, Ron wrote: On 3/4/23 02:03, Peter J. Holzer wrote: [snip] So your plan is to create a unique constraint (backed by a unique index) and then to drop the index and keep the constraint? That doesn't work. A unique const

psql \set variables in crosstab queries?

According to https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-VARIABLES and experience, variables don't interpolate inside of string literals: " Variable interpolation will not be performed within quoted SQL literals and identifiers. Therefore, a construction such as ':foo' doesn't

Re: psql \set variables in crosstab queries?

On 3/4/23 19:22, Tom Lane wrote: Ron writes: According to https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-VARIABLES and experience, variables don't interpolate inside of string literals: " Variable interpolation will not be performed within quoted SQL literals and i

Re: psql \set variables in crosstab queries?

On 3/4/23 19:32, David G. Johnston wrote: On Sat, Mar 4, 2023 at 5:20 PM Ron wrote: But crosstab takes text strings as parameters.  How then do you use \set variables in crosstab queries? You need to dynamically write the textual query you want to send to the crosstab function.  In

Re: How does Postgres store a B-Tree on disk while using the OS file system?

On 3/6/23 18:24, Siddharth Jain wrote: I am trying to sharpen my understanding of Postgres. As I understand, Postgres does not write directly to disk blocks. It uses the file system provided by the OS: https://dba.stackexchange.com/questions/80036/is-there-a-way-to-store-a-postgresql-database-d

psql \conninfo in tabular form?

v13.10 Instead of a sentence like this: You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5433". I'd rather have something tabular like:    keyword  |   value +---   database  | postgres   user      | postgr

Re: Behavior of PL/pgSQL function following drop and re-create of a table that it uses

On 3/8/23 15:29, Bryn Llewellyn wrote: [snip] create table s.t(k int primary key, c1 int, c2 int, c3 int); insert into s.t(k, c1, c2, c3) values(1, 17, 42, 57); create type s.x as (c1 int, c2 int, c3 int); [snip] This is an excellent analysis. Native PG doesn't provide much metadata or tool

Re: Practice advice for use of %type in declaring a subprogram's formal arguments

On 3/10/23 15:28, Bryn Llewellyn wrote: I'm thinking about "language plpgsql" subprograms—but I expect that my question can be generalized to cover "language sql" too. The payload for "create [or replace]" for a "language plpgsql" subprogram specifies various metadata elements like its qualifi

Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay

On 3/11/23 14:23, Atul Kumar wrote: Hi, Could someone help me in telling the difference between these three parameters 1. max_standby_archive_delay 2. max_standby_streaming_delay 3. recovery_min_apply_delay My basic motive is to make the standby database server to be delayed to apply the cha

Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay

On 3/12/23 09:01, Laurenz Albe wrote: On Sun, 2023-03-12 at 01:53 +0530, Atul Kumar wrote: Could someone help me in telling the difference between these three parameters 1. max_standby_archive_delay 2. max_standby_streaming_delay 3. recovery_min_apply_delay My basic motive is to make the standb

Re: Getting error while upgrading postgres from version 12 to 13

On 3/18/23 15:39, shashidhar Reddy wrote: Hello, I am in a process of upgrading postgresql from version 12 to 13 using pg_upgrdrade but I am getting error stating  FATAL:  database files are incompatible with server  DETAIL:  The data directory was initialized by PostgreSQL version 13 , which

Re: PostgreSQL vs MariaDB

What RDBMS is your data currently on? On 3/25/23 01:15, Inzamam Shafiq wrote: Hi Ben, We have a complex running, and we will be having a lot of Insert, update and deletes. We have many partitioned tables with huge data and some complex SQL is written at the application logic, some analytical

Re: Cluster table based on grand parent?

On 3/28/23 08:17, Dominique Devienne wrote: Hi again, I just sent a question regarding parent/child and cascading FKs. But in reality, our schema has not 2 but 3 "layers", with an additional grandchild "leaf" table (see below). Given that many acces patterns are parent-based, i.e. get all child

Re: Cluster table based on grand parent?

On 3/28/23 11:28, Dominique Devienne wrote: On Tue, Mar 28, 2023 at 6:06 PM Ron wrote: You can only get from parent to grandchild via//child.id <http://child.id> to grandchild.parent, so why not cluster grandchild on grandchild.parent? Hi. I don't understand your qu

COPY and custom datestyles. Or some other technique?

Postgresql 13.10 $ psql -h myhost.example.com -X dba \     -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH DELIMITER '|';" ERROR:  date/time field value out of range: "2013061914122501" CONTEXT:  COPY t_id_master, line 1, column update_timestamp: "2013061914122501" The timestamp

Re: COPY and custom datestyles. Or some other technique?

On 3/29/23 19:20, Thorsten Glaser wrote: On Wed, 29 Mar 2023, Ron wrote: There are 550+ tables, so something that I can do once on this end would make my life a lot easier. Some quick perl or awk or shell job to batch-change the field to an accepted syntax is probably quicker. Even easier

Re: COPY and custom datestyles. Or some other technique?

On 3/29/23 18:31, Adrian Klaver wrote: On 3/29/23 16:24, Ron wrote: Postgresql 13.10 $ psql -h myhost.example.com -X dba \ -c "\copy ${tbl} from '/var/lib/pgsql/Rdb/${tbl}.csv' WITH DELIMITER '|';" ERROR:  date/time field value out of range: &qu

Re: COPY and custom datestyles. Or some other technique?

On 3/29/23 21:06, David G. Johnston wrote: On Wed, Mar 29, 2023 at 6:51 PM Ron wrote: It would be really helpful to be able to reposition columns in tables.  That way, one could: add the new TIMESTAMP column, populate it using to_timestamp(), drop the text column

Re: Patroni vs pgpool II

On 4/3/23 01:33, Inzamam Shafiq wrote: Hi Guys, Hope you are doing well. Can someone please suggest what is one (Patroni vs PGPool II) is best for achieving HA/Auto failover, Load balancing for DB servers. Along with this, can you please share the company/client names using these tools for l

Re: Patroni vs pgpool II

On 4/6/23 23:16, Tatsuo Ishii wrote: But, I heard PgPool is still affected by Split brain syndrome. Can you elaborate more? If more than 3 pgpool watchdog nodes (the number of nodes must be odd) are configured, a split brain can be avoided. Split brain is a hard situation to avoid. I suppose OP

Re: Patroni vs pgpool II

On 4/7/23 05:46, Jehan-Guillaume de Rorthais wrote: On Fri, 07 Apr 2023 18:04:05 +0900 (JST) Tatsuo Ishii wrote: And I believe that's part of what Cen was complaining about: « It is basically a daemon glued together with scripts for which you are entirely responsible for. Any small mist

Re: FW: Error!

Are you sure that you're entering the correct password? On 4/10/23 19:55, Arquimedes Aguirre wrote: Sent from Mail for Windows *From: *Arquimedes Aguirre *Sent: *Sunday, April 9, 2023 5:59 PM *To: *pgsql-advoc...@pos

<    1   2   3   4   5   6   7   8   9   10   >