Re: [SQL] two queryes in a single tablescan

esults in a faster way. PostgreSQL 8.3 contains great improvements in this area, you can simply start the selects from concurrent connections, and the backend will synchronize the scans. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Softwar

Re: [SQL] null values in non-nullable column

s it to fix this (e. G. by disallowing NULL-generating JOINs on NOT NULL domains?) Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.or

Re: [SQL] Autovaccum

the total number of transactions since the last vacuum, and decides whether to VACUUM and/or ANALYZE. The thresholds for those settings can be configured per table, with the defaults in the postgresql.conf. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | S

Re: [SQL] join and sort on 'best match'

Hi, Ashish, "Ashish Ahlawat" <[EMAIL PROTECTED]> wrote: > hi pls tell me > > if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need > join ??* Please try to write in English, so we can understand and answer your questions. Regards, Markus

Re: [SQL] pg_xlog on separate drive

not reply the WAL log, those half-written pages will not be repaired, and your data may be inconsistent at a very low sematic level (duplicate rows, missing rows, broken rows, backend crashes etc.) with no way to repair. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International

Re: [SQL] UNICODE and PL/PGSQL

ons, that's a prerequirement for them to fulfil their job. Time to search a new employer, honest! > I get the error in a plpgsql function Ah, I just saw that the "decode" function returns bytea, not text. I'll need a further look into this... Sorry, Markus -- Markus Schaber | Logic

Re: [SQL] UNICODE and PL/PGSQL

Strange. Does it happen in a plpgsql function, or when issuing the select as PREPARE statement via pgadmin? Which PostgreSQL version are you running? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against

Re: [SQL] UNICODE and PL/PGSQL

(i);" Maybe you can change it to (ASCII version): "charset := charset || chr(i);" or (256-bit version): "charset := charset || decode(to_hex(i),'hex'); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Develo

Re: [SQL] PgAgent [where is it?]

HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Recursive pl/pgsql function ...

is a nice example where the recursive design approach is not necessary, and even hurts. A simple while-Loop will do the same, more efficient, and without any stack problems. Btw, it may even be possible to use an explicit formula to calculate this problem, but date and time calculations are always

Re: [SQL] Foreign key reference counting strategy?

e still are records with the same key (IF EXISTS()), and deletes the referenced row otherwise. It won't hurt to have an index on the referencing column, for speed reasons. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development

Re: [SQL] deleting rows in specific order

afraid that this is the only solution if you can't simplify the trigger by other means. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] deleting rows in specific order

th the ordered delete? Maybe there's a better way to solve your underlying problem. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org ww

Re: [SQL] optimal insert

saction, and using COPY instead will give you better benefits. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(

Re: [SQL] Assigning a timestamp without timezone to a timestamp

Hi, Christopher, christopher wood wrote: > what is AFAICT ? "As Far As I Can Tell". It's explained in the "Jargon File": http://www.catb.org/jargon/html/A/AFAIK.html HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. |

Re: [SQL] How to overwrite a table [only data]?

onstraints intact, keeping only those rows that have NULL in the foreign key. I think that's not exactly what you want. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www

Re: [SQL] Update 3 columns w/ 1 function calc 3 values?

hear number. It might work to mark the function as "stable", and have it return a compound type in a subselect with an "AS foo" aliasname. Then you should be able to address the compound's elements via foo.x, foo.y and foo.z. Or use PostGIS, it's capable of proc

Re: [SQL] Assigning a timestamp without timezone to a timestamp

portive community, and commercial supporters and niche-derivates. As long as you want to keep learning, and don't hesitate getting involved, PostgreSQL will offer you a satisfying experience. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf.

Re: [SQL] i have table

eate all missing indices, foreing key constraints etc. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcas

Re: [SQL] Assigning a timestamp without timezone to a timestamp

x27;re the brightest people on the planet. :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--

Re: [SQL] Query regarding Writing a report using the file sysstem

ble for registered MS developers knows the answer for most problems, I've been told by our Windows Gurus here. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii

Re: [SQL] unique rows

Hi, TJ, TJ O'Donnell wrote: > So, is there a way (a constraint, a check?) that will simply > REJECT a duplicate when I insert the raw data directly into x > rather than FAILING on an error as it does > with the unique constraint above? Failing on an error is exactly the way PostgreSQL (and the S

Re: [SQL] Make Trigger run after completion of ENTIRE transaction

the trigger has to do some integrity checking work? Could you reformulate it as foreign key or check constraints? (you may even call a complex plpgsql function from the check constraint). Most constraint checks can be deferred to the end of the transaction, see the docs for more details. HTH, Marku

Re: [SQL] Aggregates with internal state type?

Hi, Tom, Markus Schaber wrote: >>> This lead me to the question whether it is possible to use "internal" as >>> state type for an Aggregate whose functions are implemented in C. >> No, because the system has no idea what the representation of an >>

Re: [SQL] How to delete multiple records

try with a matching research date? Then go along something like DELETE FROM product WHERE EXISTS ( SELECT * from product innr WHERE research_date < '2006-01-01' AND product_id = innr.product_id AND item_id = innr.item_id ); HTH, Markus -- Markus Schabe

Re: [SQL] Aggregates with internal state type?

s. But I'm afraid that this gets more difficult. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcas

Re: [SQL] Aggregates with internal state type?

Hi, Tom, Tom Lane wrote: > Markus Schaber <[EMAIL PROTECTED]> writes: >> This lead me to the question whether it is possible to use "internal" as >> state type for an Aggregate whose functions are implemented in C. > > No, because the system has no idea what

[SQL] Aggregates with internal state type?

n C. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] on connect/on disconnect

uses this frequently. See the docs for more details. http://www.postgresql.org/docs/8.1/interactive/sql-set-session-authorization.html HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU!

Re: [SQL] on connect/on disconnect

the whole thing. > Purely session/connection-based temporary tables would also do what > I need, but temporary tables don't seem to be able to work that way. What's the exact problem with them? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | So

Re: [SQL] help with pagila

es implemented) a simple "first()" aggregate to put around those other columns. Some of those cases could be fixed by creative use of "DISTINCT ON", but sometimes lead to suboptimal query plans (due to the useless sorting). Markus -- Markus Schaber | Logical Tracking&Tracing Int

Re: [SQL] UTF8 Vs Latin9 and valid encoding.

; to show the encoding the server thinks the client uses. Try one of: SET client_encoding TO LATIN9; -- Tell server: client uses latin9 \encoding LATIN9; -- Set clients encoding You can also set UTF-8 here, the server will convert between the client and database encoding (but it needs to know).

Re: [SQL] NULL becomes default

that at the > database level? Did you try a "before insert" trigger that checks new_id for null values, and replaces it with nextval()? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents

Re: [SQL] Undo an update

Hi, Kis, Kis János Tamás wrote: > So, if you send every insert, update, delete command to a > logger-table, then you can to undo anything. But this is just re-inventing the wheel, we already have Point-in-Time recovery. Or do I miss something? Markus -- Markus Schaber | Logical Tr

Re: [SQL] Undo an update

n upstream source, like phonebooks, street network data, or ISBN/ISSN/EAN databases, etc. -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Alternative to serial primary key

Hi, Andrew, Andrew Sullivan wrote: > On Thu, Jul 06, 2006 at 01:10:55PM +0200, Markus Schaber wrote: >> Then you'll have to use the re-check approach, like: > > That sort of undermines the value of the calculated primary key, > though, doesn't it? He'd need t

Re: [SQL] Alternative to serial primary key

Hi, David, David Clarke wrote: > On 7/6/06, Markus Schaber <[EMAIL PROTECTED]> wrote: >> This is a good idea if you want to have taller indices, but you still >> need to re-check the "real" key due to hash collisions. > > I am aware there are collisions with

Re: [SQL] Alternative to serial primary key

you've plenty of time to spend, you could also bring the hash index type back to life, which is currently deprecated according to the PostgreSQL docs... HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight again

Re: [SQL] Views and query planner

the planning performance may vary. (This effect can be mildered by careful use of prepared statements.) HTH, Markus [1] Queries reaching some complexity level may lead to different plans, due to optimizer limits, the gequo kicking in, or other effects. -- Markus Schaber | Logical Tracking&am

Re: [SQL] Data Entry and Query forms

e use PostgreSQL as backend for "real" applications.) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---

Re: [SQL] Question on UNION

Hi, Mark, [EMAIL PROTECTED] schrieb: > I ran into something I wasn't expecting while developing a new > application. I have two similar tables that are occasionally unioned > pulling only about 3 fields from each. During my testing phase I > noticed that the union statement was returning what a

Re: [SQL] Does PG have a database

Hi, Mark, Mark Fenbers schrieb: > Bottom line: What trick can I use to determine whether a box has a > living, breathing Pg database if I don't know the DB owner? If all your Postmasters run on the default port, use nmap on the port, so you see that postmasters are running there. When they run,

Re: [SQL] usernames of a group from SQL

Hi, Kijato, kijato schrieb: > CREATE FUNCTION kodok.group_users(group_name "varchar") > RETURNS SETOF "varchar" AS > $BODY$ > SELECT pg_user.usename > FROM pg_user, pg_group > WHERE usesysid = any (pg_group.grolist) AND pg_group.groname=$1; > $BODY$ > LANGUAGE 'sql' VOLATILE; The pg_user.usernam

Re: [SQL] Constraint question

I guess your solution is cleaner. Yes, it works, but it needs more disk space (it has to index all rows, and it needs to save both columns), and thus is slower, too. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight ag

Re: [SQL] Constraint question

o ON onp_crm_businessfield_company(company_id) WHERE is_prefferred; HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end o

Re: [SQL] [JDBC] error : could not access status of transaction

/00FC": No such file or directory This sounds like a broken PostgreSQL data directory, not a jdbc problem. You should check whether you can access the table via psql or other non-jdbc tools, and if not, ask on PostgreSQL SQL List or pgsql-general. Markus -- Markus Schaber | Logical Track

Re: [SQL] Help with a seq scan on multi-million row table

th and help narrow this down, please > let me know. Did you try to set higher statistics targets for this columns? For experimenting, I'd try to set it to 100 or even higher, then ANALYZE the table, and then retest the query. HTH, Marks -- Markus Schaber | Logical Tracking&

Re: [SQL] Multi-column index not used, new flipped column index is

> http://www.postgresql.org/docs/whatsnew . Bitmap Index Scans can be your solution, but AFAIK they were invented in 8.1. For bitmap index scans, you have one index on fkColumnOne and one on fkColumnTwo, and the query planner knows to combine them when both columns are given in a WHERE

Re: [SQL] Returning String as Integer

Hi, Ben, Ben K. schrieb: > I tried int8(id) but java didn't take it as Integer. (It took int8 as > something like Long.) Yes, and that's good, as PostgreSQL int8 and java long actually are the same datatype (64-bit signed two's-complement). PostgreSQL int4 and Java int are the same (32-bit), as

Re: [SQL] Most efficient way to hard-sort records

Hi, Miroslav, Miroslav Šulc schrieb: > I have a table with cca 100,000 records. I need to hard-sort the records > by a key from second table. By hard-sorting I mean storing position of > each row in the main table. Here is what my tables look like: > > main_table: id, name, position > key_table:

Re: [SQL] How can I read display message from a C function

an I see the messages? Hmm, would statement logging help you? If not, you could use elog(NOTICE, "message with time"); HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software p

Re: [SQL] selects on differing subsets of a query

ink that the moderators are aided by some automatic spam classification techque. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: [SQL] selects on differing subsets of a query

t; policy, but I'm getting of topic. > Thank you for your help, I will study it when I get development time > on the database. Tell us about your findings here. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fig

Re: [SQL] selects on differing subsets of a query

can. SELECT count(*),count(nullif(diet_pref='veg', f)) FROM table WHERE gender='m' Should also give you both counts, this time in different columns, also avoiding the duplicated table scan. It relies on the fact that count(something) is only called if something is not null,

Re: [SQL] Compute hash of a table?

Hi, Peter, Peter Manchev wrote: > Is it possible to get the last time(stamp) when the content of a given > table in pgsql has changed??? My solution would be to add a trigger to the table which updates a timestamp in a second table on every insert/update/delete. HTH, Markus --

Re: [SQL] i am getting error when i am using copy command

m. This can have two causes, IMHO: You either have a _totally broken_ un*x[1] installation, or you run on Windows or another platform that has different file naming conventions. HTH, Markus [1] un*x is a shortcut for all unixoid operating systems, including BSD, Linux and even MacOS X. -- Ma

Re: [SQL] Slightly confused error message

at information (about the acutally offending subquery) to the message. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(e

[SQL] Slightly confused error message

be used in an aggregate function As the query looks, streets.coverage_area is actually used in the GROUP BY. I know how to fix the query, but I wonder whether it is worth the effort to try improving the error message. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing Internationa

Re: [SQL] Migrating a Database to a new tablespace

file operations from transactions that never completed. It will slow those operations down, but such file operations are both seldom and relatively expensive on most filesystems. And it does not help in case of fsync=off, but those admins already take worse risks than wasting disk space. Thank

Re: [SQL] Migrating a Database to a new tablespace

lf-automated. I thought of a tool that enumarates suspective files. Then admins can look at the mtime/atime, or move them away and try what happens, or even pipe the output to "xargs rm -rf" if they want. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International A

Re: [SQL] Migrating a Database to a new tablespace

eftovers are likely to be caused by hard kills and backend crashes, so I would not go into deeper analysis, but maybe the finding and possibly removing of such leftovers should be half-automated to assist server admins. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl

Re: [SQL] Migrating a Database to a new tablespace

5 301509 301877 302048 302216 318418397397 2616 301511 301879 302050 302218 318420397417 2617 301512 301881 302054 30 318553397419 2618 301714 301885 302056 302224 318554397421 2619 301716 301887 302058 302229 318718397692 Thanks, Markus -- Mar

Re: [SQL] Migrating a Database to a new tablespace

n I assume it is safe to empty the tablespace directory manually, and then drop the tablespace? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.noso

Re: [SQL] Migrating a Database to a new tablespace

machine. Thanks for your patience, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)---

Re: [SQL] Migrating a Database to a new tablespace

Hello, Markus Schaber wrote: > What is the easiest way to migrate a complete database from one > tablespace to another? > > ALTER DATABASE only allows to set the default tablespace, but not > migrating the existing database. Is there really no way to do this? Thanks, Mar

Re: [SQL] Migrating a Database to a new tablespace

Hello, Markus Schaber wrote: >>What is the easiest way to migrate a complete database from one >>tablespace to another? >> >>ALTER DATABASE only allows to set the default tablespace, but not >>migrating the existing database. > > Is there really no way to

Re: [SQL] Field length ??

hey are handled as variable length datatypes internally (even if given a limit). See http://www.postgresql.org/docs/8.1/static/datatype-character.html HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software paten

Re: [SQL] Field length ??

ld. http://www.postgresql.org/docs/8.1/interactive/xtypes.html might be a start if you want to read more on this topic. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwa

[SQL] Migrating a Database to a new tablespace

Hello, What is the easiest way to migrate a complete database from one tablespace to another? ALTER DATABASE only allows to set the default tablespace, but not migrating the existing database. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl.

Re: [SQL] [JDBC] Thoughts on a Isolation/Security problem.

t application into a multi-deployment application with some limited cooperation / data sharing. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.noso

Re: [SQL] [JDBC] Thoughts on a Isolation/Security problem.

nd such shield the application from directly accessing the global data. We even need to mere local and global data this way in some cases. It is ugly, but it works fine and is manageable. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Devel

Re: [SQL] Joins involving functions

Do you really want the cross join between bar.* and f_foo(bar.b)? If yes, use SELECT x.b, y.b FROM bar x CROSS JOIN bar y; If not, you maybe want SELECT b, f_foo(b) FROM bar; Or it may be you want something like: SELECT * from bar CROSS JOIN (SELECT f_foo(bar.b) from bar) as foo ; HTH, Markus

Re: [SQL] have you feel anything when you read this ?

an extension for lipq that lets you select binary and textual representation column-wise (which might need a protocol extension, I don't have the specs in mind). But it absolutely does not make any sense to break the whole concept of text representations by making it bi

Re: [SQL] have you feel anything when you read this ?

nary representation for everything if you don't want to display the data to the user directly. One could speculate that the textual representation is just a little help for "generic" tools like pg_dump, pgadmin or psql that display data to the user without having any knowl

Re: [SQL] have you feel anything when you read this ?

d value. >>why not to define your own unique more_sofisticated representation ? >>(as for bytea is defined.) > AFAICS, there is one, the binary format for integer. Exactly. AFAICS, all built-in data types have both a text and binary representation, as well as most extension types

Re: [SQL] SELECT composite type

"immutable" or, at least, "stable". Then the qery planner should flatten them to be called only once. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www

Re: [SQL] Ugly group by problem

emporary table to collect the sets of link ids. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend

[SQL] Ugly group by problem

JAVA application, but I'm looking for a way to express this via sql / plpgsql to ease deployment. I could imagine some ugly code using ARRAY (not tried yet), but how would you pack this problem? It seems that I'm just stuck in my thoughts and miss the beauty way to solve it. Thanks, Ma

Re: [SQL] Update question

for your help. It should go like: UPDATE cost SET edge_id = (SELECT cost FROM cost innr WHERE innr.edge_id = edge_id AND innr.cost is not null) WHERE cost is null; HTH, Marku -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Figh

Re: [SQL] Problem using set-returning functions

that the SRF special semantics are ugly, and would vote for adjacent tables to be implemented as replacement. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepat

Re: [SQL] pgsql2shp - Could not create dbf file

s on the directory to create those three files? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)

Re: [SQL] [postgis-users] Problem using set-returning functions

gt; ' LANGUAGE 'sql' IMMUTABLE STRICT; Yes, it seems to work. This will solve the OPs case. But it is still a good question whether it is possible to accomplish this using plpgsql. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf.

Re: [SQL] Permission to Select

Hi, Eugene, Eugene E. wrote: > This means that some privileges are NOT INDEPENDENT. No, it means that the UPDATE operation needs both UPDATE and SELECT privileges. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight

Re: [SQL] pgsql aggregate: conditional max

ories (for > one aid) with the same max weight. Yet, I should be able to remove the > duplicates somehow...:) Try SELECT DISTINCT aid, cat FROM tablename AS t JOIN (SELECT aid, max(weight) AS weight FROM tablename GROUP BY aid) AS s USING (aid, weight); HTH, Markus -- Markus Schabe

Re: [SQL] Problem using set-returning functions

th generate_series() and dump()? Confused, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)---

Re: [SQL] functions in WHERE clause

column_b); > Is it possible to do what I'm trying to do? I've written a few > simple sql and pl/pgsql functions over the years, but I'm no expert. Yes, it is. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software De

Re: [SQL] [postgis-users] Problem using set-returning functions

g functions are expected to work as long as "adjacent tables" are not implemented. http://www.mail-archive.com/pgsql-sql@postgresql.org/msg20545.html Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight again

[SQL] Problem using set-returning functions

rs +--+--- foo| integer | bar| geometry | navteq=# select foo,asText(bar) from test; foo | astext -+- 42 | MULTIPOINT(1 2,3 4,5 6) 23 | MULTIPOINT(7 8) (2 rows) I'm shure its a small detail I've blindly ignored, but

Re: [SQL] dump with lo

7.4 server is one of those problematic cases. Install the postgresql-client-8.1 debian package and use /usr/lib/postgresql/8.1/bin/psql directly. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against

Re: [SQL] Set generating functions and subqueries

idding, I just wanted to point out the different behaviour between equal-length and inequal-length sequences. Thanks, markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! w

Re: [SQL] dbLink Query

nction definitions. On my debian machine, it is under: /usr/share/postgresql/7.4/contrib/dblink.sql /usr/share/postgresql/8.1/contrib/dblink.sql HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software pat

Re: [SQL] pg reserved words

(foo int); ^ postgres=# create table "freeze" (foo int); CREATE TABLE postgres=# select * from "freeze"; foo - (0 rows) postgres=# DROP TABLE "freeze" ; DROP TABLE HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International

Re: [SQL]

Hi, Klay, Klay Martens wrote: > I am really battling to figure out how to do the same in a postgres > function. http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS could be helpful. HTH Markus -- Markus Schaber | Logical Tracking&a

[SQL] Set generating functions and subqueries

s=# select (select generate_series(1,2)),generate_series(3,4),''; ERROR: more than one row returned by a subquery used as an expression Have fun, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fi

Re: [HACKERS] [SQL] Interval subtracting

day' can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1 month 30 days', depending on the timestamp we apply the interval. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS

[SQL] CREATE TABLE AS and tablespaces

it is even possible to further unify CREATE TABLE and CREATE TABLE AS. Thanks, Schabi -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: [SQL] Feature, my misconception or bug??

T '255.255.255.255/25'::cidr; > cidr > > 255.255.255.255/25 > (1 row) This one is refused in 8.1, so I guess that's a fixed bug. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS F

Re: [SQL] Feature, my misconception or bug??

d by the fact that /24 complains about bits 25-31 beeing set, while /25 does not complain aobut bigs 26-31 beeing set. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nos

Re: [SQL] passing array(java) to postgre sql function

After this i am passing this.getArray() values to database. As a first guess, have a look at java.sql.Array interface. And maybe the people on the pgsql-jdbc@postgresql.org mailing list know better. :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. In

Re: [SQL] group by complications

Hi, Mark, Mark Fenbers schrieb: > Wow! I didn't know you could have a (select ...) as a replacement for a > 'from' table/query. Your SQL worked as-is, except I had to add a 'limit > 1' to the first subquery. > > Thanks! I would have never figured that out on my own! SQL has more power than mo

  1   2   >