Re: Content for talk on Postgres Type System at PostgresConf

On 2/29/24 14:47, Guyren Howe wrote: What is worth saying about custom range types? What even would be a good use case? A range of strings or ip address ranges, something like that, I suppose? I wrote a blog post and an extension for an inetrange type. It's from 2016 so it might need some

Re: Need efficient way to remove (almost) duplicate rows from a table

On 10/17/23 15:48, David Gauthier wrote: I have a table with millions of records and there are a lot of "almost" duplicates that I want to get rid of in an efficient way.  Best to illustrate with a simple example... This sounds like a classic use-case for a "correlated subquery". Assuming

psql -f and PAGER

Hello, I noticed that this runs your pager: psql -f <(echo 'select * from pg_class;') but not this: echo 'select * from pg_class;' | psql A client encountered this when the psql command run from their deb's .postinst file started to hang. We can prevent it with PSQL_PAGER='', but

Re: a very naive question about table names in Postgres

On 12/31/21 12:27 PM, Martin Mueller wrote: I know that on my Mac the tables are  kept in the  data directory /Users/martinmueller/Library/Application Support/Postgres/var-13.  If I go there I see that internally the tables have numbers.  Somewhere in the system there must be a table that maps

Re: Incremental backup

On 10/28/21 5:23 AM, Edu Gargiulo wrote: Is there any way to make incremental backups in postgres-12? wal-e and wal-g are useful tools for this and easy to set up. They take periodic full backups and then also continuously archive WAL files. To restore they apply the last full backup and

Re: Temporal tables as part of main release

On 6/17/21 11:10 PM, David G. Johnston wrote: And, since its not in the commitfest app, and the last emails I found are over a year old, it doesn’t seem to be a priority for anyone at this time. I'm working on the application-time (aka valid-time) side of this, and there is another patch for

Re: Treating float arrays as vectors?

On 6/17/21 7:13 AM, Celia McInnis wrote: I would love it if there was a vector data type in postgresql along with such vector operations as addition, subtraction, scalar multiplication, cross product, dot product, normalization, length and various sorts of vector distances. I wrote an

Re: Slick way to update multiple tables.

On 4/1/21 11:54 AM, Michael Lewis wrote: postgresql.org/docs/current/sql-createview.html My apologies. It seems INSTEAD OF triggers are required to implement updates across multiple tables. I thought not if all were simple joins. My

Re: Enforcing uniqueness on [real estate/postal] addresses

On 5/11/20 9:55 AM, Peter Devoy wrote: Of course, if any of the fields are NULL (which they often are) I end up with duplicates. One solution may be to add NOT NULL constraints and use empty strings instead of NULL values but, until asking around today, I thought this was generally considered

Re: Reg: Help to understand the source code

On 4/23/20 8:44 AM, Preethi S wrote: I am fairly new to postgres and I am trying to understand how the data is processed during the insert from buffer to the disk. Can someone help me with that? Also, I would like to see source code workflow. Can someone help me with finding the source code

Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

On 2/25/20 11:46 AM, Stanislav Motycka wrote: Dňa 25. 2. 2020 o 20:28 Paul A Jungwirth napísal(a): I take the proposal to mean this: SELECT listOfColumns [EXCEPT listOfColumns] FROM ... Exactly, simply exclude unneeded columns from the base clause "SELECT", nothing more .. Not that this

Re: Issue upgrading from V11 to V12 on Debian

On 12/3/19 9:41 AM, stan wrote: Well, this gets more interesting. I decided to try the reinstall thing agai, so I did an apt-get purge on the V12 server, started the V11 server (admiitely I did not check to see what port it started on), reinstalled the V1 package, and now BOTH are running, with

Re: Issue upgrading from V11 to V12 on Debian

On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on the machine I am testing this on now: Ver Cluster Port Status OwnerData directory Log file 11 main5433 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log 12 main

Re: Issue upgrading from V11 to V12 on Debian

On 12/3/19 7:53 AM, stan wrote: I am working on upgrading from V11 to V12 on Debian. My first attempt failed, and I have figured out that this is because I have added extensions to the V11 DB, at least one of which was not installed using the Debian packages. So, it looks like i need to

Re: Pg_auto_failover

On 9/25/19 8:55 AM, Sonam Sharma wrote: My user has permission to touch inside the Fs. I am not getting why it is checking for /backup. Any suggestions on this , what else I can check In addition to the code already linked to, I would look at

Re: When does Postgres use binary I/O?

On 9/18/19 7:26 AM, Tom Lane wrote: The core regression tests don't systematically exercise binary I/O, and they certainly wouldn't magically cover a new type they didn't use to. You'd need to add test case(s). Thanks! I thought psql might use binary IO internally when available, or at least

Re: How to handle things that change over time?

On 9/13/19 2:57 AM, stan wrote: I am working on a system that will support internal bossiness work for a company. Periodicly things will change in their "world". Oh this is my favorite topic. :-) It's a common problem, although solutions don't seem to be well-known in the programming

Re: Conditional INSERT

On 3/15/19 10:55 AM, basti wrote: I want to insert data into table only if condition is true. For example: INSERT into mytable (domainid, hostname, txtdata) VALUES (100,'_acme.challenge.example', 'somedata'); The insert should only be done if Hostname like %_acme.challenge%. I would use

Re: Shared hosting with FDW on AWS RDS

On 2/10/19 2:57 PM, auxsvr wrote: We'd like to configure an RDS server for shared hosting. The idea is that every customer will be using a different database and FDW will be configured, so that the remote tables have access to the full data I've set up something like this before (but on

Re: nested query problem

On 09/06/2018 01:59 PM, David Gauthier wrote: I'm having trouble with this query... select sr.sqf_id, sr.status, fse.perl_sub_name, fse.end_datetime   from     sqf_runs sr,     (select perl_sub_name, end_datetime from flow_step_events_view where sqf_id = sr.sqf_id order by 2 limit 1) fse  

Full table lock dropping a foreign key

Hello, I noticed that Postgres takes an AccessExclusiveLock (a lock on the whole table) against the *referenced* table when dropping a foreign key. I wasn't expecting that, and some experimentation showed it does *not* take one when creating the FK. For example: pjtest=# create table parent

Re: When exactly is a TIMESTAMPTZ converted to the sessions time zone?

On 08/09/2018 01:49 PM, Bruce Momjian wrote: On Tue, Jul 3, 2018 at 05:00:17PM -0400, Tom Lane wrote: Thomas Kellerer writes: A recent discussion around timestamptz behaviour has lead me to question my own understanding on how a TIMESTAMPTZ is converted to the session's time zone. I assumed

Re: Is postorder tree traversal possible with recursive CTE's?

On 06/19/2018 02:05 PM, Alban Hertroys wrote: On the more theoretical front: The question remains whether it is possible to calculate fields in post-order tree traversal. I think that would be a semantically proper way to express this type of problem and it wouldn't need the kinds of

Performance opportunities for write-only audit tables?

Hi, A project of mine uses a trigger-based approach to record changes to an audit table. The audit table is partitioned by month (pg 9.5, so old-fashioned partitioning). These tables are write-heavy but append-only and practically write-only: we never UPDATE or DELETE, and we seem to consult

Extension make installcheck: include update/insert feedback?

Hello, I have a custom extension that uses the usual REGRESS Makefile variable to indicate files in {sql,expected} that should be used when you say `make installcheck`. I've noticed that if my test code does an INSERT or DELETE, the usual `INSERT 0 1` and `UPDATE 2` messages don't appear in

Re: How to get an inclusive interval when using daterange

On 04/03/2018 09:40 AM, hmidi slim wrote: I tried insert into availability values ('product x', '[2018-02-02,2018-03-01]'::daterange); and I got the same result such as insert into availability values ('product x', daterange('2018-02-02', '2018-03-01', '[]'). Yes, those are equivalent ways

Re: Using Lateral

On 03/28/2018 05:22 AM, Johann Spies wrote: Thanks Paul. I was hesitant to post my full query. It is a long and complicated query. Ha ha, you aren't joking. :-) With something that long I don't think I'd want to split every xpath call into a new join. I guess you could try it and see

Re: Proposition for better performance

On 03/27/2018 11:42 AM, hmidi slim wrote: This is the message that I got: Successfully run. Total query runtime: 2 min. 1500 rows affected. Sorry, I don't think this is enough information to suggest anything. -- Paul ~{:-) p...@illuminatedcomputing.com

Re: Proposition for better performance

On 03/27/2018 11:14 AM, hmidi slim wrote: Query plan:* Bitmap Heap Scan on product_availabilities  (cost=33728.79..236086.04 rows=878500 width=26) (actual time=2775.058..5792.842 rows=1500 loops=1)   Recheck Cond: (during @> '[2018-02-01,2018-04-01)'::daterange)   Heap Blocks:

Re: Proposition for better performance

(Including the list) On 03/27/2018 10:49 AM, hmidi slim wrote: Sorry I didn't copy it very well: create index idx on availability using gist(during); and during = daterange(start_date,end_date) And the query plan used was seq scan not index scan. It sounds like there must be some

Re: Proposition for better performance

On 03/27/2018 10:04 AM, hmidi slim wrote: the query that I used to fetch products was: select * from availability where ('27-03-2018' between start_date and end_date) and ('31-03-2018' between start_date and end_date); I added another column named during of type daterange and I created a gist

Re: Proposition for better performance

On 03/27/2018 07:42 AM, hmidi slim wrote: Hi, I'm trying to create an availability table for existing products. I'm fetching the suitable schema to design in order to get good performance when I fetch products in a table contains millions of rows. I think to make a schema like this: *create

Re: Using Lateral

On 03/27/2018 03:22 AM, Johann Spies wrote: In the past I could use this in a query: SELECT DISTINCT ut, CASE WHEN xpath_exists ('//t:address_spec/t:country/text()', q.address_spec, p.ns) THEN unnest (xpath

Re: Autonomous transaction, background worker

I try to setup a logging facility and want it to do its work asynchronously in autonomous transactions. I have read http://blog.dalibo.com/2016/08/19/Autonoumous_transactions_support_in_PostgreSQL.html and chapter 47 of the documentation believing those articles are about the same. However,

Re: Check constraints.

On 03/27/2018 04:23 AM, Steve Rogerson wrote: I am trying to add/change a constraint programmatically, but not if it already exists, and is the same as before. ... Is there a way of getting the "normalised" version of constraint so decide if I need to update the constraint if one already exists?

Re: Primary key gist index?

On 03/14/2018 11:44 AM, Tom Lane wrote:> Also, as you mention, extrapolating behavior that's not really equality to situations like foreign keys gets pretty interesting pretty fast. An exclusion constraint using && might ensure that no two values in the column are identical, but it would not be

Re: Primary key gist index?

On 03/14/2018 06:19 AM, Jeremy Finzel wrote: Hello!  From all that I can tell, it is not possible using a btree_gist index as a primary key.  If so, why not?  I have a table with this gist index which truly ought to be its primary key.  as_of_date is of range date type: EXCLUDE USING gist

Re: system catalog permissions

On 02/26/2018 03:47 PM, Tom Lane wrote: PropAAS DBA writes: We have a client which is segmenting their multi-tenant cluster (PostgreSQL 9.6) by schema, however if one of their clients connects via pgadmin they see ALL schemas, even the ones they don't have access to read. PG

Re: Given a set of daterange, finding the continuous range that includes a particular date

On 02/22/2018 04:44 PM, Ken Tanzer wrote: Hi, hoping to get some help with this.  I'm needing to take a specific date, a series of dateranges  and, given a specific date, return a single conitinuous daterange that includes that date. The part about joining multiple touching dateranges to give

Re: Trigger (or something similar) on table rename?

On 02/15/2018 10:52 AM, Ken Tanzer wrote: Hi.  I'm wondering about possibilities for taking action when a table is renamed. I've looked into this a bit. Here is what I understand: Since 9.3 Postgres has had "event triggers" which can run code on DDL events

Re: Use of Port 5433 with Postgresql 9.6

On 01/01/2018 10:40 AM, Jeff Janes wrote: The Ubuntu packages use 5433 if you already have something (either a different packaged version, or an unpackaged system) running on 5432. Also on Ubuntu lots of the Postgres utilities will obey an envvar like `PGCLUSTER=9.5/main` to let you choose