(val) from x;
select array_agg(val) from x;
Well, everything works except the last statement. In 8.4 I get
ERROR: could not find array type for data type integer[]
I was hoping for [1,2,3,4,5] or at least [1,2,3,3,4,5] or even [[1,2,3],[3,4,5]]
Am I not understanding something?
Thanks,
TJ O'Do
a fn that
returns integer[]
AND I can't return a two-dimensional array. Not a happy 9.0 camper.
Anyone know of any plans to 9.0 plpython to support multi-dimensional arrays?
TJ O'Donnell
On Tue, Dec 21, 2010 at 4:02 PM, Adrian Klaver wrote:
> On Tuesday 21 December 2010 3:25:48
imensional arrays in plpython?
TJ O'Donnell
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
json_encode. I'm all set now.
Thanks for the help!
TJ O'Donnell
On Thu, Sep 23, 2010 at 1:18 AM, Raymond O'Donnell wrote:
> On 23/09/2010 02:00, Adrian Klaver wrote:
>>
>> On Wednesday 22 September 2010 5:40:55 pm David Wilson wrote:
>>>
>>> On W
Many frameworks do not natively recognize "t" as true and "f" as false.
I'm using php, json_encode and extjs.
Is it possible to cause the default output of boolean values to be something
other than "t" and "f", say "true" and "false" or even 1 and 0?
Of course I can do this for an individual query
It seems you haven't done anything you need to save, so why
not save time and just reinstall postgres?
TJ
Hi
Â
I started the installation of postrgres got distracted and then started again but forgot my password
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make c
should use https, and not hard-code
passwords...all the usual security precautions.
Anything ready? Hard to say without knowing more about your
specific needs.
TJ O'Donnell
http://www.gnova.com
I'd like to launch some sql script asynchronously from a web app and
have some kind of feed
This is a very cool animation for your
amusement, amazement and edification.
http://www.vimeo.com/1081680
TJ O'Donnell
http://www.gnova.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/
I use arrays of integers, double or numeric, and text.
They are 1 or 2 dimensional.
They are usually limited to <100 elements in
one dimension and <10 in the other.
TJ O'Donnell
http://www.gnova.com
---(end of broadcast)---
TIP 1:
I think decode('your string', 'escape') might be what you need.
It returns bytea when 'your string' is type text or varchar.
TJ
http://www.gnova.com
Is there a function that will do what I want to convert the datatype
without having to create a CAST that PostgreSQL doesn't have natively?
How e
r than databases, in that should disk
corruption occur, most of the mail is likely to be unaffected, and any
that is damaged can usually be recovered."
How naive (optimistic?) is it to think that "the database" can
replace "the filesystem"?
TJ O'Donnell
http://www.g
I wholeheartedly support the approach BJ is advocating.
The notion that methods (functions) and variables (tables)
can be defined together is a very useful OO approach.
I too find it difficult to recall which functions "belong"
to which tables. Of course, some of my functions are very
generic and
s back into a R dataframe.
There is a nice way to dump a dataframe back into a table which
is created for you, with columns and datatypes as appropriate.
Hope this helps.
TJ O'Donnell
http://www.gnova.com/
I'd like to get R to talk to postgresql, but my head's spinning among a
web of
> We've PostgreSQL database, with SQL_ASCII or LATIN1 encoding. We would
> like to migrate them to UNICODE. Is there some contributed/available
> script, or this is something we should do at hand?
I had a similar problem migrating from 7.4 to 8.1 and wanting to
go from sql_ascii to utf8. I did the
and my orsum without INITCOND returned the proper OR of the remaining values
when including all rows in the aggregate. it did not return null.
maybe the aggregator (whoever,whatever that is) handles null args differently,
not calling the func when the arg is null?
pg8.1.3
TJ
Florian G. Pf
"TJ O'Donnell" <[EMAIL PROTECTED]> writes:
Presumably, the only reason I would HAVE TO recompile
is when some header file changes. Is there any guarantee
that header files DO NOT change, for example from
7.4.5 to 7.4.8 or even 7.4.12? Can I assume that header
fi
I have several c-language extensions to postgres that I
distribute. I generally have to recompile my code
with a new release of postgres, although sometimes it
appears that it functions properly (passes my tests at least)
without a recompile. Since my users are all on various
releases of postgre
erformance diminishes with large tables
(we’ll be going to upwards of hundreds of millions of rows)."
Is this pure speculation, opinion, known fact?
Does anyone know of measured performance of postgres
vs. oracle, specifically with very large tables?
TJ O'Donnell
www.gnova.com
--
my func is rather expensive. Is func(x) evaluated three times
in the above statement? Would it be evaluated only once if I used
select func(x)group by 1 order by 1
TJ O'Donnell
www.gnova.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Slashdot had this today.
http://searchopensource.techtarget.com/originalContent/0,289142,sid39_gci1172668,00.html
TJ O'Donnell
www.gnova.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desi
any purchases were intended simply to
confuse
people about the future of MySQL and therefore ecourage them to select Oracle?
TJ O'Donnell
http://www.gnova.com/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
decode(your_string,'escape') will convert text to bytea
You can just use this as a sort of type cast, or:
1. Create a new bytea column in your table
2. Update the table, setting the newcolumn = decode(oldcolumn,'escape')
3. Drop the oldcolumn (or not)
TJ
Date: Wed, 01 Feb 2006 10:04:36 +
Fr
ny default user/password?
Is this an issue we should be concerned about, at some level?
TJ O'Donnell
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
data.
bitmask is precomputed/stored as bit(1024) = fingerprint(datacolumn)
contains(a,b) returns bool as 'select b=(a&b);'
This works well because matches() is an expensive functions.
But it would work better if bitmask could be indexed, no?
TJ O'Donnell
I would unix su, edit pg_hba.conf to allow open access
temporarily, connect to pg and change the posgres password.
Don't forget to change pg_hba.conf back again to password
protect the db!
TJ
I have lost the superuser (user postgres) password, but I still have
the unix root password. Is there
I was needing something similar last week, not to throw an error,
but to catch an error when 99% of my column's data is real, but some
is not (e.g. '1.2-1.4' or '>32.7'). I wanted to do it in pure
SQL, but settled on this. Is there a way to do this in pure
SQL (so it will be faster)?
Declare x
I have begun to use some static variables in my c-language
functions to maintain state (3rd party licensing issues) during
the course of a session (postgres process, spawned by postmaster).
When I use dynamic loading of my .so,
each session is independent, with its own static variables.
Will the s
I have begun to use some static variables in my c-language
functions to maintain state (3rd party licensing issues) during
the course of a session (postgres process, spawned by postmaster).
These are declared static outside the scope of any function.
(is global the correct term anymore?)
When I us
i in the text range, one could
say there is a text equivalent, but in general not.
I think you're looking for some kind of printf-type %b function? Maybe
someone has written one, or maybe you'll have to do so. If you do,
how about a hex representation of bit(n) - I could use th
I've been developing c-language functions in 7.4.5 and 7.4.8.
I had not encountered a need to recompile, because of pg .h file,
or other differences. I expected a need to do so when upgrading
to 8.0.3. But I forgot to do so and it worked!! I figured I
lucked out, a suspicion which was confirmed
Alvaro Herrera wrote:
On Mon, Aug 15, 2005 at 01:15:03PM -0700, TJ O'Donnell wrote:
While writing installation instructions for my new PostgreSQL product, I
found myself
writing the following sentence:
"For first time users, we recommend building the gnova database,
since it has
While writing installation instructions for my new PostgreSQL product, I found
myself
writing the following sentence:
"For first time users, we recommend building the gnova database,
since it has no impact on other databases."
Is this really true? Of course, my gnova database will take some dis
How can I append to the current search_path?
How can I put $user (back) into the search path?
These commands all fail:
set search_path $user,public,gnova;
set search_path \$user,public,gnova;
set search_path '$user',public,gnova;
Ideally, I would like something like PATH=${PATH}:/usr/local/bi
Alvaro Herrera <[EMAIL PROTECTED]> writes:
On Tue, Aug 09, 2005 at 04:01:33PM -0400, Tom Lane wrote:
Yeah, you can store the pg_class OID of the table,
Maybe it is possible to use a column of type regclass to store it. Not
sure exactly what advantage that would give, but it's an idea.
> TJ O'Donnell wrote:
>> I have many different tables that I want to keep track of.
>> So, I thought of a master table with those table names in it.
>> But, to maintain this (suppose a table changes
>> its name, gets dropped) I want to have some kind of referentia
I have many different tables that I want to keep track of.
So, I thought of a master table with those table names in it.
But, to maintain this (suppose a table changes
its name, gets dropped) I want to have some kind of referential
integrity - the way foreign keys and constraints do.
What could I
I know of the four user's group listed at http://pugs.postgresql.org/
I'm interested in starting one in the San Diego area.
If there are others in San Diego who are interested,
please get in touch with me.
Thanks,
TJ O'Donnell
[EMAIL PROTECTED]
---(e
It is clear from the manual that c-functions can cause the dynamic loading
of .so's and that subsequent usage in the same database session will use
the already loaded function. But, when the session is done, will the loaded
function remain in the server for subsequent sessions?
When/how does a dyn
ichael Fuhr wrote:
On Mon, Jul 11, 2005 at 08:16:17PM -0700, TJ O'Donnell wrote:
CREATE or REPLACE FUNCTION cansmiles(varchar) RETURNS varchar
AS 'gnova', 'oe_cansmiles' LANGUAGE 'c' IMMUTABLE STRICT;
requires preloading of oe_chem.so to work.
Is there any
Until now I have been content to have the superuser CREATE FUNCTION...LANGUAGE
'C'
because I noticed that ordinary users could not:
ERROR: permission denied for language c
I would like to allow a user to create C language functions, but can't
find just which privilege I need to grant. The user
I'm using bit and bit varying datatypes to store chemical
"fingerprints" and need various functions to operate on
these datatypes. The logical operators (and, or, not)
and the shift operators, as well as length and octet-length
all work fine on bit types. I was hoping for a manual entry
that spec
I have N-bit data pairs. I want to write a c-language function
which compares bits set in each. N is typically 512, but could be other.
I can store this as bit varying(512) or bytea(64). I can't decide which.
Here are the questions that concern me.
1) will each take the same storage?
2) can I pa
I understand the value of indexes and of ANALYZE for the efficient use of them.
In the following statement, you can see that the index scan is being used.
Even though it takes 80 seconds (for a 1.25 million row table), it is
much faster than without the index.
But, if I repeat this search, it speed
the histograms that Analyze creates?"
Are they available anywhere? The docs mention them (bins) and I
was hoping Analyze Verbose would show them to me.
TJ
Tom Lane wrote:
"TJ O'Donnell" <[EMAIL PROTECTED]> writes:
This I don't get. Why is an index scan not used? Isn&#
I have a table of about 5 million rows, 24 columns.
Integer column _c is BTREE indexed (as is _n, _o and 3 others).
This I understand and like:
Explain Analyze Select count(smiles) from structure where _c = 30
Aggregate (cost=105595.11..105595.11 rows=1 width=32) (actual
time=17.722..17.724 rows
I realize that using OR will not result in an index scan.
I will never be interested in a OR condition for the kinds
of searches I use. In my Select statements, I always name
every column of the multi-column index in same order that
they were named when creating the index. I always use
the >= con
n't a straight binary tree, I don't think that having highly
> dissimilar data in the
> index should be a problem.
>
> Do you have data or experience that shows otherwise?
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Beh
Does anyone know of a tools that allows one to visualize
the tree created by a multi-column B-tree index?
A picture of a tree with branches, showing how "branchy" the
tree is would be great.
I'm wondering how well I've "clustered" the data in my table
using the multi-column index. In other words,
seen so far.
TJ
Tom Lane wrote:
"TJ O'Donnell" <[EMAIL PROTECTED]> writes:
The only type of search will be of the type:
Select smiles,id from structure where oe_matches(smiles,'c1c1C(=O)N');
You haven't really said much about how you expect an index to be
s which
would speed up the search), is there a way I can create and maintain
a table related to the table containing the smiles - and all
behind the scenes so the sql user is unaware of this.
My thought was that an index is like that and I might borrow some
of the internal uses of indexing for m
To add to my last followup posting, the only way I use
oe_mathces(smiles) is in something like the following:
Select smiles,id from structure where oe_matches(smiles,'CCOC');
The match string 'CCOC' in this case, varies widely according to
the needs of the user during that session.
It is analogous
table related to the table containing the smiles - and all
behind the scenes so the sql user is unaware of this.
My thought was that an index is like that and I might borrow some
of the internal uses of indexing for my purposes.
TJ O'Donnell
[EMAIL PROTECTED]
Pierre-Frédéric Caillaud wrote:
ese details from the user - meaning
they would not have to create/update these additional columns?
Thanks,
TJ O'Donnell
---(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
53 matches
Mail list logo
