Re: exclusion constraint question

Greetings, > I think I got it : > > ALTER TABLE shelves ADD CONSTRAINT shelves_excl EXCLUDE USING gist( > array_remove((ARRAY[l_mug_id,c_mug_id,r_mug_id]::int[]),NULL) WITH && ); > > but the caveat is I had to cast to 32-bit int array. Haven't found any > opclass for int8 (bigint) in intarray

exclusion constraint question

Greetings All, I have the following table: CREATE TABLE shelves( shelf_id bigint PRIMARY KEY, l_mug_id bigint UNIQUE, c_mug_id bigint UNIQUE, r_mug_id bigint UNIQUE, CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id <> r_mug_id), EXCLUDE USING gist (l_mug_i

Re: Dynamically accessing columns from a row type in a trigger

Hello again > > Actually, now that I'm thinking about it, I don't really want to store > > the value into a variable because the pk_col might be of any given > > type. So ideally, I'd love a way to just get the value from OLD and > > use it directly in another query. Something along the lines of

Re: Dynamically accessing columns from a row type in a trigger

or maybe look into using TG_ARGS. Rhys Peace & Love | Live Long & Prosper On Sat, Aug 12, 2023 at 3:31 PM Adrian Klaver wrote: > > On 8/12/23 13:09, Rhys A.D. Stewart wrote: > > Greetings all, > > > > I am writing a trigger and would like to know how to dynamicall

Dynamically accessing columns from a row type in a trigger

Greetings all, I am writing a trigger and would like to know how to dynamically access a column from the "OLD" variable. pk_col is the column name from the table. I've come up with either doing this: EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD; which looks a bit excessive, or

Getting data from a record variable dynamically

Greetings All, I have a trigger that is attached to several different tables. In the trigger function I retrieve a single row and I want the info from a specific column. This column is dependent on the table in question. and I have the column name stored in a variable as well. Without writing a co

Exclusion constraint with custom operator not working as expected

Greetings All, Firstly, apologies for cross posting. I would like to create a table which will contain postGIS geometries, specifically linestrings. Each line string should be unique, unique in the sense that no linestring should st_equals any other. (see https://postgis.net/docs/manual-3.1/ST_Eq

Re: Testing an extension against multiple versions of Postgresql

Thanks!! Rhys Peace & Love|Live Long & Prosper On Sat, Jul 13, 2019 at 3:15 AM Luca Ferrari wrote: > On Sat, Jul 13, 2019 at 8:03 AM Ian Barwick > wrote: > > > So the questions are: > > > Can I have make install & make installcheck run against multiple > versions at the same time or how do I

Testing an extension against multiple versions of Postgresql

Greetings All, I have both postgresql versions 10 and 11 installed on my box. I'm working on an extension and just realized that 'make install' installs the extension to the 11 server, but 'make installcheck' runs against the 10 server (I had previously installed the extension there). Running psq

Moving large table between servers: logical replication or postgres_fdw

Greetings Folks, I have a relatively large table (100m rows) that I want to move to a new box with more resources. The table isn't doing anything...i.e its not being updated or read from. Which approach would be faster to move the data over: a). Use pg_fdw and do "create local_table as select *

Re: postgres_fdw pushdown problem.

The devil really is in the details. > end might be using a different timezone setting.) I'm pretty sure > either timestamp = timestamp or timestamptz = timestamptz would be > pushable. Yeah, casting to plain old timestamp worked. Thanks Tom. Rhys Peace & Love|Live Long & Prosper

postgres_fdw pushdown problem.

Greetings all, I'm using postgres_fdw and am having a pushdown issue. The TL;DR is that the where clause doesn't get pushed down for the last of the following three queries. SELECT * FROM service.mrrdr_synth WHERE premises = '1057430'; SELECT * FROM service.mrrdr_synth WHERE reading_val = '8'; SE

Combining \i and \copy in psql

Greetings, I have a query in a .sql file and I'd like to use \i to execute it and \copy to save it to a csv file. Is there any way to combine the two? Something along the lines of: \copy \i myquery.sql to 'output.csv' Rhys Peace & Love|Live Long & Prosper

Re: array_agg and/or =ANY doesn't appear to be functioning as I expect

Hannes, Thanks for your observations.. Will take a look at the data. Regards, Rhys On Jan 20, 2018 11:00 PM, "Hannes Erven" wrote: Hi Rhys, Am 2018-01-21 um 02:42 schrieb Rhys A.D. Stewart: > Greetings All, > I'm having an issue which is very perplexing. T

array_agg and/or =ANY doesn't appear to be functioning as I expect

Greetings All, I'm having an issue which is very perplexing. The having clause in a query doesn't appear to be working as I expect it. Either that or my understanding of array_agg() is flawed. I'm using PostgreSQL 10.1, compiled by Visual C++ build 1800, 64-bit. The table in question looks like: