On 23/05/09 06:05, Rodrigo E. De León Plicet wrote:
>>> Is there a better way?
>>
>> Yeah, natural keys.
>
> +1.
>
> Also, what Ben described reeks of EAV.
>
> Ben, please read:
>
> http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html
Ah yes, the great Celko. I ha
Greets,
I have recently been trying out table partitioning on 8.3.7 (on our
master->slaves cluster) and am experiencing an unexpectedly high file
descriptor penalty ('high', of course, being a relative term).
Before partitioning there were about 70 tables and lots of indexes and
file desc
On Fri, May 22, 2009 at 10:38 PM, Scott Marlowe wrote:
> On Fri, May 22, 2009 at 4:10 PM, Benjamin Smith
> wrote:
>>
>> Is there a better way?
>
> Yeah, natural keys.
+1.
Also, what Ben described reeks of EAV.
Ben, please read:
http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-ta
On Fri, May 22, 2009 at 4:10 PM, Benjamin Smith
wrote:
> I have some questions about the best way to best use foreign keys in complex
> schemas. It's becoming cumbersome to manage a large set of foreign keys - is
> there a better way?
>
> // FOUNDATIONAL //
>
> Let's say that you want to keep addr
Hello postgres community,
I am running postgresql 8.3 on Vista 64 and trying to do some
performance tuning to make better use of my system resources. Anytime I
make any changes to the postgresql.conf file, I cannot connect to the
server - I get a "Server Doesn't Listen" error. I also tried v
Brandon Metcalf wrote:
> Assume I have an UPDATE statement that looks like
>
> UPDATE foo
> SET
> pattern = '$pattern',
> shape = '$shape',
> length = $length,
> comment = '$comment'
> WHERE foo_id = $foo_id
>
> and length is defined as NUMERIC. Is there any
Yes, it is correct. Issue appers not significant at first glance.
However, if you take into account users who are trying to migrate
their apps from MSSQL or Oracle and not willing to rewrite their apps
(relying on the OLEDB driver), you discover that it is not that small
issue. The driver j
I have some questions about the best way to best use foreign keys in complex
schemas. It's becoming cumbersome to manage a large set of foreign keys - is
there a better way?
// FOUNDATIONAL //
Let's say that you want to keep addresses, and one of the values that you need
to keep is the state
Hi Scott,
Brilliant, that's exactly what I wanted.
I guess the only thing that worries me is if the table being aggregated
is very large, I assume this solution will use a lot of memory - since
it creates an array containing all of the values in the target
expression - but I suspect in my applica
On Friday 22. May 2009, Alvaro Herrera wrote:
>Hey, if you want to add your functions to
> http://wiki.postgresql.org/wiki/Snippets , that would be great.
+1
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
--
Sent via pgsql-general maili
arta...@comcast.net wrote:
> Excellent observation Dave. Sometimes I can't see outside of the box I'm in.
> And at the time I was focusing on text so statistics was in another box. I've
> update post with final functions for mode(), median() and range().
Hey, if you want to add your functions to
On Fri, May 22, 2009 at 05:23:47PM +, arta...@comcast.net wrote:
>
> On Fri, May 22, 2009 at 03:23:07PM +, arta...@comcast.net wrote:
> > > I want to use an aggregate function that will return the most
> > > commonly occurring value in a column.
> >
> > It's actually dead simple in Pos
On Fri, 2009-05-22 at 15:24 -0400, Tom Lane wrote:
> > I think what he's asking for is the ability for a line in the server to
> > allow multiple authentication methods, and then for the client to be
> > able to specify which one of these it uses.
>
> There's no way we could implement that without
Nico Sabbi wrote:
> Hi,
> i can't believe my eyes. Why on earth I can't drop a user without
> previously revoking his privileges?
> This is really _crazy_ in my opinion.
>
> I'm not speaking of object ownership, but of GRANTs.
As Tom says, it's a known limitation. Did you try REASSIGN OWNED and
Assume I have an UPDATE statement that looks like
UPDATE foo
SET
pattern = '$pattern',
shape = '$shape',
length = $length,
comment = '$comment'
WHERE foo_id = $foo_id
and length is defined as NUMERIC. Is there any kind of magic that
would allow me to use the
Hello all,
I have a perplexing problem which I cannot figure out. I have a somewhat
complex query that is returning two identical rows, where only one row
exists in the table. If I run a simpler query, I receive the one row as
desired. This is on a windows vista development machine with Postgres 8.
Magnus Hagander writes:
> In his scenario, different clients connecting from the same subnet (so
> they can't use different pg_hba lines) need to use different
> authentication methods. (IIRC, this is an environment coming from
> Microsoft SQL Server which does support this)
Perhaps he could use
Keith Hayden writes:
> I tried the following:
> update pg_type set typalign = 'd' where typname = 'int4'
Not int4, _int4 (that is, array of int4).
> then
> update pg_statistic set stavalues1 = array[1,10,20,30,40,50,60,70,80,90,100]
> where starelid = 24751
> this failed with:
> ERROR: table
Tom Lane wrote:
> Konstantin Izmailov writes:
>> I've asked a few people at pgcon2009 about the issue of connecting users
>> differently: some in SSIS mode, some - with password. This is still an
>> issue: if server is configured for SSIS, there is no way to connect with
>> password.
>
> Huh? Th
Konstantin Izmailov writes:
> I've asked a few people at pgcon2009 about the issue of connecting users
> differently: some in SSIS mode, some - with password. This is still an
> issue: if server is configured for SSIS, there is no way to connect with
> password.
Huh? The server decides which aut
2009/5/22 Keith Hayden
> 2009/5/22 Tom Lane
>
> Keith Hayden writes:
>> > I need to spoof statistics, and so need to update the stavaluesN columns
>> in
>> > pg_statistics, which are of type anyarray. Can this be done using an
>> UPDATE
>> > statement ? I have tried using array[...] and '{...
I've asked a few people at pgcon2009 about the issue of connecting users
differently: some in SSIS mode, some - with password. This is still an
issue: if server is configured for SSIS, there is no way to connect with
password. It appears that the solution of the issue is to introduce
connection str
Scott Bailey writes:
> I'd like to make an XMLTABLE() function, probably in plpython (not C).
> And I'd like to follow the SQL/XML standard. So a function call should
> look something like this:
> SELECT *
> FROM XMLTABLE('//node'
>PASSING xmldoc
>COLUMNS id INT PATH '@id',
> name
Just wanted to let everyone know that I set up partitioning and got my
30 days of data in. This sucker is FAST. And it wasn't nearly as
complicated to make happen as I thought it would be. I didn't use the
triggers or rules to make sure data goes in the right table on insert,
as there's only
2009/5/22 Tom Lane
> Keith Hayden writes:
> > I need to spoof statistics, and so need to update the stavaluesN columns
> in
> > pg_statistics, which are of type anyarray. Can this be done using an
> UPDATE
> > statement ? I have tried using array[...] and '{...}' syntax with no
> luck.
> > Any
On Fri, May 22, 2009 at 03:23:07PM +, arta...@comcast.net wrote:
> > I want to use an aggregate function that will return the most
> > commonly occurring value in a column.
>
> It's actually dead simple in Postgres. No C either. You just need to
> create an aggregate function. I wrote a m
Tom Lane ha scritto:
Nico Sabbi writes:
i can't believe my eyes. Why on earth I can't drop a user without
previously revoking his privileges?
Yeah, it's a known limitation. The reason it's not implemented is that
some of the privileges may be in other databases besides the particular
Nico Sabbi writes:
> i can't believe my eyes. Why on earth I can't drop a user without
> previously revoking his privileges?
Yeah, it's a known limitation. The reason it's not implemented is that
some of the privileges may be in other databases besides the particular
one you're connected to, an
Hi,
i can't believe my eyes. Why on earth I can't drop a user without
previously revoking his privileges?
This is really _crazy_ in my opinion.
I'm not speaking of object ownership, but of GRANTs.
Why?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to you
Keith Hayden writes:
> I need to spoof statistics, and so need to update the stavaluesN columns in
> pg_statistics, which are of type anyarray. Can this be done using an UPDATE
> statement ? I have tried using array[...] and '{...}' syntax with no luck.
> Any other ideas as to how to achieve this
On Fri, May 22, 2009 at 9:04 AM, Brandon Metcalf
wrote:
> s == s...@samason.me.uk writes:
>
> s> On Fri, May 22, 2009 at 08:41:46AM -0500, Brandon Metcalf wrote:
> s> > I am looking for criteria on deciding whether or not to use a serial
> s> > (auto-incrementing) key for rows in a table.
>
>
On Fri, May 22, 2009 at 03:23:07PM +, arta...@comcast.net wrote:
> > I want to use an aggregate function that will return the most
> > commonly occurring value in a column.
>
> It's actually dead simple in Postgres. No C either. You just need to
> create an aggregate function. I wrote a most
On Fri, May 22, 2009 at 3:33 PM, Sam Mason wrote:
> Not that I'm aware of; it's a fuzzy design choice with benefits and
> costs for either option. There are lots of people who arbitrarily
> pick one side which tends to make things worse, using one or the other
> *exclusively* will add complicatio
On Thu, May 21, 2009 at 3:37 PM, Alex Thurlow wrote:
> I was hoping to not have to change all my code to automate the partitioning
> table creation stuff, but if that's really the best way, I'll check it out.
> Thanks for the advice.
About a 18 months ago we split a large table with 300+ million
> I want to use an aggregate function that will return the most commonly
> occurring value in a column.
It's actually dead simple in Postgres. No C either. You just need to create an
aggregate function.
I wrote a most() aggregate a while back that does exactly what you are asking
for. Here, I'
Hi,
I need to spoof statistics, and so need to update the stavaluesN columns in
pg_statistics, which are of type anyarray. Can this be done using an UPDATE
statement ? I have tried using array[...] and '{...}' syntax with no luck.
Any other ideas as to how to achieve this ?
Thanks,
Keith.
s == s...@samason.me.uk writes:
s> On Fri, May 22, 2009 at 08:41:46AM -0500, Brandon Metcalf wrote:
s> > I am looking for criteria on deciding whether or not to use a serial
s> > (auto-incrementing) key for rows in a table.
s> Wow, that's the second time today someone asked that!
s> > Intui
On Fri, May 22, 2009 at 3:26 PM, Roderick A. Anderson
wrote:
> Brandon Metcalf wrote:
>> That does help. So, in my example of a table consisting of rows for
>> each periodic table element, the atomic number would suffice as a
>> unique key since, well, it's unique and not going to change. Right?
Brandon Metcalf wrote:
g == gryz...@gmail.com writes:
g> you should use it, whenever you need db to keep its own key internally.
g> Advantage of sequence is also the fact, that you can have the sequence
g> value used on different columns/tables .
g> My rule of thumb is , in that case: as lo
On Fri, May 22, 2009 at 08:41:46AM -0500, Brandon Metcalf wrote:
> I am looking for criteria on deciding whether or not to use a serial
> (auto-incrementing) key for rows in a table.
Wow, that's the second time today someone asked that!
> Intuitively, it's pretty clear to me when a serial index i
2009/5/22 Brandon Metcalf :
> That does help. So, in my example of a table consisting of rows for
> each periodic table element, the atomic number would suffice as a
> unique key since, well, it's unique and not going to change. Right?
Well, yes :)
At least that's my opinion and experience.
No
g == gryz...@gmail.com writes:
g> you should use it, whenever you need db to keep its own key internally.
g> Advantage of sequence is also the fact, that you can have the sequence
g> value used on different columns/tables .
g> My rule of thumb is , in that case: as long as it is a short type
you should use it, whenever you need db to keep its own key internally.
Advantage of sequence is also the fact, that you can have the sequence
value used on different columns/tables .
My rule of thumb is , in that case: as long as it is a short type (not
of toastable, or/and variable length), and
This may be better discussed in the pgsql-sql forum. Please let me
know if so.
I am looking for criteria on deciding whether or not to use a serial
(auto-incrementing) key for rows in a table. For example, if I have a
table of, say, the elements on the periodic table I could use the
atomic numbe
On May 21, 2009, at 10:31 PM, Rodrigo Gonzalez wrote:
you have to change RETURN NULL; with RETURN NEW;
No he doesn't, it's an AFTER insert trigger; the data has already been
inserted at that point, so the return value is not relevant.
And don't top-post, please.
On 05/21/2009 04:57 PM, K
On Fri, 22 May 2009 17:48:44 +1000
"Michael Harris" wrote:
> Hi Experts,
>
> I want to use an aggregate function that will return the most
> commonly occurring value in a column.
>
> The column consists of VARCHAR(32) values.
>
> Is it possible to construct such an aggregate using PL/PgSql ?
Hello
I thing, so the function in C is the best solution. And I thing, so
you can use PostgreSQL functionality inside. Pg support hash arrays
and hashing function too.
regards
Pavel Stehule
2009/5/22 Michael Harris :
> Hi Experts,
>
> I want to use an aggregate function that will return the most
Hi Experts,
I want to use an aggregate function that will return the most commonly
occurring value in a column.
The column consists of VARCHAR(32) values.
Is it possible to construct such an aggregate using PL/PgSql ?
If I was trying to do something like this in Perl I would use a hash
table t
Postgres User wrote:
Hi,
I have a database that was created with SQL-ASCII encoding
(unfortunately). I ran pg_restore to load the struct and data into a
new database with UTF-8 encoding but no surprise- I'm seeing this
error for a number of tables:
pg_restore: [archiver (db)] COPY failed: ERRO
I'd like to make an XMLTABLE() function, probably in plpython (not C).
And I'd like to follow the SQL/XML standard. So a function call should
look something like this:
SELECT *
FROM XMLTABLE('//node'
PASSING xmldoc
COLUMNS id INT PATH '@id',
name VARCHAR(30) PATH 'name')
Is this possib
50 matches
Mail list logo
