[SQL] Indexing an array?

[Silke Trissl]

Hi,

I have a problem with arrays in Postgres. I want to create a really
large array, lets say 3 billion characters long.

As far I could read from the documentation - this should be possible.
But my question is, is there a kind of index on the array.

Lets say, I want to get element 2,675,345,328. Does Postgres have to
load the entire array into memory and then run through the 2.6 billion
characters to return the one I want or does Postgres have an index - as
where to find this element on disk?

Any advice is welcome and thanks in advance

Silke Trißl




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Indexing an array?

[Josh Berkus]

Silke,

> I have a problem with arrays in Postgres. I want to create a really
> large array, lets say 3 billion characters long.

Change your application design.  

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Indexing an array?

[Dmitri Bichko]

Really seems like that array should be a separate table, then Postgres would 
definitely know how to index it.

Dmitri

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Silke Trissl
> Sent: Thursday, September 08, 2005 12:14 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Indexing an array?
> 
> 
> Hi,
> 
> I have a problem with arrays in Postgres. I want to create a 
> really large array, lets say 3 billion characters long.
> 
> As far I could read from the documentation - this should be 
> possible. But my question is, is there a kind of index on the array.
> 
> Lets say, I want to get element 2,675,345,328. Does Postgres 
> have to load the entire array into memory and then run 
> through the 2.6 billion characters to return the one I want 
> or does Postgres have an index - as where to find this 
> element on disk?
> 
> Any advice is welcome and thanks in advance
> 
>   Silke Trißl
> 
>   
> 
> 
> ---(end of 
> broadcast)---
> TIP 6: explain analyze is your friend
> 
The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. Any 
review, retransmission, dissemination or other use of, or taking of any action 
in reliance upon, this information by persons or entities other than the 
intended recipient is prohibited. If you received this in error, please contact 
the sender and delete the material from any computer

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Indexing an array?

[Tom Lane]

Silke Trissl <[EMAIL PROTECTED]> writes:
> I have a problem with arrays in Postgres. I want to create a really
> large array, lets say 3 billion characters long.

Forget it --- quite aside from indexing inefficiencies, the max size of
an array (or any other single field) is just 1Gb.  Don't try to use
arrays to replace tables.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Statistics from Sequences

[Michael Fuhr]

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Thu, Sep 08, 2005 at 02:32:12PM -0300, Joÿffe3o Carvalho wrote:
> Can you give me a help about:
> 
> >The min value
> >The max value
> >The increment value
> >The last used number

The example I posted shows how to obtain those values:

CREATE SEQUENCE fooseq;
SELECT * FROM fooseq;

If you want only particular columns then specify them:

SELECT min_value, max_value, increment_by, last_value FROM fooseq;

-- 
Michael Fuhr

---(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: [SQL] column names, types, properties for a table

[thomas.silvi]

  Hello,
   there is the view "columns" in the schema "information_schema" that 
can give you most of the informations you need

   ( for PosgreSQL version >= 7.4.8 if I'm right).

   SELECT   *
   FROM information_schema.columns
   WHEREtable_name = 'mytable';

   See 
http://www.postgresql.org/docs/8.0/interactive/infoschema-columns.html#AEN26185
 or  
http://www.postgresql.org/docs/8.0/static/infoschema-columns.html#AEN26185


  Regards,
Thomas

Roger Tannous a écrit :

Hi, 


Is it possible to issue an SQL query that lists column names, types (int,
varchar, boolean, etc.), properties (like NOT NULL or UNIQUE) 
for a given table name ?



Regards,
Roger Tannous.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

 




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Statistics from Sequences

[Joÿffffffffffe3o Carvalho]

Michael Fuhr <[EMAIL PROTECTED]> escreveu:

[Please copy the mailing list on replies so others can participatein and learn from the discussion.]On Thu, Sep 08, 2005 at 02:32:12PM -0300, Joÿffe3o Carvalho wrote:> Can you give me a help about:> > > The min value> > The max value> > The increment value> > The last used numberThe example I posted shows how to obtain those values:CREATE SEQUENCE fooseq;SELECT * FROM fooseq;If you want only particular columns then specify them:SELECT min_value, max_value, increment_by, last_value FROM fooseq;-- Michael Fuhr---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly
Thank you very much, you've been a great help.
Regards
		 
Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!

[SQL] a "catch all" type ... such a thing?

[Marc G. Fournier]

Are there any data types that can hold pretty much any type of character? 
UTF-16 isn't supported (or its missing from teh docs), and UTF-8 doesn't 
appear to have a big enough range ...



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[SQL] how to replace

[Michael Höller]

Hello,

I initially thought this is simple.. I want to relpace a character to
nothing. Eg. relace "B" to "" ->  ABCD to ACD.

All me approches vaild but I am sure that I have seen it already and
think it was not tricky..

Can someone please help me ?

Thanks a lot 
Michael




---(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: [SQL] how to replace

[Owen Jacobson]

Michael Höller wrote:

> Hello,
> 
> I initially thought this is simple.. I want to relpace a character to
> nothing. Eg. relace "B" to "" ->  ABCD to ACD.
> 
> All me approches vaild but I am sure that I have seen it already and
> think it was not tricky..
> 
> Can someone please help me ?

From the manual:

replace(string text, from text, to text) returns text
  Replace all occurrences in string of substring from with substring to.



To replace only on output, for example:

SELECT replace (sometextcolumn, from 'B', to '') FROM sometable;

To replace the data,

UPDATE sometable SET sometextcolumn = replace (sometextcolumn, from 'B', to '');

HTH,
Owen

---(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: [SQL] how to replace

[Anthony Molinaro]

Michael,
  You practically solved it yourself in the subject of the email ;)

 select replace('abcd','b','') from your_table;

Hope that helps,
  Anthony

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of "Michael Höller"
Sent: Thursday, September 08, 2005 5:25 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] how to replace

Hello,

I initially thought this is simple.. I want to relpace a character to
nothing. Eg. relace "B" to "" ->  ABCD to ACD.

All me approches vaild but I am sure that I have seen it already and
think it was not tricky..

Can someone please help me ?

Thanks a lot 
Michael




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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Statistics from Sequences

[Michael Fuhr]

[Again, please copy the mailing list on replies so others can
participate in and learn from the discussion.]

On Thu, Sep 08, 2005 at 06:25:33PM -0300, Joÿffe3o Carvalho wrote:
> There's one thing. If the sequence name was fooseq in uppercase
> (FOOSEQ) it does not work. In that case it returns the error: ERROR:
> relation "fooseq" does not exist.

This isn't a sequence issue, it's an identifier issue.  See "Why
are my table and column names not recognized in my query?" in the
FAQ and "Identifiers and Key Words" in the documentation:

http://www.postgresql.org/docs/faqs.FAQ.html#4.20
http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

-- 
Michael Fuhr

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] queries problems

[Joÿffffffffffe3o Carvalho]

The problem here is to compare int2vector with int2.
 
Regards
João Carvalho
Michael Fuhr <[EMAIL PROTECTED]> escreveu:
[Please copy the mailing list on replies so others can contributeto and learn from the discussion.]On Thu, Sep 01, 2005 at 10:33:44PM -0300, João Carvalho wrote:> Michael Fuhr <[EMAIL PROTECTED]>escreveu:> > http://www.postgresql.org/docs/8.0/static/monitoring.html> > http://www.postgresql.org/docs/8.0/static/catalogs.html> > Tanks a lot, it is part of that information in there. I resolved > the schema problem and probably the toast table problem. but I still> can´t get any information in there about the users of the locks and> wich atributes are part of each index.For locks, you can join pg_locks.pid with pg_stat_activity.procpidand get user names from
 pg_stat_activity.usename.http://www.postgresql.org/docs/8.0/static/view-pg-locks.htmlhttp://www.postgresql.org/docs/8.0/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLEFor index attributes, see pg_index.indkey and pg_attribute.attnum,or the pg_get_indexdef() function.http://www.postgresql.org/docs/8.0/static/catalog-pg-index.htmlhttp://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.htmlhttp://www.postgresql.org/docs/8.0/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE-- Michael Fuhr---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
		 
Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!

Re: [SQL] queries problems

[Michael Fuhr]

On Thu, Sep 08, 2005 at 10:33:48PM +, Joÿffe3o Carvalho wrote:
> The problem here is to compare int2vector with int2.

Presumably you're talking about joining pg_index.indkey against
pg_attribute.attnum -- is that correct?  Will pg_get_indexdef() not
work for your needs?

I don't know if there's an easier way to check if an int2 is a
member of an int2vector, but you could write a function to convert
an int2vector to an int2 array and then use an "= ANY" expression.
The following seems a little ugly, but it works for me in simple
tests:

CREATE FUNCTION int2vector2array(int2vector) RETURNS int2[] AS $$
BEGIN
RETURN string_to_array(textin(int2vectorout($1)), ' ');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

SELECT 2::smallint = ANY (int2vector2array('1 2 3'));
 ?column? 
--
 t
(1 row)

SELECT 4::smallint = ANY (int2vector2array('1 2 3'));
 ?column? 
--
 f
(1 row)

Improvements or other suggestions welcome.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] queries problems

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> I don't know if there's an easier way to check if an int2 is a
> member of an int2vector, but you could write a function to convert
> an int2vector to an int2 array and then use an "= ANY" expression.

FWIW, as of 8.1 an int2vector *is* an int2 array, so = ANY just works.

regards, tom lane

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

[SQL] Numeric Columns

[miwalsh]

I need to make a column that is capable of holding numeric values along with 
certain modifiers such as "<" or ">". The column needs to be searchable by 
numbers. For example, if someone searches for values lower than 10.0 the 
column should return the relevant values. However, the column needs to be able 
to hold values like "<0.05".

Is this possible or do I have to change the schema?


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Numeric Columns

[Tom Lane]

miwalsh <[EMAIL PROTECTED]> writes:
> I need to make a column that is capable of holding numeric values along with 
> certain modifiers such as "<" or ">". The column needs to be searchable by 
> numbers. For example, if someone searches for values lower than 10.0 the 
> column should return the relevant values. However, the column needs to be 
> able 
> to hold values like "<0.05".

contrib/seg might do more or less what you're looking for, but none of
the standard datatypes will.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] how to replace

[A. Kretschmer]

am  08.09.2005, um 23:25:05 +0200 mailte "Michael Höller" folgendes:
> Hello,
> 
> I initially thought this is simple.. I want to relpace a character to
> nothing. Eg. relace "B" to "" ->  ABCD to ACD.

In the new version 8.1 there are a regex_replace() - function. A other
solution: write your own function with plperl.


Regards, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] a "catch all" type ... such a thing?

[Eugene E.]

Marc G. Fournier wrote:


Are there any data types that can hold pretty much any type of 
character? UTF-16 isn't supported (or its missing from teh docs), and 
UTF-8 doesn't appear to have a big enough range ...


PLEASE Note: type of caracter is generally not a matter of _datatype_

---(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] a "catch all" type ... such a thing?

[Michael Glaesemann]

On Sep 9, 2005, at 3:09 PM, Eugene E. wrote:


Marc G. Fournier wrote:

Are there any data types that can hold pretty much any type of  
character? UTF-16 isn't supported (or its missing from teh docs),  
and UTF-8 doesn't appear to have a big enough range ...




PLEASE Note: type of caracter is generally not a matter of _datatype_


That said, perhaps BYTEA would work. Not exactly the same as some  
kind of text string though, as you could only use the BYTEA functions  
for data manipulation. The SQLASCII encoding is *very* accepting, but  
has its own issues, which a look in the archives will provide more info.


Michael Glaesemann
grzm myrealbox com



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq