On Tue, Nov 23, 2010 at 02:09:19PM +1030, Dan Kortschak wrote:
> I want to be able to insert,
> uniquely, biological sequences into a table returning the sequence id -
> this part is fine. However, if the sequence already exists in the table
> I want to return to id.
The term you're looking for is
On Tue, Sep 28, 2010 at 02:35:09PM +0300, Allan Kamau wrote:
> I have access
> to a server running PG 8.4 on Ubuntu and I have noticed that after a
> day of intense use the PG slows down significantly, "free -g" reports
> almost no free memory available (something seems to leak memory on
> this Ubu
On Mon, Sep 27, 2010 at 06:36:25PM -0700, Mike Christensen wrote:
> Thus, the users table already has:
>
> MikeChristensen1
> MikeChristensen2
> MikeChristensen3
> MikeChristensen4
>
> I want to write a SQL query that figures out that MikeChristensen5 is
> the next available username and thus sug
On Fri, Sep 17, 2010 at 04:51:46PM +0200, Willy-Bas Loos wrote:
> i have a function that produces a result in xml.
> that is one row, one value even, but it grows pretty large.
> how is that handled?
Rows are sent back in the entireity, so the PG instance would need
enough memory to work with that
On Fri, Sep 17, 2010 at 06:16:44PM +0200, julia.jacob...@arcor.de wrote:
> Hello everybody out there using PostgreSQL,
>
> After having read the official documentation and having done extensive
> web search, I'm wondering how to perform something like a SELF LEFT
> OUTER JOIN in PostgreSQL, i.e. a
On Fri, Sep 17, 2010 at 02:14:57PM +0100, Sam Mason wrote:
> Postgres, the server software, will spill large results (and any
> intermediate working sets) to disk automatically as needed. I believe
> any memory allocated for this task will be up to work_mem in size.
That wasn't ver
On Fri, Sep 17, 2010 at 03:00:36PM +0200, Willy-Bas Loos wrote:
> Where does postgres keep the query result until it is returned?
> In the shared_buffers?
> Or in extra memory that was not previously allocated, or something else?
Postgres, the server software, will spill large results (and any
int
On Thu, Sep 16, 2010 at 01:44:30PM -0400, Aram Fingal wrote:
> On Sep 16, 2010, at 12:28 PM, Sam Mason wrote:
> > If you want to do the transformation in SQL, you'd be writing something
> > like:
> >
> > SELECT drug, dose
> >MIN(CASE subject WHEN 1 TH
On Thu, Sep 16, 2010 at 11:42:21AM -0400, Aram Fingal wrote:
> create table results(
> expt_no int references experiments(id),
> subject int references subjects(id),
> drug text references drugs(name),
> dose numeric,
> response numeric
> )
What's the primary key? I presume it's (expt_no,subject,
On Thu, Sep 16, 2010 at 03:22:15PM +0200, Andreas wrote:
> We are talking about nearly 500.000 records with considerable overlapping.
Other things to consider is whether each one contains unique entries and
hence can you do a "best match" between datasets--FULL OUTER JOIN is
your friend here, but
On Thu, Sep 16, 2010 at 04:40:42AM +0200, Andreas wrote:
> I need to clean up a lot of contact data because of a merge of customer
> lists that used to be kept separate.
> I allready know that there are double entries within the lists and they
> do overlap, too.
>
> Relevant fields could be na
On Fri, Sep 10, 2010 at 01:23:39PM -0700, bjjjrn lundin wrote:
> I usually do like this on a new box
>
> sudo su -
> su - postgres
> createuser bnl
> exit
> exit
It would be somewhat easier to use sudo's "-u" switch, the following
should do the same as the above:
sudo -u postgres createuser "$
On Tue, Sep 07, 2010 at 10:42:53PM -0400, p...@slatech.com wrote:
> i am currently populating the textsearch column with the following
> command:
>
> UPDATE
> products
> SET
> textsearch=setweight(to_tsvector('english', description), 'A') ||
> setweight(to_tsvector('english', part_number, 'B')
On Wed, Sep 01, 2010 at 10:22:20AM -0500, Bayless Kirtley wrote:
> About twice per month, it is necessary to reset the modem and router. This,
> of course, loses the manager's connection to the DB. The problem is, it also
> seems to break the connection at the cash register. The next time it trie
On Wed, Aug 25, 2010 at 08:47:10PM +0200, Wojciech Strzaaaka wrote:
> The data set is 9mln rows - about 250 columns
250 columns sounds very strange to me as well! I start to getting
worried when I hit a tenth of that.
> CPU utilization - 1,2% (half of the one core)
> iostat shows writes ~6MB/s
On Thu, Aug 19, 2010 at 05:40:21AM +0200, Adrian von Bidder wrote:
> On Thursday 19 August 2010 01.32:06 Benjamin Smith wrote:
> > This way we can be sure that either all the databases are in synch, or
> > that we need to rollback the program patch/update.
>
> I guess this might be more a hack th
On Tue, Jul 27, 2010 at 09:25:05PM -0400, Patrick May wrote:
> On Jul 27, 2010, at 9:21 PM, Steve Atkins wrote:
> > select date_trunc('hour', foo) + interval '30 minutes' *
> > floor(extract(minute from foo) / 30) as start, event, count(*) from bar
> > group by 1, 2 order by 1 asc;
>
> Tha
On Sun, Jul 25, 2010 at 12:15:06PM +0200, Torsten Bronger wrote:
> I need statistics about the PG server load. At the moment, I use
> for this
>
> SELECT tup_returned + tup_fetched + tup_inserted + tup_updated +
> tup_deleted FROM pg_stat_database WHERE datname='mydb';
>
> However, the figu
On Tue, Jul 20, 2010 at 10:18:59AM +0100, Jennifer Trey wrote:
> What is the most generic exception in postgres ? Throwable in Java ?
AFAIR, from programming Java many moons ago, you really don't want to go
about catching the most general exception. The ThreadDeath exception
for instance is deriv
On Mon, Jul 19, 2010 at 05:55:48PM +0200, arno wrote:
> But when using a custom function to compute my where parameter
> inet_to_bigint is a function that transform an inet address its integer
> representation.
> Is there a way, either to put function return value in a variable, or to tell
> pos
On Sat, Jul 17, 2010 at 07:46:23PM +0200, Thomas Kellerer wrote:
> Tom Lane wrote on 17.07.2010 19:35:
>> I'd dismiss those numbers as being within experimental
>> error, except it seems odd that they all differ in the same direction.
>
> And it's reproducable (at least on my computer). As I said I
On Wed, Jul 07, 2010 at 01:53:25PM +, Matthew Wilson wrote:
> create table scheduled_email (
> to_address text,
> email_subject text,
> email_body text,
> deliver_at timestamp,
> sent boolean
> );
>
> I know I could write an external process to p
On Tue, Jul 06, 2010 at 12:06:06AM -0700, GrGsM wrote:
> SELECT closedate,status,
>SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
>SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
>SUM (CASE WHEN empcode = 'NT-050' THEN 1 ELSE 0
On Tue, Jul 06, 2010 at 12:55:22PM +0100, Andre Lopes wrote:
> update aau_utilizadores
> set group_id = 3
> where email = pEMAIL;
[..]
> If I use the clause WHERE only in "id" will not work fot both cases, or will
> work?
Yes, it'll do the "right thing". OLD always refers
On Tue, Jul 06, 2010 at 12:28:35PM +0100, Andre Lopes wrote:
> Ok, I have done the UPDATE RULE like this and works!
> where
> (id = OLD.id or username = OLD.username or email = OLD.email)
I'm pretty sure you just want to be using the id column above. Using an
OR expression as you're doing could
On Mon, Jul 05, 2010 at 02:43:58PM -0700, Dennis Gearon wrote:
> I would like to open a conversation about either changing our email to
> be more like google groups, or a move to google groups.
You know you can read pg-general in google groups if you want:
http://groups.google.com/group/pgsql.g
On Mon, Jul 05, 2010 at 12:44:55PM -0300, Pedro Zorzenon Neto wrote:
> Em 05-07-2010 12:22, Sam Mason escreveu:
> > SELECT DISTINCT ON (hardware_id) hardware_id, diag_value, ts
> > FROM diagnose_logs
> > WHERE ts <= '2009-12-25 23:59:59'
> > ORDER BY
On Mon, Jul 05, 2010 at 11:48:37AM -0300, Pedro Zorzenon Neto wrote:
> for ($i = 1; $i < 500; $i++) {
> // return me the "most recent" diag_value from a hardware_id $i
> // at the desired timestamp
> runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
> 23:59:59' and hardwa
On Mon, Jul 05, 2010 at 01:52:20PM +, zeeshan.gha...@globaldatapoint.com
wrote:
> So, is this there a restriction with 32-bit PostgreSQL, a bug or
> configuration issue?
It's a restriction because of the 32bit address space. You've basically
got between two and three GB of useful space left
On Mon, Jul 05, 2010 at 10:20:30AM +0200, saitenhe...@web.de wrote:
> Datum count_person(PG_FUNCTION_ARGS) {
> SPI_connect();
> int ret = SPI_exec("SELECT count(*) FROM person", 0);
> SPI_finish();
> PG_RETURN_INT32(ret);
> }
>
> But I guess I still did something wrong, because no matter how many
On Thu, Jul 01, 2010 at 10:01:02AM -0700, Mike Christensen wrote:
> Yup, the problem is line 170 doesn't actually match up to the
> DB.dbs.out file line 170 (which is a blank line). I believe it means
> line 170 from the stdin pipe it was processing for the copy command.
Doh, that's annoying. It
On Thu, Jul 01, 2010 at 09:47:03AM -0700, Mike Christensen wrote:
> On Thu, Jul 1, 2010 at 9:44 AM, Sam Mason wrote:
> > On Thu, Jul 01, 2010 at 09:31:12AM -0700, Mike Christensen wrote:
> >> Then, I edited the file in Notepad and saved it as UTF8 which also
> >> appear
On Thu, Jul 01, 2010 at 09:31:12AM -0700, Mike Christensen wrote:
> Then, I edited the file in Notepad and saved it as UTF8 which also
> appears to have worked.
I don't think you want to be doing this. The file should contain a "set
client_encoding" of the correct value which will let PG to do the
On Thu, Jul 01, 2010 at 04:53:51PM +0200, Arnaud Lesauvage wrote:
> Le 1/07/2010 16:48, Sam Mason a écrit :
>> How about using the built in character conversion routines. Something
>> like:
>>
>>col = convert_from(convert_to(col, 'LATIN9'),'LATI
On Tue, Jun 29, 2010 at 04:52:22PM +0200, Arnaud Lesauvage wrote:
> We have a database in UTF8, from which we have to export text files in
> LATIN9 encoding (or WIN1252, which is almostthe same I believe).
>
> Records are entered via MSAccess forms (on psqlodbc-linked tables).
> The problem is th
On Thu, Jul 01, 2010 at 04:26:38AM -0700, David Fetter wrote:
> On Wed, Jun 30, 2010 at 12:11:35AM -0700, Nick wrote:
> > Is this the most efficient way to write this query? Id like to get a
> > list of users that have the categories 1, 2, and 3?
> >
> > SELECT user_id FROM user_categories WHERE c
On Wed, Jun 30, 2010 at 05:54:51PM +0200, Anders Steinlein wrote:
> No one with any response on this?
Fun problem, how about:
SELECT x.email, x.segmentid
FROM (
SELECT c.email, t.segmentid, t.tagname, t.tagtype
FROM contacts c, segments_tags t) x
LEFT JOIN contacts_tags t USIN
On Tue, Jun 29, 2010 at 02:24:00PM +0200, Arnaud Lesauvage wrote:
> I'd like to generate CSV files from the output of a query.
> I can't get the srings in the output to be quoted though. I thought that
> this was the default for CSV, and even adding the "QUOTE" parameter does
> not help :
>
> p
On Mon, Jun 14, 2010 at 08:27:49AM -0400, David Wilson wrote:
> On Mon, Jun 14, 2010 at 5:24 AM, Leonardo F wrote:
> > > For "inserts" I do not see the reason why it would be better to
> > > use index partitioning because AFAIK b-tree would behave exactly
> > > the same in both cases.
> >
> > no,
On Sun, Jun 06, 2010 at 10:15:52AM -0500, Little, Douglas wrote:
> They should generate equivalent results,
> But the difference is the constraint on bu.bid=5.
> In the 1st case it's being done after the join.
> In the 2nd case it is being done before the join.
In PG version 8.1 (?) and onward
On Wed, Jun 02, 2010 at 04:47:01PM +0200, A. Kretschmer wrote:
> In response to Sam Mason :
> > SELECT c.*
> > FROM customer c, (
> > SELECT *, row_number() OVER ()
> > FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord)
> > WHERE c.id = x.v
On Wed, Jun 02, 2010 at 06:28:14AM -0700, David Fetter wrote:
> On Tue, Jun 01, 2010 at 06:16:06PM -0400, m. hvostinski wrote:
> > I have a simple query like:
> >
> > SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10)
> >
> > The problem is that I need to retrieve the rows in the same order a
On Tue, Jun 01, 2010 at 06:16:06PM -0400, m. hvostinski wrote:
> I have a simple query like:
>
> SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10)
>
> The problem is that I need to retrieve the rows in the same order as the set
> of ids provided in the select statement. Can it be done?
Yes,
On Mon, May 24, 2010 at 05:04:10PM -0400, Merlin Moncure wrote:
> Let me humbly state that the #1 problem that beginners face with
> security and encryption is focusing too much on the mechanics and not
> enough on the 'big picture' issues:
One more that OP seems to be avoiding is why would anybod
On Thu, May 20, 2010 at 09:33:23PM -0500, Peter Hunsberger wrote:
> On Thu, May 20, 2010 at 8:03 PM, Richard Walker
> wrote:
> > If the hacker gets root access so they can read
> > the raw database files, they most likely also
> > have access to the means to decrypt any
> > encrypted data. This i
On Fri, Apr 09, 2010 at 10:08:38AM -0700, arya6000 wrote:
> Connection refused. Check that the hostname and port are correct and that
> the postmaster is accepting TCP/IP connections.
Running the following on your server:
netstat -tnl
will tell you if PG is actually listening on the port you e
On Fri, Feb 05, 2010 at 10:13:21AM +0100, Stefan Schwarzer wrote:
> probably not too complicated, but although googling my way through
> many pages, I don't find the solution.
>
> I have a query which uses already an apostrophe to escape something
> else:
Not sure if "dollar quoting" may be e
On Mon, Feb 01, 2010 at 06:21:55AM -0600, ray joseph wrote:
> I am new to this so I could
> use a little help: What do you mean to connect to it and how would I do it?
I'd have a flick through the manual if I were you; the following is a
reasonable place to start:
http://www.postgresql.org/doc
On Sun, Jan 31, 2010 at 11:36:55PM -0800, Neil Stlyz wrote:
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01';
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20';
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01';
>
> A
On Wed, Jan 27, 2010 at 10:40:17AM -0500, Aycock, Jeff R. wrote:
> EXECUTE 'SELECT *, $1, now() INTO schema_1.'||whoami||' FROM
> schema_2.'||whoami||' where created_dt between $2 and $3;'
You'll also need to expand those other parameters. The code is executed
in an independent scope and hence P
On Mon, Jan 25, 2010 at 04:05:57PM +0100, Stefan Schwarzer wrote:
> Guess I'd need to do this as/with postgres user/role.
Yup, or at least somebody with superuser rights. Try "\du" in psql.
> But really not
> sure how this goes. Thought it should be something like this:
>
> sudo su - po
On Fri, Jan 22, 2010 at 01:49:50AM -0800, Yan Cheng Cheok wrote:
> By refering to
> http://archives.postgresql.org/pgsql-general/2010-01/msg00846.php
>
> Does this means, I shall convert *ALL* my stored procedure, which use
> function parameter during its SQL query, to use EXECUTE, to ensure I
> a
On Fri, Jan 22, 2010 at 11:45:30AM -, Alberto Colombo wrote:
> select extract(epoch from timestamp 'epoch');
>
> date_part
> ---
> -3600
>
> Shouldn't that be zero? My timezone is Europe/London (but does it
> matter?).
Writing "timestamp" like that says that you want the time
On Thu, Jan 21, 2010 at 10:59:42PM -0800, Yan Cheng Cheok wrote:
> Currently, I am having JOIN statement as follow (1st case)
>
> SELECT measurement_type.value, measurement.value, measurement_unit.value
> FROM
> measurement_type INNER JOIN
> (measurement_unit INNER JOIN
>
On Tue, Jan 19, 2010 at 07:40:00AM +0100, Philippe Lang wrote:
> I'm trying to figure out how to use an index on an immutable function
> call in order to speed up queries.
[..]
> Unfortunately, Postgreql does not use the index at all.
Yup, an index isn't going to be very useful in what you're doin
On Mon, Jan 18, 2010 at 09:57:02AM -0500, Tom Lane wrote:
> Greg Smith writes:
> > Craig Ringer wrote:
> >> For those non-Oracle users among us, what's an external table?
>
> > External tables let you map a text file directly to a table without
> > explicitly loading it. In PostgreSQL, if you h
On Tue, Jan 12, 2010 at 04:06:20PM +0200, Sergey Levchenko wrote:
> but I have to fold it in multi-dimensional, array like {{71629130,
> 15518, 0}, {2668722, 616, 0}} ?
> but it's not possible to pass more then one argument to the array_agg
> function :/
Tuples work fine. Multidimensional arrays
On Fri, Jan 08, 2010 at 11:28:15AM +0100, Ivan Sergio Borgonovo wrote:
> Is there a way to know/estimate how much is left to complete a
> restore?
maybe something like "pv" would help?
http://www.ivarch.com/programs/pv.shtml
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing
On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
> postgres=# \l
> List of databases
>Name| Owner | Encoding | Collation |Ctype| Access
> privileges
> ---+--+--+-+-+
On Fri, Jan 08, 2010 at 02:55:53PM +, Grzegorz Jaaakiewicz wrote:
> Is there any nice way to do something like that in plpgsql:
>
> EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';
>
> It would probably work, but some values are NULL, and plpgsql
> interpreter just puts
Hi,
I've just realised that I'm performing the same rewrite on lots of my
queries to get performance reasonable. They take the form of something
like:
SELECT a.x, b.y, COUNT(*) AS n
FROM foo a, bar b
WHERE a.z = b.z
GROUP BY a.x, b.y;
And I rewrite them to:
SELECT a.x, b.y, SUM(b.cou
On Mon, Jan 04, 2010 at 12:45:00PM -0500, Tom Lane wrote:
> Sam Mason writes:
> > Um, I think the OP is right. Notice he does: ...
> > showing that PG is auto-magically inserting a cast from BIGINT to OID.
>
> Yes, as a quick look into pg_cast will show you, bigint -> o
On Mon, Jan 04, 2010 at 05:12:56PM -0800, Yan Cheng Cheok wrote:
> Measurement table will have 24 * 50 million rows in 1 day
> Is it efficient to design that way?
>
> **I wish to have super fast write speed, and reasonable fast read speed from
> the database.**
When writing software there's (al
On Mon, Jan 04, 2010 at 07:46:29AM -0800, shulkae wrote:
> We have few tables which we would like to convert to XML and store it.
Not sure if you've seen them, but Postgres provides a few built in functions
that will help with simple tasks:
http://www.postgresql.org/docs/current/static/functio
On Mon, Jan 04, 2010 at 03:55:15PM +0100, Albe Laurenz wrote:
> donniehan wrote:
> > postgres=# create table test1(c1 OID, c2 BIGINT);
> > postgres=# create view v1 as select coalesce(c1,c2) from test1;
> > postgres=# \d v1
> > SELECT COALESCE(test1.c1, test1.c2::oid) AS "coalesce"
> >FROM tes
On Mon, Jan 04, 2010 at 04:53:16AM -0800, Yan Cheng Cheok wrote:
> From general point of view, having 3 SQL statement wrapped in a
> single stored procedure shall perform better due to reduced overhead
> to communicate with SQL server. Is that true? Or that is my false
> assumption?
I'd be tempted
On Thu, Dec 17, 2009 at 10:24:28AM -0500, Tom Lane wrote:
> Justin Bailey writes:
> > If s was automatically truncated, the insert would succeed, but it
> > fails with a "value too long" error.
>
> Oh, I thought the failure was the behavior you wanted. There's
> no automatic truncation here ---
On Wed, Dec 16, 2009 at 05:18:12PM -0800, yuliada wrote:
> Sam Mason wrote:
> > How about combining all 1000 selects into one?
>
> I can't combine these selects into one, I need to run them one after
> another.
Hum, difficult. What other information is in the row that
On Wed, Dec 16, 2009 at 04:56:16AM -0800, yuliada wrote:
> I have a table with column of character varying(100). There are about
> 150.000.000 rows in a table. Index was created as
>
> CREATE INDEX idx_stringv
> ON bn_stringvalue
> USING btree
> (lower(value::text));
>
> I'm trying to execu
On Wed, Dec 16, 2009 at 12:47:36AM -0800, Mike Christensen wrote:
> When the user searches for a new pasta dish, the UI would generate a query
> something like this:
>
> SELECT * FROM Recipes where RecipeTitle ilike '%pasta%';
>
> I only need the data from the recipes table since I display a summ
On Sun, Dec 06, 2009 at 02:21:09PM -0800, John R Pierce wrote:
> yourusername $ sudo su - postgres
> postgres $ psql
Somebody pointed out to me on this list that you don't need su for that.
The following is equivalent:
sudo -u postgres psql
Also, because it's all one line it plays nicely when
On Sat, Dec 05, 2009 at 09:54:58AM -0500, Merlin Moncure wrote:
> GIN is a pretty heavy price to pay for something that should be btree
> indexable. Also note he is using a multi column index with array as
> second column...that would be pretty awkward with GIN.
Yup, sounds as though it's not goi
On Sat, Dec 05, 2009 at 02:23:13AM +0100, Denes Daniel wrote:
> 2009/12/4 Sam Mason
> > CREATE INDEX test_my_idx ON test (type,(ident[1]));
>
> Sorry, but this approach is no good, since I may search like:
> SELECT * FROM test WHERE type = 'three' AND (ident[1] =
On Fri, Dec 04, 2009 at 06:58:21PM +0100, Denes Daniel wrote:
> SELECT * FROM test WHERE type = 'two' AND ident[1] = 'test';
>
> this query uses the primary key index only for the "type" field, and
> then filters for ident[1]. Is there a way to make it use the index for the
> array prefix search t
On Thu, Dec 03, 2009 at 10:46:54AM -0500, Tom Lane wrote:
> Sam Mason writes:
> > As others have said; BYTEA is probably the best datatype for you to
> > use. The encoding of BYTEA literals is a bit of a fiddle and may need
> > some changes, but it's going to be much m
On Thu, Dec 03, 2009 at 08:33:38AM +0100, Kern Sibbald wrote:
> Bacula gets the "raw" filename from the OS and stores it on the Volume
> then puts it in the database. We treat the filename as if it is UTF-8
> for display purposes, but in all other cases, what we want is for the
> filename to go in
On Thu, Nov 19, 2009 at 10:47:02AM -0800, Scott Bailey wrote:
> Sam Mason wrote:
> >Is "idx" really the best name for this?
>
> Well I used idx() because there was already a idx(int[], int) function
> with the _int contrib module.
I don't remember ever using that
On Thu, Nov 19, 2009 at 12:43:38PM -0500, Merlin Moncure wrote:
> we could use a version
> of unnest that works like that (returns idx, elem)? It would be a
> small efficiency win over generate_series based approaches.
What would "idx" look like for multidimensional arrays? I think PG
needs a s
On Thu, Nov 19, 2009 at 09:46:42AM -0800, Scott Bailey wrote:
> We had an idx() function in the _int contrib module. I wonder if it
> would be useful to write this in C now that _int is deprecated?
Is "idx" really the best name for this? there could be multiple
occurrences of a value in an array
On Thu, Nov 19, 2009 at 05:24:33PM +0100, Pavel Stehule wrote:
> it should be little bit more effective:
I'm not sure if it will be much more; when you put a set returning
function into a FROM clause PG will always run the function to
completion---as far as I know, but I've only got 8.3 for testin
On Mon, Nov 09, 2009 at 11:23:52PM +, Raymond O'Donnell wrote:
> On 09/11/2009 22:43, Raymond O'Donnell wrote:
> > Finally, you can use OUT parameters to return multiple values from the
> > function - this is handy if you need to return just a few values.
>
> Just to clarify further - this las
On Mon, Nov 09, 2009 at 09:15:03AM -0500, Chris Barnes wrote:
> Would anyone in the postgres community have a shell script that
> performs a full online backup of postgres?
Have you tried pg_dumpall?
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgres
On Mon, Nov 09, 2009 at 01:47:55PM +0100, Anders Moe wrote:
> As far
> as ODBC is concerned I'm just working from the assumption that a native link
> is generally preferable.
You'll get a bit more control, but most of the time that's not needed.
> If anyone tells me that the psql ODBC link has th
On Mon, Nov 09, 2009 at 09:47:48AM +0100, Anders Moe wrote:
> We're trying to get our 64 bt windows application to use postgresql, which
> means I have to #include stuff from psql and link to 64 bit versions of the
> libs. Which do not seem to exist.
>
> Spesifically I need to get Qt compiled with
On Mon, Nov 09, 2009 at 05:43:30PM +0530, Anand wrote:
> As i need to update the PostgreSQL version 8.2.6 with higher versions.
> But in release notes its mentioned that upgrading the PostgreSQL version
> 8.2.6 is as follows:
> PostgreSQL version 8.2.6-->8.2.7-->8.2.11-->8.2.14
AFAIK that's just a
On Sun, Nov 08, 2009 at 05:07:16PM -0300, Mariano Mara wrote:
> I have this plpython function that I need to execute with a non
> superuser. I logged in the postgres account, create it and grant execute
> rights to the target user.
> However I cannot execute it with this user: I'm getting a "funct
On Sun, Nov 08, 2009 at 02:25:26PM +0200, Jukka Inkeri wrote:
> Jukka Inkeri wrote:
> >tested using Cygwin 1.5.x and 1.7.x
> >
> >Ssh tty problem using Windows native psql.exe ?
>
> If I use flag -f in psql, then no problem.
[...]
> Interactive work not.
If I remember correctly it's to do with
On Sat, Nov 07, 2009 at 08:10:23AM -0500, Geoffrey wrote:
> We now want to add a second PITR machine that is in a remote location.
> The question is, what is the best solution for such an effort? We've
> considered shipping the wal files to both locations, but the concern is
> that if one fails
On Sat, Nov 07, 2009 at 10:48:14AM +0200, Johan Nel wrote:
> update pg_attribute set attlen = 4 +
> where attname = 'yourcolumnname'
>
> That will take only a couple of milliseconds to do.
It will also update *every* column with that name. Something involving
the "attrelid" would be much safer.
On Wed, Nov 04, 2009 at 11:31:55AM -0500, Carlo Stonebanks wrote:
> When I try the following command:
> ALTER TABLE mdx_core.audit_impt RENAME TO _audit_impt;
>
> ERROR: type "_audit_impt" already exists
>
> How do I search the system catalogs to find this particular "type"?
select typname, ty
On Wed, Nov 04, 2009 at 12:41:25PM +0330, shahrzad khorrami wrote:
> Column | Type |
> id | integer | not null default
> f1 | character varying(32) |
> f3 | character varying(32) |
> f4 | character varying(32) |
> f5 | character varying(32) |
On Mon, Nov 02, 2009 at 08:10:47PM -0800, Greg Stark wrote:
> As far as i know all of these actually work with doubles
> though, so you'll lose precision.
IEEE 754 floating point numbers (i.e. float8 or "double precision" in
PG) are defined to have a 52 bit significand and hence can store integer
On Mon, Nov 02, 2009 at 10:03:49AM -0500, Tom Lane wrote:
> Sam Mason writes:
> > Rotating the array by 180 degrees in every dimension would give the same
> > answer for 1-dimensional arrays and give sensible answers for higher
> > dimensional arrays.
>
> The easy im
On Mon, Nov 02, 2009 at 09:55:20AM -0500, Merlin Moncure wrote:
> On Mon, Nov 2, 2009 at 9:51 AM, Sam Mason wrote:
> > On Mon, Nov 02, 2009 at 09:20:38AM -0500, Tom Lane wrote:
> >> Craig Ringer writes:
> >> > Before I go ahead and try to write a decent qualit
On Mon, Nov 02, 2009 at 09:20:38AM -0500, Tom Lane wrote:
> Craig Ringer writes:
> > Before I go ahead and try to write a decent quality version: is there
> > any chance an array_reverse() function (in C) would be accepted into Pg
> > mainline?
>
> What would it mean for a multi-dimensional array
On Mon, Nov 02, 2009 at 10:52:40AM +, Jasen Betts wrote:
> what's the absolute value of '1month -30 days'::interval
>
> if I add it to the first of march it goes forwards if I add it to the
> first of february if goes backwards. if I add it to the first of april
> it goes nowhere.
>
> select
On Mon, Nov 02, 2009 at 11:22:00AM +, Jasen Betts wrote:
> On 2009-10-27, Sam Mason wrote:
> > On Tue, Oct 27, 2009 at 03:25:02PM +0000, Sam Mason wrote:
> >> If the absolute value of an interval was defined to strip out all the
> >> negation signs you&#x
On Fri, Oct 30, 2009 at 01:09:30PM -0700, Scott Bailey wrote:
> Sam Mason wrote:
> >My personal feeling is that when you provide any ordering operator and
> >negation you can easily provide an absolute value operator. We've
> >already (somewhat arbitrarily) decided t
On Fri, Oct 30, 2009 at 06:23:28PM +, Sam Mason wrote:
> On Fri, Oct 30, 2009 at 10:47:26AM -0700, Blake Starkenburg wrote:
> > ID | scores
> > 2 | {54,14,21,8}
> > 3 | {12,0,7}
> >
> > Now I want to append the score of 12 on row:ID 2 so the new sco
On Fri, Oct 30, 2009 at 10:47:26AM -0700, Blake Starkenburg wrote:
> ID | scores
> 2 | {54,14,21,8}
> 3 | {12,0,7}
>
> Now I want to append the score of 12 on row:ID 2 so the new scores would
> read {54,14,21,8,12}.
You need to use the normal array concatenation operator, ||, for
example:
1 - 100 of 702 matches
Mail list logo
