gging
> collector process
As of release 0.9.0 the connection parameters are no longer defined in
postgresql.conf; if present the parameters will be ignored and a warning
issued, like you saw here.
See the documentation for further details, particularly:
http://bdr-project.org/docs/0.9/rel
st need to dump the one database
rather than looping through a variable number) and will make life easier if you
ever need to do some kind of query involving multiple customers.
There will also be less overhead when adding a new schema vs adding
a new database.
Regards
Ian Barwick
--
Ian
ensuring any remote dependencies
are
present on the local server. PostgreSQL 9.5 will provide the IMPORT FOREIGN
SCHEMA
command, however this is limited to table/view definitions.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7
> # IPv4 local connections:
> hostall all 127.0.0.1/32md5
> # IPv6 local connections:
> --
Looks like you're missing an entry for IPv6 in pg_hba.conf; something
like this:
hostall
> transaction
(...)
>
> == Data from Node2 log file
>
> < 2015-06-08 10:08:45.957 PDT >LOG: entering standby mode
It looks like you're trying to run BDR on a streaming replication standby.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.c
at
> any given time we have only six months of postgresql data on the postgresql
> database server.
Have you looked at Barman ( http://www.pgbarman.org/ )?
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA
n how much WAL to retain, though you do
then need to be
careful that all standbys are actually consuming WAL otherwise files will be
retained for ever
(or until disk space runs out, whichever comes first).
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
Post
I don't think we've had this particular use-case before,
so I'll add some notes to the documentation on how best to handle it.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sen
uffers (which is what I guess you mean with "shared_memory")
can be a different value appropriate to the standby's hardware resources.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & S
plit that date into two columns on my select:
>
> 2015-12-18 = date column
> 02:40:00 = time column
>
> How can I do that without modifying any column/data?
> Maybe in a select?
TO_CHAR() is your friend:
https://www.postgresql.org/docs/current/static/functions-formatting.html
Regard
perform queries on the current database.
*However*, the syntax works when the named database is the same as one
you're connected to. If you do
\c MyTest
mytest=# select * from MyTest.public.cars;
the query will work (case is not the problem here).
HTH
Ian Barwick
--
http://sql-info.de
it's Not A Good Idea to rely on client-side
validation for security-related operations ;).
Regards
Ian Barwick
--
http://sql-info.de/index.html
---(end of broadcast)---
TIP 6: explain analyze is your friend
2007/11/18, Mag Gam <[EMAIL PROTECTED]>:
> Hi All,
>
> Planning to implement tsearch2 for my websitem and dbschema. I wanted to
> know if there is a "Best practices" guide I should be following. While
> reading about it, I noticed there were lot of 'gotchas' with this, such as
> back-up/restore, Sl
I can do this in code - but I was curious if there was a postgres way of
> doing it (didn't see any, but..)
You want the network address functions and operators, I presume:
http://www.postgresql.org/docs/8.2/interactive/functions-net.html
HTH
Ian Barwick
--
http://sql-i
ting the same error. Please advise
> if there was something else I needed to do.
Was the database server restarted after the new version was built?
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
binary will still be running.
Do also take note of Craig Ringer's email on this thread; in
particular you should always run the latest minor version, which
is currently 9.2.8.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 2
connstr from dblink_conf))
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
l.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
"All the date/time data types also accept the special literal value 'now' to
specify the current date and time"
and also here:
http://www.postgresql.org/docs/9.3/static/datatype-datetime.h
am = 'StarvationCrk+';
> count ---
> 0
You have a newline character. Try:
select count(*) from benthos where stream = E'StarvationCrk\n';
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 2
rendering of the newline character, the unicode linestyle (suggested
by Tom Lane in the previous thread) should do the trick:
postgres=> \pset linestyle unicode
Line style (linestyle) is unicode.
postgres=> SELECT E'foo\n';
?column?
──
foo ↵
(1
On 14/08/28 8:04, Ian Barwick wrote:
> On 14/08/28 7:31, Rich Shepard wrote:
>> I have some rows in a table where a column attribute has a newline (\n)
>> appended to the string. How do I represent that newline character in a SQL
>> statement using psql?
>>
>>
general package management.
> ps: is there a mac build for 9.4 beta 3 yet ? we would use that if
> available
> and not too hard to install
beta3 has not yet been released (October 9th is the scheduled date).
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQ
psql connections.
>
> Why would CGI connections fail?
It's possible that whatever driver the CGI scripts use is expecting to find
the socket in another directory, e.g. /var/run/postgresql/.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
Postgr
national track which was mainly
Asian, and there's talk of setting up a PGasia conference.
(Personally I'm British but kind of from Germany, now in Japan).
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, T
nd: ((id)::text ~ '.*12345.*'::text)
Planning time: 0.241 ms
Execution time: 61.257 ms
(7 rows)
[1] http://www.postgresql.org/docs/current/interactive/pgtrgm.html
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Developm
.conf" set "pg_ctl_options" to something like:
pg_ctl_options='-l /path/to/log'
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
So what am I missing ?
Strange is that I followed
Did you reload the configuration after changing pg_hba.conf, e.g.
"SELECT pg_reload_conf()"?
Also, looks like you have a typo:
> host replication repuser 127.0.0.1/32 mds5
> host replication repuser 0.0.0
ept a set
> CONTEXT: PL/pgSQL function "actionlist" line 11 at return next
You probably need to do
SELECT * FROM actionlist(123)
Ian Barwick
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
#x27;m actually living / working in Tokyo and though I'm not sure whether I'll be
able to attend all of the conference I will certainly be around and it would
be great to meet up.
Also, if anyone needs any help / advice / translation etc., feel free to ask and
I will do my best.
I
lines is removed in general since I
cannot read csv-files with blank lines, and I do not understand how
the option "force_not_null" can do the trick since that is on the
column level and not lines/row.
The "blank lines" referred to here are in the source code itself.
Regards
x27;s a longer backstory here that I'm happy to explain if
> necessary.) I'm looking for an efficient way to dump all the data in the DB
> without having to SELECT * on each table.
COPY might be of use:
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html
though you wil
thwhile results quickly with Postgres.
>
> Bruce Momjian's book is an excellent primer:
> http://www.postgresql.org/docs/books/awbook.html
It is *very* outdated (I remember using that to get started myself,
almost 10 years ago) and a lot has been improved since then.
Ian Barwick
--
to
have been obsoleted 5 years or so ago, see:
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/pg_upgrade/Attic/pg_upgrade
)
HTH
Ian Barwick
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2010/9/21 Bruce Momjian :
> Bruce Momjian wrote:
>> Ian Barwick wrote:
>> > Hi
>> >
>> > Just a quick note for anyone else building 9.0 from source and
>> > experimenting
>> > with pg_upgrade - if you get a message like
olumn xx.foobar does not exist
LINE 1: SELECT xx.foobar FROM xx;
Reproducible on 8.4.1 and 8.4.2; does not work in 8.3.1
(old test version I happen to have hanging around).
Questions:
- is this a feature?
- and if so, where is it documented?
(given that the key word in this is "name", thi
Hi Adrian,
2010/3/9 Adrian Klaver :
> On Tuesday 09 March 2010 12:50:45 am Ian Barwick wrote:
>> Hi
>>
>> I was wondering where some spurious information in a query was
>> coming from - it looked like something was inserting all the
>> values of a table row as
2010/4/8 Ognjen Blagojevic :
> Hi,
>
> How do you name a table which sole purpose is to store a list of values?
(...)
> Is this:
> a. Lookup table
> b. Classifier
> c. Cypher(er)?
>
> I'm looking for the appropriate term in English.
I'd call it a lookup-tab
ng at the ALTER TABLE commands available and there seems
> nothing that allows me to simply change column size from varchar(20)
> to varchar(35)?
The syntax you want (at least in more recent PostgreSQL versions, you don't
mention which version you have) is:
ALTER TABLE users ALTER COLUMN
abase setup from these; and a file which
contains the SQL required to make the changes between application
releases.
Ian Barwick
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Y sp.name LIMIT 1 OFFSET 2)
AS seller_name3
FROM (SELECT DISTINCT(store_id) FROM salesperson_store) store
ORDER BY store_id;
store_id | seller_name1 | seller_name2 | seller_name3
--+--+--------+--
101 | COLON, CRISTOBOL | OPEZ, CARLOS
ally a Windows/DOS
system?', the answer is 'no'."
From: http://firebird.sourceforge.net/index.php?op=history&id=ann_2
(This page: http://firebird.sourceforge.net/index.php?op=history&id=ann_1
says also: "InterBase started in the shower." Maybe the "somethi
an error which the app isn't dealing with (e.g. due to some change in
PostgreSQL between 7.2 and 7.4, possibly a "LIMIT x,y" type clause
or an attempt to insert a value which is longer than the database field
etc.) and this is causing the insertion of invalid values in the
above statem
sing items to the author. If he refuses them send
> them to me and I'll start an addendum.
The author writes: all additions, corrections etc. most welcome. I
haven't had a chance to update the site much recently, but I'm slowly
going through the list to update it for MySQL 5.
Ian Barwick
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
've updated the list.
I'd like to take the opportunity to point out that following the
original, unexpected "success" of the MySQL gotchas list, I created
one for PostgreSQL for the sake of "balance". Though I'm really having
to scrape the ba
s just in (via another post on this list):
http://www.prnewswire.com/cgi-bin/stories.pl?ACCT=104&STORY=/www/story/10-07-2005/0004163873&EDATE=
http://www.oracle.com/innodb/index.html
Oracle acquires Innobase, which is the company behind the InnoDB table
bit of MySQL, i.e. the engine with the
instead ( a big
source of problems with web-based apps); also, the LIMIT x,y syntax
will no longer work.
Your best bet is fro someone who knows your system to go through the
PostgreSQL release notes.
Ian Barwick
---(end of broadcast)---
TIP 9
tgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
for further information and hints.
> summe := summe + anzahl;
> END LOOP;
> return summe;
> END;$BODY$
> LANGUAGE 'plpgsql' VOLATILE;
BTW you don't need to specif
; having c > 1;
>
> But under PG, it errors out
> ERROR: column "c" does not exist
>
> Is it possible to do a query like this with PostgreSQL?
select lastname,count(*) as c from names group by lastname having count(*) > 1;
HTH
Ian Barwick
[EMAIL P
ters since 7.2 or 7.3. See
src/include/postgres_ext.h and look for NAMEDATALEN.
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
cl ON(cl.relname=cu.table_name)
LEFT JOIN pg_index i ON(cl.oid= i.indrelid)
WHERE c.table_name='insert_tablename_here'
Caveats:
- this is _not_ schema-aware.
- requires the information schema, e.g. 7.4 and later
- might just be horribly wrong anyway, but you get the general idea ;-)
HTH
I
> If I want to work with two different databases, it's possible to do a
> single transaction on two different databases ??
Not AFAIK, though contrib/dblink might be able to help you.
HTH
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
ype is 23 how can I get the string "integer"
> or something like that? Are these mappings stored anyware?
pg_catalog.pg_type ?
Ian Barwick
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
ike this too:
SELECT number
FROM procedures
WHERE date + (numdays || ' days')::interval <= CURRENT_TIMESTAMP;
Ian Barwick
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-n
ce function fubar() returns varchar as '
> declare
> l integer;
> begin
> l = 38;
> if l < 38 then
> return ''< 38'';
> elseif l >= 38 then
Try "elsif" here.
(No, I don't know what the problem with "elseif&q
ised at all.
from "PostgreSQL Weekly News - December 7th 2004"
( http://archives.postgresql.org/pgsql-announce/2004-12/msg9.php ):
"... The current plan is to make final release around
December 15th if everything goes well. ..."
Ian Barwick
---(end of broadcast)--
Oracle is positively minuscule (at least as far as its "installation
footprint" goes). I've run PostgreSQL, MySQL, DB2 and Oracle on the
same development machine without any issues. Of course if another
application is in constant use on a production server Postg
able (name) VALUES (:myvalue);
in 8.0 also:
\set myvalue '$$C\0xc3\0xb4te d\'Azur$$'
or:
INSERT INTO table (name) values
('C'||encode(decode('c3b4','hex'),'escape')||'te d''Azur');
Ian Barwick
-
t
for the latter).
You'll probably be best off explicitly providing schema names for your common
functions, e.g. SELECT * FROM common.mytable . Depending on your app,
that could be better from a security point of view in PostgreSQL as well,
if you want to prevent your users from sneakily
SQL AB), so
> a fork was created on pgFoundry. I believe it's called pgpedia.
That'd be "wikipgedia":
http://pgfoundry.org/projects/wikipedia/
Ian Barwick
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will
@package MediaWiki
*/
(There's also "includes/DatabaseOracle.php" but it doesn't say
anything about status).
Ian Barwick
---(end of broadcast)---
TIP 6: explain analyze is your friend
ossibly the hardware.
I've seen this kind of error in connection with hardware errors
(typically bad RAM or severe hard disk errors). Have you attempted
replicating this problem on another system to confirm / exclude this
as a possible cause?
Ian Barwick
--
http://sql-in
ue into that column to the
intended default value).
Ian Barwick
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
error for a selectrow_array, or one of those others mentioned?
Just checking, but do all your scripts have :
use strict;
use warnings;
at the top?
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe co
wasn't handling
it.
On the other hand - after copying the files from the good disk,
PostgreSQL started with barely a complaint and I couldn't detect any
corruption.
Ian Barwick
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On Sun, 18 Jul 2004 15:30:01 +0200, Ian Barwick <[EMAIL PROTECTED]> wrote:
> On Tue, 13 Jul 2004 05:53:36 +0100 (BST), raj veluchamy
> <[EMAIL PROTECTED]> wrote:
> > hello all,
> > i am running postgres in compiling postgres in OSCAR
> > cluster.
> >
d up the lengths of the columns and make
> my own judgements about averages for varchar columns. The "hidden"
> storage overhead is what I'm wondering about, I guess.
There is a little info in the FAQ:
http://www.postgresql.org/docs/faqs/FAQ.html
particul
On Tue, 24 Aug 2004 00:46:50 +0200, Markus Bertheau
<[EMAIL PROTECTED]> wrote:
>
>
> Ð ÐÐÐ, 23.08.2004, Ð 23:04, David Wheeler ÐÐÑÐÑ:
> > On Aug 23, 2004, at 1:58 PM, Ian Barwick wrote:
> >
> > > er, the characters in "name" don't seem to match
On Mon, 23 Aug 2004 16:50:04 -0700, David Wheeler <[EMAIL PROTECTED]> wrote:
> On Aug 23, 2004, at 4:34 PM, Ian Barwick wrote:
>
> > wild speculation in need of a Korean speaker, but:
> >
> > [EMAIL PROTECTED]:~/tmp> cat j.txt
> > ããã
> > íêì
>
;
SET
ctest=# INSERT INTO coding VALUES('müller');
INSERT 349960 1
ctest=# SELECT * FROM coding;
data
-
müller
(1 row)
ctest=# SET client_encoding TO UNICODE;
SET
ctest=# SELECT * FROM coding;
data
-
müller
(1 row)
Ian Barwick
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
t encoding does the 7.4.x database have?
Ian Barwick
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
JOIN clinical_text
ON xref.text_id=clinical_text.text_id
WHERE xref.drug_id=? <- insert query value here
AND xref.function_id=? <- and here
(disclaimer: statement untested)
The values not available will be returned as NULL.
> Thanks a bun
s generally advisable to also use ORDER BY in order to
guarantee consistent result sets over repeated queries.
HTH
Ian Barwick
--
Ian Barwick - Developer
http://www.akademie.de
Remove SUNGLASSES to reply ;-)
---(end of broadcast)---
TIP 5:
error), so I can check on that and do
> update instead.
This kind of query should work; just leave out the "FROM dummy_table" bit.
(in Oracle it would be "FROM dual").
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Wednesday 25 June 2003 21:37, Mike Mascari wrote:
> Ian Barwick wrote:
> > On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote:
(...)
> > This kind of query should work; just leave out the "FROM dummy_table"
> > bit. (in Oracle it would be "FROM dual&
ER TABLE tbl DROP COLUMN whatever
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=ddl-alter.html
IIRC it was introduced fairly recently (7.3?)
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Ha
parser: parse error at or near "date"
changed in 7.2, see:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=release-7-2.html
"The timestamp(), time(), and interval() functions are no longer available.
Instead of timestamp(), use timestamp 'string' or CA
LECT * FROM testtable WHERE testfield like '%olo%';
>
> brings both results instead of only showing Polo.
One solution[*]:
SELECT * FROM testtable WHERE to_ascii(testfield,'LATIN1') LIKE '%olo%'
Note this might not work with all database encodings, especiall
sql-general&m=105656988915991&w=2
(B>
(B> Ian Barwick wrote:
(B>
(B> [...]
(B>
(B> I proposed that same solution 3 years ago. Tom shoots it down:
(B
(B(This quote is not from Mike Mascari, not me)
(B
(B> [...]
(B>
(B> I couldn't get the link to work
On Tuesday 15 July 2003 09:51, Stefan Armbruster wrote:
> Hi,
>
> Am Mon, 2003-07-14 um 21.01 schrieb Ian Barwick:
> > On Monday 14 July 2003 16:04, Stefan Armbruster wrote:
> > > Hi,
> > >
> > > I tried to migrate a database from 7.3.2 down to Post
Linux, FreeBSD, etc., collectively?)
Unixen, or possibly GNU/Unixen ? ;-)
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED]
doesn't have line completion.
>
> The person that manages the machine installed from sources.
It needs to be (re)compiled with readline support, and you will
need the readline headers (SuSE provides a readline-devel rpm).
Ian Barwick
[EMAIL PROTECTED]
out something like:
select
case when 'now' between t.begin and t.end then t.login else 'None' end
as log,
LOWER(case when 'now' between t.begin and t.end then t.login else 'None'
end)
as log_lower
from my_table t
orde
ue,
info text
) type=innodb;
create table abc1234 (
moreinfo text,
ref_id int REFERENCES abc123(id)
) type=innodb;
but the foreign key defined on ref_id is (I presume)
transported to a remote forest in Sweden and eaten
by goats ;-)
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
83 matches
Mail list logo
