Re: [GENERAL] pg_dump --compress error
On 31 Aug 2010, at 6:44, yasin malli wrote: Hi everyone. I try this command ' pg_dump --compress=5 DBNAME ***.sql ' and ' psql -f ***.sql -d DBNAME ' but I take some error because of compression. how can restore compressed dump file without taking any error ? By using pg_restore instead of psql. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c7c9a6510401193214009! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump --compress error
On 31 Aug 2010, at 8:17, yasin malli wrote: Don't reply to just me, include the list. if I took my dump file with 'pg_dump -Ft ' command, I would use 'pg_restore', but I take my dump file at plain-old format for compressing data ( tar format dump hasn't compress feature ) when I tried your suggestion, I take this error : pg_restore: [archiver] input file does not appear to be a valid archive Ah right, most people use --compress in combination with the custom format (-Fc). I have little space on my device so I have to compress db files. For example; when I took dump_file with 'pg_dump -Ft' dump_files size : 56K 'pg_dump --compress=5' : 4K Try pg_dump -Fc --compress=5, I think you'll reach comparable sizes and you'll get much more flexibility to restore your database. Shouldn't you be using level 9 btw, if you're worried about disk space? I can take a dump_file but I can't restore it. Is there any other way to restore compressed data ? Didn't you read the man page for the --compress option? You can just pipe your dump through gunzip. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c7ca37210401517469623! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump --compress error
I tried it and it ran without any error but my table wasn't created so problem is going on. compress level isn't important because when I controlled it gave me same results ( 5 or 9 ) Unfortunately, only plain-old dump works correctly while restoring. if command contains any compress option, it won't work any suggestion ? -- Yasin MALLI System Software Development Engineer yasinma...@gmail.com , yasinma...@yahoo.com On Mon, Aug 30, 2010 at 11:38 PM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: On 31 Aug 2010, at 8:17, yasin malli wrote: Don't reply to just me, include the list. if I took my dump file with 'pg_dump -Ft ' command, I would use 'pg_restore', but I take my dump file at plain-old format for compressing data ( tar format dump hasn't compress feature ) when I tried your suggestion, I take this error : pg_restore: [archiver] input file does not appear to be a valid archive Ah right, most people use --compress in combination with the custom format (-Fc). I have little space on my device so I have to compress db files. For example; when I took dump_file with 'pg_dump -Ft' dump_files size : 56K 'pg_dump --compress=5' : 4K Try pg_dump -Fc --compress=5, I think you'll reach comparable sizes and you'll get much more flexibility to restore your database. Shouldn't you be using level 9 btw, if you're worried about disk space? I can take a dump_file but I can't restore it. Is there any other way to restore compressed data ? Didn't you read the man page for the --compress option? You can just pipe your dump through gunzip. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:1164,4c7ca36210403062783909!
Re: [GENERAL] IBATIS support for postgres cursors
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- How did you get OTHER to work? Did you define your own TypeHandler? That's the only change we had to make. The Java code works as is. We only changed the jdbcType from ORACLECURSOR TO OTHER parameterMap id=getUsersParam class=Map parameter property=results jdbcType=OTHER javaType=java.sql.ResultSet mode=OUT resultMap=allUsersResult/ parameter property=errors jdbcType=VARCHAR javaType=java.lang.String mode=OUT/ /parameterMap Thanks, Zeeshan This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global DataPoint Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means of e-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] GiN indexes
On Tue, 31 Aug 2010, Stavroula Gasparotto wrote: Currently, only the B-tree, GiST and GIN index types support multicolumn indexes. What does this mean exactly if I'm trying to create a multicolumn GIN index? Does this mean the index can contain one or more tsvector type fields only, or can I combine tsvector type field with other type fields in the index, such as a timestamp column? It does both. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump --compress error
yasin malli yasinma...@gmail.com writes: Unfortunately, only plain-old dump works correctly while restoring. if command contains any compress option, it won't work --compress is normally used as an adjunct to -Fc. I'm not real sure what you get if you specify it without that; maybe a compressed plain-text-script dump? If so, you'd have to pass it through gunzip and then to psql to do anything useful with it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Forcing the right queryplan
Hi,
I've the problem my database is not using the 'right' queryplan in all
cases. Is there a way I can force that and/or how should I tuned the
table statistics?
I'm doing a rsyslog database in PostgreSQL with millions of records
(firewall logging). The db scheme is the so called 'MonitorWare' scheme,
to wich I added two extra indexes.
syslog=# select version();
version
--
PostgreSQL 8.4.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20
080704 (Red Hat 4.1.2-48), 64-bit
syslog=# \d systemevents
Table public.systemevents
Column |Type | Modi
fiers
+-+-
--
id | integer | not null default nextval('sy
stemevents_id_seq'::regclass)
customerid | bigint |
receivedat | timestamp without time zone |
devicereportedtime | timestamp without time zone |
facility | smallint|
priority | smallint|
fromhost | character varying(60) |
message| text|
ntseverity | integer |
importance | integer |
eventsource| character varying(60) |
eventuser | character varying(60) |
eventcategory | integer |
eventid| integer |
eventbinarydata| text|
maxavailable | integer |
currusage | integer |
minusage | integer |
maxusage | integer |
infounitid | integer |
syslogtag | character varying(60) |
eventlogtype | character varying(60) |
genericfilename| character varying(60) |
systemid | integer |
Indexes:
systemevents_pkey PRIMARY KEY, btree (id)
fromhost_idx btree (fromhost)
msgs_idx gin (to_tsvector('english'::regconfig, message))
The GIN index is to do text searching (via LogAnalyzer).
Now there are two types of query plans:
syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost,
syslogtag, infounitid, message FROM systemevents WHERE ( (
to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) ) ORDER BY
id DESC LIMIT 100; QUERY PLAN
-
Limit (cost=0.00..10177.22 rows=100 width=159)
- Index Scan Backward using systemevents_pkey on systemevents (cost=0.00..
1052934.86 rows=10346 width=159)
Filter: (to_tsvector('english'::regconfig, message) @@ to_tsquery('131.
211.112.9'::text))
(3 rows)
This one is useless (takes very long). However this one:
syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost,
syslogtag, infounitid, message FROM systemevents WHERE ( (
to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) ) ORDER BY
id DESC LIMIT 500;
QUERY PLAN
---
Limit (cost=40928.89..40930.14 rows=500 width=159)
- Sort (cost=40928.89..40954.76 rows=10346 width=159)
Sort Key: id
- Bitmap Heap Scan on systemevents (cost=2898.06..40413.36 rows=1034
6 width=159)
Recheck Cond: (to_tsvector('english'::regconfig, message) @@ to_t
squery('131.211.112.9'::text))
- Bitmap Index Scan on msgs_idx (cost=0.00..2895.47 rows=10346
width=0)
Index Cond: (to_tsvector('english'::regconfig, message) @@
to_tsquery('131.211.112.9'::text))
(7 rows)
works acceptable.
Stats:
syslog=# SELECT relname, relkind, reltuples, relpages
FROM pg_class WHERE
relname LIKE 'systemevents%';
relname| relkind | reltuples | relpages
---+-+-+--
systemevents_pkey | i | 2.06915e+06 |71985
systemeventsproperties| r | 0 |0
systemeventsproperties_pkey | i | 0 |
[GENERAL] Finding intercept of two documents (two tsvector fields)
I am looking for a way to obtain the words that are common amongst two tsvector records. The long workaround I know is to: 1)convert the contents of the tsvector fields to text then find and replace single quote followed by space then single quote with a comma character then stripping away the first and last single quote characters. 2)convert this CSV data into array using string to array. 3)Perform a join on these two arrays and unnest them and join on the unnested values. 4)Perform array_agg on any one field (of unnested array values) of these two tables with aid of a GROUP BY. 5)cast the array to string using a white space as the delimiter. 6)cast the string to tsvector. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump --compress error
On Tue, 2010-08-31 at 00:50 -0700, yasin malli wrote: I tried it and it ran without any error but my table wasn't created so problem is going on. compress level isn't important because when I controlled it gave me same results ( 5 or 9 ) Unfortunately, only plain-old dump works correctly while restoring. if command contains any compress option, it won't work Compress will not work in plaintext format for restore. You need to use -Fc and then pg_restore. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] are there any documents describe the index mechanic?
sunpeng wrote: are there any documents describe the index mechanic? For example, how to store the B tree in tables in hard disk? thanks! peng There is a README in the source tree: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/nbtree/README?rev=1.22 and many other README files as well. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump --compress error
Excerpts from yasin malli's message of mar ago 31 00:44:36 -0400 2010: Hi everyone. I try this command ' pg_dump --compress=5 DBNAME ***.sql ' and ' psql -f ***.sql -d DBNAME ' but I take some error because of compression. how can restore compressed dump file without taking any error ? You can restore this with zcat ***.sql | psql -d DBNAME (or gunzip -c ***.sql if you don't have zcat) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] table - sequence dependent information
Hi all - Is there a way I can tell table - sequence dependent information. which sequences are being used by which table? thanks for the help
[GENERAL] table - sequence dependent informatio
Hi all - Is there a way I can tell table - sequence dependent information. which sequences are being used by which table? thanks for the help
Re: [GENERAL] table - sequence dependent informatio
On 31 August 2010 18:02, akp geek akpg...@gmail.com wrote: Hi all - Is there a way I can tell table - sequence dependent information. which sequences are being used by which table? thanks for the help Take a look at the post Finding orphaned sequences on this blog: http://blog.tapoueh.org/blog.dim.html Change the last part of the query from WHERE a.tablename IS NULL to WHERE a.tablename IS NOT NULL. That'll show you what tables your SERIAL sequences are being used on. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] On-disk size of db increased after restore
I tried to restore one of our db backups to 3 different machines today. After restore, all machines reported larger on-disk size, and also psql's \l+ confirmed that. Here is the live machine: On-disk size: 84 GB Size reported by psql: 79 GB Backup machine 1: On-disk size: 162 GB Size reported by psql: 177 GB Backup machine 2: On-disk size: 179 GB Size reported by psql: 177 GB I have seen the opposite of this tons of times before, but I haven't seen an increase after restore before. Does anyone know what may cause this? Where should I look at? Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Scalar subquery
I just got my hands on mysql (5.0.something) and it does not cache the
scalar subquery result.
So... now I'm completely puzzled whether this is a bug, a desired result or
just a loosely standardized thing.
Help anyone?
On Fri, Aug 27, 2010 at 5:41 PM, Vyacheslav Kalinin v...@mgcp.com wrote:
Hi,
Apparently scalar subquery when used as a part of SELECT statement and when
it does not depend on outer query columns
is executed only once per statement, e.g.:
postgres=# select i, (select random()) rand from generate_series(1, 3) i;
i | rand
---+---
1 | 0.992319826036692
2 | 0.992319826036692
3 | 0.992319826036692
(Though term depend is subtle, compare these:
postgres=# select i, (select random() + case when false then i else 0 end )
rand from generate_series(1, 3) i;
i | rand
---+---
1 | 0.806265413761139
2 | 0.806265413761139
3 | 0.806265413761139
(3 rows)
postgres=# select i, (select random() where i=i ) rand from
generate_series(1, 3) i;
i | rand
---+---
1 | 0.426443862728775
2 | 0.133071997668594
3 | 0.751982506364584
(3 rows)
postgres=# select i, (select random() where i=i or i is null ) rand from
generate_series(1, 3) i;
i | rand
---+---
1 | 0.320982406847179
2 | 0.996762252878398
3 | 0.076554249972105
(3 rows)
Looks like dependence is not there anymore if PG is smart enough to
simplify boolean expressions)
Anyway, as some older PG versions and Oracle behave similarly I suppose
this result is expected and desired (correct?),
but unfortunately not well-documented (did I miss it mentioned?).
Can anyone shed some light on this and/or probably update docs?
P.S.
I got bitten by a statement like this:
select (select nextval('someseq') * a + b from somefunc()), col1,
with a and b being OUT parameters of somefunc().
Re: [GENERAL] Scalar subquery
Vyacheslav Kalinin v...@mgcp.com writes: I just got my hands on mysql (5.0.something) and it does not cache the scalar subquery result. So... now I'm completely puzzled whether this is a bug, a desired result or just a loosely standardized thing. It's loosely standardized. AFAICS, the spec doesn't address the detailed semantics of subqueries at all, except in wording to this effect: Each subquery in the search condition is effectively executed for each row of T and the results used in the ap- plication of the search condition to the given row of T. If any executed subquery contains an outer reference to a column of T, the reference is to the value of that column in the given row of T. There is wording like this for subqueries in WHERE and HAVING, but I haven't found anything at all that mentions the behavior for subqueries in the SELECT targetlist. In any case, the fact that they said effectively executed and not simply executed seems to be meant to leave implementors a lot of wiggle room. In particular, there isn't any wording that I can find suggesting that the presence of volatile (or in the spec's classification, nondeterministic) functions ought to affect the behavior. PG's interpretation is that if there is no outer reference in a subquery, it's okay to implement it as an initplan, meaning it gets evaluated at most once per call of the containing query. We don't pay attention to whether there are volatile functions in there. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table update problem works on MySQL but not Postgres
Let me stress that this is not a bug in PostgreSQL; if anything at all, it's only a lack of a stupid feature. I'm working on a project for a client where I have a table for arbitrary categories to be applied to their data, and they need to be able to set the order in which the categories appear. A simplified version of the table as I created is as follows: create table mydemo (cat_id int not null, cat_name varchar(25) not null, cat_order int not null, primary key(cat_id,cat_order)); During my coding, I unwittingly backed myself into a corner, fully expecting to issue queries such as: update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order = 0 in order to insert categories at the top of the sorted list for example. As you can probably guess, this query doesn't work very well. On both MySQL and PostgreSQL I get a constraint violation. That makes sense; I screwed up. But out of pure curiosity to see if I could circumvent this issue, I added an order clause, making that query this instead: update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order = 0 order by cat_order desc This is where the interesting thing happens: On MySQL the query actually works as intended, but it doesn't on PostgreSQL. As I said, I'm sure this is not a bug in PostgreSQL, but the lack of a stupid user trick. While my project is on MySQL, and I could theoretically leave my code as is to take advantage of this trick, I'm sure I'd be a complete idiot to leave it instead of fixing it. However, I wanted to share this little tidbit with the PostgreSQL community. Raymond -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] On-disk size of db increased after restore
2010/8/31 Devrim GÜNDÜZ dev...@gunduz.org: I tried to restore one of our db backups to 3 different machines today. After restore, all machines reported larger on-disk size, and also psql's \l+ confirmed that. Here is the live machine: On-disk size: 84 GB Size reported by psql: 79 GB Backup machine 1: On-disk size: 162 GB Size reported by psql: 177 GB Backup machine 2: On-disk size: 179 GB Size reported by psql: 177 GB They're about 2x as big. Any chance you've restored to different dbs and have two copies? Or double the data in one db? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
On Tue, Aug 31, 2010 at 07:56:23PM -0400, Raymond C. Rodgers wrote: Let me stress that this is not a bug in PostgreSQL; if anything at all, it's only a lack of a stupid feature. PostgreSQL's version involves UPDATE ... FROM. Use an ORDER BY in the FROM clause like this: UPDATE mydemo SET cat_order = m.cat_order+1 FROM ( SELECT cat_order, client_id FROM mydemo WHERE client_id = 1 AND cat_order = 0 ORDER BY cat_order) m WHERE mydemo.cat_order = m.cat_order AND mydemo.client_id = m.client_id More details on PostgreSQL's UPDATE are at: http://www.postgresql.org/docs/current/static/sql-update.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
On Tue, Aug 31, 2010 at 7:56 PM, Raymond C. Rodgers sinful...@gmail.com wrote: Let me stress that this is not a bug in PostgreSQL; if anything at all, it's only a lack of a stupid feature. I'm working on a project for a client where I have a table for arbitrary categories to be applied to their data, and they need to be able to set the order in which the categories appear. A simplified version of the table as I created is as follows: create table mydemo (cat_id int not null, cat_name varchar(25) not null, cat_order int not null, primary key(cat_id,cat_order)); During my coding, I unwittingly backed myself into a corner, fully expecting to issue queries such as: update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order = 0 in order to insert categories at the top of the sorted list for example. As you can probably guess, this query doesn't work very well. On both MySQL and PostgreSQL I get a constraint violation. That makes sense; I screwed up. But out of pure curiosity to see if I could circumvent this issue, I added an order clause, making that query this instead: update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order = 0 order by cat_order desc This is where the interesting thing happens: On MySQL the query actually works as intended, but it doesn't on PostgreSQL. As I said, I'm sure this is not a bug in PostgreSQL, but the lack of a stupid user trick. While my project is on MySQL, and I could theoretically leave my code as is to take advantage of this trick, I'm sure I'd be a complete idiot to leave it instead of fixing it. You have it backwards, mysql is broken, postgresql is not. Anyways, you can do it in postgres like this: alter table mydemo alter cat_order type int using cat_order + 1; merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
On Tue, 2010-08-31 at 20:17 -0400, Merlin Moncure wrote: This is where the interesting thing happens: On MySQL the query actually works as intended, but it doesn't on PostgreSQL. As I said, I'm sure this is not a bug in PostgreSQL, but the lack of a stupid user trick. While my project is on MySQL, and I could theoretically leave my code as is to take advantage of this trick, I'm sure I'd be a complete idiot to leave it instead of fixing it. You have it backwards, mysql is broken, postgresql is not. That is what he said. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table update problem works on MySQL but not Postgres
On 8/31/2010 8:17 PM, Merlin Moncure wrote: On Tue, Aug 31, 2010 at 7:56 PM, Raymond C. Rodgerssinful...@gmail.com wrote: Let me stress that this is not a bug in PostgreSQL; if anything at all, it's only a lack of a stupid feature. I'm working on a project for a client where I have a table for arbitrary categories to be applied to their data, and they need to be able to set the order in which the categories appear. A simplified version of the table as I created is as follows: create table mydemo (cat_id int not null, cat_name varchar(25) not null, cat_order int not null, primary key(cat_id,cat_order)); During my coding, I unwittingly backed myself into a corner, fully expecting to issue queries such as: update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order= 0 in order to insert categories at the top of the sorted list for example. As you can probably guess, this query doesn't work very well. On both MySQL and PostgreSQL I get a constraint violation. That makes sense; I screwed up. But out of pure curiosity to see if I could circumvent this issue, I added an order clause, making that query this instead: update mydemo set cat_order = cat_order + 1 where client_id = 1 and cat_order= 0 order by cat_order desc This is where the interesting thing happens: On MySQL the query actually works as intended, but it doesn't on PostgreSQL. As I said, I'm sure this is not a bug in PostgreSQL, but the lack of a stupid user trick. While my project is on MySQL, and I could theoretically leave my code as is to take advantage of this trick, I'm sure I'd be a complete idiot to leave it instead of fixing it. You have it backwards, mysql is broken, postgresql is not. Anyways, you can do it in postgres like this: alter table mydemo alter cat_order type int using cat_order + 1; merlin Like I said and stressed twice, it's not a problem with PostgreSQL. David's solution is actually better than that, but I accidentally sent just a private reply to him acknowledging that it's good to know that PostgreSQL can also save a stupid programmer's butt... :-) Raymond -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problems with ODBC Driver
I have a notebook that I am using as a server for testing purposes and it has the official ODBC driver installed. I can access this and use it connect to PostreSql. On a second machine on the same network - I also have the same ODBC driver installed. The behaviour of this one is quite different. I have a test program that works on the server but when I run it on this workstation, it can create a file with a couple of fields which I can verify by looking inside PostgreSQL. But when I go to open up that file and refer to one of its fields, I get an error message saying the field doesn't exist. I am running XP Professional on both machines. I have no idea what is happening - I have opened up the firewall on both machines to allow port 5432 to be used by all applications with TCP (also tried TCP and UDP). Does anyone have any suggestions? Cheers Phil Jackson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
