[GENERAL] Exporting 1 text column from all rows from a table to a file per row?
Hi, Is it possible, and if so how, to export a single column of a table into a separate file per row? I have a table with ~21000 rows that have a column "body1" containing ASCII text and I want to have 21000 separate ASCII files, each containing that column "body1". The name of the file does not matter, although it would be nice if they had the extension "txt". TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Example of full text search ranking function somewhere?
Hi, Is there an example /howto available that I can use to start developing mij own ranking function for full text search? I have looked at the ts_rank source code in src/backend/utils/adt/tsrank.c but that is rather complex an not easy to start learning from. Preferably a minimal boilerplate example with installation instructions in C/C++ ;-). TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Howto get the contents of mystopwords/dictionary/synonym/thesaurus ?
Hi, Is it possible to get an overview/the contents of the stopwords list, dictionary, synonyms or thesaurus using an SQL query, e.g. "SELECT * from stopwords"? Is it possible to add or remove entries from the dictionaries using SQL? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tools for converting XML file with dtd to table?
Hi, Are there any tools available for converting XML files for which a dtd is available into a PostgreSQL table without any programming on my part? The files are basically XML formatted table dumps, with some elements having attributes. TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl -- 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] In-place conversion of type bool
Hi Tom, On Wed, 2008-08-13 at 21:27 -0400, Tom Lane wrote: > "Joost Kraaijeveld" <[EMAIL PROTECTED]> writes: > > The database says that it's bool implementation is char(1), just as > > PostgreSQL does. I can copy te data OK, but I would like to change the > > actual type of the column from char(1) to bool. Is that possible without > > copying the column to a temporary column, dropping the old column and > > renaming the temporary columns to the old column? > > ALTER TABLE ... ALTER COLUMN TYPE might help you. Use the USING clause > if you need a non-default data conversion -- in this case it might look > like USING (col = '1') or some such. ALTER TABLE odbcdest ALTER COLUMN odbc_bool TYPE bool gives: ERROR: column "odbc_bool" cannot be cast to type "bool" ** Error ** ERROR: column "odbc_bool" cannot be cast to type "bool" SQL state: 42804 > > (This is probably not physically more efficient than making a temp > table, however.) Could you elaborate on this? I have tried something like this, but when the column in question is a column that is used in foreign keys I am forced to drop any foreign keys that point to this column. Which is quit annoying on a 200 table database with lots of foreign keys. TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] In-place conversion of type bool
Hi, I am converting a database to PostgreSQL. I use ODBC to create the PostgreSQL database and copy the data. The database says that it's bool implementation is char(1), just as PostgreSQL does. I can copy te data OK, but I would like to change the actual type of the column from char(1) to bool. Is that possible without copying the column to a temporary column, dropping the old column and renaming the temporary columns to the old column? I also have another table that has mac addresses stored *exactly* in the same format as PostgreSQL macaddr type that are used as primary key and dropping that key would mean a major headache. In this case I would like to be able to change the type from varchar to macaddr. TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of memory error, FreeBSD 6.2, PostgreSQL 8.2.4
-- !!! Must be in every alter script. Change script filename below only
!!!
INSERT INTO execution_histories (name,last_executed) VALUES
('0029_15.08.2007.sql','now');
COMMIT;
2007-08-31 12:35:51 CEST zorgmaatwerk LOG: duration: 0.120 ms statement:
ROLLBACK;
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] ERROR: relation "xxx" already exists but where????
I managed to drop a table without apparently droppig it's primary key After recreating the table I try to recreate the primary key. If I run the following: ALTER TABLE case_histories ADD CONSTRAINT case_histories_pkey PRIMARY KEY(case_history_id); Postgresql responds with: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "case_histories_pkey" for table "case_histories" ERROR: relation "case_histories_pkey" already exists SQL state: 42P07 The table does not have this key. Assuming that the response is correct, where can I find and/or delete this relation? I have deleted an entry inpg_constraint with that name but that did not do the trick TIA Joost Joost ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Can I backup/restore a database in a sql script?
Hi, I want to write a sql script in which I backup a database and restore a new (altered) version of that database. Is that possible? If so , can anyone give me an example of how to do that? I can run it from any command prompt (psql -U postgres template1 < my_db.backup)but I would like it to run from psql (which should give me 1 script for all platforms I must do this: Windows, FreeBSD and Debian) TIA Joost ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] psql : Error: Cannot stat /pgdata/8.2/main
Hi, I have moved my database files from their default location to their own partition on with their own controller and disks. PostgreSQL works OK and I can connect with Pgadmin (Debian Lenny AMD64, PostgreSQL 8.2.4). When I want to connect with psql however (with a non-root account) I get the following: panoramix:~$ psql -d my_database Error: Cannot stat /pgdata/8.2/main /pgdata/8.2/main is the location where the database files are actually located. Why do I get this error and what should I do to be able to connect to the database? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL crashes on Windows 2003
Hi all, Thanks for looking into it. The problem is solved: on both machines there appeared to be a hung-up backup process that locked PostgreSQL. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL crashes on Windows 2003
On Mon, 2007-03-12 at 15:37 +0100, Magnus Hagander wrote: > On Mon, Mar 12, 2007 at 10:13:48AM -0400, Tom Lane wrote: > > Magnus Hagander <[EMAIL PROTECTED]> writes: > > > It looks to me that you may be a victim of the bug patched in > > > > http://archives.postgresql.org/pgsql-committers/2007-01/msg00214.php. > > > Tom, whatever happened about a backpatch for that one? > > > > It's applied. > > > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/bgwriter.c > > Bah. I looked aruond but didn't find it. Clearly I'm blind. Thanks. > > So - if you're not on 8.1.7 or 8.1.8, upgrade to 8.1.8 and let us know > if you still have the problem. I wrote 8.1.x but we are actually on 8.1.8 and the problem is there (I just checked because I am not the local system administrator). -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL crashes on Windows 2003
On Mon, 2007-03-12 at 10:13 -0400, Tom Lane wrote: > Magnus Hagander <[EMAIL PROTECTED]> writes: > > It looks to me that you may be a victim of the bug patched in > > http://archives.postgresql.org/pgsql-committers/2007-01/msg00214.php. > > Tom, whatever happened about a backpatch for that one? > > It's applied. > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/bgwriter.c Thanks, to which version of PostgreSQL is that patch applied? 8.1.7 and higher ? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL crashes on Windows 2003
On Mon, 2007-03-12 at 14:39 +0100, Magnus Hagander wrote: > What version of PostgreSQL is this? > Oh, forgotten: 8.1.x with no upgrade option (company policy) -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PostgreSQL crashes on Windows 2003
Hi, The PostgreSQL of a customer database (running on Windows 2003) crashed and wrote the messages below in the logfile. According to the customer the drives are working OK, partitions are not full and nothing has changed in the permissions. The customer could restart the PostgreSQL service manually, without resorting to a backup. Could this be the result of not enough check_point segments? 2007-03-08 22:36:26 ERROR: could not read block 15894 of relation 1663/3227318/3227439: Permission denied 2007-03-08 22:36:26 STATEMENT: UPDATE deliver_cares SET org_personnel_id=$1, spent_time=$2, status=$3, when_registered=$4, actively_registered=$5 WHERE deliver_care_id=$6 2007-03-08 22:37:31 ERROR: could not write block 8 of relation 1663/3227318/4689678: Permission denied 2007-03-08 22:37:31 CONTEXT: writing block 8 of relation 1663/3227318/4689678 2007-03-08 22:38:57 FATAL: could not read from statistics collector pipe: No error 2007-03-08 22:38:57 FATAL: could not write to statistics collector pipe: No connection could be made because the target machine actively refused it. 2007-03-08 22:40:00 PANIC: could not write to log file 9, segment 219 at offset 15073280, length 24576: Permission denied 2007-03-08 22:40:00 STATEMENT: COMMIT This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2007-03-08 22:40:00 LOG: server process (PID 20508) was terminated by signal 3 2007-03-08 22:40:00 LOG: terminating any other active server processes 2007-03-08 22:40:00 WARNING: terminating connection because of crash of another server process 2007-03-08 22:40:00 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-03-08 22:40:00 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-03-08 22:40:00 WARNING: terminating connection because of crash of another server process 2007-03-08 22:40:00 DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2007-03-08 22:40:00 HINT: In a moment you should be able to reconnect to the database and repeat your command. 2007-03-08 22:40:01 LOG: all server processes terminated; reinitializing 2007-03-08 22:40:01 LOG: database system was interrupted at 2007-03-08 22:36:24 W. Europe Standard Time 2007-03-08 22:40:01 LOG: could not read from log file 9, segment 219 at offset 14876672: Permission denied 2007-03-08 22:40:01 LOG: invalid primary checkpoint record 2007-03-08 22:40:01 LOG: could not read from log file 9, segment 219 at offset 14721024: Permission denied 2007-03-08 22:40:01 LOG: invalid secondary checkpoint record 2007-03-08 22:40:01 PANIC: could not locate a valid checkpoint record This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2007-03-08 22:40:01 LOG: startup process (PID 20504) was terminated by signal 3 2007-03-08 22:40:01 LOG: aborting startup due to startup process failure 2007-03-08 22:40:01 LOG: logger shutting down TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why does explain differ from explan analyze?
On Thu, 2006-12-07 at 10:20 -0500, Tom Lane wrote: > Well, the answer is that these aren't the same query. For instance You are right. I did not check the report thorough wnought. Sorry. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why does explain differ from explan analyze?
Hi Tom,
On Wed, 2006-12-06 at 14:48 -0500, Tom Lane wrote:
> Joost Kraaijeveld <[EMAIL PROTECTED]> writes:
> > I have a query that if I do "explain" shows an other plan than if I do
> > "explain analyze" with that same query (immediately after the explain).
>
> Really? What PG version is this? Can you provide a self-contained
> test case?
Yes, really ;-).
PostgreSQL 8.1.5 on Windows XP.
Alas, no self contained test case, I do have the query, the "explain"
and the "explain analyse" output. Sorry about the long text.
SELECT *
FROM deliver_cares t0_$deliver_cares
LEFT OUTER JOIN cares t1_$deliver_cares_care ON
t0_$deliver_cares.care_id=t1_$deliver_cares_care.care_id
LEFT OUTER JOIN components t2_$deliver_cares_care_component ON
t1_$deliver_cares_care.component_id=t2_$deliver_cares_care_component.component_id
LEFT OUTER JOIN indication_functions t3_$deliver_cares_care_indicatio ON
t1_$deliver_cares_care.indication_function_id=t3_$deliver_cares_care_indicatio.indication_function_id
LEFT OUTER JOIN indications t4_$deliver_cares_care_indicatio ON
t3_$deliver_cares_care_indicatio.indication_id=t4_$deliver_cares_care_indicatio.indication_id
LEFT OUTER JOIN patients t5_$deliver_cares_care_indicatio ON
t4_$deliver_cares_care_indicatio.patient_id=t5_$deliver_cares_care_indicatio.patient_id
LEFT OUTER JOIN org_personnels t6_$deliver_cares_registeredOrgP ON
t0_$deliver_cares.registered_org_personnel_id=t6_$deliver_cares_registeredOrgP.org_personnel_id
LEFT OUTER JOIN org_personnels t7_$deliver_cares_assignedOrgPer ON
t0_$deliver_cares.assigned_org_personnel_id=t7_$deliver_cares_assignedOrgPer.org_personnel_id,
timeframes t1_pdam__$deliver_cares_timeframe
WHERE (((((((((
(t4_$deliver_cares_care_indicatio.patient_id = 21) -- pdam
AND
(t0_$deliver_cares.deliver_date >= current_date - 200) -- pdam
)
AND (t0_$deliver_cares.deliver_date <
current_date) -- pdam
)
AND (t0_$deliver_cares.timeframe_id = 45)
-- pdam
)
AND (t1_$deliver_cares_care.workers_number = 1)
-- pdam
)
AND (t0_$deliver_cares.status = 'P')
-- pdam
)
AND (t1_$deliver_cares_care.status <> 'S')
-- pdam
)
AND (t0_$deliver_cares.assigned_org_personnel_id IS NULL)
)
AND t0_$deliver_cares.care_id=t1_$deliver_cares_care.care_id
AND
t1_$deliver_cares_care.indication_function_id=t3_$deliver_cares_care_indicatio.indication_function_id
AND
t3_$deliver_cares_care_indicatio.indication_id=t4_$deliver_cares_care_indicatio.indication_id
))
AND
t0_$deliver_cares.timeframe_id=t1_pdam__$deliver_cares_timeframe.timeframe_id
ORDER BY t0_$deliver_cares.deliver_date ASC
, t1_pdam__$deliver_cares_timeframe.start_time ASC
explain:
--
Sort (cost=38222.31..38222.49 rows=71 width=3089)
Sort Key: "t0_$deliver_cares".deliver_date,
"t1_pdam__$deliver_cares_timeframe".start_time
-> Nested Loop (cost=1457.34..38220.13 rows=71 width=3089)
-> Seq Scan on timeframes "t1_pdam__$deliver_cares_timeframe"
(cost=0.00..1.25 rows=1 width=126)
Filter: (timeframe_id = 45)
-> Nested Loop Left Join (cost=1457.34..38218.17 rows=71 width=2963)
-> Nested Loop Left Join (cost=1457.34..37803.85 rows=71
width=2175)
-> Hash Left Join (cost=1457.34..37389.53 rows=71
width=1387)
Hash Cond: ("outer".patient_id = "inner".patient_id)
-> Hash Join (cost=1451.54..37383.36 rows=71
width=486)
Hash Cond: ("outer".indication_function_id =
"inner".indication_function_id)
-> Hash Left Join (cost=1438.21..37258.40
rows=22198 width=323)
Hash Cond: ("outer".component_id =
"inner".component_id)
-> Hash Join (cost=1409.91..36897.13
rows=22198 width=233)
Hash Cond: ("outer".care_id =
"inner".care_i
[GENERAL] Why does explain differ from explan analyze?
I have a query that if I do "explain" shows an other plan than if I do "explain analyze" with that same query (immediately after the explain). Is that possible, and if so, why? Does it have to do something with vacuum? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Locking in PostgreSQL?
Does PostgreSQL lock the entire row in a table if I update only 1 column? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to determine initdb parameters on old database?
On Mon, 2006-10-23 at 02:11 -0400, brian wrote: > pg_controldata - display control information of a PostgreSQL database > cluster > > pg_controldata [ datadir ] I was hoping for the actual command but this suggests deduction ;-) -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] How to determine initdb parameters on old database?
Is it possible to find out the parameters used with initdb on an old database or is this a matter of deduction? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Strange query results with invalid multibyte
Hi Tom, Thanks for putting up with the questions. On Wed, 2006-09-27 at 14:58 -0400, Tom Lane wrote: > LANG=en_US locale charmap > I have done this on both machines: The working machine says: ISO-8859-1 The not working machine says: ISO-8859-1 I still do not understand what is happening and maybe more important, how I can solve this (without re-installing the OS / database). A better understanding of how the server OS, database, codepages, client OS, charmaps etc work would be nice -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Strange query results with invalid multibyte
On Wed, 2006-09-27 at 12:10 -0400, Tom Lane wrote: > Joost Kraaijeveld <[EMAIL PROTECTED]> writes: > > On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote: > >> But have they got the same locale settings (lc_collate, lc_ctype)? > > > According to the postgresql.conf of the *working* database the locales > > are: > > lc_messages = 'C' > > lc_monetary = 'C' > > lc_numeric = 'C' > > lc_time = 'C' > > > According to the other obe: > > lc_messages = 'en_US' > > lc_monetary = 'en_US' > > lc_numeric = 'en_US' > > lc_time = 'en_US' > > You did not show us the settings that actually count, but based on this > I'm guessing they are en_US also Ah, sorry: "show lc_collate" and "show lc_ctype" confirm your guess. > What you need to find out next is > what character set encoding that locale implies on your machine. I'm > betting it's not utf8 though :-( I am not sure what you mean by that but maybe this helps: the output of "locale" on the working machine is: LANG= LANGUAGE=en_US:en_GB LC_CTYPE="POSIX" LC_NUMERIC="POSIX" LC_TIME="POSIX" LC_COLLATE="POSIX" LC_MONETARY="POSIX" LC_MESSAGES="POSIX" LC_PAPER="POSIX" LC_NAME="POSIX" LC_ADDRESS="POSIX" LC_TELEPHONE="POSIX" LC_MEASUREMENT="POSIX" LC_IDENTIFICATION="POSIX" LC_ALL= The output of the same command on the non-working machine: LANG=en_US LANGUAGE=en_NL:en_US:en_GB:en LC_CTYPE="en_US" LC_NUMERIC="en_US" LC_TIME="en_US" LC_COLLATE="en_US" LC_MONETARY="en_US" LC_MESSAGES="en_US" LC_PAPER="en_US" LC_NAME="en_US" LC_ADDRESS="en_US" LC_TELEPHONE="en_US" LC_MEASUREMENT="en_US" LC_IDENTIFICATION="en_US" LC_ALL= If this is not what you mean, could you help me in the right direction? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Strange query results with invalid multibyte
Hi Tom, On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote: > "Joost Kraaijeveld" <[EMAIL PROTECTED]> writes: > > I have 2 database, both created with: > > CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE > > = pg_default; > > But have they got the same locale settings (lc_collate, lc_ctype)? According to the postgresql.conf of the *working* database the locales are: lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' According to the other obe: lc_messages = 'en_US' lc_monetary = 'en_US' lc_numeric = 'en_US' lc_time = 'en_US' Could this be the difference? Is there any documentation available somewhere, on how these locale settings work and interact (in combination with the server and/or client platform???), besides the explanation in the PostgreSQL manual (http://www.postgresql.org/docs/8.1/interactive/charset.html#LOCALE) (which is to terse for me to understand)? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Strange query results with invalid multibyte character
Hi, I have a strange problem. I have 2 database, both created with: CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; Running the queries below on the first database is OK and do what I expect. If I create a backup of the first datase and restore that backup in the second database the following happens: The first query (see below) returns 17 records, all containing 'Boonk%'. The second query (see below)returns 'ERROR: invalid multibyte character for locale' Query 1: select lastname from salesordercustomer where lower(lastname) like 'boonk%' Query 2: select lastname from salesordercustomer where upper(lastname) like 'BOONK%' Both database are running *exactly* the same version of Debian Etch AMD64 and *exactly* the same version of PostgreSQL (8.1.4 for AMD64) Can anyone explain this to me? TIA Joost ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Is what I want possible and if so how?
On Mon, 2006-07-03 at 07:54 -0400, Douglas McNaught wrote: > Have all the clients use SELECT FOR UPDATE. OK, thanks, your answer lead me to a re-read of the docs and I think I found a way to do it. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Is what I want possible and if so how?
Hi, I have a table with events that must be handled by multiple clients. It does not matter which client handles an event, but no two clients may handle the same event and an event may only handled once. A client can only determine the availability of an event by querying the database. The access to the table should be queue-like with synchronization. My idea was that a client should do a "SELECT" on the table and mark the selected records as "being handled" to avoid double handling. I have read the manual about "LOCK", "SET TRANSACTION" and "SELECT...FOR UPDATE", but from what I understand, they cannot prevent a "SELECT" from another client, based on the "SELECT". Is there a way to make this possible? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Top 10 Wishlist
On Wed, 2006-01-18 at 10:10 +0100, Tino Wildenhain wrote: > Joost Kraaijeveld schrieb: > > On Tue, 2006-01-17 at 09:52 -0800, David Fetter wrote: > > > ... > > Because a lot of tools that I use to manage a database during > > *development* (e.g. PgAdmin) show the columns in an other order than the > > order of attributes in my Java/C++ code. The "logical" order of the > > columns/attributes can change during development. > > > Actually when I issue: > > SELECT a,b,c FROM sometable; in pgadmin3 I get the columns exactly > in the order specified. Does it work differently for you? ;) No, it does not. But the order of "select *" (or PgAdmin's "View data") differs (may differ) from your query *and the order of attributes in my C++/Java class* and I (sometimes) would like them to be the same without much work on my part. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
As a service for the non-dutch speaking people, the abstract of Frank's comment (hi Frank ;-)): On Wed, 2006-01-18 at 09:10 +0100, ir. F.T.M. van Vugt bc. wrote: > Op woensdag 18 januari 2006 08:10, schreef Joost Kraaijeveld: > > Because a lot of tools that I use to manage a database during > > *development* (e.g. PgAdmin) show the columns in an other order than the > > order of attributes in my Java/C++ code. The "logical" order of the > > columns/attributes can change during development. > > Ter info, ik snap je vraag om ondersteuning van dit 'geintje' middels een > regulier system command, maar als het erg belangrijk is voor je of > incidenteel valt het nu ook te editten via de system tables. Iets van > 'ordinal position' in de table/field definitions, maar het is al erg lang > geleden dat ik daar mee gespeeld heb ;) Je kunt dit terugvinden in 'select * > from information_schema.columns', maar je zult zelf even terug moeten zoeken > welke systeemtabellen daar bij zijn betrokken, ik denk overigens dat dit > pg_attribute.attnum is. Denk eraan dat dit soort wijzigingen in de > systeemtabellen zonder garantie komen.. ;) If the order of the columns is *really* important, than one could try to change the system tables. My point is not that I really want it, but that i can see reasons why one could want it. Especially if one must learn how an application interacts with a database, it is nice te be able to see the relation between a class, it's attributes and the tables with the columns. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Top 10 Wishlist
On Tue, 2006-01-17 at 09:52 -0800, David Fetter wrote: > On Tue, Jan 17, 2006 at 10:28:03AM -0600, Tony Caduto wrote: > > As long as we are talking wish lists... > > > > What I would like to see is some way to change the ordering of the > > fields without having to drop and recreate the table. > > Why are you asking us to optimize the 'SELECT *' case which almost > never belongs in production code in the 1st place? Because a lot of tools that I use to manage a database during *development* (e.g. PgAdmin) show the columns in an other order than the order of attributes in my Java/C++ code. The "logical" order of the columns/attributes can change during development. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] index and ilke question
Hi, I want to use the following query: select * from customers where lastname ilike 'jansen%' Explain says it uses a sequential scan on customers while there is an index on lastname (and 'jansen%' contains 1800 entries in a table of 370.000 customers so a index scan should be more logical?). The docs say "However, if your server does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries." This seems to be the case as it does not use the index. Two questions: 1. How can I check if my (PostgreSQL or Linux?) server uses the C locale ? 2. And if it does not the (correct?) C locale is the syntax for a correct index the following, assuming that lastname is of type "text": CREATE INDEX test_index ON prototype.customers (lastname text_pattern_ops); (I tried this, but it did not change anything so I assume that either my assumptions about when to use an index as described above or my syntax are wrong) TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is this a bug or am I doing something wrong?
Hi Richard (s), The problem was reproducable untill I restarted my whole machine. Now it works. Still have no clue what this was about, but thanks to you, now I know where to start looking next time. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Is this a bug or am I doing something wrong?
Hi, I am trying to create a foreign key, followed by creating anindex on the foreign key, using PgAdmin III The command that are generated : ALTER TABLE prototype.orderlines ADD CONSTRAINT fk_orderlines_orders FOREIGN KEY (orderobjectid) REFERENCES prototype.orders (objectid) ON UPDATE RESTRICT ON DELETE RESTRICT; CREATE INDEX fki_orderlines_orders ON prototype.orderlines(orderobjectid); Ont the second command PostgreSQL hangs forever. The prototype.orderlines table is completely empty, the prototype.orders table contains 1.000.000+ records. Is this a known feature and am I doing something wrong or a bug? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [GENERAL] How do I copy part of table from db1 to db2
On Wed, 2005-08-31 at 10:29 -0400, Tom Lane wrote: > Joost Kraaijeveld <[EMAIL PROTECTED]> writes: > If, as seems more likely, there's a mishmash of different encodings then > you are in for some pain. At the minimum you'll have to separate out Yep. The original database (which is copied to an SQL-ASCII PostgreSQL database) is a mishmash of encodings. Actually no official encoding is given for the database. But I managed to get an acceptable (for me that is) import, only losing all the diacritical chars for this moment (see other mail) Thanks for responding, Joost. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] How do I copy part of table from db1 to db2
On Wed, 2005-08-31 at 14:14 +0200, Thomas Pundt wrote: > pg_dump -t artik munttest | recode latin1..utf8 | psql muntfinal Because the source encoding is unknown (the actual source database was an ODBC source without known encoding that was copied with a C++ written to a SQL_ASCII PostgreSQL database) I used no source encoding: "pg_dump -t artik munttest | recode ..utf8 | psql muntfinal" and that worked: no errors. I just lost all diacritical chars as far as I can see (which is a minor and someone else's problem ;-)). Thanks for the sugggestion. Joost ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How do I copy part of table from db1 to db2 (and
On Wed, 2005-08-31 at 12:40 +0200, Roman Neuhauser wrote: > check these man pages: pg_dump(1), pg_restore(1), alter_table(7) I am afraid that the problem is more complex. The original database (which is created with SQL_ASCII) contains invalid byte sequences in some columns (target database created with UNICODE): [EMAIL PROTECTED]:~/postgresql$ pg_dump -t artik munttest | psql muntfinal > output.txt ERROR: invalid byte sequence for encoding "UNICODE": 0xeb207a CONTEXT: COPY artik, line 11705, column omschrijving: "Anna v. Groot Brittannië zi prf 38.61 mm" So I cannot dump/restore/alter table. I was hoping that piping the text from stdout to psql that a valid conversion to unicode would take place but apparently that is not the case. Any other ideas? Joost ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] How do I copy part of table from db1 to db2 (and rename the columns)?
Hi, I want to copy several columns of a source table from db1 to db2, and create the target table and rename the columns in the process. Is that possible in PostgresQL? If so, an example or url for such a command /script would be appreciated... TIA Joost ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to return a resultset/table from a sql function?
OK, to answer my own question (typo's possible, works here ;-)): CREATE TYPE abonnementartikelheader AS (col1 int4, col2 int4); CREATE OR REPLACE FUNCTION getabonnementartikelheader(int4) RETURNS SETOF abonnementartikelheader AS ' select (SELECT COUNT(klantnummer)::int4 FROM abo_klt WHERE abonnement = $1), (SELECT SUM(aantal_abonnementen)::int4 FROM abo_klt WHERE abonnement = $1); ' LANGUAGE 'sql' VOLATILE; Groeten, Joost ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] How to return a resultset/table from a sql function?
Hi,
Is it possible to return the following (parameterized) qyery from a sql or
plpsql function, and if so, what is the syntax?
SELECT{ (SELECT COUNT(klantnummer) FROM abo_klt WHERE
abonnement = $1),
(SELECT SUM(aantal_abonnementen)FROM abo_klt WHERE
abonnement = $1));
Groeten,
Joost
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Query, view join question.
Hi both, Thanks for taking the trouble to help me. Based on your responses I realized that a multi key join should do what I wanted and it does. Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Query, view join question.
Hi Tom, [EMAIL PROTECTED] schreef: > "Joost Kraaijeveld" <[EMAIL PROTECTED]> writes: >> CREATE OR REPLACE VIEW even AS >> SELECT DISTINCT abo_his.klantnummer, > abo_his.artikelnummer, abo_his.code_retour, > abo_klt.aantal_abonnementen, abo_klt.afgewerkt >>FROM abo_his >>JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer >> WHERE abo_his.abonnement = 238 >> ORDER BY abo_his.klantnummer, abo_his.artikelnummer, > abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; > > Okay ... but the view is constraining abo_his.abonnement and > outputting abo_klt.aantal_abonnementen. Why would you assume that > joining on klantnummer would cause these two fields to necessarily be > the same? In the table abo_klt there is no record where abo_klt.abonnement = 238 and abo_klt.afgewerkt > 0: munt=# select * from abo_klt where abonnement = 238 and afgewerkt > 0; ... (0 rows) So I assumed that in no join between abo_his (which has no "afgewerkt" column at all ) and abo_klt (which has 0 records with a "afgewerkt" columns > 0) as created above ( with WHERE abo_his.abonnement = 238) there could be a record with both abonnement = 238 and afgewerk >0. But there are: on the view there are : munt=# select * from even where afgewerkt > 0; . (797 rows) SO I must understand something wrong... Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query, view join question.
Hi Tom, I could give you access to the database itself if needed. But these are the actual tables and view. I hope I will never make any tpo's again to upset you this way. CREATE TABLE abo_his ( klantnummer int4, abonnement int2, artikelnummer int4, omschrijving char(40), nummer_vd_levering int2, artikelnummer_gratis int4, artikelnummer_gratis_2 int4, artikelnummer_gratis_3 int4, omschrijving_gratis_artikel char(40), omschrijving_gratis_artikel_2 char(40), omschrijving_gratis_artikel_3 char(40), datum_selectie date, ordernummer int4, code_retour int2, briefnummer int2, orderbedrag_guldens numeric(8,2), orderbedrag_valuta numeric(8,2), aantal_besteld int4, verzendkosten numeric(8,2), handmatige_toevoeging int2 ) WITH OIDS; CREATE TABLE abo_klt ( klantnummer int4 NOT NULL, abonnement int2 NOT NULL, waardering_klant char(10), gem_betaaltermijn int4, reden_blokkade_oud char(40), aantal_abonnementen int2, herkomst int4, datum_abonnee date, datum_laatste_selectie date, reden_blokkade int2, datum_blokkade date, max_bedrag_lev_jaar numeric(8,2), bestelfrequentie_in_dagen int2, incasso int2, instap_categorie int2, afgewerkt int2, eenmaligemachtigingeerstekeer int2, naar_ander_abo int2 ) WITH OIDS; CREATE OR REPLACE VIEW even AS SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt FROM abo_his JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer WHERE abo_his.abonnement = 238 ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Query, view join question.
Hi all, I have 2 tables: table1 with the columns objectid, refobjectid, commonvaluecol and value1. table2 with the columns objectid, refobjectid, commonvaluecol and value2. A "select * from table2 where commonvaluecol = 123 and value2 > 0" returns no rows. I create a view: create view miracle as select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2 from table1 joing table2 on table1.refobjectid = table2.refobjectid where commonvaluecol = 123 Than I do a "select * from miracle where commonvaluecol = 123 and value2 > 0" This query returns many rows. (How) Is this possible? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Index on a view??
Hi all, Is it possible to create an index on a view? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Generating unique values for TEXT columns
Hi Frank I use the following constructs to generate an objectid's in my database: CREATE SEQUENCE public.tsfraction MAXVALUE 99; CREATE FUNCTION getobjectid() RETURNS text AS ' select((select(to_char(current_timestamp, \'-mm-dd-hh-mm-ss\'))) || (select(to_char((nextval(\'tsfraction\')),\'-FM00MI\' as return; ' LANGUAGE 'sql'; CREATE TABLE public.object ( objectid text NOT NULL DEFAULT getobjectid(), -- other columns omited CONSTRAINT pk_object PRIMARY KEY (objectid) ) WITH OIDS; Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Select number of children of a parent query
Hi John, John Sidney-Woollett schreef: > Useful to add a title to your messages before you post... It escaped before finishing. > How about: > > select parentid, count(*) as number_of_children > from childtable > group by parentid > order by parentid; It works but can you tell me why this works? Is the count(*) over the "group by parentid"? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL]
Hi all, Is it possible to count and display the number of children of a parent in a generic query? parent table: id child table: id, parent_id Example output of the query: parentidnumber_of_children parent1 2 parent2 6 parent3 0 Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] pgsql question
Hi all, I need to to change an ip addres in a plpgsql function from aaa.bbb.ccc.ddd to an sring containing "aaa bbb ccc ddd upload". The following code does not work. It complaines about the replace line (or is it the assignment?): ERROR: syntax error at or near "replace" at character 1 CONTEXT: PL/pgSQL function "getcustomerdownload" line 14 at SQL statement) : CREATE OR REPLACE FUNCTION getcustomerdownload(text, timestamp, timestamp) RETURNS int4 AS ' DECLARE my_ipaddress inet; my_ipaddress_as_text text; BEGIN select into my_ipaddress customertable.ipaddress from ( select customer.objectid, getcustomername(customer.objectid) as customername, customer.ipaddress from customer ) as customertable where customertable.customername like ''$1%''; my_ipaddress_as_text := host(my_ipaddress); replace(my_ipaddress_as_text ,''.'','' ''); return cast( (sum(bytes)/(1024*1024)) as int8) from logs where rule_name = my_ipaddress_as_text | '' upload'' and that_time between cast( abstime($2) as int4) and cast( abstime($3) as int4); END ' LANGUAGE 'plpgsql' VOLATILE; Does anyone have any idea of how to do this? TIA Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] SQL query question
Hi all, I have 2 tables, with a 1-n relation: parent( oid, parent_name) child(oid, child_name, iod_parent) How do I get the parent_names of all parents without a child? TIA Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Bug in queries ??
Hi Jim and Richard, Thank for pointing out something that I should have known. Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Bug in queries ??
Hi all,
I have three questions about 1 table
CREATE TABLE public.logs
(
rule_name varchar(32) NOT NULL,
bytes int8 NOT NULL,
pkts int8 NOT NULL,
hostname varchar(100),
that_time int4 NOT NULL
) WITH OIDS;
Question 1.
If I run the following query:
select cast(min(that_time) as abstime), cast(max(that_time) as abstime),
(sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
where
that_time between cast( abstime('2004-10-1 00:00') as int4) and cast(
abstime('2004-11-1 00:00') as int4)
and
rule_name = 'Incoming 83 50 in' or
rule_name = 'Outgoing 83 50 out'
I expect that the outcome will be between "2004-10-1 00:00" and "2004-11-1
00:00" (the month october). However, I get the following result:
min max Totaal in Megabytes
"2004-09-01 00:00:01+02" "2004-11-23 11:50:01+01"; "82768.623353004456"
The min date is the date of the first entry ever, the max entry the last entry
ever. Why is this?
Question 2.
If I refrase the above query to:
select cast(min(that_time) as abstime), cast(max(that_time) as abstime),
(sum(bytes)/(1024*1024)) as "Totaal in Megabytes" from logs
where
rule_name = 'Incoming 83 50 in' or
rule_name = 'Outgoing 83 50 out'
and
that_time between cast( abstime('2004-10-1 00:00') as int4) and cast(
abstime('2004-11-1 00:00') as int4)
I get a diffent answer (see the Totaal in Megabytes):
min max Totaal in Megabytes
"2004-09-01 00:00:01+02" "2004-11-23 12:00:01+01" "92406.07880896"
My question why is this?
Question 3.
Querying for just "rule_name = 'Incoming 83 50 in'" gives 34990 Megabytes, just
querying for "rule_name = 'Outgoing 83 50 out'" gives 5524 Megabytes. How does
that compare to the queries above?
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [GENERAL] Postgres Win32
> You can run PostgreSQL on Windows NT using the Cygwin > toolkit, but getting > that to work can sometimes be a final project as well. I disagree. Using the instructions on http://people.freebsd.org/~kevlo/postgres/portNT.html it is almost a no-brainer. Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 email: [EMAIL PROTECTED] web: www.askesis.nl
RE: [GENERAL] Working under NT
Follow the instructions on http://people.freebsd.org/~kevlo/postgres/portNT.html and it will run. The regression test will fail on 2 tests: horology an alter_table. The first one seems to be a problem in the CygWin environment, the second on I don't know. Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 email: [EMAIL PROTECTED] web: www.askesis.nl
RE: [GENERAL] Win 9x support
> I see that postgre supports win32, but all I see mentioned is > NT. Does > postgre run on Win 9x? I don't want this as a production environment, > but need it for development. No, as far as I know it does not run on 95/98. Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 email: [EMAIL PROTECTED] web: www.askesis.nl
RE: [GENERAL] How to compile PosttgreSQL on NT]
> > who is andy piper ? > > No idea. I would be glad to add more information to the file > if I knew > anything. Perhaps the author can add something. Don't know him either. But he has a webpage from which his tools (take the ones for CygWin 20.1) can be downloaded: http://www.xemacs.freeserve.co.uk/ Joost Kraaijeveld Askesis B.V. Groenewoudseweg 46 6524VB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 email: [EMAIL PROTECTED] web: www.askesis.nl > -Oorspronkelijk bericht- > Van: Bruce Momjian [mailto:[EMAIL PROTECTED]] > Verzonden: Wednesday, July 07, 1999 6:31 PM > Aan: Jeff MacDonald > CC: Joost Kraaijeveld; Pgsql-General@Postgresql. Org > Onderwerp: Re: [GENERAL] How to compile PosttgreSQL on NT] > > > > I have used this file to take a hack at installing on nt. > > May I suggest more detail on items such as > > > > > > 1. Install the Andy Piper Tools > > > > -- > Bruce Momjian| http://www.op.net/~candle > [EMAIL PROTECTED]| (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, > Pennsylvania 19026 >
