Re: [SQL] Difference in columns

ECT a.ts, b.size - a.size AS diff FROM x_temp a, x_temp b WHERE b.id = a.id + 1 OR (b.id = (SELECT min(id) FROM x_temp) AND a.id = b.id); -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Difference in columns

to constrain the range of values processed that's not too tricky - either feed the function a refcursor for a query result set to iterate over, or pass it parameters to constrain the query with a WHERE clause. The former is more flexible, the latter is easier to use. -- Craig Ringer -- Sent vi

Re: [SQL] merge timestamps to intervals

approach this problem. About 12 hours ago there was a conversation in pgsql-sql with subject "Difference in columns" that included examples that can be trivially adapted to your problem. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to

Re: [SQL] Substract queries

what you really want is a query that returns all records in the first query EXCEPT those returned by the second query, then see: http://www.postgresql.org/docs/8.3/static/queries-union.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your sub

Re: [SQL] Extremely Low performance with ODBC

query it won't know to use the index, especially if there are also values for `somethingelse' that occur a lot. Try running your query in psql/pgadmin using PREPARE and EXECUTE and see if you get the same result. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@post

Re: [SQL] Extremely Low performance with ODBC

ke the ones to enable/disable seqscan, nested loop, etc. That might really help with tracking down issues that appear to only happen with queries run by an app, or though a particular interface. Can it be done? Or is the DB server not capable of generating explain output (say to a log) and als

Re: [SQL] design resource

esign patterns for common database problems - I know they're out there, and I'd be interested in specific references myself if anyone knows any ISBNs. I wouldn't be at all surprised to see books on addressing alone. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] design resource

almost as much as I loathe printers and printer drivers. - Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Trouble with exception

rence when the constraint check was fairly expensive. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Update and trigger

an inline procedure for the execute No, at present you must create a function that returns TRIGGER and then use that as the target to execute. At least as far as I know. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http:

Re: [SQL] How to find space occupied by postgres on harddisk

's what you meant). That'll tell you for sure. It's not like a 5GB dump will take all that long to load. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] PERSISTANT PREPARE (another point of view)

n, I don't know much about Pg's innards, so that doesn't mean much. Tom Lane responded to that post to point out some of the complexities: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00868.php -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@pos

Re: [SQL] PERSISTANT PREPARE (another point of view)

ECUTE or SELECT function(params). It's pretty clear that there are some tricky aspects though, what with schema search paths, role priveleges, etc. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.or

Re: [SQL] Function returning setof taking parameters from another table

t run this one to see what it does. -- SELECT x, gsp(x) FROM generate_series(1,10) as x; -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] more than 1000 connections

ning into connection count limits, but I shouldn't have to guess that. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] more than 1000 connections

ink we're psychic" email template on the wiki soon, as the number of people who don't provide the basic information needed to usefully answer most questions seems to be continually increasing.] > I need configure a pool of connection or something. Yes, I'd say so. What is preven

Re: [SQL] pg_restore in java connection (auto_commit = false)

whether there's a better way to achieve what you're trying to do in the first place that doesn't involve all this per-table dump and restore hassle at all. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Multi-line text fields

any trailing \r in case of DOS line endings. A little C function that copied the input only up to the first newline would instead probably be the fastest. It'd also let you easily strip the trailing \r if any was present. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Special grouping on sorted data.

like postgresql to send it that way. If the next n is different create a new row. I suspect that Crystal Reports may be pulling the whole data set from PostgreSQL then doing its processing client-side. Try turning on query logging in the server and running your report. See what SQL Crystal Reports

Re: [SQL] EXISTS

dmin/maintenance scripts) but the current approach does work fine. It also helps that you can often achieve the required logic with plain, standard SQL. The CASE statement is particularly useful: SELECT CASE WHEN col1 = 'mystring' THEN [expression or function call] END FROM T

Re: [SQL] [PERFORM] Can we activate WAL runtime?

h. Simon's given no argument at all why it would be safe to flip it on-the-fly." Again, though, that may be new in 8.3, I really would wait for some confirmation. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www

Re: [SQL] UPDATE and DELEte with a lot of register is to slow...

? Is the problem that the UPDATE or DELETE takes a long time, or that it slows down other queries? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Sequence and nextval problem

om/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html for sample operator/cast definitions. There are some notes in the top of the "passthrough.txt" module I attached that explain the ODBC driver options you'll want. -- Craig Ringer Private Sub Form_BeforeInsert(Can

Re: [SQL] Sequence and nextval problem

Earlier, Craig Ringer wrote: for sample operator/cast definitions. There are some notes in the top of the "passthrough.txt" module I attached that explain the ODBC driver options you'll want. Also, note that in the message I just posted the function DSN() must be adapted

Re: [SQL] How much the max image size can be inserted into Postgresql

then the appropriate section of the MANUAL is: http://www.postgresql.org/docs/current/static/largeobjects.html If those assumptions are invalid or insufficient, perhaps you could provide a more complete description of how you're doing things? -- Craig Ringer -- Sent via pgsql-

Re: [SQL] dynamic OUT parameters?

s information to the application, so it can call the first function to get the information required to correctly call your dynamic reporting function. > 2) Can i make a special type "on_the_fly" and returning setof "that_type"? You're better off using SETOF RECORD

Re: [SQL] dynamic OUT parameters?

, you don't have to do any special work to call the function, and you can (with most DB access APIs) FETCH records from the cursor rather conveniently. See: http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgre

Re: [SQL] dynamic OUT parameters?

: http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#AEN40465 Personally, I find it difficult to imagine what could be wrong with that. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] = or LIKE ?

Jesper Krogh wrote: So I cannot rely on the like operator to behave correct if I'd like to compare strings with backslashes (e.g. filepaths from MS Windows filesystems)? test=# SELECT E'\\' LIKE E'\\' ESCAPE ''; ?column? ------ t (1 row) -- Craig Ri

Re: [SQL] Creating a function with single quotes

as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Performance problem with row count trigger

your `dataset' table as narrow as possible. If the catalog_id, t_begin, t_end, ctime and mtime fields do not change almost as often as the assoc_count field, split them into a separate table with a foreign key referencing dataset_id, rather than storing them directly in the dataset table. -- C

Re: [SQL] using a list to query

kid IN ( SELECT x FROM regexp_split_to_table(myTextfield, ',') AS x ); ... but you should consider storing your list in an array instead, or using a more conventional child table with a (pkid, refid) pair list. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@po

[SQL] select regexp_matches('a a a', '([a-z]) a','g');

leading `a' characters. What you need is a zero-width lookahead assertion, available in Perl-style extended regular expressions. Handily, recent PostgreSQL versions support these, so you can write: test=> select regexp_matches( 'a a a', '([a-z]) (?=a)', 'g'); r

Re: [SQL] backup and restore

ed mail clients will thread your message under a now-unrelated thread. Compose a new message instead." -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] backup and restore

Craig Ringer wrote: ... something kinda rude, in retrospect. Sorry. Unpleasantness is going around in my immediate environment, and I'm apparently prickly and grumpy. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:

<    1   2