Re: [GENERAL] Full text search question: "01.Bez." --> "Erster Bezirk"

Hello, ​ On Sat, Mar 12, 2016 at 11:40 AM, Johann Höchtl wrote: > I fear I have an involved challenge concerning FTS. > > Assume I have the following text in a column: > > Graz,06.Bez.:Blah > > This parses as: > SELECT alias, description, token FROM ts_debug('german',

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 4:48 PM, Adrian Klaver wrote: > > >> As an example of where this leads see: >> >> >> http://www.postgresql.org/message-id/7224.1452275...@sss.pgh.pa.us >> >> ​Thanks for the heads up. The good news is all machine access to >>

[GENERAL] Check constraints and function volatility categories

Hello, I'm discovering that I need to write quite a few functions for use strictly w/ check constraints and I'm wondering if declaring the volatility category for said functions will affect their behavior when invoked by PostgreSQL's check constraint mechanism. Essentially what I'm trying to

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 3:22 PM, Dane Foster <studdu...@gmail.com> wrote: > > On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 02/01/2016 11:17 AM, Dane Foster wrote: >> >>> Hello, >>> >>> I'm

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 02/01/2016 11:17 AM, Dane Foster wrote: > >> Hello, >> >> I'm discovering that I need to write quite a few functions for use >> strictly w/ check constraints and I'm wond

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 02/01/2016 12:36 PM, David G. Johnston wrote: > >> On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdu...@gmail.com >> <mailto:studdu...@gmail.com>>wrote: >> >>

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 02/01/2016 11:17 AM, Dane Foster wrote: >> >>> Hello, >>> &

Re: [GENERAL] Check constraints and function volatility categories

On Mon, Feb 1, 2016 at 3:36 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdu...@gmail.com> wrote: > >> >> On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston < >> david.g.johns...@gmail.com>

[GENERAL] PL/pgSQL debugger

Hello, I googled "plpgsql debugger" and eventually ended up at http://pgfoundry.org/projects/edb-debugger/, where the latest release is almost 8 years old. I am aware that this is not an EDB forum but given that I sent them an e-mail at 9AM EST and I haven't received a response yet I figured it

Re: [GENERAL] PL/pgSQL debugger

> > wrote: > >> Hi >> >> 2016-01-29 21:52 GMT+01:00 Dane Foster <studdu...@gmail.com>: >> >>> Hello, >>> >>> I googled "plpgsql debugger" and eventually ended up at >>> http://pgfoundry.org/projects/edb-debugger/,

[GENERAL] A contradiction in 13.2.1

Hello, I'm trying to understand concurrency in PostgreSQL so I'm slowly reading through chapter 13 of the fine manual and I believe I've found a contradiction in section 13.2.1. ​My understanding of the second sentence of the first paragraph is that read committed mode never sees "changes

Re: [GENERAL] A contradiction in 13.2.1

On Wed, Jan 27, 2016 at 5:59 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jan 27, 2016 at 3:23 PM, Dane Foster <studdu...@gmail.com> wrote: > >> Hello, >> >> I'm trying to understand concurrency in PostgreSQL so I'm slowly reading >

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus wrote: > > > > Am I on the right track, or is there some better way to set this up? My > > understanding is that views really aren't meant for insert/update > > operations, and I have seen on the web that using views to

[GENERAL] Can PostgreSQL use multi-column index for FK constraint validation?

Hello, If I have a primary key index of the form: (col1, col2, col3) and a foreign key constraint of the form: FOREIGN KEY (col1, col2) REFERENCES foo ON DELETE CASCADE ON UPDATE CASCADE should I create a separate index (col1, col2) or is PostgreSQL capable of using the primary key's index?

Re: [GENERAL] Can PostgreSQL use multi-column index for FK constraint validation?

On Tue, Jan 26, 2016 at 3:15 PM, Josh Berkus <j...@agliodbs.com> wrote: > On 01/26/2016 11:38 AM, Dane Foster wrote: > > Hello, > > > > If I have a primary key index of the form: > > (col1, col2, col3) > > and a foreign key constraint of the form: >

Re: [GENERAL] A motion

​ On Sun, Jan 24, 2016 at 4:00 PM, bret_stern < bret_st...@machinemanagement.com> wrote: > Adrian, > I hope you reconsider. You have far more value to the list. > The CoC dictators will flame out, then where will we be. > Just sit on the sidelines until the show is > over. > Look forward to the

Re: [GENERAL] Enforcing referential integrity against a HSTORE column

On Sat, Jan 2, 2016 at 12:19 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 01/02/2016 08:13 AM, Dane Foster wrote: > > Ccing list. > >> On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>

Re: [GENERAL] Enforcing referential integrity against a HSTORE column

On Sat, Jan 2, 2016 at 10:59 AM, Bill Moran wrote: > On Sat, 2 Jan 2016 07:30:38 -0800 > Adrian Klaver wrote: > > > > So given: > > > > > > CREATE TABLE xtra_fields( > > >xfk SERIAL PRIMARY KEY, > > >xtk INTEGER NOT NULL REFERENCES

[GENERAL] Enforcing referential integrity against a HSTORE column

Hello, I'm moving a MySQL database to PostgreSQL and redesigning parts of it to take advantage of PostgreSQL's richer type system and other advance features. Currently I am attempting to replace a table of name/value pair data w/ a hstore column. But now that the data will no longer be flattened

[GENERAL] Check constraint and at least two rows

Hello, I have a design/modelling puzzle/problem. I'm trying to model a series of events. So I have two tables w/ a parent child relationship. The child table has the rule/constraint/etc that for every row in the parent table there must be at least 2 rows in the child because a series must have at

Re: [GENERAL] Check constraint and at least two rows

On Tue, Nov 3, 2015 at 7:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Nov 3, 2015 at 4:55 PM, Dane Foster <studdu...@gmail.com> wrote: > >> Hello, >> >> I have a design/modelling puzzle/problem. I'm trying to model a series of >>

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/29/2015 05:38 PM, Dane Foster wrote: > >> Hello, >> >> I think I've tripped over another mysq_fdw bug. I've filed a bug report >> on github already but just in case the p

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/30/2015 07:21 AM, Dane Foster wrote: > >> >> On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wro

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/30/2015 08:13 AM, Dane Foster wrote: > >> >> On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wro

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 1:02 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/30/2015 09:55 AM, Dane Foster wrote: > >> On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote: &g

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/30/2015 09:36 AM, Dane Foster wrote: > >> On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote: &g

[GENERAL] mysql_fdw trouble

Hello, I have a MySQL/PHP app that I want to port to PostgreSQL so I just installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw because I'd like to do the data migration in SQL if possible. Installation and set up worked flawlessly but when I run the following query SELECT

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 1:56 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/29/2015 10:47 AM, Dane Foster wrote: > >> Hello, >> >> I have a MySQL/PHP app that I want to port to PostgreSQL so I just >> installed the mysql_fdw from https://github.c

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Dane Foster <studdu...@gmail.com> writes: > > Installation and set up worked flawlessly but when I run the following > query > > ... > > ​I get the following error:​ > >

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/29/2015 12:10 PM, Dane Foster wrote: > >> On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pie...@hogranch.com >> <mailto:pie...@hogranch.com>> wrote: >> >&g

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pie...@hogranch.com> wrote: > On 10/29/2015 11:20 AM, Dane Foster wrote: > > ​I think you are correct about mysql_fdw "... sending the trim() checks > for remote execution" because according to the docs: > >

Re: [GENERAL] mysql_fdw trouble

AL '4 HOUR') || ']')::TSZ_PERIOD AS duration FROM _series ) AS v​ ​Regards,​ Dane On Thu, Oct 29, 2015 at 4:37 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/29/2015 12:56 PM, Dane Foster wrote: > >> On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver >> <adrian.

Re: [GENERAL] Domain check constraint not honored?

On Thu, Oct 29, 2015 at 7:16 PM, Rob Sargent wrote: > On 10/29/2015 05:01 PM, Tom Lane wrote: > > Eric Schwarzenbach > writes: > > ... (Also FWIW, the latest version of > this regexp is now '^([0-9]+.)*[0-9]+$') > >

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 8:22 PM, CaT <c...@zip.com.au> wrote: > On Thu, Oct 29, 2015 at 08:38:49PM -0400, Dane Foster wrote: > > For the record I know top posting is a crime against god and humanity > but I > > feel justified because this post is not directly related

Re: [GENERAL] mysql_fdw trouble

On Thu, Oct 29, 2015 at 2:20 PM, Dane Foster <studdu...@gmail.com> wrote: > On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Dane Foster <studdu...@gmail.com> writes: >> > Installation and set up worked flawlessly but when I run

Re: [GENERAL] partial JOIN (was: ID column naming convention)

On Sat, Oct 24, 2015 at 3:42 PM, Rafal Pietrak wrote: > > > W dniu 24.10.2015 o 21:03, Rafal Pietrak pisze: > > > > > > W dniu 24.10.2015 o 15:00, David G. Johnston pisze: > >> On Sat, Oct 24, 2015 at 6:41 AM, Rafal Pietrak >> >wrote: >

Re: [GENERAL] partial JOIN (was: ID column naming convention)

On Sat, Oct 24, 2015 at 5:23 PM, Dane Foster <studdu...@gmail.com> wrote: > On Sat, Oct 24, 2015 at 3:42 PM, Rafal Pietrak <ra...@ztk-rp.eu> wrote: > >> >> >> W dniu 24.10.2015 o 21:03, Rafal Pietrak pisze: >> > >> > >> > W dniu 24

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <jim.na...@bluetreble.com> wrote: > On 10/21/15 9:32 PM, Dane Foster wrote: > >> "If STRICT is not specified in the INTO clause, then target will be >> set to the first row returned by the query, or to nulls if the

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro <thomas.mu...@enterprisedb.com > wrote: > On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdu...@gmail.com> wrote: > > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <jim.na...@bluetreble.com> > wrote: > >>

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Thu, Oct 22, 2015 at 9:15 PM, Thomas Munro <thomas.mu...@enterprisedb.com > wrote: > On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdu...@gmail.com> wrote: > > On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro > > <thomas.mu...@enterprisedb.com> wrote: > &

Re: [GENERAL] My first PL/pgSQL function

On Wed, Oct 21, 2015 at 3:20 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > 2015-10-21 4:08 GMT+02:00 Dane Foster <studdu...@gmail.com>: > >> Since I'm switching to OUT parameters is there any difference >> (performance/efficienc

[GENERAL] A question about PL/pgSQL DECLAREd variable behavior

I wrote the following simple function to try to learn what happens to a DECLAREd variable whose assignment comes from an INTO statement where the query being executed does not return a result. CREATE OR REPLACE FUNCTION _test() RETURNS BOOLEAN AS $$ DECLARE r RECORD; BEGIN SELECT 1 AS one INTO r

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

On Wed, Oct 21, 2015 at 10:23 PM, Thomas Munro < thomas.mu...@enterprisedb.com> wrote: > On Thu, Oct 22, 2015 at 2:48 PM, Dane Foster <studdu...@gmail.com> wrote: > > I wrote the following simple function to try to learn what happens to a > > DECLAREd variable

[GENERAL] My first PL/pgSQL function

Hello, I'm in the very very very very early stages of migrating a MySQL/PHP app to PostgreSQL/PHP/Lua. Because we are moving to PostgreSQL one of the [many] things I intend to change is to move ALL the SQL code/logic out of the application layer and into the database where it belongs. So after

Re: [GENERAL] My first PL/pgSQL function

On Tue, Oct 20, 2015 at 12:43 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Tue, Oct 20, 2015 at 9:45 AM, Dane Foster <studdu...@gmail.com> wrote: > > Hello, > > > > I'm in the very very very very early stages of migrating a MySQL/PHP app > to > >

Re: [GENERAL] My first PL/pgSQL function

discount.expires, to_char(discount.value, '999D99') ); END IF; RETURN (discount.type, 'depleted'); END IF; END CASE; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql STRICT; Dane On Tue, Oct 20, 2015 at 1:45 PM, Dane Foster <st

Re: [GENERAL] My first PL/pgSQL function

On Tue, Oct 20, 2015 at 4:35 PM, John R Pierce <pie...@hogranch.com> wrote: > On 10/20/2015 1:22 PM, Dane Foster wrote: > >> Here is the updated version w/ the feedback incorporated. I'm going to >> install PostgreSQL 9.6 from source this weekend so I can start >&g

Re: [GENERAL] My first PL/pgSQL function

On Tue, Oct 20, 2015 at 5:53 PM, Jim Nasby wrote: > On 10/20/15 11:43 AM, Merlin Moncure wrote: > >> *) let's compare notes on your doxygen style code markup. I've been >> trouble finding a good robust tool that does exactly what I want, >> curious if you did better. >>

Re: [GENERAL] My first PL/pgSQL function

., INTO STRICT outparam1, outparam2, ..., outparamN? Thanks, Dane On Tue, Oct 20, 2015 at 4:37 PM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > 2015-10-20 22:22 GMT+02:00 Dane Foster <studdu...@gmail.com>: > >> Here is the updated version w/ the feedback incorpo

[GENERAL] Returning JSON or JSONB

Hello, I was wondering when returning JSON data from a PostgreSQL function for consumption by clients (e.g., PHP or Lua) does it make any difference to declare the function's return type as JSON or JSONB? Now that I've actually written the question down it occurs to me that what I really want to

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On Fri, Oct 2, 2015 at 3:03 PM, Kevin Grittner wrote: > pinker wrote: > > > I've tried to write audit trigger which fires only when data > > changed, so I used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause > > as described in documentation. Should this clause

Re: [GENERAL] Q: text query search and

Kevin, I am also interested in a deeper explanation of what you mean by, The easy way to do that is just to add a trigram index and search for similar strings, and forget about full text search. Because I need to make a decision about whether to use full text search or use other pattern matching

[GENERAL] A table of magic constants

Hello, My reason/excuse for asking what I'll eventually ask is that I'm a new PostgreSQL practitioner so the amount of things I don't know about PostgreSQL is depressingly large. While reading through the recent Row level security - notes and questions thread I saw this SQL statement: CREATE

Re: [GENERAL] A table of magic constants

, ... WITH CHECK (username = SESSION_USER), w/o first knowing that SESSION_USER was a thing. ​Regards,​ Dane On Sat, Jul 11, 2015 at 3:54 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/11/2015 12:21 PM, Raymond O'Donnell wrote: On 11/07/2015 20:07, Dane Foster wrote: snip As a recent

Re: [GENERAL] A table of magic constants

specific things that are further ahead in the docs than chapter 9. Dane On Sat, Jul 11, 2015 at 6:34 PM, Michael Nolan htf...@gmail.com wrote: On Sat, Jul 11, 2015 at 4:53 PM, Vincent Veyron vv.li...@wanadoo.fr wrote: On Sat, 11 Jul 2015 16:55:44 -0400 Dane Foster studdu...@gmail.com wrote

[GENERAL] Dynamic multi dimensional arrays in SQL

Hello, I'm trying to dynamically construct a multi dimensional array where the outer most array's elements are binary arrays. My initial attempt was something to the effect of: SELECT ARRAY(SELECT ARRAY[true::text,false::text] FROM generate_series(1,2)); My expectation is a result of the form:

[GENERAL] Why does the range type's upper function behave inconsistently?

I don't understand the inconsistent behavior of the range types' upper function in regard to inclusive ranges. For example(s): 1. SELECT upper(int4range(1, 4, '[]')) = 4; -- FALSE 2. SELECT upper(int8range(1, 4, '[]')) = 4; -- FALSE 3. SELECT upper(numrange(1, 4, '[]')) = 4; -- TRUE 4. SELECT

Re: [GENERAL] Why does the range type's upper function behave inconsistently?

, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/05/2015 10:13 AM, Dane Foster wrote: I don't understand the inconsistent behavior of the range types' upper function in regard to inclusive ranges. For example(s): 1. SELECT upper(int4range(1, 4, '[]')) = 4; -- FALSE 2. SELECT upper