[GENERAL] Exporting 1 text column from all rows from a table to a file per row?

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

[GENERAL] Example of full text search ranking function somewhere?

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

[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

[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

[GENERAL] In-place conversion of type bool

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

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

[GENERAL] Out of memory error, FreeBSD 6.2, PostgreSQL 8.2.4

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

[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

[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

[GENERAL] psql : Error: Cannot stat /pgdata/8.2/main

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

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

[GENERAL] PostgreSQL crashes on Windows 2003

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

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

Re: [GENERAL] PostgreSQL crashes on Windows 2003

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

Re: [GENERAL] PostgreSQL crashes on Windows 2003

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

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

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

[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

[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

[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

Re: [GENERAL] Strange query results with invalid multibyte

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

[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

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

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

[GENERAL] Is what I want possible and if so how?

, 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

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

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

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

Re: [GENERAL] PostgreSQL Top 10 Wishlist

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

[GENERAL] index and ilke question

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

[GENERAL] Is this a bug or am I doing something wrong?

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

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

[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

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

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

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

[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

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

[GENERAL] Query, view join question.

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

Re: [GENERAL] Query, view join question.

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

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

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

[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

Re: [GENERAL] Generating unique values for TEXT columns

(\'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

[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

Re: [GENERAL] Select number of children of a parent query

? 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

[GENERAL] pgsql question

*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

[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

[GENERAL] Bug in queries ??

: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

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

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

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

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

RE: [GENERAL] How to compile PosttgreSQL on NT]

://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