Re: SQL syntax

> On Mar 2, 2018, at 5:41 PM, Adrian Klaver wrote: > >> On 03/02/2018 04:36 PM, Dale Seaburg wrote: >> My mind is drawing a blank. Not sure where to go to find the answer. Here >> is the statement in C#: >> sSQL = "SELECT \"Image_Filename\" FROM \"Instruments\" WHERE "; >> To finish off the W

Re: postgresql-10.3 on unbuntu-17.10 - how??

Thanks, I forgot that the older repos also received the pg-10 update. Unfortunately but no luck with Xenial either, slightly different but similar conflicts. My main motivation for updating to 10.3 was to be able to load data dumped from a 10.3 database.  pg_restore complained about "unsupported

Re: postgresql-10.3 on unbuntu-17.10 - how??

Or you compile it? That was going to be my next step.  But I don't think a simple compile from source would do because Ubuntu's package manager wouldn't be aware that Postgresql was now available to satisfy other packages' dependencies. So I would need to rebuild the Ubuntu source package.  I

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/28/2018 08:29 PM, Ken Beck wrote: Is it possible a big problem is that the password for user postgres not longer works for some reason, not sure why. It appears the password can not be rest or changed without knowing the original, and what I thought was the original no longer works. Ma

Re: Please suggest the best suited unit test frame work for postgresql database.

On 04/02/2018 10:58 AM, Steven Lembark wrote: On Sun, 1 Apr 2018 10:26:32 +0530 Raghavendra Rao J S V wrote: Good morning. Please suggest the best suited unit test frame work for postgresql database and also shared the related documents to understand the framework. If anyone there knows Pe

Re: single sql, multiple schemas, one result set

On 04/03/2018 11:47 AM, PegoraroF10 wrote: Suppose a DB with dozens of schemas with same structure. DB Schema1 Table1 Table2 Schema2 Table1 Table2 Schema3 Table1 Table2 Then we want to execute a SQL on specific schemas and the result of it could be a UNION

Re: Archiving Data to Another DB?

On 04/11/2018 10:24 AM, Ron wrote: On 04/11/2018 11:15 AM, Don Seiler wrote: Let's say I have two DBs: main (9.6.6) and archive (10.2). I have a table in main where I want to archive data older then 60 days. For various reasons, the table is not partitioned, so for now we must use DELETE.

Re: hardcode password in connect string

On 04/13/2018 12:46 PM, James Keener wrote: Is setting it as an environment variable an option? https://www.postgresql.org/docs/9.1/static/libpq-envars.html Alternatively, a service file? https://www.postgresql.org/docs/9.0/static/libpq-pgservice.html Jim On April 13, 2018 2:43:01 PM EDT, D

Re: Postgresql database encryption

On 04/20/2018 05:43 PM, Ron wrote: On 04/20/2018 06:11 PM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: On 04/20/2018 03:55 PM, Vick Khera wrote: On Fri, Apr 20, 2018 at 11:24 AM, Vikas Sharma For anyone to offer a proper solution, you need to say what purpose y

Surprised by index choice for count(*)

Should I be?  I would have thought the pk would have been chosen v. function index? explain analyse select count(*) from bc.segment s; QUERY PLAN --

Re: Surprised by index choice for count(*)

Thank you both. Simple, as expected. And I’m easily surprised. Version 10 (perhaps obviously) for those scoring at home. > On May 1, 2018, at 10:11 AM, Tom Lane wrote: > > Rob Sargent writes: >> Should I be? I would have thought the pk would have been chosen v. >> fu

Re: Add schema to the query

> On May 6, 2018, at 8:19 PM, Igor Korot wrote: > > Hi, ALL, > > Is there an easy way to add the schema to the following query: > > SELECT u.usename FROM pg_class c, pg_user u WHERE u.usesysid = > c.relowner AND relname = ? > > Thank you. > Are you looking for the owner of a particular sche

Re: Help with SQL

> On May 27, 2018, at 6:42 PM, anand086 wrote: > > Hi, > > I have a requirement of writing plpgsql function to create partial indexes > on child tables if it exists on parent table. The function will have > schemname, childtablename, tableowner as input. > > I am using the below code to ident

Re: reduce number of multiple values to be inserted

> On May 29, 2018, at 6:10 PM, tango ward wrote: > > > Hi, > > I am working on inserting multiple values for a table. I need to insert 3 > values of data for each age of the students from the same village. It will be > 3 different ages of student per village. > > My sample code: > > > c

Re: reduce number of multiple values to be inserted

> On May 29, 2018, at 6:32 PM, tango ward wrote: > > On Wed, May 30, 2018 at 8:29 AM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: > > Is “current_timezone, current_timezone” just a typo? I think you need to > make the 117 data lines and load using \copy &

Re: reduce number of multiple values to be inserted

On 05/29/2018 06:36 PM, Adrian Klaver wrote: On 05/29/2018 05:10 PM, tango ward wrote: Hi, Not sure where you are pulling the data from and how it is ordered, but an outline: data_input (Assuming sorted by village and then age) for village in data_input: for age in village:

Re: Insert UUID GEN 4 Value

> On May 30, 2018, at 9:57 PM, tango ward wrote: > >> On Thu, May 31, 2018 at 11:53 AM, tango ward wrote: >>> On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe >>> wrote: >>> tango ward wrote: >>> > I found this: >>> > >>> > CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; >>> > SELECT uuid_genera

Re: Query hitting empty tables taking 48 minutes

On 06/07/2018 12:55 PM, Robert Creager wrote: On Jun 7, 2018, at 12:40 PM, Adrian Klaver > wrote: On 06/07/2018 11:17 AM, Robert Creager wrote: I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 1.8.0_131, jdbc 9.3-1104-jdbc41 which

Re: Query hitting empty tables taking 48 minutes

On 06/07/2018 01:13 PM, Adrian Klaver wrote: On 06/07/2018 12:11 PM, Rob Sargent wrote: What's the url doing in "blob_id = ds3.blob.id <http://ds3.blob.id";? I have run into this before, it is an email artifact. OK, thanks. The code presented is just a genera

Re: Run Stored procedure - function from VBA

> On Jun 18, 2018, at 9:47 AM, Łukasz Jarych wrote: > > CREATE OR REPLACE FUNCTION totalRecords () > RETURNS integer AS $total$ > declare > total integer; > BEGIN >SELECT count(*) into total FROM COMPANY; >RETURN total; > END; > $total$ LANGUAGE plpgsql;

Re: Run Stored procedure - function from VBA

> On Jun 18, 2018, at 9:47 AM, Łukasz Jarych wrote: > > Hi Guys, > > i have example function : > > CREATE OR REPLACE FUNCTION totalRecords () > RETURNS integer AS $total$ > declare > total integer; > BEGIN >SELECT count(*) into total FROM COMPANY; >RETURN total; > END; > $total$

Re: Is postorder tree traversal possible with recursive CTE's?

On 06/19/2018 01:14 PM, Hellmuth Vargas wrote: Hi with partial sum: with recursive pizza (name, step, ingredient, quantity, unit, rel_qty, path, weight) as (         select                 name, step, ingredient, quantity, unit         ,       quantity::numeric(10,2)         ,       ste

Re: Run Stored procedure - function from VBA

On 06/18/2018 09:51 PM, Łukasz Jarych wrote: Thank you Rob, question is it is the optimal way to run SP from VBA? Or not? Best, Jacek 2018-06-19 1:34 GMT+02:00 Rob Sargent <mailto:robjsarg...@gmail.com>>: On Jun 18, 2018, at 9:47 AM, Łukasz Jarych mailto:jarys...@gmail.co

Re: Load data from a csv file without using COPY

On 06/19/2018 03:14 PM, Ravi Krishna wrote: If performance is relevant then your app should probably be using COPY protocol, not line by line inserts. It's supported by most postgresql access libraries. If your app does that then using "\copy" from psql would be an appropriate benchmark. Ac

Re: Run Stored procedure - function from VBA

:00 Asif Ali : >> how the fuck i unsubscribe to this mailing list , i get more than 100 emails >> a day >> >> Bye >> >> >> From: Rob Sargent >> Sent: Wednesday, June 20, 2018 12:54 AM >> To: Łukasz Jarych >> Cc: pgsql-gene...@postgre

Re: CTE optimization fence

I don’t think the fence analogy works. Try wall (a la Berlin). I count myself amongst those who thought “Ooh this little CTE will garner just what the rest of the query needs”. Only to find the planner didn’t groc that optimization. Is it a matter of deciding to trust the SQLer and runtime ana

Re: Not able to update some rows in a table

On 07/02/2018 09:59 AM, Marco Fochesato wrote: I would suggest writing a self-contained script that creates the table, inserts a single record, and updates that record.  Present that for consideration along with a description or capture of the results of running the script on

Re: FK v.s unique indexes

> On Jul 5, 2018, at 1:30 AM, Rafal Pietrak wrote: > > > > W dniu 04.07.2018 o 00:55, David G. Johnston pisze: >> On Tuesday, July 3, 2018, Rafal Pietrak > > wrote: >> >> >>ERROR: there is no unique constraint matching given keys for referenced >>table "tes

Re: Open Source tool to deploy/promote PostgreSQL DDL

On 07/10/2018 05:04 PM, Hustler DBA wrote: Thanks Adrian and Rich, I will propose sqitch to the client, but I think they want something with a GUI frontend. They want to deploy database changes, track which environments the change was deployed to, be able to rollback a change (with a rollb

Re: How to watch for schema changes

On 07/12/2018 05:28 PM, Igor Korot wrote: Hi, On Thu, Jul 12, 2018 at 12:16 PM, David G. Johnston wrote: On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot wrote: No, see: https://www.postgresql.org/docs/10/static/sql-createtrigger.html AFTER trigger on views are STATEMENT level only. But I d

Re: User documentation vs Official Docs

On 07/19/2018 11:04 AM, Peter J. Holzer wrote: On 2018-07-18 08:09:35 +1000, Tim Cross wrote: If using web widgets to author content on the wiki is the main impediment for contributing content, maybe we should see if the wiki provides alternative access methods. I've used wikis in the past wh

Re: User documentation vs Official Docs

On 07/19/2018 06:58 PM, Adrian Klaver wrote: On 07/19/2018 05:54 PM, Adrian Klaver wrote: On 07/19/2018 05:43 PM, Melvin Davidson wrote:  > Then again people might use shared, university or library computers Would you please be so kind as to inform us which university or library allo

Re: User documentation vs Official Docs

On 07/20/2018 05:48 PM, Joshua D. Drake wrote: On 07/20/2018 03:59 PM, Alvaro Herrera wrote: I don't see why we need this thread to continue.  This sounds like somebody looking for a solution when they don't yet know what the problem is. Unfortunately, you don't understand the problem whic

alter table docs

I was just looking up alter table add constraint syntax under "current(10)" and we get     ADD /table_constraint/ [ NOT VALID ]     ADD /table_constraint_using_index/ There is a description below for the using_index version but none for the plain version.  There is a block for the plai

Re: alter table docs

On 07/30/2018 03:07 PM, Adrian Klaver wrote: On 07/30/2018 09:57 AM, Rob Sargent wrote: I was just looking up alter table add constraint syntax under "current(10)" and we get     ADD /table_constraint/ [ NOT VALID ]     ADD /table_constraint_using_index/ There is a d

Re: alter table docs

OK, I was expecting a block in the enclosing text-area for this simple form of the command similar to the one for the /using_index/ form.  I suppose the existence of the latter lead me to expect the former.  If it's as intended I'm fine with that. Aah I see, you where referring to: "and t

Re: alter table docs

I'm not anxious to see it back-patched. On 07/30/2018 04:25 PM, Tom Lane wrote: Rob Sargent writes: Exactly.  That that is in the "box" made me think a similar blurb for the non-index version should be there also. This seems to have been fixed in v11 but n

Re: JSONB filed with default JSON from a file

On 08/13/2018 12:11 PM, Tom Lane wrote: Merlin Moncure writes: On Mon, Aug 13, 2018 at 12:56 PM mrcasa bengaluru wrote: Thanks! However, this involves writing the entire JSON in the schema file looks inconvenient. I was hoping I would be able to reference to an external JSON file which c

Re: Erroneous behavior of primary key

> On Aug 27, 2018, at 1:50 PM, Daniel J Peacock wrote: > > Good afternoon, all. > I've got an odd situation with a table that has a varchar(255) as the primary > key that is getting key values from an Elasticsearch engine. What I'm > finding is that even though there is a primary key on the

Re: WARNING: could not flush dirty data: Function not implemented

On 09/03/2018 12:41 PM, Austin Drenski wrote: Dmitri Maziuk mailto:dmaz...@bmrb.wisc.edu>> wrote: > Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: >> Ravi Krishna mailto:sravikris...@aol.com>> writes: Whee ... so you get to cope with all the bugs/idiosyncrasies of three operating system

Re: Sv: Re: How to get shorter SERVER_VERSION in psql-prompt?

On 09/03/2018 03:42 PM, Alvaro Herrera wrote: On 2018-Sep-03, Andreas Joseph Krogh wrote: select setting as server_version from pg_settings where name = 'server_version'; ┌──┐ │  server_version  │ ├──┤ │

bad url in docs

Version 10 33.18. SSL Support The pointers off to hp.com seem to have gone away on or about 28Aug2018.  They also fall under the heading of HP OpenVMS Systems Doc which may explain why they've disappeared.

Re: bad url in docs

Sorry.  I didn't see the specific form for documentations issues. On 09/06/2018 04:52 PM, Rob Sargent wrote: Version 10 33.18. SSL Support The pointers off to hp.com seem to have gone away on or about 28Aug2018.  They also fall under the heading of HP OpenVMS Systems Doc which may ex

Re: Select rows when all all ids of its children records matches

On 09/12/2018 10:08 AM, Arup Rakshit wrote: I tried : WITH posts_tags_cte AS (         SELECT post_id, array_agg(tag_id) as tags         FROM posts_tags         WHERE tag_id in (1, 2)         GROUP BY post_id ) SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id

Re: Can I add Index to make a query faster which involves joins on unnest ?

> On Sep 13, 2018, at 12:17 PM, Arup Rakshit wrote: > > The below query basically gives the result by maintaining the order of the > sizes in the list. > > explain analyze select > "price_levels"."name", > "price_levels"."size" > from > "price_levels" > join unnest(arr

heads up on large text fields.

Playing around with files-in-text-field.  I can happily slam a 10M file into a text field in a table defined as gtdb=# \d gt.ld     Table "gt.ld"     Column    | Type | Collation | Nullable | Default --+--+---+--+-  id

Re: heads up on large text fields.

> On Sep 21, 2018, at 7:59 PM, Andres Freund wrote: > >> On 2018-09-21 18:28:37 -0600, Rob Sargent wrote: >> "/tmp/regen.file" gets very large, very fast and I have to >> pg_terminate_backend. Tried this three times, once using "\o test.blob"

Re: heads up on large text fields.

On 09/22/2018 06:00 AM, Andreas Kretschmer wrote: Am 22.09.2018 um 02:28 schrieb Rob Sargent: However, I get into deep dodo when I try redirecting psql output such as     select ld from gt.ld\g /tmp/regen.file works for me if i start psql with -t -A -o /path/to/file (pg 10.5, but psql

Re: Out of Memory

> On Sep 27, 2018, at 3:45 PM, Laurenz Albe wrote: > > Christoph Moench-Tegeder wrote: >> ## Laurenz Albe (laurenz.a...@cybertec.at): >> >>> vm.overcommit_memory = 2 >>> vm_overcommit_ratio = 100 >>> >>> Linux commits (swap * overcommit_ratio * RAM / 100), >> >> ^ >>

Re: Why my query not using index to sort?

On 09/28/2018 12:51 PM, Arup Rakshit wrote: Yes, I have shown the explain plan output. But in my application log the sql query prints 1.7 to 1.9 ms. How often does the production app make this call?  Apparently it could do it 500 times per second.  But at such a rate the network overhead

COPY threads

Can anyone here tell me whether or not the CopyManager facility in JDBC via org.postgresql:postgresql:42.1.4 is internally multithreaded? Running on CentOS 7 (all participants), java8, postgres 10.5

Re: COPY threads

> On Oct 10, 2018, at 10:50 AM, Laurenz Albe wrote: > > Rob Sargent wrote: >> Can anyone here tell me whether or not the CopyManager facility in JDBC >> via org.postgresql:postgresql:42.1.4 is internally multithreaded? >> Running on CentOS 7 (all participants), j

Re: COPY threads

> On Oct 10, 2018, at 1:24 PM, Andres Freund wrote: > > On 2018-10-10 18:50:02 +0200, Laurenz Albe wrote: >> Rob Sargent wrote: >>> Can anyone here tell me whether or not the CopyManager facility in JDBC >>> via org.postgresql:postgresql:42.1.4 is internal

Re: Advice on logging strategy

> On Oct 11, 2018, at 4:26 AM, Mike Martin wrote: > > I have a question on logging strategy > > I have loggin set to > log_statement = 'all' on a network database with logging set to csv so I can > import it to a logging table > > However the database is populated via a nightly routine down

judging acceptable discrepancy in row count v. estimate

Should reality be half again as large as the estimated row count? coon=# select count(*) from sui.segment; count -- 49,942,837 -- my commas (1 row) coon=# vacuum (analyse, verbose) sui.probandset; INFO: vacuuming "sui.probandset" INFO: scanned index "probandset_pkey" to remove 31

Re: judging acceptable discrepancy in row count v. estimate

> On Oct 16, 2018, at 1:01 PM, Tom Lane wrote: > > Rob Sargent writes: >> Should reality be half again as large as the estimated row count? >> coon=# select count(*) from sui.segment; >> count >> -- >> 49,942,837 -- my commas >>

Re: judging acceptable discrepancy in row count v. estimate

> On Oct 16, 2018, at 1:01 PM, Tom Lane wrote: > > Rob Sargent writes: >> Should reality be half again as large as the estimated row count? >> coon=# select count(*) from sui.segment; >> count >> -- >> 49,942,837 -- my commas >>

Re: Measuring the Query Optimizer Effect: Turning off the QO?

> On Jul 7, 2019, at 5:22 PM, Tom Mercha wrote: > > Hi All > > As we know, a query goes through number of stages before it is executed. > One of these stages is query optimization (QO). > > There are various parameters to try and influence optimizer decisions > and costs. But I wanted to m

Re: Measuring the Query Optimizer Effect: Turning off the QO?

> On Jul 7, 2019, at 5:49 PM, Tom Mercha wrote: > > On 08/07/2019 01:46, Rob Sargent wrote: >> >> >>> On Jul 7, 2019, at 5:22 PM, Tom Mercha wrote: >>> >>> Hi All >>> >>> As we know, a query goes through number of stages be

Re: Measuring the Query Optimizer Effect: Turning off the QO?

> On Jul 7, 2019, at 6:01 PM, Rob Sargent wrote: > > > >> On Jul 7, 2019, at 5:49 PM, Tom Mercha > <mailto:merch...@hotmail.com>> wrote: >> >> On 08/07/2019 01:46, Rob Sargent wrote: >>> >>> >>>> On Jul

Re: Measuring the Query Optimizer Effect: Turning off the QO?

> On Jul 7, 2019, at 6:02 PM, Rob Sargent wrote: > > > >> On Jul 7, 2019, at 6:01 PM, Rob Sargent > <mailto:robjsarg...@gmail.com>> wrote: >> >> >> >>> On Jul 7, 2019, at 5:49 PM, Tom Mercha >> <mailto:merch...@ho

Re: Measuring the Query Optimizer Effect: Turning off the QO?

> O > Sorry, maybe my question wasn't clear enough. > > A query can be rewritten in various ways by applying rules and costs of > relational algebra operators, as well as their parallelisation. I am > talking about turning off this query optimization, so I am already > as

Re: Measuring the Query Optimizer Effect: Turning off the QO?

> On Jul 7, 2019, at 6:29 PM, Rob Sargent wrote: > > > >> O >>>>>> Sorry, maybe my question wasn't clear enough. >>>>>> >>>>>> A query can be rewritten in various ways by applying rules and costs of >>>&

Re: How to run a task continuously in the background

> On Jul 17, 2019, at 1:26 AM, Dirk Mika wrote: > > > We used a trigger that called pg_notify > (https://www.postgresql.org/docs/9.5/sql-notify.html > ​) and then had another > > process that LISTENed for notifications. > > > > What

Re: Rearchitecting for storage

> > That would likely keep the extra storage requirements small, but still > non-zero. Presumably the upgrade would be unnecessary if it could be done > without rewriting files. Is there any rule of thumb for making sure one has > enough space available for the upgrade? I suppose that wou

Re: Running concurrent txns and measuring the timings in Postgres

> On Jul 24, 2019, at 1:22 PM, Souvik Bhattacherjee wrote: > > > It would help to know what problem you are trying to solve? > > Multiple txns are inserting tuples into a table concurrently. Wanted to > measure > the total time taken to complete the insertion process. Some txns overlap > wit

Re: Which version to upgrade upto

On 7/31/19 9:57 AM, Vikas Sharma wrote: The architects and developers have perception that the latest release always will have bugs and others might be using in production. They feel 11.2 will be better bet than 11.4. Except of course for the bugs fixed in .3 and .4.

Re: pg_wal fills up on big update query

> On Aug 7, 2019, at 7:34 AM, Daniel Fink (PDF) wrote: > > Hi all, > > I have a migration where I > · Add a new nullable column to a table > · update almost every row in this big table (8 million rows) from > another table where I set this new column > > I have also a repli

Re: Input validation

On 8/7/19 12:07 PM, stan wrote: Have table that contains employee keys, paired up with work type keys (both foreign keys) and a 3rd column that you enter a billing rate in. Then I have a table where employees enter their work. I need to validate that the employee, work type pair exists,

Re: Recomended front ends?

On 8/7/19 1:38 PM, Adrian Klaver wrote: On 8/7/19 11:57 AM, stan wrote: I am in the process of defining an application for a very small company that uses Postgresql for the backend DB. This DB will eventually run on a hosted machine. As you imagine all of the employees have Windows machines

Re: Guidance needed on an alternative take on common prefix SQL

On 8/7/19 3:36 AM, Laura Smith wrote: On Wednesday, August 7, 2019 2:01 AM, Andy Colson wrote: On 8/6/19 6:25 PM, Laura Smith wrote: Hi, I've seen various Postgres examples here and elsewhere that deal with the old common-prefix problem (i.e. "given 1234 show me the longest match"). I'm i

Re: pg_wal fills up on big update query

transaction. So I have to do this outside of the tool. Best Regards, Daniel *From:* Rob Sargent [mailto:robjsarg...@gmail.com <mailto:robjsarg...@gmail.com>] *Sent:* Wednesday, August 7, 2019 4:22 PM *To:* Daniel Fink (PDF) mailto:daniel.f...@pdf.com>> *Cc:* pgsql-general@lists.po

Re: Generate test data inserts - 1GB

-- Adrian Klaver adrian.kla...@aklaver.com Thanks for the reply Adrian. Missed one requirement. Will these methods generate wal logs needed for replication? Actually the data is to check if replication catches up. Below is scenario : 1.

Re: Probably a newbie question

> > I'd look here: > > SELECT > name > FROM > vendor > WHERE > bom_item.vendor_key = > ( > SELECT > vendor_key > FROM >

Re: Bulk Inserts

> On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee wrote: > > Hi Adrian, > > Thanks for the response. > > > Yes, but you will some code via client or function that batches the > > inserts for you. > > Could you please elaborate a bit on how EXP 1 could be performed such that it > uses bul

Re: Quoting style (was: Bulk Inserts)

Sorry. I thought I had cut most of the redundancy > On Aug 11, 2019, at 2:26 AM, Peter J. Holzer wrote: > >> On 2019-08-10 21:01:50 -0600, Rob Sargent wrote: >>On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee >>wrote: >> >>Hi Adria

Re: Changing work_mem

On 8/13/19 11:04 AM, rihad wrote: On 08/13/2019 08:44 PM, rihad wrote: On 08/13/2019 08:22 PM, Luca Ferrari wrote: On Tue, Aug 13, 2019 at 5:59 PM rihad wrote: [dbname] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp93683.257381", size 594 The setting 'work_mem' is within context 'us

Re: Missing Trigger after pgdump install

On 8/16/19 4:45 PM, Susan Hurst wrote: We're using the 9.5.14 in the sandbox to extract data and objects from the pgdump that was created in the 9.5.0 version.  Hope I answered your question correctly.  If not, let me know and I'll try again. Our biggest concern is that there may be other si

Re: A user atribute question

> On Aug 17, 2019, at 9:45 AM, stan wrote: > > Just starting to expore setting up roles & useres. I ran this statement: > > GRANT CONNECT ON DATABASE stan TO employee; > > But yet \du still reports: > > employee| Cannot login > > What am I doing wrong? > > -- > "They that would

Re: SELECT all the rows where id is children of other node.

> On Aug 19, 2019, at 7:42 PM, pabloa98 wrote: > > Hello, > > I have a huge table (100 million rows) of relations between nodes by id in a > Postgresql 11 server. Like this: > > CREATE TABLE relations ( > pid INTEGER NOT NULL, > cid INTEGER NOT NULL, > ) > > This table has parent-

Re: SELECT all the rows where id is children of other node.

> On Aug 19, 2019, at 7:42 PM, pabloa98 wrote: > > Hello, > > I have a huge table (100 million rows) of relations between nodes by id in a > Postgresql 11 server. Like this: > > CREATE TABLE relations ( > pid INTEGER NOT NULL, > cid INTEGER NOT NULL, > ) > > This table has parent-c

Re: SELECT all the rows where id is children of other node.

> On Aug 19, 2019, at 7:42 PM, pabloa98 wrote: > > Hello, > > I have a huge table (100 million rows) of relations between nodes by id in a > Postgresql 11 server. Like this: > > CREATE TABLE relations ( > pid INTEGER NOT NULL, > cid INTEGER NOT NULL, > ) > > This table has parent-c

Re: SELECT all the rows where id is children of other node.

> On Aug 19, 2019, at 7:42 PM, pabloa98 wrote: > > Hello, > > I have a huge table (100 million rows) of relations between nodes by id in a > Postgresql 11 server. Like this: > > CREATE TABLE relations ( > pid INTEGER NOT NULL, > cid INTEGER NOT NULL, > ) > > This table has parent-c

Re: SELECT all the rows where id is children of other node.

> On Aug 21, 2019, at 3:35 AM, Francisco Olarte wrote: > > Pablo: > > On Tue, Aug 20, 2019 at 6:49 PM pabloa98 wrote: >> Thank you for your responses Rob. Appreciated. The problem with recursive >> queries is that they are executed several times and it has and impact in >> performance. >> I

Re: Postgres SQLSTATE[08006] [7] timeout expired

> On Aug 24, 2019, at 4:42 PM, Howard Wells wrote: > > I have three servers behind a load balancer and a fourth server solely for > Postgres 10 database that is not behind the load balancer. All four are > behind the same firewall, with port 5432 open. > > I have a simple browser-based ht

Re: Postgres SQLSTATE[08006] [7] timeout expired

> > console. > The pg_hba.conf has these lines enabled: > pg_hba.conf: > hostall [username] 0.0.0.0/0 trust > hostall all 0.0.0.0/0 md5 > hostall all ::/0

Re: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

> On Aug 25, 2019, at 1:09 PM, David Wall wrote: > > Using the latest PostgreSQL, does it matter if my code does a ROLLBACK or a > COMMIT on an non-modifying SELECT statement? My impression is they'd be the > same as nothing is changed and therefore there's nothing to commit or > rollback,

Re: Work hours?

On 8/27/19 4:59 PM, Adrian Klaver wrote: On 8/27/19 3:27 PM, stan wrote: I am just starting to explore the power of PostgreSQL's time and date functionality. I must say they seem very powerful. I need to write a function that, given a month, and a year as input returns the "work hours" in t

Re: Selecting rows having substring in a column

On 8/29/19 8:47 AM, Rich Shepard wrote: On Thu, 29 Aug 2019, Gary Cowell wrote: Look at the 'LIKE' function Gary, Yes, I thought of like but didn't think to look for it in the postgres manual. Thank you very much, Rich I've given up on the "likes" in favour of the ~ (tilde) and ~* (til

Re: Query using 'LIKE' returns empty set

On 8/29/19 10:39 AM, Rich Shepard wrote: Next problem is one I've not before encountered. The .sql file used to import data to the fish_counts table has rows such as this one: ('1237796458250','0','17174','Buchanan Creek','Buchanan Creek trib to North Fork Nehalem River','0-3.25','161980'

Re: Returning a table from a function, that requires multiple selects?

> On Aug 30, 2019, at 2:09 PM, Guyren Howe wrote: > > On Aug 30, 2019, at 13:03 , stan mailto:st...@panix.com>> > wrote: > >> I need to encapsulate, what are basically 2 related function calls into a >> single >> function. The result of each of th calls is a date type. >> >> y current think

Re: Returning a table from a function, that requires multiple selects?

> On Aug 30, 2019, at 2:03 PM, stan wrote: > > I need to encapsulate, what are basically 2 related function calls into a > single > function. The result of each of th calls is a date type. > > y current thinking is to return a 2 row table with the 2 dates in it. But, I > seem to > be having

floating point output

I've found the description of floating point types (here ), but I'm looking for the rationale of the output format, particularly with respect to total digits presented (variable in a single select's output) and the droppin

Re: floating point output

> On Sep 4, 2019, at 9:14 PM, Adrian Klaver wrote: > >> On 9/4/19 5:23 PM, Rob Sargent wrote: >> I've found the description of floating point types (here >> <https://www.postgresql.org/docs/10/datatype-numeric.html#DATATYPE-FLOAT>), >> but I'm

Re: floating point output

> On Sep 5, 2019, at 7:32 AM, Rob Sargent wrote: > > > >> On Sep 4, 2019, at 9:14 PM, Adrian Klaver wrote: >> >>> On 9/4/19 5:23 PM, Rob Sargent wrote: >>> I've found the description of floating point types (here >>> <https:

Re: database "cdf_100_1313" does not exist

> On Sep 9, 2019, at 7:16 AM, nikhil raj wrote: > > Hi Dan, > > Still facing the same issue. > > /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT > CONNECT ON DATABASE $DBLIST TO cpupdate" > ERROR: database "cdf_100_1313" does not exist > Can we see the output

Re: pldbgapi extension

On 9/17/19 10:16 AM, Adrian Klaver wrote: On 9/17/19 7:44 AM, Prakash Ramakrishnan wrote: I installed devel and contrib also but not worked here. Yeah, --contrib won't help as pldbpgapi is not one of the contrib modules as found here: https://www.postgresql.org/docs/11/contrib.html The b

Re: PostgreSQL License

> On Sep 17, 2019, at 4:18 PM, Andreas Joseph Krogh wrote: > > På tirsdag 17. september 2019 kl. 22:55:02, skrev Peter J. Holzer > mailto:hjp-pg...@hjp.at>>: > On 2019-09-17 14:56:30 +0300, Ashkar Dev wrote: > > but it is saying (without fee) > > if I create a database with it to work with Web

Re: PostgreSQL License

On 9/18/19 11:50 AM, Ashkar Dev wrote: Hi all thanks, I meant maybe I create a web app with PostgreSQL that work locally for example for a pharmacy that stores data by barcode while the DB was created by PostgreSQL how I can sell the Database for him, how to deliver the product to him can I

Re: PostgreSQL License

> On Sep 18, 2019, at 12:17 PM, Adrian Klaver wrote: > > On 9/18/19 11:06 AM, Rob Sargent wrote: >> On 9/18/19 11:50 AM, Ashkar Dev wrote: >>> Hi all thanks, >>> I meant maybe I create a web app with PostgreSQL that work locally for >>> example

  1   2   3   4   5   6   7   >