Re: [GENERAL] jsonb

On Tue, Nov 14, 2017 at 4:30 PM, hmidi slim wrote: > I'm trying to use the function to_jsonb and create the name: > to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}'); > But after that I used Objection.js ORM to get data using the query: >

Re: [GENERAL] jsonb

On Tue, Nov 14, 2017 at 3:32 PM, hmidi slim wrote: > I have a column name of type 'jsonb' on my table named product. The format > of the column: > name: {"key1": "text1", "key2": "text2"} > > When I make a query to fetch data from the table I got this format: > name:

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

On Thu, Nov 9, 2017 at 8:22 AM, Adam Brusselback wrote: >> Since you are migrating data into a staging table in PostgreSQL, you may set >> the field data type as TEXT for each field where you have noticed or >> anticipate issues. >> Then after population perform the

Re: [GENERAL] idle in transaction, why

On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent wrote: > > > On 11/06/2017 01:41 PM, Tom Lane wrote: >> >> Rob Sargent writes: >>> >>>idle_in_transaction_session_timeout | 0 | default | >>> || A value of 0 turns off the timeout. |

Re: [GENERAL] query not scaling

On Thu, Oct 26, 2017 at 10:01 AM, Tom Lane wrote: > Laurenz Albe writes: >> Also, to have PostgreSQL inline the function, which would be good >> for performance, it should be declared IMMUTABLE. > > Actually, if you hope to have a SQL function be

Re: [GENERAL] startup process stuck in recovery

On Mon, Oct 9, 2017 at 6:12 PM, Christophe Pettus wrote: > >> On Oct 9, 2017, at 14:29, Tom Lane wrote: >> Hmm. Creating or dropping a temp table does take AccessExclusiveLock, >> just as it does for a non-temp table. In principle we'd not have to >>

Re: [GENERAL] a JOIN to a VIEW seems slow

On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman <fr...@chagford.com> wrote: > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: >> >> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman <fr...@chagford.com> >> > wrote: >> > >> >> I

Re: [GENERAL] a JOIN to a VIEW seems slow

On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman <fr...@chagford.com> wrote: > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: >> >> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman <fr...@chagford.com> >> > wrote: >> > >> >> I

Re: [GENERAL] a JOIN to a VIEW seems slow

On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman wrote: > On 2017-09-18 Frank Millman wrote: >> >> Here are the timings for running the query on identical data sets using >> Postgresql, Sql Server, and Sqlite3 - >> >> PostgreSQL - >> Method 1 - 0.28 sec >> Method 2 –

Re: [GENERAL] a JOIN to a VIEW seems slow

On Thu, Sep 14, 2017 at 8:17 AM, Pavel Stehule wrote: > 2017-09-14 15:09 GMT+02:00 Pavel Stehule : >> >> >> >> 2017-09-14 14:59 GMT+02:00 Frank Millman : >>> >>> Pavel Stehule wrote: >>> >>> 2017-09-14 10:14 GMT+02:00 Frank

Re: [GENERAL] looking for a globally unique row ID

On Thu, Sep 14, 2017 at 12:11 PM, Rafal Pietrak <ra...@ztk-rp.eu> wrote: > W dniu 14.09.2017 o 15:54, Merlin Moncure pisze: >> On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak <ra...@ztk-rp.eu> wrote: >>> Hello everybody, >>> >>> Can anybody help m

Re: [GENERAL] looking for a globally unique row ID

On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak wrote: > Hello everybody, > > Can anybody help me find a way to implement an ID which: > > 1. guarantees being unique across multiple tables. > > 2. guarantees its uniqueness not only during INSERT, but also during the > lifetime of

Re: [GENERAL] B-tree index on a VARCHAR(4000) column

On Friday, September 8, 2017, John Turner wrote: > > > On Fri, Sep 8, 2017 at 6:57 AM Tom Lane > wrote: > >> Ron Johnson >

Re: [GENERAL] B-tree index on a VARCHAR(4000) column

On Thu, Sep 7, 2017 at 10:48 PM, Ron Johnson wrote: > Hi, > > v 9.2.7 > > Based on LENGTH(offending_column), none of the values are more than 144 > bytes in this 44.2M row table. Even though VARCHAR is, by definition, > variable length, are there any internal design issues

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] clustered index benchmark comparing Postgresql vs Mariadb

On Wed, Aug 30, 2017 at 9:03 PM, 유상지 wrote: > I want to get help with Postgresql. > > I investigated that Postgresql could be rather fast in an environment > using a secondary index. but It came up with different results on benckmark. > > The database I compared was mariadb, and

Re: [GENERAL] Would you add a --dry-run to pg_restore?

On Wed, Aug 2, 2017 at 11:49 AM, Tom Lane wrote: > Edmundo Robles writes: >> I mean, to verify the integrity of backup i do: >> gunzip -c backup_yesterday.gz | pg_restore -d my_database && echo >> "backup_yesterday is OK" > >> but my_database's

Re: [GENERAL] Shared Constants in PLPGSQL

On Tue, Aug 1, 2017 at 8:29 AM, Tim Uckun wrote: > In my case I don't expect these constants to be changed on a regular basis. > They will be set just once and that's it. I was thinking it would be just as > easy to set them in a proc as it would be to set them in a table. By

Re: [GENERAL] Shared Constants in PLPGSQL

On Tue, Aug 1, 2017 at 8:04 AM, Merlin Moncure <mmonc...@gmail.com> wrote: > re-evaluate. The main advantage over your approach is that you don't > have to modify multiple things every time you add a new config values; > just add a column and replace the function. This can be aut

Re: [GENERAL] Shared Constants in PLPGSQL

On Tue, Aug 1, 2017 at 4:56 AM, Tim Uckun wrote: > What's the best way to deal with global constants in PLPGSQL. Currently I am > putting them in a function with out parameters and then calling that > function from every other function that needs them like this. > > CREATE OR

Re: [GENERAL] Imperative Query Languages

On Mon, Jul 10, 2017 at 4:26 PM, Christopher Browne wrote: > On 5 July 2017 at 01:22, Jason Dusek wrote: >> Hi All, >> >> This more of a general interest than specifically Postgres question. Are >> there any “semi-imperative” query languages that have

Re: [GENERAL] Imperative Query Languages

On Wed, Jul 5, 2017 at 12:22 AM, Jason Dusek wrote: > Hi All, > > This more of a general interest than specifically Postgres question. Are > there any “semi-imperative” query languages that have been tried in the > past? I’m imagining a language where something like this:

Re: [GENERAL] Is the row version available in SQL?

On Wed, Jun 28, 2017 at 9:54 AM, Rob Nikander wrote: > Hi, > > I'm reading about MVCC here: > https://www.postgresql.org/docs/current/static/mvcc.html. > > In some cases I can use these transaction isolation modes, but in other > cases, I may want to manage the versions

Re: [GENERAL] CREATE TABLE & composite type

On Wed, Jun 28, 2017 at 8:37 AM, Adrian Klaver wrote: > On 06/28/2017 06:27 AM, gmb wrote: >> >> Hi Referencing https://www.postgresql.org/docs/9.6/static/rowtypes.html >> Taking a chance here Is there a short-hand way in which I can create a >> table with the same

Re: [GENERAL] performance considerations of jsonb vs separate rows

On Mon, Jun 19, 2017 at 2:29 PM, Rob Nikander wrote: > I'm wondering about the tradeoffs, specifically: is it possible to update > one piece of a jsonb value without having to rewrite the entire field? There > are cases where that data field was getting pretty big (500kb).

Re: [GENERAL] effective_io_concurrency increasing

On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes wrote: > If you have a RAID, set it to the number of spindles in your RAID and forget > it. It is usually one of the less interesting knobs to play with. (Unless > your usage pattern of the database is unusual and exact fits the

Re: [GENERAL] Advisory lock deadlock issue

On Wed, Jun 7, 2017 at 9:16 AM, David Rosenstrauch wrote: > I'm running a Spark job that is writing to a postgres db (v9.6), using the > JDBC driver (v42.0.0), and running into a puzzling error: > > 2017-06-06 16:05:17.718 UTC [36661] dmx@dmx ERROR: deadlock detected >

Re: [GENERAL] storing large files in database - performance

On Fri, May 19, 2017 at 2:04 PM, Eric Hill wrote: > I am pleased to report that with Merlin's suggestion of using the > pg-large-object middleware, I have a test case now showing that I can write a > 25MB buffer from Node.js to Postgres in roughly 700 milliseconds. Here is

Re: [GENERAL] union all taking years - PG 9.6

On Mon, May 15, 2017 at 6:21 PM, Patrick B wrote: > I created a view selecting from both tables, with a UNION ALL between them. > When selecting from that view, it's really slow. I can't even run explain > analyze (it's been 1h and query did not finished yet). Have you

Re: [GENERAL] storing large files in database - performance

On Thu, May 18, 2017 at 7:34 AM, Eric Hill wrote: > I would be thrilled to get 76 MB per second, and it is comforting to know > that we have that as a rough upper bound on performance. I've got work to do > to figure out how to approach that upper bound from Node.js. > > In

Re: [GENERAL] storing large files in database - performance

On Tue, May 16, 2017 at 9:51 AM, Thomas Kellerer wrote: > John R Pierce schrieb am 16.05.2017 um 16:44: >> On 5/16/2017 7:35 AM, Thomas Kellerer wrote: >>> When my (JDBC based) SQL client and the database server are on the same >>> computer... >> >> node.js is Javascript, not

Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB

On Mon, May 15, 2017 at 12:02 PM, Ronny Abraham wrote: > 4. Insert 10,000 rows to JSON, execution time (sec): > 5. Insert 10,000 rows to JSONB, execution time (sec): > > What’s interesting is that inserting to JSONB is slightly faster than > inserting to JSON. With those times,

Re: [GENERAL] dynamic schema modeling and performance

On Tue, Apr 11, 2017 at 12:46 PM, Rj Ewing wrote: > I'm looking for thoughts on the best way to handle dynamic schemas. > > The application I am developing revolves around user defined entities. Each > entity is a tabular dataset with user defined columns and data types. >

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

On Mon, Apr 10, 2017 at 1:43 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Mon, Apr 10, 2017 at 1:35 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> >> Yes, and in fact we documented the ORDER-BY-in-subselect solution back >> before we had the ORDER-BY-in-agg

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

On Mon, Apr 10, 2017 at 1:35 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johns...@gmail.com> writes: >> On Mon, Apr 10, 2017 at 11:02 AM, Merlin Moncure <mmonc...@gmail.com> wrote: >>> Sure, but isn't it fair to con

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

On Mon, Apr 10, 2017 at 12:01 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Merlin Moncure <mmonc...@gmail.com> writes: >> On Sun, Apr 9, 2017 at 4:27 PM, Guyren Howe <guy...@gmail.com> wrote: >>> If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs, will the

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

On Sun, Apr 9, 2017 at 4:27 PM, Guyren Howe wrote: > > If I do a SELECT with a GROUP_BY and multiple ARRAY_AGGs, will the ARRAY_AGGs > be guaranteed to have entries in the same (ie corresponding) order? > > eg > > SELECT > u.name, > ARRAY_AGG(o.order_date) AS order_dates, >

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

On Tue, Apr 4, 2017 at 8:20 AM, Daniel Westermann wrote: > Hi, > > PostgreSQL 9.6.2 on CentOS 7.3 x64. > > This is my data set: > > drop table if exists users; > drop table if exists ids; > create table users ( user_id int >, username

Re: [GENERAL] My humble tribute to psql -- usql v0.5.0

On Mon, Apr 3, 2017 at 4:42 PM, Kenneth Shaw wrote: > UTF-8 works just fine with MS SQL: > > ms:booktest@192.168.1.5=> select N'这是一个'; > col0 > +--+ > 这是一个 > (1 rows) confirmed! merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] My humble tribute to psql -- usql v0.5.0

On Sun, Apr 2, 2017 at 7:55 AM, Kenneth Shaw wrote: > Hi All, > > I apologize in advance if this is somewhat off-topic, but I thought I > would inform the people (ie, psql users) about usql, a > universal-command line tool that aims to work the same way psql does, > but with

Re: [GENERAL] Advice about software engineering inside Postgres?

On Mon, Mar 20, 2017 at 2:32 PM, Guyren Howe wrote: > I’m working on a project to get the likes of web developers to make more > effective use of Postgres. This amounts to saying that much of the M in MVC > should be implemented as code and relations in Postgres. > This is

Re: [GENERAL] JSONB Overlap Operator.

On Fri, Mar 17, 2017 at 9:43 AM, Eduardo Felipe wrote: > Hi there! > > In a project I was experimenting with replacing array columns with JSONB > columns, to allow a greater flexibility of types. > > One thing that I found missing is the "overlap" operator (&&). > > JSONB

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, Feb 22, 2017 at 3:19 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure <mmonc...@gmail.com> wrote: >> On Thursday, February 16, 2017, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> >>> Tim Bellis <tim.

Re: [GENERAL] postgresql how to duplicate rows in result.

On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston wrote: > If it wasn't lateral the reference to number in "generate_series(1, number)" > would fail. huh -- I didn't know that! Testing it out, all JOIN types imply LATERAL if the function call is tlist SRF style

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Thursday, February 16, 2017, Tom Lane wrote: > Tim Bellis > writes: > > Even though this is a read only query, is it also expected to be blocked > behind the vacuum? Is there a way of getting indexes for a table which > won't be

Re: [GENERAL] PostgreSQL corruption

On Tue, Feb 14, 2017 at 7:23 PM, James Sewell wrote: > OK, > > So with some help from the IRC channel (thanks macdice and JanniCash) > it's come to light that my RAID1 comprised of 2 * 7200RPM disks is > reporting ~500 ops/sec in pg_test_fsync. > > This is higher than

Re: [GENERAL] postgresql how to duplicate rows in result.

On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi > <alessandro.ba...@gmail.com> wrote: >> Hi list, >> sorry for my english, I will try to example as well. I've a query that joins >>

Re: [GENERAL] postgresql how to duplicate rows in result.

On Tue, Feb 14, 2017 at 12:42 PM, John McKown wrote: > On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi > wrote: >> >> Hi list, >> sorry for my english, I will try to example as well. I've a query that >> joins multiple tables and

Re: [GENERAL] postgresql how to duplicate rows in result.

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi wrote: > Hi list, > sorry for my english, I will try to example as well. I've a query that joins > multiple tables and return a result like: > > id,customers,phone,code,number > 1 , ,3,123 , 2 > 2 ,

Re: [GENERAL] Postgres

On Sat, Feb 11, 2017 at 12:48 AM, prakash ramakrishnan wrote: > Hi, > >Am Prakash from Chennai and am working in postgres edb 9.5 I need > your help for pgpool and pgbouncer configuration steps and please keep in > touch if I get any error. why don't you ask some

Re: [GENERAL] Can we not give tyrannical pedants control of #postgresql?

On Thu, Jan 19, 2017 at 5:23 PM, Julian Paul wrote: > I hope that particular stereotypes aren't proven here, but it appears > #postgresql encourages a particular tier and makes aware of it's rigid > hierarchy. I owe alot to #postgresql but not to these particular users, I've

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

On Thu, Jan 19, 2017 at 1:28 AM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On 01/19/2017 06:21 AM, Merlin Moncure wrote: >> > ... >> >> >> yes. Either way, I would like to very much understand how server is >> preferring 3m cost pla

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

On Wed, Jan 18, 2017 at 11:10 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 01/18/2017 08:58 PM, Merlin Moncure wrote: >> >> On Wed, Jan 18, 2017 at 2:12 PM, Melvin Davidson <melvin6...@gmail.com >> <mailto:melvin6...@gmail.com>> wrote: >>

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

On Wed, Jan 18, 2017 at 2:12 PM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Wed, Jan 18, 2017 at 3:06 PM, Merlin Moncure <mmonc...@gmail.com> > wrote: > >> On Wed, Jan 18, 2017 at 1:04 PM, Ravi Tammineni >> <rtammin...@partner.aligntech

Re: [GENERAL] Queries are taking way longer in 9.6 than 9.5

On Wed, Jan 18, 2017 at 1:04 PM, Ravi Tammineni wrote: > Hi Chris, > > Here is the query and execution plan in 9.5 and 9.6. Can you verify tblpuorderstatus and tblpuorderstatushistory have all indexes accounted for on both servers? It seems incredible server

Re: [GENERAL] efficiently migrating 'old' data from one table to another

On Fri, Jan 13, 2017 at 12:03 PM, <kbran...@pwhome.com> wrote: > On Jan 12, 2017, Jonathan Vanasco <postg...@2xlp.com> wrote: >>On Jan 12, 2017, at 5:52 PM, Merlin Moncure wrote: >> >>> On Thu, Jan 12, 2017 at 2:19 PM, bto...@computer.org >>> <

Re: [GENERAL] efficiently migrating 'old' data from one table to another

On Thu, Jan 12, 2017 at 2:19 PM, bto...@computer.org wrote: > > > - Original Message - >> From: "Jonathan Vanasco" >> To: "pgsql-general general" >> Sent: Thursday, January 12, 2017 3:06:14 PM >> Subject: [GENERAL]

Re: [GENERAL] Queries on very big table

On Mon, Jan 2, 2017 at 5:23 AM, Job wrote: > Hello guys and very good new year to everybody! > > We are now approaching some queries and statistics on very big table (about > 180 millions of record). > The table is partitioned by day (about ~3 Gb of data for every

Re: [GENERAL] Write-optimized data structures

On Thu, Dec 29, 2016 at 4:03 PM, selforganized wrote: > Hi, > > Does Postgresql have any write-optimized data structure like LSM-tree? if > not is there any plan to implement that? > > I'm building a write-heavy OLTP application. I'm looking at write-optimized > databases

Re: [GENERAL] PostgreSQL not reusing free space in table ?

On Fri, Jan 6, 2017 at 4:09 AM, Pierre Ducroquet wrote: > Hi > > Running PostgreSQL 9.4, I am running in the following issue. > On a huge table, I have to remove the content (set to '') of a column that > makes for 99% of the weight of the table. Since the

Re: [GENERAL] Row value expression much faster than equivalent OR clauses

On Wed, Jan 4, 2017 at 8:49 AM, Kevin Grittner wrote: > On Wed, Dec 28, 2016 at 10:58 AM, Steven Grimm wrote: > >> WHERE ((e.timeStamp > '2016-12-19T20:34:22.315Z') >> OR (e.timeStamp = '2016-12-19T20:34:22.315Z' >> AND

Re: [GENERAL] Performance PLV8 vs PLPGSQL

On Wed, Dec 28, 2016 at 3:15 AM, Tim Uckun wrote: > I have seen various links on the internet which indicate that PLV8 is > significantly faster than PL-PGSQL sometimes an order of magnitude faster. > > Is this uniformly true or is it just in certain circumstances? > > Is

Re: [GENERAL] storing C binary array in bytea via libpq

On Wed, Dec 7, 2016 at 4:10 AM, Tom DalPozzo wrote: > Hi, > I tried both ways: they're ok. > Also, multiple VALUES in one INSERT is actually better as performance. If you are chasing performance with the binary protocol you might want to take a look at libpqtypes:

Re: [GENERAL] When to use COMMENT vs --

On Wed, Dec 7, 2016 at 9:57 AM, Rich Shepard wrote: > I have used '-- ' to enter comments about tables or columns and am curious > about the value of storing comments in tables using the COMMENT key word. > When is the latter more appropriate than the former? Main

Re: [GENERAL] About the MONEY type

On Wed, Nov 30, 2016 at 2:16 PM, John McKown wrote: > On Wed, Nov 30, 2016 at 1:23 PM, bto...@computer.org > wrote: > Speaking generically, I guess maybe MONEY needs to be somewhat like a > TIMESTAMP. At least in PostgreSQL, a TIMESTAMP can

Re: [GENERAL] select function alias

On Wed, Nov 30, 2016 at 12:15 PM, David G. Johnston wrote: > On Wed, Nov 30, 2016 at 8:15 AM, bto...@computer.org > wrote: >> >> 5. Use a CTE: >> >> with shortnames as ( >> select to_char(impressions_create_date,'-mm-dd') as ymd

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

On Wed, Nov 30, 2016 at 11:05 AM, George <pinkisntw...@gmail.com> wrote: > On Wed, Nov 30, 2016 at 6:45 PM, Merlin Moncure <mmonc...@gmail.com> wrote: >> On Wed, Nov 30, 2016 at 10:42 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> George <pinkisntw...@gmail.

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

On Wed, Nov 30, 2016 at 10:42 AM, Tom Lane wrote: > George writes: >> explain select * from wg3ppbm_transaction where partner_uuid in ( >> select p.uuid >> from wg3ppbm_userpartner up >> join wg3ppbm_partner p on

Re: [GENERAL] About the MONEY type

On Wed, Nov 30, 2016 at 7:43 AM, Raymond O'Donnell wrote: > On 30/11/16 12:05, Thomas Kellerer wrote: >> >> Tobia Conforto schrieb am 30.11.2016 um 12:15: >>> >>> I think MONEY is a great datatype, at least in theory. >> >> >> I personally find it pretty useless to be honest -

Re: [GENERAL] "Fuzzy" Matches on Nicknames

On Tue, Nov 29, 2016 at 6:56 PM, rob stone wrote: > Hello Michael, > On Tue, 2016-11-29 at 19:10 -0500, Michael Sheaver wrote: >> Greetings, >> >> I have two tables that are populated using large datasets from >> disparate external systems, and I am trying to match records

Re: [GENERAL] Change column type from int to bigint - quickest way

On Fri, Nov 11, 2016 at 9:30 AM, Andreas Brandl wrote: > Hi, > > we have a pretty big table with an integer-type primary key. I'm looking for > the quickest way to change the column type to bigint to avoid hitting the > integer limit. We're trying to avoid prolonged lock

Re: [GENERAL] Change column type from int to bigint - quickest way

On Friday, November 11, 2016, Andreas Brandl wrote: > Hi, > > we have a pretty big table with an integer-type primary key. I'm looking > for the quickest way to change the column type to bigint to avoid hitting > the integer limit. We're trying to avoid prolonged lock

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Nov 4, 2016 at 9:38 AM, Alban Hertroys <haram...@gmail.com> wrote: > On 4 November 2016 at 14:41, Merlin Moncure <mmonc...@gmail.com> wrote: >> On Fri, Nov 4, 2016 at 8:08 AM, Kim Rose Carlsen <k...@hiper.dk> wrote: >>> The nulls are generated b

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Nov 4, 2016 at 8:08 AM, Kim Rose Carlsen wrote: >>> It might raise another problem, that the nulls are generated through LEFT > >>> JOINS where no rows are defined. Then the 0 or -1 value need to be >>> a computed value. Won't this throw off index lookups? (I might be >>>

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Mon, Oct 31, 2016 at 9:28 AM, Kim Rose Carlsen wrote: > On Sat, Oct 29, 2016 at 8:27 AM, Kim Rose Carlsen wrote: > >> > I have tried creating a function called >> > zero_if_null(int) : int that just select COALESCE($1, 0) >> > and adding a index on

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Sat, Oct 29, 2016 at 8:27 AM, Kim Rose Carlsen wrote: >>> This doesn't do much good. This doesn't tell the planner that the 3 > >>> customer_ids are actually of same value, and it therefore can't filter >>> them >>> as it sees fit. > >> You do know you can index on a function,

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 2:54 PM, Kim Rose Carlsen wrote: >> > On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: > >> > Hi >> > >> > I was wondering if there is a way to hint that two columns in two >> > different >> > tables IS NOT DISTINCT FROM each other.

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 1:20 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Fri, Oct 28, 2016 at 1:17 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Scott Marlowe <scott.marl...@gmail.com> writes: >>> On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen <k..

Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other

On Fri, Oct 28, 2016 at 1:17 PM, Tom Lane wrote: > Scott Marlowe writes: >> On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote: >>> I was wondering if there is a way to hint that two columns in two different >>> tables IS NOT

Re: [GENERAL] libpq heartbeat

On Thu, Oct 27, 2016 at 12:45 PM, Francisco Olarte <fola...@peoplecall.com> wrote: > Merlin: > > On Thu, Oct 27, 2016 at 7:29 PM, Merlin Moncure <mmonc...@gmail.com> wrote: >> On Thu, Oct 27, 2016 at 11:18 AM, Francisco Olarte >> <fola...@peoplecall.com&

Re: [GENERAL] libpq heartbeat

On Thu, Oct 27, 2016 at 11:18 AM, Francisco Olarte <fola...@peoplecall.com> wrote: > Merlin: > > On Thu, Oct 27, 2016 at 6:10 PM, Merlin Moncure <mmonc...@gmail.com> wrote: >> On Thu, Oct 27, 2016 at 10:01 AM, Francisco Olarte >> <fola...@peoplecall.com>

Re: [GENERAL] libpq heartbeat

On Thu, Oct 27, 2016 at 10:01 AM, Francisco Olarte wrote: > And I'd like to point libpq sessions does not sound to be the best > kind of traffic across a firewall, not a good service / protocol to > expose. meh -- it's perfectly fine to expose postgres to the internet as

Re: [GENERAL] json rendering without pretty option (compact)

On Tue, Oct 18, 2016 at 12:00 PM, Nicolas Paris wrote: > Hello, > > I want to minimize postgresql json size when I fetch them. > I translate columnar table to json thought json_build_object/array or even > row_to_jeon. > > While row_to_json do have a "pretty_bool" option, the

Re: [GENERAL] custom average window function failure

On Sun, Oct 9, 2016 at 10:10 PM, Sebastian P. Luque wrote: > On Sun, 09 Oct 2016 16:00:21 -0400, > Tom Lane wrote: > >> "Sebastian P. Luque" writes: >>> Tom Lane wrote: On closer inspection, the error is only in

Re: [GENERAL] Transactional-DDL DROP/CREATE TABLE

On Thu, Oct 6, 2016 at 4:21 AM, Geoff Winkless wrote: > Hi > > I have code that does (inside a single transaction) > > DROP TABLE IF EXISTS mytable; CREATE TABLE mytable > > Occasionally this produces > > ERROR: duplicate key value violates unique constraint >

Re: [GENERAL] Lock contention in TransactionIdIsInProgress()

On Wed, Oct 5, 2016 at 5:38 AM, Tomáš Uko wrote: > Hi Jeff, > > > > We have encountered same problem as you (in 9.5.4), it seems that so far it > hasn’t been ported back from 9.6, but if you take this commit and apply it > to 9.5 source codes, it seems to be working > > But

Re: [GENERAL] BRIN indexes and ORDER BY

On Wed, Oct 5, 2016 at 3:27 PM, Stephen Frost wrote: > Darren, > > * Darren Lafreniere (dlafreni...@onezero.com) wrote: >> Tom Lane wrote: >> > > Gavin Wahl wrote: >> > >> It seems trivial to accelerate a MAX or MIN query with a BRIN index. You >> > >>

Re: [GENERAL] Predicting query runtime

On Mon, Sep 12, 2016 at 9:03 AM, Vinicius Segalin wrote: > Hi everyone, > > I'm trying to find a way to predict query runtime (I don't need to be > extremely precise). I've been reading some papers about it, and people are > using machine learning to do so. For the feature

Re: [GENERAL] Is there a way to fix this ugliness

On Fri, Sep 9, 2016 at 8:30 AM, Tim Uckun wrote: > I am trying to get the child elements of a one to many table to be rolled up > into a json field in the parent table. The query I am running is > > select > ob.id > ,case when array_position(array_agg(im.image_type),

Re: [GENERAL] PostgreSQL Database performance

On Tue, Sep 6, 2016 at 11:12 PM, Pradeep wrote: > Dear Naveed, > > I am using PostgreSQL 9.3 version on Windows .After changing these > parameters, I have not seen any resource management utilization. > > I have observed before and after changing the parameter values

Re: [GENERAL] a column definition list is required for functions returning "record"

Fri, Sep 2, 2016 at 6:55 PM, Jim Nasby wrote: > On 8/29/16 6:28 AM, Tom Lane wrote: >> >> Pavel Stehule writes: >>> >>> > 2016-08-29 1:59 GMT+02:00 Jim Nasby : >> It would be nice if there was a way to pass

Re: [GENERAL] How to retrieve jsonb column through JDBC

On Sat, Aug 27, 2016 at 5:39 AM, Alexander Farber wrote: > Hello, > > what do you use to retrieve a jsonb column using JDBC? > > I have tried > > Object last_tiles = rs.getObject("last_tiles"); > > and the resulting Object seems to be a String. > > Then I have

Re: [GENERAL] a column definition list is required for functions returning "record"

On Mon, Aug 29, 2016 at 6:28 AM, Tom Lane wrote: > Pavel Stehule writes: >> 2016-08-29 1:59 GMT+02:00 Jim Nasby : >>> It would be nice if there was a way to pass dynamically formed records >>> around, similar to how you can

Re: [GENERAL] a column definition list is required for functions returning "record"

On Fri, Aug 26, 2016 at 10:50 AM, Cachique wrote: > Hi > From the documentation... ( > https://www.postgresql.org/docs/current/static/sql-select.html ) > > 'Function calls can appear in the FROM clause. (This is especially useful > for functions that return result sets, but

Re: [GENERAL] PG vs ElasticSearch for Logs

On Fri, Aug 19, 2016 at 2:32 AM, Thomas Güttler wrote: > I want to store logs in a simple table. > > Here my columns: > > Primary-key (auto generated) > timestamp > host > service-on-host > loglevel > msg > json (optional) > > I am unsure which DB to

Re: [GENERAL] Jsonb extraction very slow

On Thu, Aug 11, 2016 at 8:45 AM, Tom Lane wrote: > Jim Nasby writes: >> I never dug into why. As Tom posited, decompression might explain the >> time to get a single key out. Getting 10 keys instead of just 1 wasn't >> 10x more expensive, but it was

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

ability to declare session-scope variables, 'DO' is just >> not needed after that >> - SELECTs not targeted into a variable - are written to client output >> - (C) Merlin Moncure - "Ability to embed collection of statements in the >> database under a name and invoke those state

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

On Mon, Aug 8, 2016 at 7:25 PM, Xtra Coder wrote: > Hi, > > I'm just curious about the reasons of the design of 'DO' statement so that > it is not able to return result of the SELECT in its body. > > References: > https://www.postgresql.org/docs/current/static/sql-do.html

Re: [GENERAL] pasting a lot of commands to psql

On Thu, Jul 7, 2016 at 2:48 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Merlin Moncure wrote: >> On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvhe...@2ndquadrant.com> >> wrote: >> > Tom Lane wrote: >> > >> >> You might have

Re: [GENERAL] pasting a lot of commands to psql

On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera wrote: > Tom Lane wrote: > >> You might have better luck with "psql -n", or maybe not. > > I've wished sometimes for a "\set READLINE off" psql metacommand for > this kind of thing. It's pretty annoying when the text being

  1   2   3   4   5   6   7   8   9   10   >