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

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

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

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

[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

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 >

[GENERAL] In-place conversion of type bool

y 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

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

ecution_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 t

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

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

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

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

Re: [GENERAL] PostgreSQL crashes on Windows 2003

27;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 administrato

Re: [GENERAL] PostgreSQL crashes on Windows 2003

ut 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 Ni

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

[GENERAL] PostgreSQL crashes on Windows 2003

up 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

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

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 explai

[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. Molu

[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

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. Moluk

[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

ng 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-388806

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.c

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;

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

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

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

d "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 Nijmeg

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

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 d

Re: [GENERAL] PostgreSQL Top 10 Wishlist

an 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.ask

[GENERAL] index and ilke question

his, 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-mai

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

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 data

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

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

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 WHER

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

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 >

Re: [GENERAL] Query, view join question.

lt 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-518

[GENERAL] Query, view join question.

" 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)

[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

ect(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; Gro

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

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 -

[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

[GENERAL] pgsql question

address_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 ' L

[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

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

[GENERAL] Bug in queries ??

le_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 As

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 Askesi

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 65

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

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