>>
>>> select * from dblink(‘dbname=database2 username=db_link
>>> password=mypassword','select username, email from appuser') as t1(username
>>> text, email text);:
>
> I think the problem is the above- ^^^
>
> username=db_link should be user=db_link
>
> The accepted key
Hi,
I’m trying to use dblink to connect from database1 to database2 on the same
machine. The Postgres version is 9.3.5.
Firstly, I’ve created a user to use with the connection, while logged in as the
postgres superuser to database2:
> create user db_link with password ‘mypassword’;
> grant sel
I'm sure no one else on this list has done anything like this, but here's a
cautionary tale.
I wanted to synchronise data in two tables (issue lists) - i.e. whenever a
record is added into one, add a similar record into the other. The two tables
are similar in format but not exactly the same so
On 9 Sep 2013, at 21:03, Alvaro Herrera wrote:
> select string_agg(case when words like '*%*' then upper(btrim(words, '*'))
> else words end, ' ')
> from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy*
> dog', ' ') as words;
>
> string_agg
On 9 Sep 2013, at 14:41, David Johnston wrote:
> Oliver Kohll - Mailing Lists wrote
>> select regexp_replace(sentence,'\*(.*?)\*','' || upper('\1'),'g') from
>> sentences;
>
> Yeah, you cannot embed a function-call result in the &qu
Hello,
Given a string with certain words surrounded by stars, e.g.
The *quick* *brown* fox jumped over the *lazy* dog
can you transform the words surrounded by stars with uppercase versions, i.e.
The QUICK BROWN fox jumped over the LAZY dog
Given text in a column sentence in table sentences, I
On 19 Oct 2012, at 15:09, "Albe Laurenz" wrote:
> You should always include the list in your replies -
> other people might be interested in the solution.
Oops, thought I had.
>
> There must be at least two transactions involved
> to create a locking problem like you describe.
> But since CRE
On 19 Oct 2012, at 13:28, Frank Lanitz wrote:
> Just an idea without bigger investigation: Whare are the idle in
> transactions are doing? Maybe they are blocking the create index.
>
> Cheers,
> Frank
Good question, I don't know. The app runs on Java / Apache Tomcat, which
maintains a connecti
Hi,
I'm getting a problem where my application hangs in the process of adding a
field to a table. The app adds the field, then creates an index on the field.
It hangs for ages (minutes) until I cancel the query. My investigation so far
has been
Look at current queries:
agilebasedata=# SELECT
Here's a bit of positive news spin - in a backhanded way perhaps, but still a
compliment:
http://www.theregister.co.uk/2012/08/31/postgresql_too_cool_for_school/
Oliver
www.agilebase.co.uk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscript
On 21 Aug 2012, at 13:32, Vincent Veyron wrote:
>>
>> Since Amazon has added new high I/O instance types and EBS volumes,
>> anyone has done some benchmark of PostgreSQL on them ?
>>
>
> I wonder : is there a reason why you have to go through the complexity
> of such a setup, rather than simpl
On 22 Mar 2012, at 10:17, Albe Laurenz wrote:
>> Or is there a better way of finding view dependencies? I see there's a
> pg_catalog entry for tables
>> that a view depends on but that's not what I'm after.
>
> You can use pg_depend and pg_rewrite as follows:
>
> SELECT DISTINCT r.ev_class::reg
Hello,
I'm doing some SELECTs from information_schema.views to find views with
dependencies on other views, i.e.
SELECT table_name FROM information_schema.views WHERE view_definition ILIKE
'%myviewname%';
and each is taking about 1/2 a second, which is getting a bit slow for my use.
There are
On 28 Jan 2012, at 15:27, "Greg Sabino Mullane" wrote:
>> Is this a case for multi master do you think?
>> I.e. running one on the internet, one locally.
>
> Yes, could be.
>
>> b) changing schemas (new tables, fields, views etc.) as well as data
>
> That's a tall order; I don't think anythin
T 0) x WHERE x.invoiced = false
>
> Regards
>
> Pavel Stehule
>
> 2011/11/5 Oliver Kohll - Mailing Lists :
>> b2deliveryorders.complete = false AND b2deliveryorders.invoiced = false
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
JOIN dbvcalc_delivery_charges ON b2deliveryorders.idb2deliveryorders =
dbvcalc_delivery_charges.idb2deliveryorders
WHERE b2deliveryorders.complete = false AND b2deliveryorders.invoiced = false
ORDER BY b2deliveryorders.expectedby NULLS FIRST;
Oliver
Begin forwarded message:
> From: Oliver Kohll - Maili
Hi,
I have a query I'm trying to optimise. It takes just under a second to run, not
too bad for my users but I'm worried that as the size of the data increases, it
will get worse. Of course the plan may change when that happens but I'd also
like to learn a bit more about optimisation anyway.
T
Many thanks both, those solutions are great and have gone in my wiki for future
ref.
Regards
Oliver
On 21 Sep 2011, at 21:56, Szymon Guz wrote:
>
>
> >> Short answer is: yes. More information you can find
> >> here
> >> http://simononsoftware.com/problem-with-random-in-postgresql-subselect/
>
Hi,
I understand random() is a volatile function and runs multiple times for
multiple rows returned by a SELECT, however is there a way of getting it to run
multiple times *within* another function call and in the same row. i.e.
something like
select regexp_replace('+1 555 555 555', E'\\d', tr
On 7 Jan 2011, at 22:02, "THOMPSON, JARED (ATTBAPCO)" wrote:
> I assume when people use dashboards they are not being queried every
> second for updating but maybe every minute?
>
> Are there any tools that work good on top of postgres?
>
> (I see in the stock market (though I am looking at/for
On 22 Jul 2010, at 12:57, Geoffrey wrote:
>> For completeness, the earthdistance module also provides the distance
>> between two lat/longs, the point<@>point syntax is simple to use:
>> http://www.postgresql.org/docs/8.3/static/earthdistance.html
>
> Disgregard my last post, Surely as soon as
On 21 Jul 2010, at 23:14, Joe Conway wrote:
> If you want something simple, and not requiring PostGIS, but plpgsql
> instead, see:
>
> http://archives.postgresql.org/pgsql-sql/2003-12/msg00193.php
For completeness, the earthdistance module also provides the distance between
two lat/longs, the
Hello,
I'm interested in using WAL shipping / replication for backup purposes but have
no interest in failover. Currently my situation is:
I have two servers, live and backup, which are in different cities. The backup
server is also a test/development machine.
Backups of my most important data
>
> Curious note - how does the non-subselect version and the subselect
> version compare performance-wise?
Magnus,
On a test table with 12,000 rows there's not much in it, the subselect has a
simpler plan but they both take practically the same time.
The two plans (note I've been rewriting th
>>
>> Aren't you looking for something along the line of:
>>
>> SELECT year, sum(c) over (order by year)
>> FROM (
>> SELECT extract(year from signup_date) AS year, count(email_address) AS c
>> FROM email_list
>> GROUP BY extract(year from signup_date)
>> )
>>
>> (adjust for typos, I didn't t
On 29 Apr 2010, at 10:01, Magnus Hagander wrote:
>>
>> select extract(year from signup_date),
>> count(email_address),
>> sum(count(email_address)) over (partition by 1 order by 1 asc rows
>> unbounded preceding)
>> from email_list group by 1 order by 1;
>>
>> Does anyone have any other ideas
Hello,
Many thanks to andreas.kretschmer for this helpful reply about how to set up a
window function to perform a running total:
http://archives.postgresql.org/pgsql-general/2010-03/msg01122.php
It works perfectly with the simple test data but I've just got back to work,
tried implementing it
On 25 Apr 2010, at 07:34, Scott Bailey wrote:
>
>> I can point you to the relevant code in GitHub if you're interested (it's
>> Java).
>
> Absolutely. Thanks
>
> Scott Bailey
Line 813 of
http://github.com/okohll/agileBase/blob/master/gtpb_server/src/com/gtwm/pb/model/manageSchema/DatabaseDefn
Scott,
I implemented a system exactly like this for the app in my signature below.
Basically the system stores metadata containing each view definition and the
joins between them. When someone needs to alter a view, say to remove a column,
a DROP CASCADE is performed then each view is recreated
Hello,
I'm still reasonably new to windowing functions, having used a few since 8.4
came out. I wonder if anyone can help with this one.
I've got a table of email addresses in a CRM system similar to the following:
CREATE TABLE test(
signup_date timestamp,
email_address varchar(1000)
);
INS
On 29 Mar 2010, at 14:33, Clemens Eisserer wrote:
> Hi,
>
> Sorry for beeing a bit off-topic.
> Recently I've done some database-fontends, which I used
> java+swingset+netbeans-gui-builder for.
> Compared to plain java development is quite fast - however I wonder
> wether you could recommend db-
Hello,
May I take a lull between PG releases to ask an upgrade Q? Documentation and
people often recommend that when doing a major version upgrade, to dump data
from the old server with the new version of pg_dump, since the new pg_dump may
have bugfixes not available to the old.
I've never don
Hello,
I've just come across this in an output from a cron backup script:
/etc/cron.hourly/gtwm_backup_databases.sh:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not open relation with OID
572838
pg_dump: The command was: SELECT
pg_catalog.pg_get_viewdef('57283
On 27 Sep 2009, at 21:10, InterRob wrote:
Peter, may I invite you to privately share some more details on the
system you are using and the design of it? Did you implement it
using PostgreSQL? Looking forward to your reply.
(And with respect to your previous message: whom are you actually
On 25 Sep 2009, at 07:22, InterRob wrote:
I guess it IS quite overengineered indeed...
What I'm trying to do is to facilitate different fieldwork
methodologies for archaeological research (on project basis); there
is no final agreement on data structure and semantics; however, on a
meta-
On 27 Aug 2009, at 17:11, pgsql-general-ow...@postgresql.org wrote:
From: Kelly Jones
Date: 27 August 2009 14:43:51 BST
To: pgsql-general@postgresql.org
Subject: Viable alternatives to SQL?
Many sites let you search databases of information, but the search
queries are very limited.
I'm creat
On 31 Jul 2009, at 19:49, Jeff Davis wrote:
Yes -- you can do what you want anyway. If you want it to be more
readable, you can redefine the division operator yourself -- it's
just a
function and operator that happens to be named "/" (although that
might
not be wise). Maybe you can call it "
Hello,
Divide by zero errors have come up a couple of times on this list
(once raised by me). I wonder if I could propose a feature for
discussion. Could the result of a division by zero be treated as
infinity or null, rather than raising an error? Floating point types
already have the co
On 8 Jun 2009, at 19:01, David Fetter wrote:
Hello!
I've the following data:
datetime | val1 | val2
time1|4 | 40%
time2|7 | 30%
time3| 12 | 20%
...
I'd like to sum up the following:
(7-4)*30% + (12-7)*20% + ...
This is best done in 8.4 using Windowing. Sadly, it's an
On 8 Jun 2009, at 17:23, Merlin Moncure wrote:
Is there a way when creating a table to limit it to one row? That
is,
without using a stored procedure?
I searched the documentation, but didn't find anything.
CREATE TABLE x (...);
CREATE UNIQUE INDEX x_only_one_row ON ((1));
very cleve
On 4 Jun 2009, at 13:11, Sam Mason wrote:
You need to take care of only one case here: denominator == 0; rest
of the
cases will be handled sanely by the database.
CASE WHEN packet_size = 0 THEN null ELSE cost/packet_size END as
unit_cost
Yes; or even shorter:
cost/nullif(packet_size,
Hello,
We have a system that allows users to create views containing
calculations but divisions by zero are commonly a problem.
An simple example calculation in SQL would be
SELECT cost / pack_size AS unit_cost from products;
Either variable could be null or zero.
I don't think there's a w
Hello,
Some of the new language features in 8.4 seem like pretty major
additions. I know that the window functions will be very useful. There
have been many times in the past when I've wanted to aggregate in this
way:
http://elegantcode.com/2009/01/04/sql-window-clause/
If this is possib
Hi,
Wondering who has any practical experience of open sourcing a software
product? I made a small internal database tool open source a while ago
and that worked quite well - www.gtportalbase.com/opensource. People
got involved in adding to the code and some people even paid a bit for
our
44 matches
Mail list logo
