Re: [GENERAL] Using Variables in Queries

On 19 October 2017 at 17:25, Scott Mead wrote: > > > On Thu, Oct 19, 2017 at 11:21 AM, Igal @ Lucee.org wrote: >> >> Hello, >> >> In other database servers, which I'm finally dropping in favor of >> Postgres, I can do the following (mind you that this is for

Re: [GENERAL] EAV Designs for Multi-Tenant Applications

le to create an index over an organisation-specific JSON 'blob' such that all fields in it are part of the index? I expect that index types aimed at text searches (word searches) would be useful in that respect. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees

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

t-hand side (alloc_trans.tran_date and due_trans.tran_date respectively) in your WHERE clauses without allowing them to be NULL. If you want those parts to behave like proper LEFT JOINs, either add OR xxx.tran_date IS NULL or move those expressions into the JOIN conditions. Alban Hertroys -- If yo

Re: [GENERAL] Insert large number of records

On 20 September 2017 at 22:55, Job wrote: > One further question: within a query launched on the MASTER table where i > need to scan every table, for exaple to search rows locatd in more partitions. > In there a way to improve "parallel scans" between more table at the

Re: [GENERAL] Insert large number of records

On 20 September 2017 at 07:42, Job wrote: > We use a "temporary" table, populated by pg_bulkload - it takes few minutes > in this first step. > Then, from the temporary table, datas are transferred by a trigger that copy > the record into the production table. > But

Re: [GENERAL] Insert large number of records

e scenario where a matching target record in the master table already exists. In our case, we convert character fields to varchar (which saves a lot of space(s)). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via

Re: [GENERAL] Performance with high correlation in group by on PK

On 8 September 2017 at 00:23, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Tue, Aug 29, 2017 at 1:20 AM, Alban Hertroys <haram...@gmail.com> wrote: >> >> On 28 August 2017 at 21:32, Jeff Janes <jeff.ja...@gmail.com> wrote: >> > On Mon, Aug 28, 2017 at 5

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

On 15 September 2017 at 11:03, Rafal Pietrak wrote: >> Isn't this typically handled with an inheritance (parent-children) >> setup. MasterDocument has id, subtype and any common columns (create >> date etc) then dependents use the same id from master to complete the >> data for

Re: [GENERAL] Performance with high correlation in group by on PK

On 28 August 2017 at 21:32, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Mon, Aug 28, 2017 at 5:22 AM, Alban Hertroys <haram...@gmail.com> wrote: >> >> Hi all, >> >> It's been a while since I actually got to use PG for anything serious, >> but we're

Re: [GENERAL] Performance with high correlation in group by on PK

On 28 August 2017 at 14:22, Alban Hertroys <haram...@gmail.com> wrote: > This is on: Just noticed I forgot to paste this in: warehouse=# select version();

[GENERAL] Performance with high correlation in group by on PK

Hi all, It's been a while since I actually got to use PG for anything serious, but we're finally doing some experimentation @work now to see if it is suitable for our datawarehouse. So far it's been doing well, but there is a particular type of query I run into that I expect we will frequently

Re: [GENERAL] cast issue in WITH RECURION

k, >sg.data + g.data, -- altered section, data is numeric(7,3) >sg.depth + 1, > path || g.id, > g.id = ANY(path) >FROM graph g, search_graph sg >WHERE g.id = sg.link AND NOT cycle > ) > SELECT * FROM search_graph; I believe the

Re: [GENERAL] Dealing with ordered hierarchies

be much more frequent than > writes and inserts will be more frequent than updates (re-ordering) More of the logic (and thus system load) gets moved to the read-side of things, that's probably a drawback, but most of it is just keeping state and counting. I don't expect that to be all that muc

Re: [GENERAL] Schedule

etected. And I'm sure there are plenty of other corner-cases you need to take into account. I bet it has a lot of problems in common with replication actually (how do we reliably get information from system A to system B), so it probably pays to look at what particular problems occur there a

Re: [GENERAL] Create extension C using IDE

de? Eclipse has many errors. > > The errors are? My guess would be its user interface… I would suggest gViM or MacViM, but that really depends on what you expect from a (I)DE and on what platform you are. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find th

Re: [GENERAL] Inconsistent performance with LIKE and bind variable on long-lived connection

> > out.append(String.format(" %3d", endTime - startTime)); > } > > stmt.close(); > > System.out.println(out); > } Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pg

Re: [GENERAL] Weirdness with "not in" query

On 8 June 2017 at 17:27, greigwise wrote: > So, I'm using postgres version 9.6.3 on a mac and the results to this series > of queries seems very strange to me: > > db# select count(*) from table1 where id in > (1706302,1772130,1745499,1704077); > count > --- > 4 >

Re: [GENERAL] Coditional join of query using PostgreSQL

> I hope there is a better solution rather than creating two separated > functions :( You can use your boolean parameter inside the join condition: […] on (tfquery.a = main.a and ((type_f and tfquery.d = main.d) or not type_f)) Beware that you don't also have a column named type_f in tha

Re: [GENERAL] Column information

> According to the documentation 'numeric_precision_radix' field should > indicate what radix the value of 'numeric_precision' is stored. > > However, even though the radix is 2, the actual value is 32, which is > not a radix 2. https://en.wikipedia.org/wiki/Radix Alban Her

Re: [GENERAL] UDP buffer drops / statistics collector

asons), which incidentally moved several connections from the switch the game-system was connected to to another switch. I never pinpointed it to UDP, but then again, TCP would correct for the lost transfers (probably at the cost of UDP traffic). Perhaps you have a switch somewhere that's o

Re: [GENERAL] Large data and slow queries

y experience, people don't usually move around much, so you should certainly be able to pinpoint them mostly to a specific area, right? (Hence my suggestions for a country column or partitioning in squares) > On 19 April 2017 at 22:50, Alban Hertroys <haram...@gmail.com> wrote:

Re: [GENERAL] Large data and slow queries

titions under a single master table. I don't think PG has a simple way of doing that (yet) though; perhaps it's possible by abusing multiple levels of inheritance, but that sounds like a bad idea. And of course, create your partitions sufficiently course to prevent overburdening the sys

Re: [GENERAL] CANNOT USE ANY INDEX ON UPSERT (INSERT.....ON CONFLICT)

ase i can't create any type of primary/unique index, like a > composite F1,F2, F3, F4 index. (correct me if i am wrong please). Correct, you'll most likely have to add a new one (unless someone comes up with better suggestions). Alban Hertroys -- If you can't see the forest for the tre

Re: [GENERAL] [ADMIN] calculating table and index size

On 7 April 2017 at 09:11, Günce Kaya wrote: > Hi again, > > Sorry for delay. > > Guillaume, I read your answer for first question but It's not clear to me. > The table has a column and index also use that column. so in that example, I > think table size and index size

Re: [GENERAL] Run statements before pg_dump in same transaction?

panies should not know who exactly those people are. ISTR that there are some tools for this purpose, but the details escape me. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

j2, obj3) as the order of their values is not variable anymore. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] CHECK for 2 FKs to be non equal

N" > LINE 2: ...REFERENCES words_users(uid) CHECK (uid <> author) ON DELETE … You put your CHECK constraint definition smack in the middle of the FK constraint definition, which starts with REFERENCES and ends with the delete CASCADE. Alban Hertroys -- If you can't see the

Re: [GENERAL] Simple Query not using Primary Key Index

Scan. > > I of course did VACUUM ANALYZE and I have reset statistics But no sign. Is > there any particular thing I should be looking at? An EXPLAIN ANALYZE would be a good start. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no f

Re: [GENERAL] (solved) R: Very strange problem on index

On 2 February 2017 at 14:57, Job wrote: > Hi Raymond, > > Thank nyou for your appreciated feedback. But what's your answer to his question? You still didn't tell. > Here is the original message: > > i really strange problem, quite near to paranormal, is occurring

Re: [GENERAL] Why is table not found?

d), IMHO the better thing to do is to remove them from your create statements. Most of the time there is no benefit creating case-sensitive identifiers in a database. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

e-cast will get applied to every candidate record each. If you have a million candidate records, that's 2x a million casts taking place (for two fields). To say more about this we would need more details about what types those fields are and why you're casting them to time. > The Postgres 9

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

plaining about a "big slowdown" for a query that goes from 1.5ms to 4ms? What is the actual problem you're trying to solve? Because I don't see one in the above. Just saying, you're obviously worried about something, but should you be? Alban Hertroys -- If you can't see the forest for

Re: [GENERAL] Special index for "like"-based query

he above isn't entirely correct, as tsrange uses timestamp without time zone, but you get the gist. However, if those time ranges can have other values than '[00:00. 23:59]', then you probably need 2 indexes on that timestamp column; one cast to date and one to time. Otherwise, you end up creating timestamp

Re: [GENERAL] LYDB: Feasible to use PG roles instead of application-level security?

e a new one or change the user's role, this procedure doesn't need calling again. > Is this practical? Has anyone here done it? What might the caveats be? It's a fairly common practice, the ML archives should contain plenty of examples. Alban Hertroys -- If you can't see the forest for the tr

Re: [GENERAL] LYDB: What advice about stored procedures and other server side code?

just something that struck me to make more than a bit of sense… Of course, for the actual view in the MVC paradigm there should be some kind of user interface, but database views could be really useful in preparing the data required for those, to make it fit the shape of the view. So far I like w

Re: [GENERAL]

sert operation performed. > Is it possible? I have no idea what you're saying. > On Wednesday, December 21, 2016, Alban Hertroys <haram...@gmail.com> wrote: >> >> On 21 December 2016 at 09:59, Yogesh Sharma <yogeshra...@gmail.com> wrote: >> > Also, every hour,i am perf

Re: [GENERAL]

On 21 December 2016 at 09:59, Yogesh Sharma wrote: > Also, every hour,i am performing VACUUM and REINDEX operation on table. Why are you running REINDEX every hour? That's a very unusual thing to do, you'd need a pretty good reason for that. -- If you can't see the

Re: [GENERAL] Is there a way to Send attachments with email using pgmail postgreSQl?

te, but a stored procedure in, say TCL, should be able to handle that. Or am I missing something? Whether it's a good idea to let the database encode attachments and send e-mails is a different matter, but if it isn't doing much beside that - well, why not? Alban Hertroys -- If you can't see the fore

Re: [GENERAL] huge table occupation after updates

have time > to make the rows reusable. > Also, issuing plain VACUUM command does nothing visibile at once, but only > after when, inserting new rows, the size doesn't increase. > I will try again as you suggest. > Thank you very much > Pupillo Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

On 16 November 2016 at 16:33, Rich Shepard wrote: > On Tue, 15 Nov 2016, Rich Shepard wrote: > If 9.6.1 is currently running after running initdb, and I can access my > databases, what does pg_upgrade do that's necessary? pg_upgrade migrates your databases from your

Re: [GENERAL] Recover from corrupted database due to failing disk

On 4 November 2016 at 11:20, Gionatan Danti wrote: > Unfortuntaly I am working with incredible constrains from customer side; > even buying two SAS disks seems a problem. Moreover, as an external > consultant, I have basically no decision/buying power :| > What I can do (and I

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

On 4 November 2016 at 14:41, Merlin Moncure wrote: > On Fri, Nov 4, 2016 at 8:08 AM, Kim Rose Carlsen wrote: >> The nulls are generated by something like this >> SELECT c.circuit_id, >>cc.customer_id >>FROM circuit AS c >> LEFT

Re: [GENERAL] initdb createuser commands

On 31 October 2016 at 15:50, Christofer C. Bell wrote: > > I think the OP's point is that having a hodgepodge of (on their face) > unrelated commands smells kinda unorganized at best and unprofessional at > worst. Wether or not he's right is up to the reader. For

Re: [GENERAL] Rows are repeating by the trigger function

On 31 October 2016 at 14:41, Adrian Klaver wrote: > On 10/31/2016 02:06 AM, Kiran wrote: >> I know 94 = 1 + (3 * 31). >> I am just having a normal insert statement into cf_question table. > > Are there any other triggers on the tables? I'm fairly confident that the

Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?

be automatically added by the commit hook of your VC of choice. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscriptio

Re: [GENERAL] Rows are repeating by the trigger function

to take changes to those _id columns into account and delete rows that belong to the OLD link and not to the NEW one (or do nothing if those stayed the same) - or you do nothing (no trigger needed) because in the majority of cases changing FK's is limited to a few power users at best and they

Re: [GENERAL] WHERE ... IN condition and multiple columns in subquery

On 28 October 2016 at 13:03, Alexander Farber wrote: > Hello, > > is it please possible to rewrite the SQL query > > SELECT DISTINCT ON (uid) > uid, > female, > given, > photo, > place > FROM

Re: [GENERAL] update records to have occurance number

; > > How would I issue an update statement to update the number column? That depends on which order you want the database to perceive those rows in. The above example suggests that alphabetical order on fname might work, in which case: update person set number = count(p2.fname) +1 from p

Re: [GENERAL] psql 9.3 automatic recovery in progress

ple from allianzgrp who knew just enough to be harmful. (Kill -9 on a database process, jeez! Keyboards should have an electroshock feature for people like that…) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-g

Re: [GENERAL] psql 9.3 automatic recovery in progress

> On 10 Oct 2016, at 21:28, Alban Hertroys <haram...@gmail.com> wrote: > > >> On 10 Oct 2016, at 21:12, Periko Support <pheriko.supp...@gmail.com> wrote: >> >>for pid in idle_record: >>try: >> #print "process de

Re: [GENERAL] psql 9.3 automatic recovery in progress

that's the best way to go about this, and best of all, you can combine that with your select statement. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postg

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

e cast to > java.util.List I'm not 100% sure it's the root of the ClassCastException here, but I'm pretty sure that Java will want to know what class of items that List consists of. If that doesn't ring a bell for you, spend some time reading about "Java generic classes" (not to be c

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

which unknown value to remove from that array of unknown values? Shouldn't the result be: {NULL,NULL,NULL}? (Sorry for sort-of hijacking this thread) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (

[GENERAL] Re: [GENERAL] RE: [GENERAL] Re: [GENERAL] A simple extension immitating pg_notify‏

arate directories, running on separate port numbers, you would have multiple clusters. Same if you distribute those servers over several hosts, what you seem to think a cluster means. That is the difference between a cluster of databases and a cluster of servers. Alban Hertroys -- If you can't see

Re: [GENERAL] Help needed structuring Postgresql correlation query

something different), one from the start, and one lagging N rows behind (you can use the lag() window function for that) and subtract the two. Good luck! Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Sequences, txids, and serial order of transactions

e to be non-transactional to be able to guarantee correct ordering. Calling nextval() will increment the sequence, but does not relate it to the transaction at that point. The select statement that does the call to nextval() receives the value from the sequence and is part of the transaction. That link

Re: [GENERAL] full text search index

CTION public.full_text_universal_cast(doc_data >> "text") >> RETURNS "tsvector" AS >> $BODY$ >> SELECT to_tsvector('english', COALESCE(TRIM(CAST(doc_data AS TEXT)), '')); >> $BODY$ >> LANGUAGE sql IMMUTABLE >> COST 1000; The query planner has

Re: [GENERAL] Function PostgreSQL 9.2

obably gets interpreted as a column name. > END IF; > IF (TG_OP = 'INSERT') THEN > UPDATE public.companies SET client_code_increment = > (client_code_increment + 1) WHERE id = NEW.id; > END IF; > RETURN NEW; > END; > $$ LANGUAGE plpgsql;

Re: [GENERAL] Thoughts on "Love Your Database"

On 4 May 2016 at 17:08, John McKown wrote: > I had a manager, long ago, who used a Lotus 1-2-3 spreadsheet to > contain all his memos. I was glassy eyed in disbelief. He also would use his > hand calculator to add up the numbers in the spreadsheet to be sure that the

Re: [GENERAL] (VERY) Slow Query - PostgreSQL 9.2

(substring (full_path from 20)) where full_path like '/userfiles/account/%'; and then use similar expressions in your query of course: where full_path like '/userfiles/account/%' and substring(full_path from 20) ~ '^[0-9]+/[a-z]+/[0-9]+'; Good luck! Alban Hertroys -- If you can't see the for

Re: [GENERAL] Slow join over three tables

On 28 April 2016 at 08:36, Tim van der Linden <t...@shisaa.jp> wrote: > On Wed, 27 Apr 2016 13:48:06 +0200 > Alban Hertroys <haram...@gmail.com> wrote: > >> In this case, you're using the values in adverse to filter relevant rid's >> for the FK join, so you mig

Re: [GENERAL] Slow join over three tables

nspecific back pain', 'back pain']) AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ) x ON x.rid = r.id ORDER BY r.created; Looking at the cardinality of your tables that does seem a bit unlikely though. Still, worth a shot... Alban Hertroys -- If you can't see the forest for the t

Re: [GENERAL] Why is the comparison between timestamp and date so much slower then between two dates

d, now() gets evaluated only once, but the slow-down is caused by having to do conversions (for two field values) for every row. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgr

Re: [GENERAL] Why is the comparison between timestamp and date so much slower then between two dates

in the table. When down-converting now(), the DB only needs to do that once for all rows. Regards, Alban Hertroys. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Really unique session ID - PID + connection timestamp?

; > > We want create same mechanism. > > If the above questions did not already cover this, what mechanism? > > > I know there are adv. locks in PG, but I want to use session id. > > This could be: > |pg_backend_pid|() > > May pid repeats. > Where I can get time

Re: [GENERAL] pg_restore real file size

stic case; in reality some values will get toasted and the dump file is compressed. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

ul though... Well, enough of my rambling! Ad 1. It is possible that you cater for the possibility that you don't know whether a "contact" has a phone number or not, in which case null would probably be the wrong choice for "no phone number" because then you wouldn't be able to

Re: [GENERAL] Optimize Query

wouldn't surprise me if that query is already significantly faster. If you're still having problems at that point, post that query and the analysis again. > Explain analyze link: > http://explain.depesz.com/s/5WJy Alban Hertroys -- If you can't see the forest for the trees, cut the trees

Re: [GENERAL] Optimize Query

query-template with enough placeholders and views that there is no way to predict how that's going to perform without at least knowing what goes into the placeholders and how the views are built up. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find the

Re: [GENERAL] Test CMake build

ry is on github: > https://github.com/stalkerg/postgres_cmake > > The compilation will be enough (tests even better). I need feedbacks so that > create issues on github. > Very interesting NetBSD, OpenBSD, Solaris. I was under the impression that the FreeBSD port already uses cma

Re: [GENERAL] execute same query only one time?

n by "copying of columns" in your reply to Adrian's solution, but I don't think that happens here. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Let's Do the CoC Right

least 3 times in this whole > conversation. > > Thanks, > Regina What about this for a CoC?: 1. Do not discuss a CoC. But, this side-thread has been going on long enough I think. Let's wrap this up, shall we? Regards, Alban Hertroys -- If you can't see the forest for t

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

On 12 January 2016 at 09:25, Chris Travers wrote: > One of the dangers of a CoC is that there are many potential issues which > may or may not become real problems. I think if we try to be clear on all > of them, then we risk creating codes instead of a general

Re: [GENERAL] WIP: CoC V2

y need a CoC. You people are all being so polite about it that it's almost offensive! Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] Definitive answer: can functions use indexes?

and could even behave differently or (more likely) not work at all on other PG instances. That said, it's not uncommon in BI to require a seq. scan anyway, in which case the point is rather moot. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll fin

Re: [GENERAL] A unique pairs version of UNNEST() ?

ory. The result you seem to be looking for is the list of possible unique combinations, as sets of elements of the total set (sets are orderless). with list_of_ids as ( select unnest(list_of_ids) as id from table ) select a.id, b.id from list_of_ids a, list_of_ids b where b.id > a.id;

Re: [GENERAL] to_timestamp alternatives

ince not all time zones are full hours apart, or have the same (if any) DST change-over dates. For example, India is currently at UTC+05:30, probably because they wrapped the entire country in the same TZ after their independence. Alban Hertroys -- If you can't see the forest for the t

Re: [GENERAL] Recurring and non recurring events.

mp ) select curr_stamp from dateRange; I suspect generate_series is faster, but since your query already almost looked like this I thought I'd offer this alternative approach. It has a little bit more flexibility too, as you can add fields and calculations to the CTE quite easily. Alban Hertroys

Re: [GENERAL] Transfer db from one port to another

;pg_restore" to executeot find a "pg_dump" to execute Aren't you trying to move a database to PG 9.4? Then you need to use the pg_dump and pg_restore utilities of the 9.4 installation, not those of the 9.3 one. Those utilities are guaranteed to be backwards compatible, but they'

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

> Before I start in to implement a DELETE / AUTOVACUUM / VACUUM approach, to > recycling disk space used for a session management table, I would like to > propose, for consideration by this forum, an idea for a different approach. > > A row in a session management table, represents a significant

Re: [GENERAL] ftell mismatch with expected position

Please refrain from top-posting. On 2 November 2015 at 10:48, Eelke Klein wrote: > Normally we call this from within our windows program where a lot of code is > involved for setting up the environment, and creating the pipes and > redirecting stdout, stderr and stdin. However I

Re: [GENERAL] Recursive Arrays 101

right mindset. PS. I usually write my hierarchical queries in Oracle, which isn't quite as good at them as Postgres is, but it's what we have @work. Hence, I'm not sure I got the syntax 100% correct. We're working on getting PG in for a project upgrade (replacing RDB on OpenVMS, which will go EOL

Re: [GENERAL] to pg

On 25 September 2015 at 13:08, Ramesh T wrote: > CREATE UNIQUE INDEX idx_load_pick ON pick (case picked when picked='y' then > load_id else null end ); > > how can i convert case expressed to postgres..above it is oracle. Assuming that your queries are written in

Re: [GENERAL] Prepared Statements and Pooling

On 11 August 2015 at 06:44, Mister Junk junkmail3568...@gmail.com wrote: I'm using prepared statements to prevent SQL injection, but I have done some reading and it seems like using Prepared statements COULD improve performance. I understand the concept, but I do not know how to implement

Re: [GENERAL] Question about copy from with timestamp format

on copying to a view, or inserting an out-of-range timestamp, when the trigger would resolve all the illegal operations if it just fired first. On Thu, Jul 30, 2015 at 5:57 AM, Alban Hertroys haram...@gmail.com mailto:haram...@gmail.com wrote: On 30 Jul 2015, at 2:27, Sherrylyn Branchaw

Re: [GENERAL] Question about copy from with timestamp format

. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] database-level lockdown

On 7 July 2015 at 12:55, Filipe Pina filipe.p...@impactzero.pt wrote: On Ter, Jul 7, 2015 at 1:41 , Adrian Klaver adrian.kla...@aklaver.com wrote: Still not sure what is you are trying to accomplish. Is it really necessary that every transaction be serialized? Or to put it another way, why are

Re: [GENERAL] use null or 0 in foreign key column, to mean no value?

parameters, if those are available to you. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] Python 3.2 XP64 and Numpy...

? ---^ It looks like you're trying to use 64-bit binaries on a 32-bit OS. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Allowing postgresql to accept 0xff syntax for data types that it makes sense for?

On 22 May 2015 at 04:46, Bill Moran wmo...@potentialtech.com wrote: I did a litle research and it appears that neither Oracle nor db2 supports the 0xff syntax ... so not _quite_ as common as it seemed to me. With all that being said, if I were to build a patch, would it be likely to be

Re: [GENERAL] date with month and year

On 21 May 2015 at 23:42, Karsten Hilbert karsten.hilb...@gmx.net wrote: You are right in the following aspect: - client sends in NOW at HERE - server knows HERE = UTC+2 And then the tectonic plate you're on shifts and you're suddenly in UTC+1 or +3 Thankfully, those things don't shift as

Re: [GENERAL] plpgsql functions organisation

to you. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] schema or database

if one customer messes up their data big time, you'll need to restore a backup for all customers in the DB. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Why doesn't `RAISE EXCEPTION` provide error context?

On 2 April 2015 at 19:15, Taylor Brown tay...@youneedabudget.com wrote: So, I would rather put a check like this at the top of my function: -- important_variable = (p_request::json-'important_variable')::integer; IF (important_variable is NULL) THEN RAISE EXCEPTION 'important_variable must

Re: [GENERAL] pgadmin3 installation on Oracle Linux 6.6 64-bit ?

On 28 March 2015 at 02:14, Yuri Budilov yuri.budi...@hotmail.com wrote: I am new to PostgreSQL and Linux (coming across from Microsoft SQL Server). I installed PostgreSQL 9.4 on Oracle Linux 6.6 and its working ok (psql, etc). Now I try to install pgadmin3 on the same OS. I am having

Re: [GENERAL] Populating missing dates in postgresql data

, untested, etc, but I think I got that mostly right. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] Installation Size showing huge size in postgres installed on windows os

On 19 March 2015 at 13:44, Raymond O'Donnell r...@iol.ie wrote: On 19/03/2015 12:39, jaime soler wrote: El mié, 18-03-2015 a las 23:05 -0700, Rajagopal NS escribió: I have installed Postgres 9.0 in my machine. When I look at Programs and Features under Control Panel, I see the Size for

Re: [GENERAL] Reg: PL/pgSQL commit and rollback

On 17 March 2015 at 15:30, Medhavi Mahansaria medhavi.mahansa...@tcs.com wrote: Yes. I have read this document. But my issue is that even when it throws and exception I need to rollback the changes made by that query and move on to the next block. Is there any way to accomplish that? Please

Re: [GENERAL] Update using non-existent fields does not throw an error

On 16 March 2015 at 17:02, Rob Richardson rdrichard...@rad-con.com wrote: Greetings! An update query is apparently succeeding, even though the query refers to fields that do not exist. Here’s the query: update inventory set x_coordinate = (select x_coordinate from bases where base =

Re: [GENERAL] Postgres not using GiST index in a lateral join

: ((shape A.shape) AND _st_contains(shape, A.shape)) } ] } ] } } How did your query plan end up in JSON notation? It's quite difficult to read like this. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general

  1   2   3   4   5   6   7   8   9   10   >