How do I calculate the sum of a field filtered by multiple windows
defined by another field?
I have table event with fields event_date, num_events, site_id. I can
easily use aggregate SQL to do SELECT SUM(num_events) GROUP BY
site_id.
But I also have another table site with fields site_id, target
When I save a VIEW, Postgres seems to convert it to a different
format, functionally equivalent but unrecognizable (whitespace,
comments, adds lots of casts, etc.)
Is there any simple way to preserve my original code?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
Version 9.2.4
On 3/15/15, David G. Johnston wrote:
> On Sunday, March 15, 2015, Robert James wrote:
>
>> How do I calculate the sum of a field filtered by multiple windows
>> defined by another field?
>>
>> I have table event with fields event_date, num_events
Is there anyway to know if a DELETE will violate an FK without actually
trying it?
Any way to get psql Tab Completion in Windows?
I'd like to be able to detect if a record has associations. I don't want to
actually delete it, just know if it could be deleted. (This is to build an
intelligent menu on a GUI)
On 5/29/07, Albe Laurenz <[EMAIL PROTECTED]> wrote:
> Is there anyway to know if a DELETE will violate an FK
> withou
1. How can I get a list of available functions (ie, user defined or contrib)
using SQL?
2. Is there any performance or other advantage to using PL/pgsql over
Pl/Perl or Python?
I have a one varchar field.
I'd like to order so that records where field='2' come first, then '1', then
'9', then anything but '0', then '0'. Is there anyway to do this in a
standard order by clause (that is, without writing a new SQL function)?
Can you clarify the need / value added for reporting tool, over just running
queries and packaging the output in HTML (with a little CSS for styling, you
can get near PDF quality). This can be done in SQL and a tad of PHP (or
Ruby).
On 10/15/07, Geoffrey <[EMAIL PROTECTED]> wrote:
>
> Ned Lilly w
We have an application in which every customer has their own database, all
running from our Postgres server.
There is a large, mostly static, database of information (chemical
information), which each customer needs read access to. Lots of customer
data points to this static db, with foreign keys
18/07, Douglas McNaught <[EMAIL PROTECTED]> wrote:
>
> "Robert James" <[EMAIL PROTECTED]> writes:
>
> > 1.) Is there a way of separating, isolating, and sharing the shared data
> that
> > will still allow FKs to it?
>
> The only approach I know of woul
Comedy aside, this makes a lot of sense:
The shared data has nothing private in it at all - it's chemical info.
Sharing it is no worse than sharing the application code, or the OS's
libraries. It's the customer's data which needs to be isolated.
On 11/18/07, Andrej Ricnik-Bay <[EMAIL PROTECTED]> w
Typically, my web application does some initial validation, then, if
it passes, does the actual query. For both performance and
simplicity, I'd like to combine these all into one trip to Postgres.
Ideally, I'd like to do this in SQL. If that's not possible, I could
use PL/pgsql, though I'm not ad
I need to INSERT a large number of records. For performance reasons,
I'd rather send them to Postgres in one giant INSERT.
However, if there's a problem in one record (eg one row doesn't meet a
constraint), I'd still like the others saved. That is, I specifically
DO NOT want atomic behavior. It
On 1/13/13, Ian Lawrence Barwick wrote:
> 2013/1/14 Robert James :
>> I have a lot of VALUES I want to INSERT. But only a subset of them -
>> only those that meet a JOIN criteria involving another table.
>>
>> I could INSERT them into a temp table, and then do a SELECT
On 1/13/13, Chris Angelico wrote:
> On Mon, Jan 14, 2013 at 3:37 PM, Robert James
> wrote:
>> Thanks. But how do I do that where I have many literals? Something like:
>>
>> INSERT INTO seltest (id, a, b) SELECT (1,2,3),(4,5,6),(7,8,9) WHERE b
>> IN (SELECT ...)
&
I'd like to organize tables into different groups. This is for 3 reasons:
1. Each group needs to be backed up and restored independently from the others
2. Each group has different permissions for particular users
3. As an aid to human understanding organization
I would do this as actual database
I've been getting a funny SQL error, which I've boiled down to this case.
SELECT (regexp_matches('abc', '(.)b(.)'))[1] IS NOT NULL
-- Returns true, as expected
SELECT (regexp_matches('abc', '(.)b(.)'))[1] IS NOT NULL AND true
-- Gives this error:
ERROR: argument of AND must not return a set
SQL s
I'd like to understand better why manually using a temp table can
improve performance so much.
I had one complicated query that performed well. I replaced a table
in it with a reference to a view, which was really just the table with
an inner join, and performance worsened by 2000x. Literally.
I'd like to better understand TIMESTAMP WITH TIME ZONE.
My understanding is that, contrary to what the name sounds like, the
time zone is never stored. It simply stores a UTC timestamp,
identical to what TIMESTAMP WITHOUT TIME ZONE stores.
And then the only difference is that WITH TIME ZONE will
On 1/18/13, Tom Lane wrote:
> Jeff Janes writes:
>> On Fri, Jan 18, 2013 at 9:29 AM, Robert James
>> wrote:
>>> In other words: Since my query is 100% identical algebraicly to not
>>> using a temp table, why is it so much faster? Why can't the pl
On 1/18/13, Steve Crawford wrote:
> On 01/18/2013 09:31 AM, Robert James wrote:
>> I'd like to better understand TIMESTAMP WITH TIME ZONE.
>>
>> My understanding is that, contrary to what the name sounds like, the
>> time zone is never stored. It simply stores
In relational algebra, I have relation R and relation S, and want to
find the cardinality of R, of S, and of R-intersect-S.
I know the SQL for R and S. What's the best way to compute the
cardinality of each relation (query) and of their intersection?
--
Sent via pgsql-general mailing list (pgs
I have two relations, where each relation has two fields, one
indicating a name and one indicating a position. That is, each
relation defines a sequence.
I need to determine their longest common subsequence. Yes, I can do
this by fetching all the data into Java (or any other language) and
comput
On 7/8/13, hubert depesz lubaczewski wrote:
> On Mon, Jul 08, 2013 at 09:09:26AM -0400, Robert James wrote:
>> I have two relations, where each relation has two fields, one
>> indicating a name and one indicating a position. That is, each
>> relation defines a sequence.
>
I'd like a stored procedure which does something like:
INSERT INTO...
SELECT... -- This should be returned as multicolumn, multifield - just
like a table or view
When I run it, though, instead of getting a table, I get one field
with all the data in it as a compound type. I'd like to return the
Is there any way to set a variable or parameter for a query?
I have a long query where a certain variable needs to be easy to
change. I'd like to do something like:
threshold = 10.3
SELECT... WHERE x > $threshold... AND y * 1.3 > $threshold...
Currently, I need to do this in a scripting langua
I have a view which is very slow to computer, but doesn't change often.
I'd like to materialize it. I thought I'd do a simple poor man's materialize by:
1) ALTER VIEW myview RENAME to _myview
2) SELECT * INTO myview FROM _myview
The only problem is that all my other views, which are dependent on
I'm doing a JOIN which is very slow:
JOIN t ON t.f1 LIKE (q.f1 || '%')
t1 has an INDEX on (f1, f2) which I thought would help for this. But
Postgres seems to still use a (very slow) Nested Loop. What type of
index would be appropriate for this?
(My goal is to join on a substring starting from
I noticed that when I have an index on (a,b) of table t, and I do an
SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I
create a new index of only a, it does use the index. Why is that?
And, more importantly, when I do a query involving a merge join of
table t, which requires sorti
On 8/13/13, Robert James wrote:
> I noticed that when I have an index on (a,b) of table t, and I do an
> SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I
> create a new index of only a, it does use the index. Why is that?
>
> And, more importantly, when I do
On 8/13/13, Craig Ringer wrote:
> On 08/14/2013 06:05 AM, Robert James wrote:
>> I'm doing a JOIN which is very slow:
>>
>> JOIN t ON t.f1 LIKE (q.f1 || '%')
>>
>> t1 has an INDEX on (f1, f2) which I thought would help for this. But
>> Postgr
On 8/14/13, Kevin Grittner wrote:
> Robert James wrote:
>
>> I'm confused: What's the difference between
>> col LIKE 'foo%'
>> and
>> col LIKE f1 || '%'
>> ?
>
> The planner knows that 'foo%' doesn'
How can I escape a string for LIKE operations?
I want to do:
SELECT * FROM t WHERE a LIKE b || '%'
But I want be to interpreted literally. If b is 'The 7% Solution', I
don't want that '%' to be wildcard. I can't find an appropriate
function to escape it and any other potential wildcards for LI
On 8/15/13, Jeff Janes wrote:
> On Thu, Aug 15, 2013 at 1:16 PM, Robert James
> wrote:
>> How can I escape a string for LIKE operations?
>>
>> I want to do:
>>
>> SELECT * FROM t WHERE a LIKE b || '%'
>>
>> But I want be to interpreted l
I have a query which, when I materialize by hand some of its
components, runs 10x faster (including the time needed to
materialize). Is there any way to force Postgres to do that? Or do I
need to do this by hand using temp tables?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql
If I have a slow_function(), and I create an index of
slow_function(field), will Postgres use that index to avoid having to
recompute the function?
Example:
SELECT slow_function(field1) FROM table1 WHERE id = 5
It won't use the index on field1 to _find_ the record. Can it use it
to compute the
What's the best way to check if string a begins with string b?
Both a and b are coming from fields in a table.
Requirements:
* Either a or b might have special chars (such as '%') in them which
should NOT do anything special - they're just plain strings, not
regular expressions.
* a and b can be
Is there a way to do a Postgres internal cron job? That is, do
something every X minutes?
Yes: Of course I can do this using cron. But I'd like to be able to
manage this from within Postgres.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscrip
I have a slow_function. My table has field f, and since slow_function
is slow, I need to denormalize and store slow_function(f) as a field.
What's the best way to do this automatically? Can this be done with
triggers? (On UPDATE or INSERT, SET slow_function_f =
slow_function(new_f) ) How?
Will c
I would like to:
INSERT INTO t
SELECT * FROM r, (x + y) AS field3
How do I correlate the names of the fields? That is, how do I indicate
which fields from r or field3 should be inserted into the right
columns in t?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make c
When forced to denormalize for performance reasons, how do you ensure data
integrity? I like to, instead of denormalizing, use materialized views,
which offer lots of the same benefits without the costs. Yet, in Postgres
they're only experimental add-ons, not to be relied on for production use.
I
I'm running a very large series of commands - mainly DDL but some DML as
well - in a large transaction. I get the following error, which doesn't
seem to be documented:
"ERROR: failed to fetch old tuple for AFTER trigger
: COMMIT"
There are no triggers that I'm aware of. I've gotten this error w
Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I
do WHERE y, it does so as well, but when I do WHERE x OR y, it doesn't. Why
is this so? And how can I shut this off?
select * from dict
where
word in (select substr('moon', 0, generate_series(3,length('moon' --
this is
PS Running "PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)"
On Sun, Jul 19, 2009 at 6:58 PM, Robert James wrote:
> Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I
> do WHERE y, it does so as well, but when I do
I would like to CLUSTER a table on its PRIMARY KEY. Now, I haven't
explicitly defined and named an index for this table - but the primary key
defines one. How can I tell Postgres to CLUSTER on it?
Also: If I define an index on a PK, will Postgres make a second one, or
realize its redundnant?
Tha
UNION was better, but still 5 times as slow as either query done
individually.
set enable_seqscan=off didn't help at all - it was totally ignored
Is there anything else I can do?
On Sun, Jul 19, 2009 at 7:47 PM, Tom Lane wrote:
> Robert James writes:
> > Hi. I notice that when
Thanks, Chris. Is there a way to do this deterministically, or at least
programatically? I have code to create the tables and cluster them
automatically?
On Sun, Jul 19, 2009 at 8:21 PM, Chris wrote:
> Robert James wrote:
>
>> I would like to CLUSTER a table on its PRIMARY KEY. No
l 19, 2009 at 8:10 PM, Robert James wrote:
> UNION was better, but still 5 times as slow as either query done
> individually.
> set enable_seqscan=off didn't help at all - it was totally ignored
> Is there anything else I can do?
>
> On Sun, Jul 19, 2009 at 7:47 PM, Tom
PM, Scott Marlowe wrote:
> On Sun, Jul 19, 2009 at 6:10 PM, Robert James
> wrote:
> > UNION was better, but still 5 times as slow as either query done
> > individually.
> > set enable_seqscan=off didn't help at all - it was totally ignored
> > Is there anything e
Two small suggestions that might make it easier for newcomers to take
advantage of the wonderful database:
1. Googling Postgres docs produces links for all different versions. This
is because incoming links are to different versions. Besides being
confusing, it pushes the pages lower in Google, a
I have two queries which should be equivalent. The Planner plans them
differently, although they are both about the same time. Can someone
explain why?
select word from dict
where
word in
(select substr('moon', 0, generate_series(3,length('moon'
select * from dict
inner join (select substr
Hi. I'm confused about the behavior of LIKE under utf8 locale.
Accoding to the docs (
http://www.postgresql.org/docs/8.2/interactive/locale.html - excerpted
below), it seems that LIKE ignores locale and hence can't use indexes. Yet,
EXPLAIN clearly shows it using indexes.
The docs suggest a worka
On Mon, Jul 20, 2009 at 11:37 AM, Martijn van Oosterhout
wrote:
> I know it's not easy, but a nice option to me would be if the 8.1 docs
> page linked to the equivalent page in the other versions. That would
> avoid the need to manually edit the URL after a google search.
>
> Oh, and +10 for the "
PM, Tom Lane wrote:
> Robert James writes:
> > Hi. I'm confused about the behavior of LIKE under utf8 locale.
>
> UTF8 is not a locale, it's an encoding. If you're using C locale then
> LIKE can use indexes, regardless of the encoding. If you're u
Thanks - I don't show any locale:
rbt_development=> \l
List of databases
Name | Owner | Encoding
+-+--
rbt_development | rbt | UTF8
...
On Wed, Jul 22, 2009 at 6:45 PM, Andreas Wenk <
a.w...@netzmeister-st-pauli.de> wr
I'd like to SELECT INTO one table into another one. However, I'd like to do
two things that I don't know how to do using SELECT INTO:
1. Copy over the indexes and constraints of the first table into the second
2. Do SELECT INTO even if the second table already exists.
Is there anyway to do eith
Is there a transitive closure (or equivalent) operator in Postgres (or
extension)?
Anything like CONNECT BY?
Or any recommended way of querying hiearchial data?
I'm working on improving my background database theory, to aid in practice.
I've found learning relational algebra to be very helpful. One thing which
relational algebra doesn't cover is aggregate functions. Can anyone
recommend any papers or web pages which provide some good theoretical
backgro
I'm currently running Postgres 8.2 on Windows XP. I would like to use some
8.4 features, but I don't want to migrate my 8.2. Is there any way to run
both instances together? Are there any problems with that?
Alternatively, is the procedure to move from 8.2 to 8.4 without data or
function loss doc
Hi. I noticed that when clients (both psql and pgAdmin) disconnect or
cancel, queries are often still running on the server. A few questions:
1) Is there a way to reconnect and get the results?
2) Is there a way to tell postgres to automatically stop all queries when
the client who queried them d
e inclined towards
the second, but not if the first is better. One thing I'm not interested in
is polemics against SQL and lamentations on how ignorant all practitioners
are.
On Mon, Jul 27, 2009 at 2:45 PM, Jeff Davis wrote:
> On Sun, 2009-07-26 at 15:36 -0400, Robert James wrote:
>
Many wrote that the functional programming 'fold' is a good model for
relational aggregate functions. I have a few difficulties with this:
1. fold doesn't offer any type of GROUP BY, which is an essential component
of aggregation.
2. I don't believe fold can handle things like AVG() or STDDEV().
this would be a major boon to high volume servers, at
least in the usage patterns I've worked with.
On Mon, Jul 27, 2009 at 9:49 PM, Tom Lane wrote:
> Robert James writes:
> > Hi. I noticed that when clients (both psql and pgAdmin) disconnect or
> > cancel, queries are of
Thanks! "SQL and Relational Theory: How to Write Accurate SQL Code" looks
like the best pick of the bunch.
On Tue, Jul 28, 2009 at 10:08 AM, Michael Glaesemann
wrote:
>
> On Jul 27, 2009, at 21:05 , Robert James wrote:
>
> 2) Database in Depth: Relational Theory for Pr
On Tue, Jul 28, 2009 at 9:47 AM, Sam Mason wrote:
> On Tue, Jul 28, 2009 at 09:14:38AM -0400, Robert James wrote:
> > Many wrote that the functional programming 'fold' is a good model for
> > relational aggregate functions. I have a few difficulties with this:
> >
I have a table of event_id, event_time. Many times, several events
happen in a row. I'd like a query which replaces all of those events
with a single record, showing the count.
Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
D,1; A,2; D,2; B,1; C,2
How can I do that?
--
On 10/22/13, Rémi Cura wrote:
> But it is immensely easier and sometimes mandatory to use instead
> a plpgsql function using cursor (or cursors).
>
> It would be something like that in plpgsql :
>
> cursor on table of letter ordered
> accum = 0;
> loop on rows of table ordered
>
> if letter = prev
Wow, this is an excellent discussion - and I must admit, a bit beyond
my abilities. Is there a consensus as to the best approach to adopt?
Is Elliot's the best?
On 10/22/13, Rémi Cura wrote:
> OK,
> just out of pure curiosity,
> is it always the case or is it due to this particular aggregate?
>
On 10/22/13, Elliot wrote:
> It looks like you already found a solution, but here's one with a CTE. I
> cobbled this together from an older query I had for doing something
> similar, for which I unfortunately lost the original source of this
> approach. Also, this implies that there is something t
Ingenious!
I actually think, however, there was a subtle bug in, though I see you fixed it.
The line:
- row_number() over () as d
needs to be:
- row_number() over (order by i asc) as d
I discovered this when working your code into my application. I got
very, very wei
I have a table of (timed) events, and I'm interested in marking events
whose properties have changed from the previous event.
I believe this can be done with window functions, but I'm not sure
how. What window function can give me a field from the _previous_
row?
(To elaborate, I'm interested in
I'm just discovering the power of window functions. Is there any
mathematical formalism for them, similar to relational algebra?
It would seem to me that window functions aren't expressable in pure
relational algebra, but that a well defined extension to it would be
possible to express them; at
On 10/25/13, Robert James wrote:
> I have a table of (timed) events, and I'm interested in marking events
> whose properties have changed from the previous event.
>
> I believe this can be done with window functions, but I'm not sure
> how. What window function can
On 10/25/13, Tom Lane wrote:
> Robert James writes:
>>> (To elaborate, I'm interested in:
>>> * Finding field x of the _previous_ row
>>> * Finding field x of the _next_ row
>>> * Finding field x of the _previous_ row that meets a certain criteria
&g
I have a table (x,y,z) - I'd like to take the rows with unique x
values - but, when more than one row have the same x value, I want the
one with the minimal z value.
How can I do that? I can imagine doing it with window functions, but
also that regular SQL should be able to do it too.
--
Sent v
I'm using Postgres for data analysis (interactive and batch). I need
to focus the analysis on a subset of one table, and, for both
performance and simplicity, have a function which loads that subset
into another table (DELETE FROM another_table; INSERT INTO
another_table SELECT ...).
Oddly enough
On 10/27/13, Thomas Kellerer wrote:
> Robert James wrote on 27.10.2013 20:47:
>> I'm using Postgres for data analysis (interactive and batch). I need
>> to focus the analysis on a subset of one table, and, for both
>> performance and simplicity, have a function which
On 10/27/13, Adrian Klaver wrote:
> On 10/27/2013 02:23 PM, Robert James wrote:
>> On 10/27/13, Thomas Kellerer wrote:
>>> Robert James wrote on 27.10.2013 20:47:
>>>> I'm using Postgres for data analysis (interactive and batch). I need
>>>> to f
o set $1 and $2. I get the same
behavior when I try just its SQL, no function.
On 10/27/13, Adrian Klaver wrote:
> On 10/27/2013 02:48 PM, Robert James wrote:
>> On 10/27/13, Adrian Klaver wrote:
>
>
>>>> Is there another problem here? Perhaps something to do with
Is there any way to do a pg_dump (or equivalent) of only part of a
table? Say I want to send data to someone for only part of the table
(expressable with a WHERE clause).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgr
I'm having trouble with setof returning functions
Let's say I have function x() returning setof integers. I want to do
SELECT x(), but only keep the values which meet a criteria. Something
like: SELECT x() as xval WHERE xval = 10.
How can I do that?
In general, is there a way to "unroll" a set
I have a table with a little active data and a lot of historical data.
I'd like to be able to access the active data very quickly - quicker
than an index. Here are the details:
1. Table has about 1 million records
2. Has a column active_date - on a given date, only about 1% are
active. active_d
I have some code which creates a function in Postgres, taken from
http://wiki.postgresql.org/wiki/Array_agg .
DROP AGGREGATE IF EXISTS array_agg(anyelement);
CREATE AGGREGATE array_agg(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND='{}'
);
The function was added in 8.4, and so the cod
act version number
programatically (ie not just a long string)
On 9/24/12, Igor Neyman wrote:
>> -Original Message-----
>> From: Robert James [mailto:srobertja...@gmail.com]
>> Sent: Monday, September 24, 2012 9:33 AM
>> To: Postgres General
>> Subject: Running CREATE only on
I have Postgres running on a SSD. The data is now almost 50GB, which
is filling up the drive.
How can I move some of the data to my HDD?
My priorities are, in this order:
1. Reliable - I don't want anything that will corrupt the data
2. Easy - I have a few dozen databases, I don't want to do too
In Postgres 8.3, how can I move a database to a different TABLESPACE?
My goal is to move rarely used databases off of the SSD and onto the
HDD.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-gen
Sergey - That's an interesting option, but I'm not sure how to use it
as an aggregate. Could you give an example?
On 12/20/12, Sergey Konoplev wrote:
> On Wed, Dec 19, 2012 at 5:28 PM, Robert James
> wrote:
>> And even better:
>> An aggregate which will return the
I see. What if I need to do this along with an Aggregate Query. Eg
something like:
SELECT x,y,z, MAX(a), MAX(b), DONT_CARE_AS_LONG_AS_NOT_NULL(c),
DONT_CAR_AS_LONG_AS_P_IS_TRUE(d,p)
...
GROUP BY x,y,z
On 12/20/12, Richard Broersma wrote:
> On Thu, Dec 20, 2012 at 5:45 AM, Robert Ja
I have a query
SELECT grouping_field, MIN(field_a), MIN(field_b)
FROM ...
GROUP BY grouping_field
But, instead of picking the MIN field_a and MIN field_b, I'd like to
pick field_a and field_b from the first record, according to an order
I'll specify. In pseudo-SQL, it would be something li
with a join on the
grouping field? Or is there a more direct way?
On 12/31/12, Jack Christensen wrote:
> On 12/31/2012 8:33 AM, Robert James wrote:
>> I have a query
>>
>>SELECT grouping_field, MIN(field_a), MIN(field_b)
>>FROM ...
>>GROUP BY gro
On 12/31/12, François Beausoleil wrote:
>
> Le 2012-12-31 à 15:38, Robert James a écrit :
>
>> DISTINCT is a very simple solution!
>> But I have one problem: In addition to the FIRST fields, I also do
>> want some aggregate functions. More accurately, it would be:
Hi. I'm setting up a new workstation (Win7 64bit Quad Core 4GB) with
Postgres, for development work, and trying to pick which version I
should install. Most of the time, Postgres is dormant - I'm not using
it all - but when I do use it, the load can be high, and I want
maximum performance.
Is th
When trying to INSERT on Postgres (9.1) to a bytea column, via E''
escaped strings, I get the strings rejected because they're not UTF8.
I'm confused, since bytea isn't for strings but for binary. What
causes this? How do I fix this? (I know that escaped strings is not
the best way for binary data
I've been using a query on Postgres 8.4 with a negative OFFSET, which
works fine:
SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name
ASC LIMIT 15 OFFSET -15
When I run the same query on Postgres 9.1, I get an error:
ERROR: OFFSET must not be negative
Question:
1. Was this ch
On 11/7/11, Merlin Moncure wrote:
> On Mon, Nov 7, 2011 at 3:47 PM, Robert James wrote:
>> I've been using a query on Postgres 8.4 with a negative OFFSET, which
>> works fine:
>>
>> SELECT DISTINCT s.* FROM s WHERE ... ORDER BY s.bday ASC, s.name
>> ASC L
I have a query returning:
name | product_id
Bob | 1
Bob | 2
Charles | 1
Charles | 4
To make it compatible with a legacy app, I need to convert it to this form:
name | product_ids
Bob | "1,2"
Charles | "1,4"
(Before you jump "That's not normal!" - I know. I didn't write the
app. I just need to
I have a very long query. Due to the planner and good indexing, it
runs quite fast. But it's so long, it's quite hard to follow.
I'm trying to break it up into pieces, but am running up against
limits of SQL. Can you help me with any of these problems?
1.
SELECT
AS A,
AS C,
AS D
...
I'd li
I see Postgres (I'm using 8.3) has bitwise aggregate functions
(bit_or), but doesn't seem to have logical aggregate functions.
How do I do the equivalent of an ANY() or ALL() in PG Aggregate SQL?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscr
To match the heavily denormalized format of a legacy app, I need to
take a query which gives this:
name | product | rent | own
Bob | Car | true | false
Bob | Car | false | true
Bob | Bike | false | true
Bob | Truck | true | true
and denormalize it into this:
name | rented_products | owned_produc
1 - 100 of 123 matches
Mail list logo
