[SQL] Select Instead on a table

ase. Thanks, Markus Schaber Footnotes: ¹ The latter was rather surprising to me, may be a defect in the mailing list archive search? -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED]

Re: [SQL] Select Instead on a table

Hi, Tom, On Thu, 28 Oct 2004 10:22:47 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > > Is it possible to create a ON SELECT DO INSTEAD rule on a table? > > Not unless you want it to become a view. That's what I suspected, after scanning the docs. Thanks, Markus --

Re: [SQL] extra info - curious delay on view/where

ect depends on the count of rows the query returns compared to the total rows in the table. And it would be helpful to know the typical queries (especially the rows in the WHERE clauses) to give additional hints on creating indices. A matching index also potentially speeds up ORDER BY queries. HTH,

[SQL] Update instead rules on Views

n a transformed query equivalent to: UPDATE realdata SET data='nono' WHERE index=1; which works as expected. Can anyone enlighten me? Thanks, Markus PS: My server version is PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-9)

Re: [SQL] Update instead rules on Views

Helo, On Tue, 2 Nov 2004 13:05:07 +0100 Markus Schaber <[EMAIL PROTECTED]> wrote: > -- But to remain compatibility with old apps, we also need to manage > -- updates to the view, which are to be rewritten as follows: > CREATE RULE testview_update_rule > AS ON UPDATE TO t

Re: [SQL] Update instead rules on Views

Hello, On Tue, 2 Nov 2004 16:20:37 +0100 Markus Schaber <[EMAIL PROTECTED]> wrote: > > -- But to remain compatibility with old apps, we also need to manage > > -- updates to the view, which are to be rewritten as follows: > > CREATE RULE testview_update_rule > &g

Re: [SQL] upper/lower for german characters

some locale setting that needs to > be done here, but could not fix this. > > I am using the ASCII encoding. By definition, ASCII does not contain any umlauts. So I would advise that, first, you switch to an umlaut capable encoding (e. G. Latin1, Latin9 or UTF-8). HTH, Markus -- markus s

Re: [SQL] Unicode problem inserting records - Invalid UNICODE

Hi, David, On Thu, 11 Nov 2004 11:29:22 -0800 "David B" <[EMAIL PROTECTED]> wrote: > show client_encoding gives: > UNICODE So is the data you send also encoded in unicode? Maybe "set client_encoding latin1" or "\encoding latin1" helps. HTH, markus

[SQL] Move table between schemas

Hello, Is there an easy way to move a table to another schema in PostgreSQL 7.4? ALTER TABLE and ALTER SCHEMA don't have this options. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 m

Re: [SQL] Move table between schemas

e much more difficult compared to e. G. renaming a table to implement this, so I couuld not imagine this does not exist until I tried to find out how to do it. Thanks, markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-

[SQL] Hide schemas and tables

seen. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 8: explain analyze is

[SQL] Split pg_dump script

Hello, The attached dump_split.sh script maybe helpful to some of you. It is called with a database name and a base file name. It first dumps out the schema and metadata of the database in a plain SQL file. After this, it dumps out the data, each table into its own file, compressed with bzip2. T

Re: [SQL] problem inserting local characters ...

. ASCII always is 7 bit. As your error message is in German, I suspect your data is encoded in LATIN1 or LATIN9 (their only difference is the EUR symbol in the latter one). Can you try to add the following command before your insert statements: set client_encoding to latin1; HTH, Markus -- mar

Re: [SQL] pg primary key bug?

problems were caused by suboptimal handling of BEGIN in the pgjdbc driver, this should be fixed in current versions of postgres.jar (build 8.0-310). Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mai

Re: [SQL] Multiples schemas

> psql DB1 > select * from DB2.schema.table In contrib/, there's a dblink module that lets you select data from other databases, but I'm afraid it is rather limited in usage and performance. Markus -- Markus Schaber | Dipl. Informatiker | Software Development GIS Figh

Re: [SQL] Building a database from a flat file

and let your application work on a set of views. Then you can change the views via "create or replace view" for switch over. Markus -- Markus Schaber | Dipl. Informatiker | Software Development GIS Fight against software patents in EU! http://ffii.org/

Re: [SQL] Building a database from a flat file

when a table is renamed, they stick to the same table disregarding name changes. Markus -- Markus Schaber | Dipl. Informatiker | Software Development GIS Fight against software patents in EU! http://ffii.org/ http://nosoftwarepatents.org/

Re: [SQL] Building a database from a flat file

RUNCATE, and recreate the constraints. Markus -- Markus Schaber | Dipl. Informatiker | Software Development GIS Fight against software patents in EU! http://ffii.org/ http://nosoftwarepatents.org/ ---(end of broadcast)

Re: [SQL] Postgres schema comparison.

the files may already fulfil your needs. 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: [ADMIN] [SQL] Postgres schema comparison.

euse some of its logics to create per-table md5sums for all tables in a database automatically. 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: [despammed] [SQL] Crosstab function

Hi, Bandeng, bandeng schrieb: > I have install postgresql-contrib finally... i'm newbie in server. > I use freebsd 4.8, I saw in documentation it is said use tablefunc.sql > but in freebsd i found file tablefunc.so , it is already compiled. is > there suggestion to install tablefunc.so ? I think

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

Hi, Andreas, Andreas Joseph Krogh schrieb: >>>So, what you're suggesting is that a restart of the webapp should make >>>vacuum able to delete those dead rows? >>Yes, but that'll only solve your problem for now. You'll have the >>problem again soon. What's keeping open the transaction? > Don't k

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

Hi, Andrew, Andrew Sullivan schrieb: > Nope. That's a problem with your pool software. It's no doubt > issuing "BEGIN;" as soon as it connects. This problem may as well be caused by some versions of the postgresql jdbc driver, no need to blame the pool software. This is fixed with the current

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

Hi, Andreas, Andreas Joseph Krogh schrieb: >>Could you try the latest postgresql-8.0-311.jdbcX.jar? The current >>versions should solve the idle in transaction problem, the previous ones >>tended to issue BEGIN; just after every COMMIT, so there's always an >>open transaction. > I could, but is i

Re: [SQL] [ANNOUNCE] pgtop, display PostgreSQL processes in `top' style

Hi, Cosimo, Cosimo Streppone wrote: > 1) is it possible to know Pg backend uptime with >SQL queries? Or must I look at postmaster.pid file? >or even something else? In contrib, there's a function caled backend_pid() defined in misc_utils.sql, it may be helpful for you. markus signatur

Re: [SQL] Mutex via database

Hi, Enrico, Enrico Weigelt wrote: > i've a dozen of servers processing jobs from a database. > Some servers may only run exactly once, otherwise I'll get jobs > done twice. > > Is there any way for implementing an kind of mutex within the > database, which is automatically released if the hol

Re: [SQL] all server processes terminated; reinitializing

Hallo, 2000 Informatica, 2000 Informatica wrote: > Estou usando PostgreSQL 8.0.2 instalado no linux Fedora FC2. > > Esta é a messagem gravada no meu "serverlog": > > WARNING: terminating connection because of crash of another server process > > DETAIL: The postmaster has commanded this server p

Re: [SQL] plpgsql triggers in rules

Hi, Chester, chester c young wrote: > is is possible for to have a "do instead" trigger on a view that is a > plpgsql function? Kinda. They're called "rules", not "triggers". See http://www.postgresql.org/docs/8.1/interactive/rules.html HTH, Schabi --

Re: [SQL] exceptions in rules

Hi, chester, chester c young wrote: > is there any way within a rule to raise an exception? Oh, so you know about rules - why did you ask for them before? You can use a plsql function to raise, if you don't find an easier way. Markus -- Markus Schaber | Logical Tracking&Tracing I

Re: [SQL] Error calling self-made plpgsql function "function XYZ(bigint)

schema' gives a list of all tables. 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] hi all......................!!

view before reCREATEing it, maybe it's best to encapsulate this inside a transaction or use a scheduled downtime. Btw, it seems that your '.'-key is broken and chatters. :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software D

Re: [SQL] Changing the transaction isolation level within the stored

ibly violated the constraits that the higher level wants to guarantee. 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] Changing the transaction isolation level within the

a function, right? Right, as you need a SELECT to actually execute your function, so the transaction commands inside the function are invoced _after_ the first SELECT began execution. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Develo

Re: [SQL] Changing the transaction isolation level within the stored

ts transaction. Be shure to read the paragraph about how "NOTIFY interacts with SQL transactions" in the NOTIFY documentation. I don't know the exact sematics of set_message_status and your checks, but it may be another solution to split thread A into two t

Re: [SQL] Changing the transaction isolation level within the stored

a collision in your example: Depending on the transaction isolation level and exact timings, colliding queries may lead to different results or even one transaction aborted, but there is no deadlock under MVCC. Not needing such locks is the whole point in using MVCC at all. Markus -- Markus

Re: [SQL] Does PostgreSQL support job?

PLPGSQL is turing complete, plain SQL is not. 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] Changing the transaction isolation level within the stored

les, but attempts to get those locks in > the opposite order, you are all but guaranteed a deadlock. MVCC > helps, but it can't avoid locking the same data when that data is > being updated. You're right, I was mislead from my memory. Sorry for the confusion I brought to thi

Re: [SQL] Does PostgreSQL support job?

se machine. For updating a stored procedure, you need just the appropriate rights in the database. On larger deployments, this can be an important difference. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against

Re: [SQL] Does PostgreSQL support job?

C2822 are structural or layout languages, but not programming languages. > Just kidding! Now, you're kidding. :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.

[SQL] no notnull values, invalid stats?

Hello, The following message occasionally appears in my postgresql log - from temporal corellation, it might be caused by autovacuum. NOTICE: no notnull values, invalid stats Is that anything I should care about? I'm running debianized postgresql 8.1.0-3. Markus -- Markus Sc

Re: [SQL] no notnull values, invalid stats?

erbatim or did you > translate it? It is verbatim from /var/log/postgresql/postgresql-8.1-main.log. But I have PostGIS installed in some of the databases, so it might be from there. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Developmen

Re: [SQL] Delete with join -- deleting related table entries?

d do it all for me Why do you think this won't work? (provided you add the missing ) and ; :-) 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] Non Matching Records in Two Tables

Hi, Ken, Ken Hill schrieb: > I need some help with a bit of SQL. I have two tables. I want to find > records in one table that don't match records in another table based on > a common column in the two tables. Both tables have a column named > 'key100'. I was trying something like: > > SELECT cou

Re: [SQL] Trigger/Sequence headache

roblem I have a > cleanup routine. Out of curiosity: Could you explain what's the problem with the holes? Bigserial should provide enough number space that holes are no problem. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS

Re: [SQL] Slow update SQL

e this index first, then CLUSTER the table, and then recreate the other indices. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarep

Re: [SQL] VIEWs and TEMP tables problem

s%' ORDER BY col_b LIMIT 10 OFFSET 10; 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 of bro

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

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] 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] 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

[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: [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] 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: [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

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] 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] 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] 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

[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] [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

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] 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] 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] 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] [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] 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] 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] 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

[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] 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

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] 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] 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 ?

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] 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] [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] [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

[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] 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

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] 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] 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

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

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

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

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

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

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

[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] 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

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] 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] 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] 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

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] 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] 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] 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] 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] 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&

  1   2   >