Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On Tue, May 31, 2016 at 10:48 AM, Tom Lane wrote: > "David G. Johnston" writes: > > Is there a reason something "SET ROLE ... WITH SETTINGS" couldn't be > > implemented? > > Unless there's something underlying that proposal that I'm not

Re: [GENERAL] Row security policies documentation question

On Tue, May 31, 2016 at 4:59 PM, Alexander M. Sauer-Budge < ambu...@alum.mit.edu> wrote: > Hello, > > Section 5.7. on Row Security Policies ( > https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html) for > 9.5 says: > [...] > ​ > > CREATE POLICY user_policy ON users > USING (us

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On Wed, Jun 1, 2016 at 8:59 AM, CN wrote: > On Tue, May 31, 2016, at 10:20 PM, Tom Lane wrote: > > There's also a bunch of issues having to do with the fact that the > > semantics of SET SESSION AUTHORIZATION are defined by the SQL standard > > and don't exactly match what you'd want, in many cas

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On Wed, Jun 1, 2016 at 9:56 AM, Tom Lane wrote: > "David G. Johnston" writes: > > ​Would a scheme whereby you basically only get to SET ROLE one time work? > > Basically the connection layer logs in and immediately SET SESSION > > AUTHORIZATION AND SET ROLE [

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Wed, Jun 1, 2016 at 8:10 PM, Patrick Baker wrote: > Hi guys, > > I need a function ( PL/PGSQL ) to perform a deletion of some BLOBS... > > ​[...] ​ > When I will call the function: *select function_1_name(5000) or **select > function_1_name(15000)* will it respect the limited by the rows? >

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Wednesday, June 1, 2016, Patrick Baker wrote: > > >> >> ​I'd suggest you setup a test environment with some unimportant data on a >> non-production machine and try it yourself. >> ​ >> ​​ >> >> David J. >> ​ >> >> >> > Thanks.. but if I'm asking the list that's because I'm already testing it >

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker wrote: > >>> It's all working, except the LIMIT... if possible can you please give > me an example of that LIMIT in some of those queries? > > ​​ You also should use ORDER BY when using LIMIT and OFFSET; though depending on the setup it could be omit

Re: [GENERAL] dumb question

On Thursday, June 2, 2016, Steve Clark wrote: > Hi List, > > I am a noob trying to do something that seems like it should be easy but I > can't figure it out. > > I have a table like so: > > id | ref_id | sts > -- > 1 || 0 > 2 | 1 | 1 > 3 || 0 > 4 |

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 1:48 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos > wrote: > >> I think sts=0 means ref_id is null >> >> So, what I think he wants to achieve is: >> >> select max(id) from yourtable where sts=0 and id not in (s

Re: [GENERAL] dumb question

​ > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark > Sent: Thursday, June 2, 2016 9:56 AM > To: pgsql > Subject: [GENERAL] dumb question > > Hi List, > > I am a noob trying to do something that seems li

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce wrote: > On 6/2/2016 11:10 AM, Steve Clark wrote: > > Thanks all the below seem to do the trick. > > On 06/02/2016 01:58 PM, David G. Johnston wrote: > > select max(id) from yourtable where sts=0 and id not in (select re

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

On Thu, Jun 2, 2016 at 4:29 PM, Stephen Frost wrote: > * Sameer Kumar (sameer.ku...@ashnik.com) wrote: > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, > > wrote: > > > Can I list all WAL files in pg_xlog by using some sql query in > Postgres? > > > > Try > > > > Select pg_ls_dir('pg_xlog'); >

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 5:03 PM, Patrick Baker wrote: > > > 2016-06-03 2:10 GMT+12:00 David G. Johnston : > >> On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker >> wrote: >> >>> >>>>> It's all working, except the LIMIT... if possible can

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 5:44 PM, Kevin Grittner wrote: > On Thu, Jun 2, 2016 at 3:23 PM, David G. Johnston > wrote: > > On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce > wrote: > >> Thanks all the below seem to do the trick. > > I doubt it -- using NOT IN requi

Re: [GENERAL] psql remote shell command

On Thu, Jun 2, 2016 at 7:30 PM, Dennis wrote: > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but > that executes the commands on the host where I am running psql from. Also, > is it possible for a

Re: [GENERAL] psql remote shell command

On Thu, Jun 2, 2016 at 7:39 PM, Michael Paquier wrote: > On Fri, Jun 3, 2016 at 8:30 AM, Dennis wrote: > > Is it possible to execute command in on system the is hosting postgresql > > remotely using psql or other mechanism? I know I can use \! in psql but > > that executes the commands on the h

Re: [GENERAL] psql remote shell command

On Thu, Jun 2, 2016 at 7:54 PM, Stephen Frost wrote: > * Dennis (denn...@visi.com) wrote: > > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but > that executes the commands on the host where I am run

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 11:37 PM, Patrick Baker wrote: > > How can I make the function to gets the next 3 rows and not use the same > rows that have been used before? > ​WHERE migrated = 0 ​ ​David J.

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Friday, June 3, 2016, Greg Navis wrote: > Hey! > > I'm playing with pg_trgm. It seems that `lhs % rhs` is _almost_ equivalent > to `similarity(lhs, rhs) < show_limit()`. The difference that I noticed is > that `%` uses a GIN index while `similarity` does not. > > ``` > grn=# \d restaurants >

Re: [GENERAL] [HACKERS] Change in order of criteria - reg

On Wed, Jun 1, 2016 at 12:07 AM, sri harsha wrote: > > Hi, > > In PostgreSQL , does the order in which the criteria is given matter > ?? For example > > Query 1 : Select * from TABLE where a > 5 and b < 10; > > Query 2 : Select * from TABLE where b <10 and a > 5; > > Are query 1 and query 2 t

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 3:13 PM, Greg Navis wrote: > Thanks for answers and sorry for not searching hard enough. > > I'm curious ... would it be difficult to modify PostgreSQL so that it'd > use the index for `similarity(lhs, rhs) >= show_limit()` too? > ​Not in a way that would be useful. ​ > O

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Fri, Jun 3, 2016 at 3:16 PM, Adrian Klaver wrote: > On 06/03/2016 12:23 AM, Patrick Baker wrote: > >> >> >> -- Creating the backup table with the essential data >> INSERT INTO table2 (row.note_id, row.size, row.file_id, row.full_path) >> >> . >> >> Still not seeing what the

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 11:59 PM, Patrick Baker wrote: > > > 2016-06-03 15:50 GMT+12:00 David G. Johnston : > >> On Thu, Jun 2, 2016 at 11:37 PM, Patrick Baker >> wrote: >> >>> >>> How can I make the function to gets the next 3 rows and not

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On Fri, Jun 3, 2016 at 3:27 PM, Jeff Janes wrote: > On Fri, Jun 3, 2016 at 12:13 PM, Greg Navis wrote: > > Thanks for answers and sorry for not searching hard enough. > > > > I'm curious ... would it be difficult to modify PostgreSQL so that it'd > use > > the index for `similarity(lhs, rhs) >=

Re: [GENERAL] Partitioned postgres tables don't need update trigger??

On Fri, Jun 3, 2016 at 5:03 PM, rverghese wrote: > I am working with partitioned tables. I have partitioned based on date and > I have the INSERT trigger in place, I don't have an Update or Delete > Trigger but both updates and deletes against the master table work > correctly. I am not sure how

Re: [GENERAL] How SQL SELECT * statement works in Postgres?

On Sunday, June 5, 2016, Sachin Srivastava wrote: > Dear Team, > > Kindly inform to me How PostgreSQL Processes SQL Statements internally? > > How SQL SELECT * statement works in Postgres? > > > > Why? David J

Re: [GENERAL] [HACKERS] OUT parameter and RETURN table/setof

On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally < sridhar@gmail.com> wrote: > Hi > > Is there any option in PGPLSQL which can RETURNS table or SETOF rows along > with an OUT parameter? > > ​No, there would be no point given the internals of how functions work. ​What is it you are tryi

Re: [GENERAL] Automate copy - Postgres 9.2

POn Wednesday, June 8, 2016, Patrick B wrote: > > ERROR: syntax error at or near "||" >> LINE 12: TO '/var/lib/pgsql/' || crtrow.account_id |... > > Which tells me you cannot use an expression as a file name. The docs support this conclusion. You probably need to use EXECUTE after constric

Re: [GENERAL] Retrieving comment of rules and triggers

On Thu, Jun 9, 2016 at 4:45 PM, Alex Magnum wrote: > Hi, is there a way to retrieve the comment of rules and triggers. I worked > it out on functions, tables, views but am kind of stuck with rules and > triggers. > ​ https://www.postgresql.org/docs/devel/static/functions-info.html ​Table ​9-63.

Re: [GENERAL] What is the general opinion on use of tablespaces

On Fri, Jun 10, 2016 at 12:26 PM, Rakesh Kumar wrote: > I saw a slide recently where the use of tablespaces was discouraged. > What does the community think of tablespaces. > They are a tool and their use should neither be encouraged nor discouraged but rather understood and used when appropriat

Re: [GENERAL] [HACKERS] Online DW

On Fri, Jun 10, 2016 at 4:11 AM, Sridhar N Bamandlapally < sridhar@gmail.com> wrote: > Hi > > Is there any feature in PostgreSQL where online DW (Dataware housing) is > possible ? > > am looking for scenario like > > 1. Production DB will have CURRENT + LAST 7 DAYS data only > > 2. Archive/DW

Re: [GENERAL] What is the general opinion on use of tablespaces

On Fri, Jun 10, 2016 at 1:12 PM, Rakesh Kumar wrote: > > Their main problem to overcome when using them is that they tie > PostgreSQL > > much more tightly to the underlying configuration of the operating system > > and thus you need to ensure that your processes and procedures > accommodate > >

Re: [GENERAL] BDR

On Fri, Jun 10, 2016 at 2:12 PM, Rakesh Kumar wrote: > Sorry if this question was asked before. As I understand currently > BDR does not support the replicating nodes to run different major > versions, like > 9.4 <-> 9.5. > > Is this in the works? > ​This seems relevant...​ ​http://bdr-project

Re: [GENERAL] table name size

On Mon, Jun 13, 2016 at 7:21 AM, Sridhar N Bamandlapally < sridhar@gmail.com> wrote: > Hi > > how to create table name with size, the limitation we are facing is 63 > length > > these are dynamic tables created from application > > issue is: > we cannot suggest/tell client to change NAMEDATALE

Re: [GENERAL] Index seems "lost" after consecutive deletes

On Monday, June 13, 2016, Edson Richter wrote: > Em 13/06/2016 23:36, Edson Richter escreveu: > > Em 13/06/2016 23:18, rob stone escreveu: > > On Mon, 2016-06-13 at 22:41 -0300, Edson Richter wrote: > > Em 13/06/2016 22:33, Edson Richter escreveu: > > I've a table "A" with 4,000,000 records. > >

Re: [GENERAL] Index seems "lost" after consecutive deletes

On Tue, Jun 14, 2016 at 12:51 AM, Edson Richter wrote: > Em 14/06/2016 01:33, David G. Johnston escreveu: > > On Monday, June 13, 2016, Edson Richter < > edsonrich...@hotmail.com> wrote: > >> Em 13/06/2016 23:36, Edson Richter escreveu: >> >> Em 13/06/2

Re: [GENERAL] How to pass jsonb and inet arguments to a stored function with JDBC?

On Tue, Jun 14, 2016 at 9:33 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Dear PostgreSQL users, > > I have a stored procedure defined as: > > CREATE OR REPLACE FUNCTION words_merge_users( > IN in_users jsonb, > IN in_ip inet, > OUT out_uid integer) >

Re: [GENERAL] How to pass jsonb and inet arguments to a stored function with JDBC?

​Please don't top-post. On Tue, Jun 14, 2016 at 10:44 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > Thank you, David - > > PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid FROM > words_merge_users(?::jsonb, ?::inet)"); > > seems to work. Is it ok to call setStri

Re: [GENERAL] regarding schema only migration from sqlserver to postgres with runmtk.sh

On Wed, Jun 15, 2016 at 8:31 AM, Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > Running EnterpriseDB Migration Toolkit (Build 49.0.4) ... > ​You should contact the supplier of this tool using their support channels. The community is not responsible for this product. I will note that by

Re: [GENERAL] Question about RUM-index

On Wed, Jun 15, 2016 at 6:56 AM, Andreas Joseph Krogh wrote: > Hi. > > First; Is this the correct forum to ask questions about the Postgres Pro's > new RUM-index? > ​-general is fairly broad in scope and the authors do monitor here as far as I am aware. I'm not sure this is strictly related to

Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

On Wed, Jun 15, 2016 at 3:31 AM, Albe Laurenz wrote: > Yogesh Sharma wrote: > > I have doubt regarding release notes of all versions. > > As per release notes, below change logs are mentioned in all versions. > > > > "(8.3.8,8.4.1,8.2.14) Make LOAD of an already-loaded loadable module > into a no

Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

On Wed, Jun 15, 2016 at 9:52 AM, Rakesh Kumar wrote: > > 8.2, 8.3, and 8.4 are all Major releases of the PostgreSQL product. For > > most other products it would as if the numbering went from "15, to 16, to > > 17". The last 8.x release was 8.4 (so, there were 5 major releases that > all > > sh

Re: [GENERAL] Moving from PHP to Java: A result was returned when none was expected.

On Wed, Jun 15, 2016 at 10:30 AM, Jan de Visser wrote: > > Point is that you're doing a SELECT. A SELECT returns a result, which can > be > empty. I would use executeQuery and ignore the result. > > There is a bit of a mismatch between the JDBC stored procedure model and > the > pgsql function mo

Re: [GENERAL] Moving from PHP to Java: A result was returned when none was expected.

On Wed, Jun 15, 2016 at 11:24 AM, Jan de Visser wrote: > On Wednesday, June 15, 2016 10:43:13 AM EDT David G. Johnston wrote: > > On Wed, Jun 15, 2016 at 10:30 AM, Jan de Visser > wrote: > > > Point is that you're doing a SELECT. A SELECT returns a result, which >

Re: [GENERAL] Moving from PHP to Java: A result was returned when none was expected.

On Wed, Jun 15, 2016 at 3:07 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > I only understand a quarter of what you guys are writing, > but to me the JDBC driver throwing SQLException > "A result was returned when none was expected" > when my stored function is declared as "void" with

Re: [GENERAL] regarding schema only migration from sqlserver to postgres with runmtk.sh

On Wed, Jun 15, 2016 at 11:27 AM, Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > may i know recommended approach to migrate the tables from sql server to > postgresql instead of using 3rd party tools > > ​Answering this specifically. There are none. The community PostgreSQL project mai

Re: [GENERAL] regarding schema only migration from sqlserver to postgres with runmtk.sh

On Wed, Jun 15, 2016 at 11:27 AM, Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > > i earlier mentioned the host name of the server (archive.tf.com) > > ​No, you did not. And if you believe you did that may indeed be part of the problem. The log you showed has this: conn =jdbc:postgresq

Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

On Wednesday, June 15, 2016, Yogesh Sharma < yogesh1.sha...@nectechnologies.in> wrote: > Dear David sir/All, > > > > Thanks for your help. > > Just wanted to confirm few things. > > > > (9.3.5,9.2.9,9.1.14,9.0.18,8.4.22) Fix REASSIGN OWNED to not fail for > text search objects (Álvaro Herrera) > >

Re: [GENERAL] regarding schema only migration from sqlserver to postgres with runmtk.sh

On Thu, Jun 16, 2016 at 10:19 AM, Durgamahesh Manne < maheshpostgr...@gmail.com> wrote: > > sir > > PostgreSQL always no1 in world > as per above discussion > > migration not working from sql to postgreSQL with runmtk.sh > > > Connecting with source SQL Server database server... > Connected to Mi

Re: [GENERAL] Comparing tsvector results

On Thu, Jun 16, 2016 at 2:21 PM, Liza Sazonova wrote: > Hello, > > I'm trying to implement a keyword-based search on my database. > > I have some text input (Say, "The A or B and C with D") and a list of > keywords ("B","X","Y"). I want to identify which keywords are present in > the text input.

Re: [GENERAL] Comparing tsvector results

Please do not top-post. ​On Thu, Jun 16, 2016 at 2:38 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Jun 16, 2016 at 2:21 PM, Liza Sazonova wrote: > >> Hello, >> >> I'm trying to implement a keyword-based search on my database. >>

Re: [GENERAL] Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

On Thu, Jun 16, 2016 at 8:40 PM, Erdmann, Markus @ Bellevue < markus.erdm...@cbre.com> wrote: > Hello, > > We’re trying to debug a performance issue affecting our staging database, > and we’ve narrowed it down to a difference in the query optimizer in 9.5.2. > Upgrading to 9.5 is important for us

Re: [GENERAL] Vacuum full: alternatives?

On Monday, June 20, 2016, John R Pierce wrote: > On 6/20/2016 8:03 AM, Scott Mead wrote: > >> >> I believe that free space is only available to UPDATE, not INSERT. >> > > incorrect. in fact, an update is performed identically to an INSERT + > DELETE(old) > > Except for heap-only-tuple optimizat

Re: [GENERAL] Help with namespaces in xpath (PostgreSQL 9.5.3)

On Sun, Jun 19, 2016 at 5:09 PM, Allan Kamau wrote: > I have an xml document from which I would like to extract the contents of > several elements. > > I would like to use xpath to extract the contents of "name" from the xml > document shown below. > > WITH x AS > ( > SELECT > ' > http://uniprot.

Re: [GENERAL] Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

On Fri, Jun 17, 2016 at 5:05 PM, Erdmann, Markus @ Bellevue < markus.erdm...@cbre.com> wrote: > Thank you Tom and David for your very helpful replies. We dumped and > restored the RDS staging database on a local installation of pg and were > not able to reproduce the issue in 9.5.2, which led us t

Re: [GENERAL] optimizing a query

On Tue, Jun 21, 2016 at 6:44 PM, Adrian Klaver wrote: > On 06/21/2016 03:33 PM, Jonathan Vanasco wrote: > >> >> >> In effort of simplifying the work, I've created indexes on t_a that have >> all the related columns. >> >> CREATE INDEX test_idx ON t_a(col_1, id) WHERE col_2 IS NOT FALSE; >

Re: [GENERAL] optimizing a query

On Wed, Jun 22, 2016 at 2:23 PM, Jonathan Vanasco wrote: > > On Jun 22, 2016, at 4:25 AM, Erik Gustafson wrote: > > > don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ? > > that table has indexes on all columns. they're never referenced because > the rows are so short. this w

Re: [GENERAL] Postgres 9.5.2 upgrade to 9.6

On Wed, Jun 22, 2016 at 2:36 PM, Michelle Schwan wrote: > I have a question about the upgrade from 9.5.2 to 9.6 > > > > I know 9.6 is still in beta – but I’m trying to be ahead of the game! > > > > I found instructions on how to move from 9.4 to 9.5. I will try testing > these steps with 9.5 to

Re: [GENERAL] optimizing a query

On Wed, Jun 22, 2016 at 2:23 PM, Jonathan Vanasco wrote: > > Note 2: > > This is odd, but this index is used by the planner: > CREATE INDEX idx_partial_fkey_id ON table_a(fkey_1, id) > WHERE col_partial IS NOT FALSE; > > but this index is never used: >

Re: [GENERAL] Sub-query having NULL row returning FALSE result

On Wed, Jun 29, 2016 at 2:07 AM, Sridhar N Bamandlapally < sridhar@gmail.com> wrote: > Hi > > Please go through below case > > postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR); > CREATE TABLE > postgres=# INSERT INTO emp VALUES (null, 'aaa'); > INSERT 0 1 > postgres=# INSERT INTO

Re: [GENERAL] Question about "grant create on database" and pg_dump/pg_dumpall

On Thu, Jun 30, 2016 at 1:38 PM, Murphy, Kevin wrote: > Is it expected that "grant * on database" grants are dumped only by > `pg_dumpall -s` and not by `pg_dump -s` or `pg_dumpall -g`? > > Some people might assume that to restore a cluster it should be sufficient > to restore pg_dumpall globals

Re: [GENERAL] Update multiple rows in a table with different values

On Fri, Jul 1, 2016 at 10:26 AM, shankha wrote: > > PREPARE updatearrayplan(BigInt[], BigInt[]) AS > for i in size($1) > DO > update s.t1 > SET c3 = $2[$i] > WHERE c2 = $1[$i] > END FOR > > EXECUTE updatearrayplan({20, 30}, {275,

Re: [GENERAL] pg_dump fundenental question

On Tue, Jul 5, 2016 at 10:54 AM, J. Cassidy wrote: > Hello all, > > I have hopefully an "easy" question. > > If I issue the pg_dump command with no switches or options i.e. > > /usr/local/pgsql/bin/pg_dump -v dbabcd > /data3TB/Backup/dbabcd > > Is their any "default" compression involved or not?

Re: [GENERAL] Possible to create canonicalized range type without being superuser?

On Tue, Jul 5, 2016 at 5:37 PM, Tom Lane wrote: > Paul Jungwirth writes: > > The problem is this (tried on 9.3 and 9.5): > > db=> create type inetrange; > > ERROR: must be superuser to create a base type > > So I'm wondering whether there is any way around this circle without > > being a superu

Re: [GENERAL] Best way to insert a row with two columns having the value based on the same argument generated by another postgres function.

On Thu, Jul 7, 2016 at 2:56 AM, Silk Parrot wrote: > Hi, > >I am trying to build a user database. The steps for creating a new user > are: > > 1. Use gen_salt to create a salt. > 2. Compute the hash based on the salt and password and store both the hash > and the salt into a new row. > > > Th

Re: [GENERAL] [BUGS] Where clause in pg_dump: need help

On Sun, Jul 10, 2016 at 12:53 PM, Prashanth Adiyodi wrote: > 1.- You have a backup with a series of tables which get inserted WITH a > timestamp. > Adi-The series of tables may or may not have timestamp > ​Then I think you cannot do what you want using only built-in PostgreSQL capabilities. Met

Re: [GENERAL] How to insert/update a bunch of JSOB values?

On Mon, Jul 11, 2016 at 8:45 AM, Deven Phillips wrote: > We need to update a JSONB data structure in our tables to include an > 'is_valid' flag. I was able to build a CTE which I use to create a temp > table containing the 'is_valid' value, the path where it needs to be set, > and the join criter

Re: [GENERAL] postgresql "init script" for postgres 9.2.15

Please don't top-post. On Wed, Jul 13, 2016 at 5:35 PM, Steve Langlois wrote: > Thank you but in our appliance, we are not running postgres as a service, > we are running it programatically with this script, call postmaster to > start it for instance instead of using the service framework. Is th

Re: [GENERAL] Merging timeseries in postgres

On Thu, Jul 14, 2016 at 7:56 AM, Tim Smith wrote: > > 2003-01-03 (null) 0.003 ​Where​ did the "0.003" come from? The result you are looking for works if you full outer join on dx1 - at least for the sample data. David J.

Re: [GENERAL] Merging timeseries in postgres

On Thu, Jul 14, 2016 at 8:18 AM, Nick Babadzhanian wrote: > Whats exactly is wrong with the following query? > > select > dx date, > nx, > nx1 > from > test t > join test1 t1 on t.dx=t1.dx1 > ; > > ​Please don't top-post. test t join test1 t1 -- th

Re: [GENERAL] migrating data from an old postgres version

On Fri, Jul 15, 2016 at 10:02 AM, Paul Ramsey wrote: > > Make sure to pg_dump -Fc > (note the flags) > > ​You need to also include "​--quote-all-identifiers" if you intend for the dump to be restored onto a newer version of PostgreSQL. https://www.postgresql.org/docs/9.6/static/app-pgdump.html

Re: [GENERAL] unique constraint with several null values

On Wed, Jul 20, 2016 at 2:14 PM, Mark Lybarger wrote: > I have a relation such as > create table order_item ( id uuid not null primary key, order_id number > not null, item_code text, make text, model text, reason text, size text, > expiration_date timestamp ); > > where the combination of the co

Re: [GENERAL] Array value from table as parameter

On Wed, Jul 20, 2016 at 1:17 PM, Aislan Luiz Wendling < aislanl...@hotmail.com> wrote: > > it doesn't work, as the value comes like this: {"x","y"} > > ​SELECT '{"x","y"}'::text[] = array['x','y']::text[]; -- TRUE David J.

Re: [GENERAL] For storing XML version in our table.

On Wed, Jul 20, 2016 at 3:09 AM, zubair alam wrote: > Hi > How i can store my xml data with their version in postgres database > table. > Works on 9.5...though it doesn't seem to validate the provided value, just stores it. ​SELECT E'\nHi'::xml::text; SELECT E'\nHi'::xml::text; CREATE TA

Re: [GENERAL] Return results of join with polymorphically-defined table in pl/pgsql

On Sunday, July 24, 2016, Peter Devoy wrote: > > However, I would like to create a function which returns the resultset > of an INNER JOIN with table1 being polymorphic and table2 being a > result set of column types which do not change. Is this possible? > Providing a concrete example might hel

Re: [GENERAL] Return results of join with polymorphically-defined table in pl/pgsql

On Mon, Jul 25, 2016 at 6:14 AM, Peter Devoy wrote: > > BEGIN > RETURN QUERY > EXECUTE > format( > ' > SELECT > %1$I.*, > dist_query.distance AS appended_distance, > dist_query.centroid AS appended_centroid > FROM %1$I >

Re: [GENERAL] How to stop script executions

On Tue, Jul 26, 2016 at 8:53 AM, Dev Kumkar wrote: > Hello Experts, > > I want to have my postgreSQL database to only execute SQLs and avoid > execution of perl and python script executions. > > Can you please suggest ways to achieve this? > > The way to go about prohibiting their usage altogethe

Re: [GENERAL] How to stop script executions

On Tue, Jul 26, 2016 at 9:16 AM, Alex Ignatov wrote: > revoke usage on language plpythonu from username; > > revoke usage on language plperl(u) from username; > Actually, the usage permission for an untrusted language appears to be pointless. Only superusers can create functions in those languag

Re: [GENERAL] How to stop script executions

On Tue, Jul 26, 2016 at 9:19 AM, Dev Kumkar wrote: > On Tue, Jul 26, 2016 at 6:35 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> The way to go about prohibiting their usage altogether is specific to >> your installation method. But by default those

Re: [GENERAL] How to stop script executions

On Tue, Jul 26, 2016 at 9:21 AM, Sameer Kumar wrote: > >> Yeah these extensions are not present, are their any chances of running >> OS commands from database? >> > > What do you mean by "from database"? I think you need to lay down your > requirement and goal more clearly. > > ​Typically this me

Re: [GENERAL] How to stop script executions

On Tue, Jul 26, 2016 at 9:48 AM, Dev Kumkar wrote: > On Tue, Jul 26, 2016 at 6:54 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> ​Superuser: yes (specifically I'm thinking the COPY FROM PROGRAM command, >> but also C language functions) >&g

Re: [GENERAL] question on parsing postgres sql queries

On Tue, Jul 26, 2016 at 3:20 PM, Jerome Wagner wrote: > > Would it make sense to use these sgml synopsis as some kind of source of > truth, parse them, and automatically generate a parser for a specifc > language ? > ​What problem are you trying to solve here?​ to whit not everything that can b

Re: [GENERAL] WAL directory size calculation

On Thu, Jul 28, 2016 at 9:25 AM, Moreno Andreo wrote: > I've read somewhere that the formula should be 16 MB * 3 * > checkpoint_segment in size. > ​[...]​ > Using the above formula I have: > 16 MB * 3 * 1 GB > that leads to to ... uh .. 48000 TB? > ​You seem to be mis-remembering the formu

Re: [GENERAL] Multiple NOTIFY is ignored

On Thu, Jul 28, 2016 at 10:29 AM, Markus Kolb < markus.kolb+postg...@tower-net.de> wrote: > If I only do one > SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT > dblink_disconnect('{4}'); > where {3} is > NOTIFY upd_pgm, '0|UPDATE|A|'; NOTIFY upd_pgm, '0|DELETE|A|' > > only

Re: [SPAM] Re: [GENERAL] WAL directory size calculation

On Fri, Jul 29, 2016 at 7:08 AM, Moreno Andreo wrote: > ​R​ > egarding backups I disagree. Files related to database must be consistent > to the database itself, so backup must be done saving both database and > images. > ​I'd suggest you consider that such binary data be defined as immutable. T

Re: [GENERAL] Log all queries before migration ?

On Fri, Jul 29, 2016 at 7:35 AM, ben.play wrote: > Hi guys, > > We have some storage problem on our dedicated server and we have to > migrate. > Do you know how can I log all queries in order to have no downtime during > migration ? > > ​I don't understand how logging of queries has anything to d

[GENERAL] Want to json_populate_record AND save/link the source JSON text

Given: CREATE TABLE jsontbl ( id int, label text, json_obj json); I need the create two rows from the following JSON, with the json_obj in each row equal to the text/json object in the content 1,'one',{"id":1,"label":"one"} 2,'two',{"id":2,"label":"two"} I'd like to not have to hard-code the co

Re: [GENERAL] pg_archivecleanup standalone bash script

On Sun, Jul 31, 2016 at 6:11 PM, Patrick B wrote: > > CHKPOINT=$(find $ARCHIVEDIR -type f -mtime +30 -name '00*' -printf '%f\n' > | sort -r | head -1) > > /usr/pgsql-${PG_VERSION}/bin/pg_archivecleanup $ARCHIVEDIR $CHKPOINT > > > ​While I'm a bit suspect of this entire script/approach I believe t

Re: [GENERAL] Commands history with psql in a Windows command line shell

On Tue, Aug 2, 2016 at 11:31 AM, Melvin Davidson wrote: > Are you doing this in PostgreSQL 7.3 and Windows XP? Can you provide us > with a little more useful information like current PostgreSQL version and > O/S? > Otherwise we have absolutely no idea what the problem might be. > > "Windows (8.1)

Re: [GENERAL] Problem with partitioning

On Wed, Aug 3, 2016 at 1:16 PM, Adrian Klaver wrote: > On 08/03/2016 10:12 AM, JotaComm wrote: > >> Hello, >> >> I'm working with partitioning and I have one trigger for insert and >> update on parent table, but my trigger is not working for update >> operations. The problem is: the trigger is no

Re: [GENERAL] Column order in multi column primary key

On Mon, Aug 8, 2016 at 1:47 PM, Craig Boucher wrote: > PG 9.5 > > > > I’m in the process of converting our application from Sql Server to > Postgresql. I’m taking advantage of this process to make some database > design changes. > > > > Our database contains data for many customers and I have a

Re: [GENERAL] Column order in multi column primary key

On Mon, Aug 8, 2016 at 3:06 PM, Craig Boucher wrote: > Here is one of the tables that can have millions of rows and foreign key > constraints to 5 other tables. > > > > CREATE TABLE public.work_session > > ( > > work_session_id integer NOT NULL DEFAULT nextval('worksession_ > worksessionid_seq'

Re: [GENERAL] Column order in multi column primary key

On Mon, Aug 8, 2016 at 4:35 PM, Craig Boucher wrote: > Thanks David. I’ve thought about the hierarchy you mentioned but the > hierarchy can change and I need to capture it as it was when the data was > generated. > > > > I should have pointed out in my last response that I was wondering if the >

Re: [GENERAL] select array_remove(ARRAY[NULL,NULL,NULL],NULL); returns {} instead of {NULL,NULL,NULL}

On Mon, Aug 8, 2016 at 5:51 PM, Alban Hertroys wrote: > > > On 08 Aug 2016, at 20:19, Tom Lane wrote: > > > > Alexander Farber writes: > >> I wonder, why the following returns NULL and not 0 in 9.5.3? > > > >> # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); > > > > Because t

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

On Wed, Aug 10, 2016 at 9:39 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > > ERROR: query has no destination for result data > HINT: If you want to discard the results of a SELECT, use PERFORM instead. > CONTEXT: PL/pgSQL function words_check_words(integer,integer,jsonb) line > 13

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

On Wed, Aug 10, 2016 at 1:19 PM, Pavel Stehule wrote: > > > 2016-08-10 19:05 GMT+02:00 Alexander Farber : > >> Thank you Adrian and others - >> >> I am trying to replace INSERT into temp table in my custom function by >> RETURN NEXT, but get an error: >> >> CREATE OR REPLACE FUNCTION words_check_

Re: [GENERAL] Postgres Pain Points #3 postgres role

On Thu, Aug 11, 2016 at 1:22 PM, Adrian Klaver wrote: > > The only other thing I have seen is this: > > https://help.ubuntu.com/community/PostgreSQL > > and it shows: > > sudo -u postgres psql postgres > > ​The second postgres redundant - the default database to connect to is the name of the conn

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Thu, Aug 11, 2016 at 1:13 PM, support-tiger wrote: > It would be great if the Ruby and Node drivers can be brought under the > Postgres team umbrella and make them as reliable and clearly documented as > the Python or jdbc drivers. What makes you say that the Python are JDBC drivers are "und

Re: [GENERAL] regexp_replace double quote

On Mon, Aug 15, 2016 at 9:27 AM, Михаил wrote: > Hi! > > I need to escape double quotes only: > test=# select regexp_replace('"""{Performer,"Boomwacker ""a"" > Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g'); > regexp_replace > ---

Re: [GENERAL] PGPASSWORD - More than one in a bash script

On Thursday, August 18, 2016, Patrick B wrote: > Hi guys, > > I'm writing a bash script to dump and restore (pg_dump + pg_restore) a > test database. > > However, the username to access the pg_dump server is different of the one > to access pg_restore. > > I'm using the PGPASSWORD parameter on th

<    1   2   3   4   5   6   7   8   9   10   >