[GENERAL] The first dedicated PostgreSQL forum

Hi everyone, This is my first post on the mailing list :) As of today I have started the first dedicated postgres forum at: http://forums.postgresql.com.au I would much appreciate anyone that has the time to sign up and subscribe to some of the forums ready to answer incoming posts. Any feedba

Re: [GENERAL] More then 1600 columns?

"Clark C. Evans" writes: > What would be most helpful though is if the answer to > this question stop being an attack on the business > requirement analysis, database design skills, and/or > sanity of the requester. It's a limitation of > PostgreSQL's implementation; a deliberate performance >

Re: [GENERAL] index row requires 10040 bytes, maximum size is 8191

"Joshua D. Drake" writes: > On Sat, 2010-11-13 at 09:48 +0800, Craig Ringer wrote: >> Thoughts, folks? Does this matter in practice, since anything you'd want >> to index will in practice be small enough or a candidate for full-text >> indexing? > I have run into this problem maybe 3 times in m

Re: [GENERAL] More then 1600 columns?

Tom, Thank you for the helpful response. It is very reasonable for the PostgreSQL developers to decide this isn't a common enough problem to justify the effort to change and/or the runtime cost. For example, I'd rather advocate for other features myself (such as CUBE). The solution "in the f

Re: [GENERAL] Basic Tutorials for 9.0

Hi, you can try to use phpPgAadmin.(must modify the pg_hba.conf, allow the web app visit db) -- blog: http://chenxiaoyu.org twitter: @nnfish psql: http://psql.info On Thu, Nov 11, 2010 at 9:58 AM, ray wrote: > I just downloaded 9.0 onto my laptop and desktop Windows

Re: [GENERAL] Basic Tutorials for 9.0

On Nov 10, 11:07 pm, robjsarg...@gmail.com (Rob Sargent) wrote: > ray wrote: > > I just downloaded 9.0 onto my laptop and desktop Windows XP machines. > > Nether one could run the stack builder.  Windows would fault any time > > I tried to run it.  I tried inputing the proxy server but that did not

Re: [GENERAL] Help with Stack Builder

On Nov 12, 10:03 am, sachin.srivast...@enterprisedb.com (Sachin Srivastava) wrote: > Can you open the specified URL in the browser? > > On Nov 12, 2010, at 4:24 PM, ray wrote: > > > I just downloaded 9.0 onto my laptop and desktop Windows XP > > machines. .Nether one could run the stack builder.  W

Re: [GENERAL] index row requires 10040 bytes, maximum size is 8191

On Sat, 2010-11-13 at 09:48 +0800, Craig Ringer wrote: > > Thoughts, folks? Does this matter in practice, since anything you'd want > to index will in practice be small enough or a candidate for full-text > indexing? > I have run into this problem maybe 3 times in my whole career, precisely b

Re: [GENERAL] index row requires 10040 bytes, maximum size is 8191

On 13/11/2010 4:52 AM, Joshua D. Drake wrote: On Fri, 2010-11-12 at 15:47 -0500, akp geek wrote: Hi all - I am trying to create an index on character varying field. The column is just character varying with no limit. I am getting the following error " index row requires 10040 bytes

Re: [GENERAL] Seeking advice on database replication.

Thanks Scott and Matthias for your replies! On Nov 11, 2010, at 5:29 PM, Scott Marlowe wrote: > Next time, post questions to the slony mailing list if you get stuck, > we're pretty helpful over there. also look at some of the "my first > replicated db" tutorials for it. > Slony, btw, supports re

Re: [GENERAL] Insert data with greek characters using psql environment

Did you read this: http://www.postgresql.org/docs/current/static/multibyte.html From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of ???SS S Sent: Friday, November 12, 2010 2:58 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Insert

[GENERAL] Insert data with greek characters using psql environment

Hello, I am a new user of PostgreSQL , and i have a problem with encoding settings of psql. Specifically, i want to insert data with greek characters using psql environment. I change the enconding settings of psql, writing the following command: *SET CLIENT_ENCODING TO 'UTF8';* but i can't insert

Re: [GENERAL] Instructions/status of modpglogger

Hi ! Thank you. But this is out my current linux experience ;-) I'll look for PGLOGD or let apache lo to a pipe and write the pipe receiver with mono [and NpgSql]. Thanks! ++mabra -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] O

Re: [GENERAL] More then 1600 columns?

"Mark Mitchell" writes: > I think it's very obvious that Postgres developers have no interest in > going over 1600 columns in the foreseeable future and which forces us > to find creative ways around it but I just don't see why it has to be > this way. Well, it's a tradeoff. Supporting > 1600 co

Re: [GENERAL] More then 1600 columns?

Mark, For these purposes (dynamically properties) we have hstore in PostgreSQL which works very well. Any table in PostgreSQL is a static composite type as well. I don't clearly understand why do you need thousands NULLable columns in a huge tables (which also makes NOT NULL constraints useless) r

Re: [GENERAL] More then 1600 columns?

These tables are created and maintained automatically by software. A table may be created with some initial columns and as respondents take a survey and we collect data each data point is stored in a column and these columns are created on the fly by the application. There is no need to "maint

Re: [GENERAL] More then 1600 columns?

On 11/12/2010 02:25 PM, Clark C. Evans wrote: > On Fri, 12 Nov 2010 21:10 +, "Dann Corbit" wrote: >> If (for access) the single table seems simpler, then >> a view can be used. > > Even if you "partition" the columns in the instrument > over N tables, you still can't query it in a single >

Re: [GENERAL] More then 1600 columns?

I can't imagine how to maintain a database with tables with 1600 columns... I can't imagine how to simple work with this garbage of data via SQL... 2010/11/13 Clark C. Evans > On Fri, 12 Nov 2010 21:10 +, "Dann Corbit" wrote: > > If (for access) the single table seems simpler, then > > a vie

Re: [GENERAL] More then 1600 columns?

On Fri, 12 Nov 2010 21:10 +, "Dann Corbit" wrote: > If (for access) the single table seems simpler, then > a view can be used. Even if you "partition" the columns in the instrument over N tables, you still can't query it in a single result set. The limit is quite deep in PostgreSQL and ex

Re: [GENERAL] More then 1600 columns?

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Clark C. Evans > Sent: Friday, November 12, 2010 12:25 PM > To: Mark Mitchell; 'Tom Lane' > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] More then 1600 co

[GENERAL] ODBC Cursor inserting records appears to lock the database

I have an external script (not maintained by me) that is using an ODBC driver to insert data into one of my databases. The COMMIT appears to take less than one second but closing the cursor takes 13s. It also appears to be locking the database as a whole. Does this sound like a feasible situation w

Re: [GENERAL] Seeking advice on database replication.

I don't know slony that much. I used WAL processing. But since 9.0 I prefer Hot-Streaming replication. This link is a good starting point - besides the documentation. A solution for your problem might be a secon

Re: [GENERAL] index row requires 10040 bytes, maximum size is 8191

On 11/12/2010 12:52 PM, Joshua D. Drake wrote: On Fri, 2010-11-12 at 15:47 -0500, akp geek wrote: Hi all - I am trying to create an index on character varying field. The column is just character varying with no limit. I am getting the following error " index row requires 10040

Re: [GENERAL] index row requires 10040 bytes, maximum size is 8191

On Fri, 2010-11-12 at 15:47 -0500, akp geek wrote: > Hi all - > > I am trying to create an index on character varying field. > The > column is just character varying with no limit. I am getting the > following > error " index row requires 10040 bytes, maximum size is 8191" > What can I

[GENERAL] index row requires 10040 bytes, maximum size is 8191

Hi all - I am trying to create an index on character varying field. The column is just character varying with no limit. I am getting the following error " index row requires 10040 bytes, maximum size is 8191" What can I do the postgres.conf to handle this error? Appreciate your help

Re: [GENERAL] More then 1600 columns?

On Fri, 12 Nov 2010, Tom Lane wrote: > Generally, wanting more than a few dozen columns is a > good sign that you need to rethink your schema design. > What are you trying to accomplish exactly? Generally speaking, yes. However, survey instruments are a very legitimate design where this is not

[GENERAL] Advice on efficient approach to aggregation statistics

Hi - I have a table for which I want to fit an exponential curve to values in one column of the table and compute a correlation statistic on the fit of the values to the exponential curve. The table consists of the following three columns: {"ID","Date","Value"}. The desired result of the exerc

Re: [GENERAL] More then 1600 columns?

Apologizes Tom I did not see that you had answered yes to my question about the hard limit. You have all been very helpful, I will give up on the 1600+ columns and look into using hstore. Cheers - Mark -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-

[GENERAL] Can you check in SQL if a fields can be encoded using specified charset

Is there a function that can say whether a textvalue can be encoded in a particular charset i.e. SELECT FIELD1 FROM TABLE1 WHERE ENCODE_AS_IS08859(FIELD1)=true thanks Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.po

Re: [GENERAL] More then 1600 columns?

"Mark Mitchell" writes: > I know storing in an array is possible but it makes it so much easier to > query the data set when each element is in its own field. I had lots of > comments on why I should not do this and the possible alternatives and I > thank everyone for their input but no one ans

Re: [GENERAL] Help with Stack Builder

Can you open the specified URL in the browser? On Nov 12, 2010, at 4:24 PM, ray wrote: > I just downloaded 9.0 onto my laptop and desktop Windows XP > machines. .Nether one could run the stack builder. Windows would > fault any time I tried to run it. I tried inputing the proxy server > but tha

Re: [GENERAL] More then 1600 columns?

On 11/12/2010 08:38 AM, Mark Mitchell wrote: > Yes I understand that this is "bad design" but what we are doing is storing > each form field in a survey in its own column. For very long surveys we end > up with thousands of elements. > I know storing in an array is possible but it makes it so

Re: [GENERAL] More then 1600 columns?

hstore does look like it could work out better then a mother load of columns like we have now so long as the index support is good and there are no limits on value length. It seems that the limit was lifted in 9.0? Is that true? I was not aware of hstore when we started using postgres, that’s for

Re: [GENERAL] More then 1600 columns?

Yes I understand that this is "bad design" but what we are doing is storing each form field in a survey in its own column. For very long surveys we end up with thousands of elements. I know storing in an array is possible but it makes it so much easier to query the data set when each element is

Re: [GENERAL] Help in Getting Started

From the shell, I use something like this : psql -d datebasename -U username -c "COPY tablename FROM 'datefile' WITH DELIMITER ',' NULL '' ;" You'll have to add the path to the psql binary and to the csv file -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-gen

Re: [GENERAL] Instructions/status of modpglogger

Hallo ! Much thanks, I'll give it a try ! I have the build tools, but never used them. I am new to linux development and think, I hard time will come ;-) br++mabra -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jeff

Re: [GENERAL] NOTIFY/LISTEN why is not a callback as notice processing.

Thanks. But also I want to use postgresql and gtk with python or with c. I think, I need to make something like QSocketNotifier with callbacks instead of signals/slots. Well, why does libpq accumulate notifi and commit developer to create loop? select() PQconsumeInput(conn) while ((notify = P

[GENERAL] Help with Stack Builder

I just downloaded 9.0 onto my laptop and desktop Windows XP machines. .Nether one could run the stack builder. Windows would fault any time I tried to run it. I tried inputing the proxy server but that did not help. This was at work where there is a proxy server. I tried this at homw wiith the

[GENERAL] Help in Getting Started

I just downloaded 9.0 onto my laptop and desktop Windows XP machines. I would like to move data from an Excel spreadsheet into a database. I understand that a method is to export the worksheet as a CVS and then 'copy' into the database. I have not been able to get function to happen in the shell.I

Re: [GENERAL] Considering Solid State Drives

On Thu, Nov 11, 2010 at 4:35 PM, Vick Khera wrote: > On Thu, Nov 11, 2010 at 1:42 AM, Allan Kamau wrote: >> After googling I found little resent content (including survival >> statistics) of using SSDs in a write intensive database environment. >> > > We use the Texas Memory RAMSan-620 external d

Re: [GENERAL] ignore errors for COPY [solved]

On 11/11/2010 08:15 PM, Guillaume Lelarge wrote: I would like to create a specific database on a another location (not in "data_directory = '/var/lib/postgresql/8.3/main'" ). I was wondering if I have to run initdb -D /other/path/ of if there is another way To create another database in anot

Re: [GENERAL] More then 1600 columns?

Sounds like semi-structured data handling. For this tasks hstore would be ideal, IMO. 2010/11/12 John R Pierce > I looked up the OP's domain.They develop medical research data analysis > software. > > > That sort of software and the kinds of data analysis they do tortures SQL > databases.

Re: [GENERAL] More then 1600 columns?

On Fri, Nov 12, 2010 at 10:18:31AM +0100, Thomas Kellerer wrote: > The hstore module would also be a viable alternative - and it's indexable > as well. That's what the post I replied to mentioned :) Also, when I looked at hstore, it had a limitation on its length which made it a little dangerous

Re: [GENERAL] More then 1600 columns?

Peter Bex, 12.11.2010 08:36: What can also work extremely well is storing the data in an array. If you need to access the array based on more meaningful keys you could store key/index pairs in another table. The hstore module would also be a viable alternative - and it's indexable as well. T

Re: [GENERAL] More then 1600 columns?

I looked up the OP's domain.They develop medical research data analysis software. That sort of software and the kinds of data analysis they do tortures SQL databases. These 1600+ element rows are likely very sparse. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] More then 1600 columns?

2010/11/12 Dmitriy Igrishin : > Well, it's possible to create GIN indexes on arrays of built-in data types > without extra efforts, because of "GIN" access method has defaul > operator class. But it's not true for GiST: now you have a true :) you need to install intarray contrib module for GiST

Re: [GENERAL] More then 1600 columns?

Well, it's possible to create GIN indexes on arrays of built-in data types without extra efforts, because of "GIN" access method has defaul operator class. But it's not true for GiST: SELECT am.amname AS index_method, opc.opcname AS opclass_name FROM pg_am am, pg_opclass opc WHERE opc.opcmethod =

Re: [GENERAL] More then 1600 columns?

Hey Pavel, Really ? I am sorry, I'll try ! 2010/11/12 Pavel Stehule > 2010/11/12 Dmitriy Igrishin : > > Hey Peter, > > > > Unfortunately, there is no indexes on arrays (only on expressions). > > there are indexes on arrays - you can use a GiST, Gin indexes. > > regards > > Pavel > > > > With hs

Re: [GENERAL] More then 1600 columns?

2010/11/12 Dmitriy Igrishin : > Hey Peter, > > Unfortunately, there is no indexes on arrays (only on expressions). there are indexes on arrays - you can use a GiST, Gin indexes. regards Pavel > With hstore we can easily create GiST index for effective access. > > 2010/11/12 Peter Bex >> >> On