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 keywords are here:
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
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
On 9 Sep 2013, at 21:03, Alvaro Herrera alvhe...@2ndquadrant.com 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;
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,
On 9 Sep 2013, at 14:41, David Johnston pol...@yahoo.com 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 replace with section;
it has to be a literal
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
On 19 Oct 2012, at 13:28, Frank Lanitz fr...@frank.uvena.de 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
On 19 Oct 2012, at 15:09, Albe Laurenz laurenz.a...@wien.gv.at 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
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
On 21 Aug 2012, at 13:32, Vincent Veyron vv.li...@wanadoo.fr 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
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::regclass
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
On 28 Jan 2012, at 15:27, Greg Sabino Mullane g...@turnstep.com 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
Stehule
2011/11/5 Oliver Kohll - Mailing Lists oliver.li...@gtwm.co.uk:
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
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.
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 - Mailing Lists
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/
regards
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',
On 7 Jan 2011, at 22:02, THOMPSON, JARED (ATTBAPCO) jt0...@att.com 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
On 21 Jul 2010, at 23:14, Joe Conway m...@joeconway.com 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
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 I hit
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
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 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?
Aren't you
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 test it)
Yes that
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 the
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.java
-
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
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)
);
On 29 Mar 2010, at 14:33, Clemens Eisserer linuxhi...@gmail.com 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
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
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
On 27 Sep 2009, at 21:10, InterRob rob.mar...@gmail.com 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:
On 25 Sep 2009, at 07:22, InterRob rob.mar...@gmail.com 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;
On 27 Aug 2009, at 17:11, pgsql-general-ow...@postgresql.org wrote:
From: Kelly Jones kelly.terry.jo...@gmail.com
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
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
On 8 Jun 2009, at 17:23, Merlin Moncure mmonc...@gmail.com 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
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 4 Jun 2009, at 13:11, Sam Mason s...@samason.me.uk 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:
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
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
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
43 matches
Mail list logo
