Re: [GENERAL] Fields re-ordered on JOIN with * and USING

> > ...least excruciating version of the relevant text... Ouch, I'm glad you folks take care of reading such stuff! What you put in the documentation was much much clearer--just wish I had found it! Speaking of which, I had looked at the "From" section of the "SELECT" page (

Re: [GENERAL] Fields re-ordered on JOIN with * and USING

... btw, a little digging shows that this ordering is required by the SQL standard. The least excruciating version of the relevant text is in SQL92 7.5 : d) Let SLCC be a of s of the form COALESCE ( TA.C, TB.C ) AS C for every column C that is a

Re: [GENERAL] Fields re-ordered on JOIN with * and USING

"David G. Johnston" writes: > On Fri, Sep 1, 2017 at 2:25 PM, Ken Tanzer wrote: >> Hi. I recently noticed that when doing a SELECT * with USING, that the >> join field(s) appear first in the output. I'd never noticed that before, >> and was

Re: [GENERAL] Fields re-ordered on JOIN with * and USING

On Fri, Sep 1, 2017 at 2:25 PM, Ken Tanzer wrote: > Hi. I recently noticed that when doing a SELECT * with USING, that the > join field(s) appear first in the output. I'd never noticed that before, > and was just curious if that is expected behavior or not. Thanks. > ​I

Re: [GENERAL] Strange SQL result - any ideas.

Paul Linehan writes: > I have a table (fred) that I want to transform into JSON and > I use the following command (ignore the backslash stuff): > ... > which is fine (note that the field "mary" is sorted correctly) but > I want "proper" JSON - i.e. with open and close square

[GENERAL] Strange SQL result - any ideas.

I have a table (fred) that I want to transform into JSON and I use the following command (ignore the backslash stuff): SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '', '\\', 'g') FROM ( SELECT * FROM fred ORDER BY mary, jimmy, paulie ) AS t; which gives

[GENERAL] Fields re-ordered on JOIN with * and USING

Hi. I recently noticed that when doing a SELECT * with USING, that the join field(s) appear first in the output. I'd never noticed that before, and was just curious if that is expected behavior or not. Thanks. Ken CREATE TEMP TABLE t1 ( f1 INTEGER, f2 INTEGER UNIQUE, f3 INTEGER, f4

Re: [GENERAL] Issue with json_agg() and ordering

>> >> I guess that the order by should be in the aggregation. >> >> SELECT json_agg(a.* ORDER BY a.last_name, a.last_year DESC) >> FROM my_table a; > > yes. however, you would say, json_agg(a... not 'a.*'). The .* > notation only works in certain contexts, and is transformed at parse > time to,

Re: [GENERAL] pglogical bidirectional replication of sequences

On 2017-09-01 10:29:51 +0200, Peter J. Holzer wrote: > pglogical supports replication of sequences, and although the way it > does this suggests that it can't really work in both directions > (actually I'm sceptical that it works reliably in one direction), of > course I had to try it. > > So I

Re: [GENERAL] Issue with json_agg() and ordering

On Fri, Sep 1, 2017 at 6:22 AM, Charles Clavadetscher wrote: > Hello > >> -Original Message- >> From: pgsql-general-ow...@postgresql.org >> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Jones >> Sent: Freitag, 1. September 2017 10:12 >> To:

Re: [GENERAL] pglogical bidirectional replication of sequences

On 2017-09-01 09:57:52 -0600, Rob Sargent wrote: > On 09/01/2017 02:29 AM, Peter J. Holzer wrote: > >TLDR: Don't. > > > >I'm currently conducting tests which should eventually lead to a 2 node > >cluster with working bidirectional logical replication. > > > >(Postgresql 9.6.4-1.pgdg90+1, pglogical

Re: [GENERAL] pglogical bidirectional replication of sequences

On 09/01/2017 02:29 AM, Peter J. Holzer wrote: TLDR: Don't. I'm currently conducting tests which should eventually lead to a 2 node cluster with working bidirectional logical replication. (Postgresql 9.6.4-1.pgdg90+1, pglogical 2.0.1-1.jessie+1 on Debian 9 (Stretch)) pglogical supports

Re: [GENERAL] Issue with json_agg() and ordering

Hello > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Jones > Sent: Freitag, 1. September 2017 10:12 > To: pgsql-general > Subject: [GENERAL] Issue with json_agg() and ordering >

[GENERAL] pglogical bidirectional replication of sequences

TLDR: Don't. I'm currently conducting tests which should eventually lead to a 2 node cluster with working bidirectional logical replication. (Postgresql 9.6.4-1.pgdg90+1, pglogical 2.0.1-1.jessie+1 on Debian 9 (Stretch)) pglogical supports replication of sequences, and although the way it does

[GENERAL] Issue with json_agg() and ordering

Hi, Could anyone give me a few pointers as to how I might resolve the following : select json_agg(my_table) from (my_table) where foo='test' and bar='f' order by last_name asc, first_name asc; ERROR: column "my_table.last_name" must appear in the GROUP BY clause or be used in an aggregate