[GENERAL] Question about partial functional indexes and the query planner

? I've been stumped on it. -Brian Dunavant Test script to display behavior below: -- Setup the test data CREATE OR REPLACE FUNCTION public.return_if_even(v_id integer) returns integer LANGUAGE sql AS $$ SELECT case when v_id % 2 = 1 then 0 else v_id end; $$; create table

Re: [GENERAL] Fwd: Ask for a question

This is not quite true. I don't believe there are any flight simulator easter-eggs hidden inside the Postgres code. :) On Wed, Jan 21, 2015 at 10:59 AM, Rémi Cura remi.c...@gmail.com wrote: More bluntly maybe : if you can do it in Excel, you can do it in Postgres. Cheers, Rémi-C

Re: [GENERAL] Some indexing advice for a Postgres newbie, please?

You should consider a BitString. http://www.postgresql.org/docs/9.4/static/datatype-bit.html On Thu, Feb 19, 2015 at 11:10 AM, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its

Re: [GENERAL] SELECT, GROUP BY, and aggregates

To lower the amount of time spent copy pasting aggregate column names, it's probably worth noting Postgres will allow you to short cut that with the column position. For example: select long_column_name_A, long_column_name_b, count(1) from foo group by 1,2 order by 1,2 This works just fine.

Re: [GENERAL] Simple Atomic Relationship Insert

What issue are you having? I'd imagine you have a race condition on the insert into hometowns, but you'd have that same race condition in your app code using a more traditional 3 query version as well. I often use CTEs like this to make things atomic. It allows me to remove transactional code

Re: [GENERAL] Simple Atomic Relationship Insert

With the single CTE I don't believe you can do a full upsert loop. If you're doing this inside of a postgres function, your changes are already atomic, so I don't believe by switching you are buying yourself much (if anything) by using a CTE query instead of something more traditional here. The

Re: [GENERAL] Simple Atomic Relationship Insert

) SELECT id INTO hometown_id FROM ins UNION ALL SELECT id FROM sel; RETURN hometown_id; EXCEPTION WHEN unique_violation THEN END; END LOOP; END; $ LANGUAGE plpgsql; On Tue, Jan 13, 2015 at 1:53 PM, Brian Dunavant br...@omniti.com wrote: With the single CTE I don't

Re: [GENERAL] Simple Atomic Relationship Insert

A very good point, but it does not apply as here (and in my article) we are not using updates, only insert and select. On Tue, Jan 13, 2015 at 6:03 PM, Thomas Kellerer spam_ea...@gmx.net wrote: Brian Dunavant wrote on 13.01.2015 22:33: What issue are you having? I'd imagine you have a race

Re: [GENERAL] date with month and year

On Thu, May 21, 2015 at 5:27 PM, Thomas Kellerer spam_ea...@gmx.net wrote: Postgres does not store the time zone. When storing a timestamp with time zone, it is normalized to UTC based on the timezone of the client. When you retrieve it, it is adjusted to the time zone of the client. Sorry,

Re: [GENERAL] date with month and year

is no longer the time that you were supposed to have been at the meeting. If you had stored that future date as a timestamp WITHOUT time zone you would have still been on-time. This is only an issue for future dates, not past ones. -Brian Dunavant (time is hard, so if I'm wrong anywhere here, someone

Re: [GENERAL] Bi-Directional replication(BDR)

I would suggest going to http://bdr-project.org/docs/stable/index.html On Tue, Aug 4, 2015 at 3:47 PM, clingare...@vsoftcorp.com wrote: Hi, Please help me on: what is the use of bidirectional replication in PostgreSQL? How BDR works? how to setup BDR? on which versions BDR works?

Re: [GENERAL] WIP: CoC

>> "3) A safe, respectful, productive and collaborative environment is >> free of negative personal criticism directed at a member of a >> community, rather than at the technical merit of a topic." >> > A safe, respectful, productive and collaborative environment is free > of non-technical or

Re: [GENERAL] WIP: CoC

> 3. A safe, respectful, productive and collaborative environment is free > comments related to gender, sexual orientation, disability, physical > appearance, body size or race. I think you meant "free OF comments". However it still picks a few special classes of complaint, some of which cause

Re: [GENERAL] Code of Conduct: Is it time?

> We expect of everyone in our spaces to try their best to do the same in a > kind and gentle manner. If you feel it's just a minor offense and the person > didn't mean harm by it, > > simply ignore it unless the pattern of talk continues. If the person > continues or they say something you feel

Re: [GENERAL] CoC [Final v2]

>> Participation does not need to be limited to copy-editing. Of all the >> ways to develop a community CoC, we're engaged in just about the worst >> possible one right now. > > so what would be a better way of developing this ? Of interesting note, the Ruby community is currently considering

Re: [GENERAL] CoC [Final]

> * Participants who disrupt the collaborative space, or participate in > a pattern of behaviour which could be considered harassment will not > be tolerated. Perhaps changing the ", or participate" to " by engaging" would make that statement more focused. > "Disrupting the collaborative space"

Re: [GENERAL] bitwise storage and operations

If it's in integer columns, bitwise logic works just like you would expect it to as well. https://www.postgresql.org/docs/current/static/functions-math.html db=# select 'foo' where (9 & 1) > 0; ?column? -- foo (1 row) db=# select 'foo' where (9 & 2) > 0; ?column? -- (0 rows)

Re: [GENERAL] Request to add feature to the Position function

That does not return the correct answer for the original poster's request. flpg=# select position('om' in reverse('Tomomasaaa')); position -- 15 (1 row) On Mon, Mar 27, 2017 at 11:43 AM, Adrian Klaver wrote: > On 03/27/2017 08:05 AM, Ron Ben

Re: [GENERAL] Request to add feature to the Position function

<adrian.kla...@aklaver.com> wrote: > On 03/27/2017 09:03 AM, Brian Dunavant wrote: >> >> That does not return the correct answer for the original poster's request. >> >> flpg=# select position('om' in reverse('Tomomasaaa')); >> position >>

Re: [GENERAL] Confusing order by error

From the docs you linked: "Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values." The "name" in your order by is a reference to the output column. The following example shows the same with

Re: [GENERAL] INSERT and ON CONFLICT

I believe the following test should answer your question. db=# create table test ( a integer not null unique ); CREATE TABLE db=# insert into test values (1); INSERT 0 1 db=# insert into test values (1); ERROR: duplicate key value violates unique constraint "test_a_key" DETAIL: Key (a)=(1)

Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

"FOR UPDATE" is part of "SELECT" not part of "UPDATE". You can select the rows "for update" which will lock those rows. You can then loop over the the results of the 'select' to do the rest of your logic. Be careful doing this if other things are also updating these rows. With SKIP LOCKED you

Re: [GENERAL] Select from tableA - if not exists then tableB

On Tue, May 9, 2017 at 6:00 PM, Patrick B wrote: > SELECT > split_part(n1.path::text, '/'::text, 18)::integer AS id, > split_part(n1.path::text, '/'::text, 14)::integer AS clientid, > lower(n1.md5::text)::character(32) AS md5, 0 AS cont, >

Re: [GENERAL] Select from tableA - if not exists then tableB

>From what you're saying about migrating, I'm assuming the new table has additional columns or something. If you can map the difference, then you could use CTE's to select from the first table, and if nothing is there, then pull from the second table and pad it with nulls so they "match". This

Re: [GENERAL] Fwd: [BUGS] BUG #14850: Implement optinal additinal parameter for 'justify' date/time function

A 'month' is an abstract measurement of time. Sometimes it's 29 days, 30, or 31. You cannot say "I have 30 days, how many months is that?" because the answer is "it depends". - gives you an interval in days. In your example, you took Jan 31 2016 and added "1 month". Postgres says "I know