Dear all,
I am able to execute command successfully through CLI or pgAdmin .
*create table user_news_new as select
record_id,field_name,field_value,news_date from user_news where
field_name in ('SOI','RelLoc','Description','Heading','news_date') and
field_value != '' ; *
But I don't know wh
Hi,
When I´m doing a conversion PostgreSQL returns SCAPES to me besides the desire
caracter. Why ?
Using Postgresql 8.2:
SELECT CONVERT('Rejeição: Duplicidade de NF-e, com diferença na Chave de
Acesso [35110100608804000178550010001009471840996034]','UTF8','LATIN1')
Result: "Rejeição: Du
On 1 February 2011 23:08, Thom Brown wrote:
> On 1 February 2011 21:32, Alban Hertroys
> wrote:
>> On 1 Feb 2011, at 21:26, Thom Brown wrote:
>>
>>> On 1 February 2011 01:05, Tom Lane wrote:
Thom Brown writes:
> I've noticed that if I try to use generate_series to include the upper
>>>
On 3 February 2011 11:31, Thom Brown wrote:
> On 1 February 2011 23:08, Thom Brown wrote:
>> On 1 February 2011 21:32, Alban Hertroys
>> wrote:
>>> On 1 Feb 2011, at 21:26, Thom Brown wrote:
>>>
On 1 February 2011 01:05, Tom Lane wrote:
> Thom Brown writes:
>> I've noticed that if
On Thu, Feb 03, 2011 at 02:26:10PM +0530, Adarsh Sharma wrote:
> globe=# CREATE FUNCTION user10() RETURNS void AS'
> globe'# DECLARE
> globe'# BEGIN
> globe'# create table user_news_new as select
> record_id,field_name,field_value,news_date from user_news where
> field_name in ('SOI','RelLoc','Desc
Hi
For now, I already can use in my PG database the PL/Python to create
functions.
But I need a library that can do the:
> Plain SHA1, which is signed with RSA signature.
Do you known any PL/Python wrapper libraries
around OpenSSL (or any other crypto library) that I could use.
I have found s
What's the deal with locale codes on windows.
especially the settings like LC_NUMERIC ansd LC_MONETARY
on windows XP in 9.0 "show LC_MONETARY" displays them as "Arabic, Bharain"
or "English, UK", "French, Canada" but everything right of the comma
seems to be ignored. (resulting in incorrect cur
On 3 February 2011 11:34, Thom Brown wrote:
> On 3 February 2011 11:31, Thom Brown wrote:
>> On 1 February 2011 23:08, Thom Brown wrote:
>>> On 1 February 2011 21:32, Alban Hertroys
>>> wrote:
On 1 Feb 2011, at 21:26, Thom Brown wrote:
> On 1 February 2011 01:05, Tom Lane wrote:
Just trying to understand good DBA design practice. This is obviously
a very general question but any feedback on what good or bad issues
would come from me dumping all my tables for applications in one
database or spread out across multiple databases on PostgreSQL.
Thank you!
As a general rule
On 3 February 2011 13:32, Thom Brown wrote:
> Actually, further testing indicates this causes other problems:
>
> postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x);
> x
> ---
> 1
> (1 row)
>
> Should return no rows.
>
> postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x);
> x
> ---
On Thu, Feb 3, 2011 at 2:38 PM, hlcborg wrote:
> For now, I already can use in my PG database the PL/Python to create
> functions.
> But I need a library that can do the:
>> Plain SHA1, which is signed with RSA signature.
>
> Do you known any PL/Python wrapper libraries
> around OpenSSL (or any o
The proposed generate_series(1,9,-1) behavior seems unusual. I think it
should throw a warning if the step direction and the start-end directions do
not match. Alternatively, the series generated could go from 9 -> 1 instead
of returning an empty series (basically the first two arguments are simp
One of the programmers I work with is interested in migrating from
Oracle to PostgreSQL as the backend for his applications.
Is there a PostgreSQL "beginners" guide available somewhere, which
might help him understand setting up a test database on his Windows
system?
I may also be performing a 30
On 3 February 2011 14:37, David Johnston wrote:
> The proposed generate_series(1,9,-1) behavior seems unusual.
I haven't proposed this behaviour as it already occurs. I just
include it for testing to ensure no other part of generate series is
affected by such changes. Whether or not that is des
Thanks for all the suggestions and everyone appears to agree that if
the applications don't need to share data, then I should split them up
into separate database and nothing more.
I appreciate your input and explanations as well.
-Carlos
--
Sent via pgsql-general mailing list (pgsql-general@po
On 2/3/2011 8:40 AM, Kenneth Buckler wrote:
One of the programmers I work with is interested in migrating from
Oracle to PostgreSQL as the backend for his applications.
Is there a PostgreSQL "beginners" guide available somewhere, which
might help him understand setting up a test database on his
On 2/2/2011 4:58 PM, matty jones wrote:
I am looking for a good book on the math and/or theory behind relational
databases and associated topics.. I am looking some works on set
theory, algebra, or any other books/papers on the mechanics that
databases are built on. I found one book online,
htt
Got it solved!
The problem was one of two things,or maybe both. I had somehow gotten
over 15 million records into the master table and even though I
"deleted" them and run VACUUM ANALYZE over the table, they were still
taking up space in the table. Perhaps even just opening a table with
that
On 2/3/2011 9:31 AM, Kenneth Buckler wrote:
I'm probably going to be presenting to a mixed audience, some of the
developers will have extensive Oracle knowledge, some will have MS SQL
Server experience. Plus, I'm not extremely familiar with Oracle, so
trying to compare the two would not work wel
On Thu, 3 Feb 2011, Andy Colson wrote:
I am looking for a good book on the math and/or theory behind relational
databases and associated topics.
Dont know any books, but have you read the wikipedia page?
Try any book by Edgar F. Codd. While at IBM he developed the concept of
relational da
Yves Weißig wrote:
I was wondering if there is more information about this switch in the
configuration. Does it really work? Where in the source code can I
follow how it works? "sgmgr.c" seems to be an entry point, but where
exactly is it used?
Currently the code only kicks in when you're do
I think an intro presentation for Oracle developers should include:
-- sqlplus to psql comparison
-- how to setup OS authenticated users in PostgreSQL
-- purpose of pg_hba.conf
-- schemas and search_path.
-- where to find things in the catalog. information_schema or pg_tables
Rick
On 02/03/2011 07:29 AM, Bill Thoen wrote:
Got it solved!
Great.
The problem was one of two things,or maybe both. I had somehow gotten
over 15 million records into the master table and even though I
"deleted" them and run VACUUM ANALYZE over the table, they were still
taking up space in the t
Wilton Wonrath writes:
> When I´m doing a conversion PostgreSQL returns SCAPES to me besides the
> desire
> caracter. Why ?
Somewhere between 8.2 and 9.0 convert() was redefined to take and return
bytea, not text, to reflect the fact that the data it deals in isn't
necessarily encoded in the da
I have been searching through the docs and mailing list and haven't found a way
to do this, so I thought I would ask the community.
I would like to know if there is a way in PostgreSQL to avoid repeating an
expensive computation in a SELECT where the result is needed both as a returned
value an
In response to Bob Price :
> I have been searching through the docs and mailing list and haven't found a
> way to do this, so I thought I would ask the community.
>
> I would like to know if there is a way in PostgreSQL to avoid repeating an
> expensive computation in a SELECT where the result
Is using a pl/pgsql function a viable option. Within or without the use of
a function you can create a temporary table to hold the needed intermediate
results. You can even use a permanent working table and write functions to
perform the needed queries against it.
Especially for expensive calcul
Bob Price writes:
> I would like to know if there is a way in PostgreSQL to avoid repeating an
> expensive computation in a SELECT where the result is needed both as a
> returned value and as an expression in the WHERE clause.
Use a subselect. You might need OFFSET 0 to prevent the planner fro
On 2011-02-02, matty jones wrote:
> I am looking for a good book on the math and/or theory behind
> relational databases and associated topics.. I am looking
> some works on set theory, algebra, or any other books/papers
> on the mechanics that databases are built on. I found one
> book online,
Hello
2011/2/3 Tom Lane :
> Bob Price writes:
>> I would like to know if there is a way in PostgreSQL to avoid repeating an
>> expensive computation in a SELECT where the result is needed both as a
>> returned value and as an expression in the WHERE clause.
>
> Use a subselect. You might need
On February 2, 2011, "William Bruton" wrote:
> How do I know which version to upgrade to from 8.1.4?
>
Well, 8.1 is no longer supported, it seems. So an upgrade to any supported
version will likely require application changes, or at least thorough
testing. You might as well go right to 9.0.3 t
On 02/03/2011 08:13 AM, Alban Hertroys wrote:
On 3 Feb 2011, at 2:17, Mage wrote:
The trigger looks like:
create or replace function trf_chat_room_users_insert() returns trigger as $$
begin
if NEW.active_at is null then
insert into chat_room_users (user_id, chat_room_id
On Thu, Feb 3, 2011 at 11:50 AM, Alan Hodgson wrote:
> On February 2, 2011, "William Bruton" wrote:
>
>> How do I know which version to upgrade to from 8.1.4?
>
>>
>
> Well, 8.1 is no longer supported, it seems. So an upgrade to any supported
> version will likely require application changes, or
Mage writes:
> The main question is that isn't "insert into ... select ... where not
> exists" atomic?
No, it isn't: it *will* fail in the presence of other transactions doing
the same thing, because the EXISTS test will only see rows that
committed before the command started. You might care to
I have been searching through the docs and mailing list and haven't found a way
to do this, so I thought I would ask the community.
I would like to know if there is a way in PostgreSQL to avoid repeating an
expensive computation in a SELECT where the result is needed both as a returned
value an
Thanks for all of the responses.
If the IMMUTABLE flag on a function does what the docs say then it might
satisfy my needs.
Here is a more specific example of what I need to do, with a new custom data
type and a new function:
- define new complex data type X
- create table mytable ( id varchar
Hi all,
The recent discussion about generate_series() made me realise you can use it to
transpose rows; meaning you can turn columns of each row into separate rows.
Here's an example:
CREATE TABLE foobarbaz(
foo text,
bar text,
baz int
);
INSERT INTO foobarbaz (foo, bar, baz) VALUES ('Foo', 'B
Your trigger is wrong. You try to insert the same row twice.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Look at unnest function.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
That's interesting Tom.
This is a discussion coming up sometimes at PostGIS lists since PostGIS
often handles "expensive" calculations.
Regina wrote a blog post about it:
http://postgresonline.com/journal/archives/113-How-to-force-PostgreSQL-to-use-a-pre-calculated-value.html
I thought the "off
Bob Price writes:
> If I set the COST of expensivefunc high, and label it IMMUTABLE, will the
> query executor note that the two invocations to expensivefunc have the same
> inputs so it can only call it once and re-use the result the second time?
No. There is a myth prevalent among certain wi
Nicklas =?ISO-8859-1?Q?Av=E9n?= writes:
> I thought the "offset 0" trick was just a dirty hack, but coming from
> you, Tom, I assume it is a robust way of doing it.
Well, I can't deny it's a dirty hack ... but it's not something we'll
break until we have a better solution.
> I also tried some of
On 02/03/2011 10:35 PM, pasman pasmański wrote:
Your trigger is wrong. You try to insert the same row twice.
I assume you didn't try it. If active_at field is null then the trigger
does another insert instead of the original one. This avoids looping or
inserting twice.
The only mistake is th
On 02/03/2011 08:23 PM, Tom Lane wrote:
Mage writes:
The main question is that isn't "insert into ... select ... where not
exists" atomic?
No, it isn't: it *will* fail in the presence of other transactions doing
the same thing, because the EXISTS test will only see rows that
committed before t
Hi,
I was wondering if Postgresql can be tuned such that it writes to
disk every n seconds or until the buffer reaches a certain threshold
to tune the performance . This is a special case where the application
wouldn't care if there is a data loss of 1 seconds or less. I would be
interested to k
Mage writes:
> On 02/03/2011 08:23 PM, Tom Lane wrote:
>> No, it isn't: it *will* fail in the presence of other transactions doing
>> the same thing, because the EXISTS test will only see rows that
>> committed before the command started. You might care to read the
>> manual's chapter about concu
Mage, add "raise notice" at the begin of your buggy trigger.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
47 matches
Mail list logo
