Re: [GENERAL] Rename extension?

[Albe Laurenz]

Moshe Jacobson wrote:
> Is there a way to rename an installed extension?
> 
> I have written an extension, but I don't like the name I originally chose, 
> and I would therefore like
> to rename it. However, it is installed on a production system, from which it 
> cannot be uninstalled,
> and I would like to rename it there, too.
> 
> 
> How can I do this?

You could fiddle with the catalogs, i.e. update pg_extension.

But try it on a test system first.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] hot_standby_feedback

[Stuart Bishop]

On Thu, Aug 29, 2013 at 2:44 PM, Tatsuo Ishii  wrote:
> I have a question about hot_standby_feedback parameter. In my
> understanding, if this parameter is on, a long running transaction on
> standby will not be canceled even if the transaction conflicts.

> As you can see vacuum on the primary removes all the rows in t1. I
> thought vacuum will not make the page entriely empty because
> of the effect of hot_standby_feedback.
>
> After while, on standby:
> test=# select * from t1;
> FATAL:  terminating connection due to conflict with recovery
> DETAIL:  User was holding a relation lock for too long.
> HINT:  In a moment you should be able to reconnect to the database and repeat 
> your command.
>
> Again, this is not what I expected. Am I missing something?

I also expected this behavior, and suffer similar problems with
PostgreSQL 9.1. I reported this as BUG #7546 with a test case and
raised it a few times on the mailing lists, but am no closer to a
solution.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] [PG9.1] CTE usage

[Ladislav Lenart]

Hello all.

I am curious about the following usage of CTEs:

Imagine three tables:
 * item (id, item_type1_id, item_type2_id, ...)
 * item_type1 (id, ...)
 * item_type2 (id, ...)
where
 * item_type1_id is FK to item_type1 (id)
 * item_type2_id is FK to item_type2 (id)

Items are of two types (type1 and type2). Each item type has different data
columns. An item is either of type1 (item_type1_id is populated) or of type2
(item_type2_id is populated). I want to delete some items along with the
corresponding rows in the tables item_type1 and item_type2 (they have no meaning
without the 'parent'). I have written the following CTE (I want to compute
items_to_delete only once):

WITH
items_to_delete AS (
SELECT
item.id AS item_id,
item.item_type1_id AS item_type1_id,
item.item_type2_id AS item_type2_id
FROM item
WHERE ... -- limit the set of items to delete
,
delete_items AS (
DELETE FROM item
WHERE item.id IN (SELECT item_id FROM items_to_delete)
),
delete_items_type1 AS (
DELETE FROM item_type1
WHERE item_type1.id IN (SELECT item_type1_id FROM items_to_delete)
),
delete_items_type2 AS (
DELETE FROM item_type2
WHERE item_type2.id IN (SELECT item_type2_id FROM items_to_delete)
)
SELECT 1;

Should this work? I thought that CTEs are evaluated once in the order of
definition, regardless when/if they are used, so:
 * First, items_to_delete is populated from the existing data.
 * Then delete_items deletes some items according to items_to_delete.
 * Then delete_items_type1 deletes some items of type1 according to 
items_to_delete.
 * Finally delete_items_type2 deletes some items of type2 according to
items_to_delete.

Does the deletes somehow modify the contents of the 'temporary table'
items_to_delete? The thing is I ran a very similar script on our testing
environment as a part of our DB migration to the new model and have just
realized that only items were deleted (i.e. no item_type1 and item_type2).

Hence I would like to ask if any of you see some obvious flaw in this style of
CTE usage.

Thank you in advance,

Ladislav Lenart



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Alban Hertroys]

On 16 September 2013 11:58, Ladislav Lenart  wrote:
> Hello all.
>
> I am curious about the following usage of CTEs:
>
> Imagine three tables:
>  * item (id, item_type1_id, item_type2_id, ...)
>  * item_type1 (id, ...)
>  * item_type2 (id, ...)
> where
>  * item_type1_id is FK to item_type1 (id)
>  * item_type2_id is FK to item_type2 (id)
>
> Items are of two types (type1 and type2). Each item type has different data
> columns. An item is either of type1 (item_type1_id is populated) or of type2
> (item_type2_id is populated). I want to delete some items along with the
> corresponding rows in the tables item_type1 and item_type2 (they have no 
> meaning
> without the 'parent'). I have written the following CTE (I want to compute
> items_to_delete only once):

Wouldn't it be much easier to define an FK constraint with ON DELETE CASCADE?
With that, you only need to worry about which rows you delete from the
parent table and dependant children will be removed automatically.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Ladislav Lenart]

On 16.9.2013 13:26, Alban Hertroys wrote:
> On 16 September 2013 11:58, Ladislav Lenart  wrote:
>> Hello all.
>>
>> I am curious about the following usage of CTEs:
>>
>> Imagine three tables:
>>  * item (id, item_type1_id, item_type2_id, ...)
>>  * item_type1 (id, ...)
>>  * item_type2 (id, ...)
>> where
>>  * item_type1_id is FK to item_type1 (id)
>>  * item_type2_id is FK to item_type2 (id)
>>
>> Items are of two types (type1 and type2). Each item type has different data
>> columns. An item is either of type1 (item_type1_id is populated) or of type2
>> (item_type2_id is populated). I want to delete some items along with the
>> corresponding rows in the tables item_type1 and item_type2 (they have no 
>> meaning
>> without the 'parent'). I have written the following CTE (I want to compute
>> items_to_delete only once):
> 
> Wouldn't it be much easier to define an FK constraint with ON DELETE CASCADE?
> With that, you only need to worry about which rows you delete from the
> parent table and dependant children will be removed automatically.


Hello.

I don't quite follow. Having item.item_type1_id FK with ON DELETE CASCADE would
delete ITEM (the parent) when ITEM_TYPE1 (the child) is deleted. You suggests
the opposite direction. Could you please describe your proposal in more detail
(just the list of tables with their columns)?

Nevertheless, I am still curious about my original question(s):
 * Whether is this style of CTE usage discouraged (i.e. rely on the in-order
evaluation of CTEs without even mentioning them in the top query).
 * Any idea what could be wrong in my example.

Thank you,

Ladislav Lenart



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Ladislav Lenart]

Nevermind, I already found the root cause of my problem: boolean logic of NULL
in conjunction with the NOT IN operator. My real usecase was a bit more 
involved:

WITH
items_to_delete AS (
SELECT
item.id AS item_id,
item.item_type1_id AS item_type1_id,
item.item_type2_id AS item_type2_id
FROM item
WHERE ... -- limit the set of items to delete
),
ok_items AS (
-- 'Required' because the planner otherwise chose a very inneficient plan.
SELECT
item.id AS item_id,
item.item_type1_id,
item.item_type2_id
FROM item
EXCEPT
SELECT * FROM items_to_delete
),
delete_items AS (
DELETE FROM item
WHERE item.id NOT IN (SELECT item_id FROM ok_items)
),
delete_items_type1 AS (
DELETE FROM item_type1
WHERE item_type1.id NOT IN (SELECT item_type1_id FROM ok_items)
),
delete_items_type2 AS (
DELETE FROM item_type2
WHERE item_type2.id NOT IN (SELECT item_type2_id FROM ok_items)
)
SELECT 1;

This does not work because the NOT IN argument in delete_items_type1 and
delete_items_type2 contain NULLs. When I change the CTEs like this:

delete_items_typeX AS (
DELETE FROM item_typeX
WHERE item_typeX.id NOT IN (
SELECT item_typeX_id
FROM ok_items
WHERE item_typeX_id IS NOT NULL
)
)

everything works as it should.


Ladislav Lenart


On 16.9.2013 13:57, Ladislav Lenart wrote:
> On 16.9.2013 13:26, Alban Hertroys wrote:
>> On 16 September 2013 11:58, Ladislav Lenart  wrote:
>>> Hello all.
>>>
>>> I am curious about the following usage of CTEs:
>>>
>>> Imagine three tables:
>>>  * item (id, item_type1_id, item_type2_id, ...)
>>>  * item_type1 (id, ...)
>>>  * item_type2 (id, ...)
>>> where
>>>  * item_type1_id is FK to item_type1 (id)
>>>  * item_type2_id is FK to item_type2 (id)
>>>
>>> Items are of two types (type1 and type2). Each item type has different data
>>> columns. An item is either of type1 (item_type1_id is populated) or of type2
>>> (item_type2_id is populated). I want to delete some items along with the
>>> corresponding rows in the tables item_type1 and item_type2 (they have no 
>>> meaning
>>> without the 'parent'). I have written the following CTE (I want to compute
>>> items_to_delete only once):
>>
>> Wouldn't it be much easier to define an FK constraint with ON DELETE CASCADE?
>> With that, you only need to worry about which rows you delete from the
>> parent table and dependant children will be removed automatically.
> 
> 
> Hello.
> 
> I don't quite follow. Having item.item_type1_id FK with ON DELETE CASCADE 
> would
> delete ITEM (the parent) when ITEM_TYPE1 (the child) is deleted. You suggests
> the opposite direction. Could you please describe your proposal in more detail
> (just the list of tables with their columns)?
> 
> Nevertheless, I am still curious about my original question(s):
>  * Whether is this style of CTE usage discouraged (i.e. rely on the in-order
> evaluation of CTEs without even mentioning them in the top query).
>  * Any idea what could be wrong in my example.
> 
> Thank you,
> 
> Ladislav Lenart
> 
> 
> 




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Adrian Klaver]

On 09/16/2013 04:57 AM, Ladislav Lenart wrote:

On 16.9.2013 13:26, Alban Hertroys wrote:




Wouldn't it be much easier to define an FK constraint with ON DELETE CASCADE?
With that, you only need to worry about which rows you delete from the
parent table and dependant children will be removed automatically.



Hello.

I don't quite follow. Having item.item_type1_id FK with ON DELETE CASCADE would
delete ITEM (the parent) when ITEM_TYPE1 (the child) is deleted. You suggests
the opposite direction.


http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html

"..In addition, when the data in the referenced columns is changed, 
certain actions are performed on the data in this table's columns. The 
ON DELETE clause specifies the action to perform when a referenced row 
in the referenced table is being deleted. .."


"..CASCADE
Delete any rows referencing the deleted row, or update the values of the 
referencing column(s) to the new values of the referenced columns, 
respectively.

.."




--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Ladislav Lenart]

On 16.9.2013 15:50, Adrian Klaver wrote:
> On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
>> On 16.9.2013 13:26, Alban Hertroys wrote:
> 
>>>
>>> Wouldn't it be much easier to define an FK constraint with ON DELETE 
>>> CASCADE?
>>> With that, you only need to worry about which rows you delete from the
>>> parent table and dependant children will be removed automatically.
>>
>>
>> Hello.
>>
>> I don't quite follow. Having item.item_type1_id FK with ON DELETE CASCADE 
>> would
>> delete ITEM (the parent) when ITEM_TYPE1 (the child) is deleted. You suggests
>> the opposite direction.
> 
> http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html
> 
> "..In addition, when the data in the referenced columns is changed, 
> certain actions are performed on the data in this table's columns. The 
> ON DELETE clause specifies the action to perform when a referenced row 
> in the referenced table is being deleted. .."
> 
> "..CASCADE
> Delete any rows referencing the deleted row, or update the values of the 
> referencing column(s) to the new values of the referenced columns, 
> respectively.
> .."


Hello.

Thank you but I have read this in the official documentation before posting my
(previous) reply. So to quote the important bit about CASCADE:

Delete any rows referencing the deleted row

My example defines the table item with FK to the table item_type1 and FK to the
table item_type2. Specifying anything on these two constraints does not help one
bit when I delete an item, because item_type1 nor item_type2 does not reference
any... Therefore I suspect that Alban Hertroys had a different model in mind 
where:
 * item would not have any FKs,
 * item_type1 would have FK to item,
 * item_type2 would have FK to item?

I just wasn't sure, hence I have asked him for a more detailed answer. However,
I am pretty sure ON DELETE CASCADE would not help in my current setup.

Ladislav Lenart



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Adrian Klaver]

On 09/16/2013 07:38 AM, Ladislav Lenart wrote:

On 16.9.2013 15:50, Adrian Klaver wrote:

On 09/16/2013 04:57 AM, Ladislav Lenart wrote:

On 16.9.2013 13:26, Alban Hertroys wrote:





.."



Hello.

Thank you but I have read this in the official documentation before posting my
(previous) reply. So to quote the important bit about CASCADE:

 Delete any rows referencing the deleted row

My example defines the table item with FK to the table item_type1 and FK to the
table item_type2. Specifying anything on these two constraints does not help one
bit when I delete an item, because item_type1 nor item_type2 does not reference
any... Therefore I suspect that Alban Hertroys had a different model in mind 
where:
  * item would not have any FKs,
  * item_type1 would have FK to item,
  * item_type2 would have FK to item?

I just wasn't sure, hence I have asked him for a more detailed answer. However,
I am pretty sure ON DELETE CASCADE would not help in my current setup.



I guess the question is whether you actually have set up FK 
relationships between items.item_type1_id, items.item_type2_id and the 
respective ids in item_type1 and item_type2?


In other words do you have REFERENCE item_type1 ... on item_type1_id?

If so and you add the ON DELETE CASCADE, you could DELETE from 
item_type1 and it would delete the respective items rows.




Ladislav Lenart






--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Ladislav Lenart]

On 16.9.2013 17:12, Adrian Klaver wrote:
> On 09/16/2013 07:38 AM, Ladislav Lenart wrote:
>> On 16.9.2013 15:50, Adrian Klaver wrote:
>>> On 09/16/2013 04:57 AM, Ladislav Lenart wrote:
 On 16.9.2013 13:26, Alban Hertroys wrote:
>>>
> 
>>> .."
>>
>>
>> Hello.
>>
>> Thank you but I have read this in the official documentation before posting 
>> my
>> (previous) reply. So to quote the important bit about CASCADE:
>>
>>  Delete any rows referencing the deleted row
>>
>> My example defines the table item with FK to the table item_type1 and FK to 
>> the
>> table item_type2. Specifying anything on these two constraints does not help 
>> one
>> bit when I delete an item, because item_type1 nor item_type2 does not 
>> reference
>> any... Therefore I suspect that Alban Hertroys had a different model in mind 
>> where:
>>   * item would not have any FKs,
>>   * item_type1 would have FK to item,
>>   * item_type2 would have FK to item?
>>
>> I just wasn't sure, hence I have asked him for a more detailed answer. 
>> However,
>> I am pretty sure ON DELETE CASCADE would not help in my current setup.
> 
> 
> I guess the question is whether you actually have set up FK 
> relationships between items.item_type1_id, items.item_type2_id and the 
> respective ids in item_type1 and item_type2?
> 
> In other words do you have REFERENCE item_type1 ... on item_type1_id?
> 
> If so and you add the ON DELETE CASCADE, you could DELETE from 
> item_type1 and it would delete the respective items rows.

Yes, this would work.

Ladislav Lenart



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Best way to populate nested composite type from JSON`

[Merlin Moncure]

On Fri, Sep 13, 2013 at 7:52 PM, Chris Travers  wrote:
> On Fri, Sep 13, 2013 at 8:51 AM, Merlin Moncure  wrote:
>> What's your client side stack?
>>
>> merlin
>
>
> Right now we are using something a little lighter weight in terms db
> discovery but it doesn't handle this situation very well.  I am the author
> of the PGObject space on CPAN and currently we are using what was the
> ancestor of PGObject::Simple::Role which does discovery based on function
> names and argument names.
>
> The PGObject namespace is basically a namespace for database-driven object
> oriented mapping systems.  The idea is that you can have an object model
> defined in the database (and with data logic coded in SQL), which gets
> discovered and used by the application at run-time.  It is PG-only of
> course.
>
> Unfortunately the problem is that once you go to any significantly complex
> data structure, my current discovery efforts fall apart.   This means that
> the application/db contract becomes more brittle and fewer things can change
> without having to fix both sides.  For example, currently, for processing
> payments we have two different implementations which take two different ways
> of trying to solve this problem and neither tolerates any semantic change to
> the arguments.  Currently these are among our least maintainable stored
> procedures.
>
> So my effort is in building a new service locator module (probably something
> like PGObject::CompositeType) that would pull in properties of a composite
> type as properties of an object model, and also create methods dynamically
> based on functions taking that type as a first argument.  The only real
> problem is serializing an arbitrarily complex composite type to the backend
> for processing.  If JSON worked it would save me a lot on the client side.
> But if not  The key requirement is that the data structures on the
> client would be pulled from named composite types on the server.

Huh. Interesting factoid:  I cowrote (see:
http://libpqtypes.esilo.com/man3/pqt-composites.html) what remains
today the only effective way to send complex types to the server.  For
non-'C' languages, json support will probably be the way to go once
the server can handle json deserialization better.

Right now whenever possible I'm deploying json directly to javascript
heavy applications with ultrathin middlewares with most of the
interesting backend processing (as you envisage) happening in the
database; It's powerful and it works.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Ladislav Lenart]

On 16.9.2013 17:30, David Johnston wrote:
> Ladislav Lenart wrote
>> Hello all.
>>
>> I am curious about the following usage of CTEs:
>>
>> Imagine three tables:
>>  * item (id, item_type1_id, item_type2_id, ...)
>>  * item_type1 (id, ...)
>>  * item_type2 (id, ...)
>> where
>>  * item_type1_id is FK to item_type1 (id)
>>  * item_type2_id is FK to item_type2 (id)
> 
> The mental model that comes to mind is:
> 
> item (item_id)
> item_type1 (type1_id, item_id )
> item_type2 (type2_id, item_id )
> 
> Or even better:
> 
> item (item_id )
> item_type1 (item_id )
> item_type2 (item_id )
> 
> You'd need a trigger on these tables if you want to enforce the "only a
> single type allowed" restriction but otherwise this model is much more
> usual.
> 
> 
> The other mental hang-up with your model is that the "item_type1_id" has a
> one-to-one relationship with the item_type1 table so that deleting the item
> means it is OK now to delete the associated type.  This is unusual.
> 
> David J.

Thank you for your insightful comments. I will give it a thought.

Ladislav Lenart



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[Vincent Veyron]

Le lundi 16 septembre 2013 à 08:30 -0700, David Johnston a écrit :
> Ladislav Lenart wrote
> > Hello all.
> > 
> > I am curious about the following usage of CTEs:
> > 
> > Imagine three tables:
> >  * item (id, item_type1_id, item_type2_id, ...)
> >  * item_type1 (id, ...)
> >  * item_type2 (id, ...)
> > where
> >  * item_type1_id is FK to item_type1 (id)
> >  * item_type2_id is FK to item_type2 (id)
> 
> The mental model that comes to mind is:
> 
> item (item_id)
> item_type1 (type1_id, item_id )
> item_type2 (type2_id, item_id )
> 
> Or even better:
> 
> item (item_id )
> item_type1 (item_id )
> 
> item_type2 (item_id )
> 

I would suggest :

item (item_id , id_type integer)
item_type1 (item_id )
item_type2 (item_id )

where item.id_type is either type_1 or type_2

So that the application knows which table to use? 

But chances are the OP can't change his model easily.



-- 
Salutations, Vincent Veyron 
http://marica.fr/ 
Gestion des contentieux juridiques, des sinistres d'assurance et des contrats



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [PG9.1] CTE usage

[David Johnston]

Ladislav Lenart wrote
> Hello all.
> 
> I am curious about the following usage of CTEs:
> 
> Imagine three tables:
>  * item (id, item_type1_id, item_type2_id, ...)
>  * item_type1 (id, ...)
>  * item_type2 (id, ...)
> where
>  * item_type1_id is FK to item_type1 (id)
>  * item_type2_id is FK to item_type2 (id)

The mental model that comes to mind is:

item (item_id)
item_type1 (type1_id, item_id )
item_type2 (type2_id, item_id )

Or even better:

item (item_id )
item_type1 (item_id )
item_type2 (item_id )

You'd need a trigger on these tables if you want to enforce the "only a
single type allowed" restriction but otherwise this model is much more
usual.


The other mental hang-up with your model is that the "item_type1_id" has a
one-to-one relationship with the item_type1 table so that deleting the item
means it is OK now to delete the associated type.  This is unusual.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG9-1-CTE-usage-tp5771048p5771104.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Segmentation fault: pg_upgrade 9.1 to 9.3: pg_dump: row number 0 is out of range 0..-1

[Jeff Janes]

On Sun, Sep 15, 2013 at 8:02 PM, Robert Nix  wrote:

> If you do the dump using 9.1's pg_dump without --binary-upgrade, and then
>> load that dump file into a new empty 9.1 server, then does it crash if you
>> take a dump against *that* server?
>>
>
> I'll give it a try.
>
> If so, would you be allowed to post that dump file?
>>
>
> I will not be able to provide the dump file but i will try to isolate the
> contents causing the issue and hopefully be able to provide a minimal dump
> file that doesn't contain any proprietary data yet still reproduces the
> crash. It's a fair amount of data, 45+G, so if i can reproduce it in a new
> database, isolating it may take some time.
>

The dump should just have the schema, not the data, because of
--schema-only.

Re: [GENERAL] Postgres 9.2.4 "Double Precision" Precision

[NWRFC Portland]

Adrian, Kevin,

Thank you for the clues.  It turns out a java process was added (between
the data source and database) at same time of postgres upgrade.  It was the
java process that incorrectly handled the double precision data.

Joanne


On Sat, Sep 14, 2013 at 9:57 AM, Adrian Klaver wrote:

> On 09/14/2013 08:51 AM, Kevin Grittner wrote:
>
>> Adrian Klaver  wrote:
>>
>>  create table float_test (id int, f_fld double precision);
>>> insert into  float_test values (1, 6.31);
>>> insert into  float_test values (2, 6.32);
>>> select * from float_test ;
>>>
>>> id | f_fld
>>> +---
>>> 1 |  6.31
>>> 2 |  6.32
>>> (2 rows)
>>>
>>
>> If, instead of those inserts I use these:
>>
>> insert into  float_test values (1, '6.31');
>> insert into  float_test values (1, '6.32');
>> insert into  float_test values (1, '6.32'::double precision);
>> insert into  float_test values (1, '6.32'::real);
>>
>> I get:
>>
>>   id |  f_fld
>> +--
>>1 | 6.31
>>1 | 6.32
>>1 | 6.32
>>1 | 6.3217166138
>> (4 rows)
>>
>> Apparently the value is being treated as a real value somewhere.
>>
>
> Interesting, more grist for the mill.
>
>
>
>> --
>> Kevin Grittner
>> EDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>

[GENERAL] Why does this array query fail?

[Ken Tanzer]

Hi.  Can someone explain to me why the last query below is failing, or what
exactly the error message means?  I'm sure there's a simple reason, but I'm
totally not seeing it.  I boiled this down from a more complicated example,
but I think the problem is the same.  Thanks in advance.

Ken

ets_reach=> SELECT ARRAY['test','pass','fail'];
  array
--
 {test,pass,fail}
(1 row)

ets_reach=> SELECT 'found' WHERE 'test' = ANY( ARRAY['test','pass','fail']
);
 ?column?
--
 found
(1 row)

ets_reach=> SELECT 'found' WHERE ARRAY['test','pass','fail'] = (SELECT
ARRAY['test','pass','fail']);
 ?column?
--
 found
(1 row)

ets_reach=> SELECT 'found' WHERE 'test' = ANY( (SELECT
ARRAY['test','pass','fail']) );

ERROR:  array value must start with "{" or dimension information
LINE 1: SELECT 'found' WHERE 'test' = ANY( (SELECT ARRAY['test','pas...
 ^


-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.

[GENERAL] using Replace funcion in postgresql

[karinos57]

SELECT 
   Volume, REPLACE(Volume,'.','')
  FROM MyTable

The data in my table looks like this:

88.97
448.58 and etc

i want to show like this with out the period:

8897
44858

I have tried to use different ways but still getting the error i hope
someone out there can help me. How can i achieve this? thanks



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/using-Replace-funcion-in-postgresql-tp5771164.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does this array query fail?

[bricklen]

On Mon, Sep 16, 2013 at 5:32 PM, Ken Tanzer  wrote:

> SELECT 'found' WHERE 'test' = ANY( (SELECT ARRAY['test','pass','fail']) );
>


It works if you drop the inner SELECT.

SELECT 'found' WHERE 'test' = ANY( ARRAY['test','pass','fail'] );

Re: [GENERAL] using Replace funcion in postgresql

[John R Pierce]

On 9/16/2013 4:55 PM, karinos57 wrote:

SELECT
Volume, REPLACE(Volume,'.','')
   FROM MyTable


The data in my table looks like this:

88.97
448.58 and etc

i want to show like this with out the period:

8897
44858

I have tried to use different ways but still getting the error i hope
someone out there can help me. How can i achieve this? thanks


still getting _the_ error?  what error is that?can we assume your 
table field is a numeric rather than a string value?


you could use   to_char(volume*100, 'FM999'), or maybe even just 
(volume*100)::integer




--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does this array query fail?

[David Johnston]

Ken Tanzer wrote
> ets_reach=> SELECT 'found' WHERE 'test' = ANY( (SELECT
> ARRAY['test','pass','fail']) );
> 
> ERROR:  array value must start with "{" or dimension information
> LINE 1: SELECT 'found' WHERE 'test' = ANY( (SELECT ARRAY['test','pas...
>  ^

Per documentation of "ANY" it accepts either an array or a subquery.  This
is the subquery form.  PostgreSQL is trying to convert 'test' into an array
in order to match the array column returned by the subquery.

Remove the "SELECT" to make it work the way you expect - i.e., the
ANY(array) form of the expression.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771170.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] using Replace funcion in postgresql

[Adrian Klaver]

On 09/16/2013 04:55 PM, karinos57 wrote:

SELECT
Volume, REPLACE(Volume,'.','')
   FROM MyTable

The data in my table looks like this:

88.97
448.58 and etc

i want to show like this with out the period:

8897
44858

I have tried to use different ways but still getting the error i hope
someone out there can help me. How can i achieve this? thanks



SELECT
 Volume, REPLACE(Volume::text,'.','')::int
FROM MyTable

--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does this array query fail?

[Ken Tanzer]

Thanks for the explanation.  I think I at least understand what it's doing
now.  I'm either surprised or confused though, as I was under the
impression that you could substitute a subquery for a value pretty much
anywhere, but I guess that's not the case?

Cheers,
Ken


On Mon, Sep 16, 2013 at 6:16 PM, David Johnston  wrote:

> Ken Tanzer wrote
> > ets_reach=> SELECT 'found' WHERE 'test' = ANY( (SELECT
> > ARRAY['test','pass','fail']) );
> >
> > ERROR:  array value must start with "{" or dimension information
> > LINE 1: SELECT 'found' WHERE 'test' = ANY( (SELECT ARRAY['test','pas...
> >  ^
>
> Per documentation of "ANY" it accepts either an array or a subquery.  This
> is the subquery form.  PostgreSQL is trying to convert 'test' into an array
> in order to match the array column returned by the subquery.
>
> Remove the "SELECT" to make it work the way you expect - i.e., the
> ANY(array) form of the expression.
>
> David J.
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771170.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Why does this array query fail?

[bricklen]

On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer  wrote:

> Thanks for the explanation.  I think I at least understand what it's doing
> now.  I'm either surprised or confused though, as I was under the
> impression that you could substitute a subquery for a value pretty much
> anywhere, but I guess that's not the case?
>
> Cheers,
> Ken
>
>
Your subquery can also be explicitly casted to make it work. Note the
"::TEXT[]"

SELECT 'found' WHERE 'test' = ANY( (SELECT
ARRAY['test','pass','fail'])::TEXT[] );

Re: [GENERAL] Why does this array query fail?

[Ken Tanzer]

OK I tried that and see it works with the cast.  But now I'm confused about
both what exactly is failing without the cast, and about the resulting
error message.

Is the query failing because PG doesn't understand the subquery is yielding
an array?  Seems unlikely.  But if the problem is a type mismatch between
'test' (on the left) and my subquery, I'd expect the same error message as
if I try to compare an int to a text array:

SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','fail']));
ERROR:  operator does not exist: integer = text[]
LINE 1: SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','f...
   ^
HINT:  No operator matches the given name and argument type(s). You might
need to add explicit type casts.

Instead of the error message I actually got:

ERROR:  array value must start with "{" or dimension information

Thanks.

Ken





On Mon, Sep 16, 2013 at 6:42 PM, bricklen  wrote:

>
> On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer  wrote:
>
>> Thanks for the explanation.  I think I at least understand what it's
>> doing now.  I'm either surprised or confused though, as I was under the
>> impression that you could substitute a subquery for a value pretty much
>> anywhere, but I guess that's not the case?
>>
>> Cheers,
>> Ken
>>
>>
> Your subquery can also be explicitly casted to make it work. Note the
> "::TEXT[]"
>
> SELECT 'found' WHERE 'test' = ANY( (SELECT
> ARRAY['test','pass','fail'])::TEXT[] );
>



-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Why does this array query fail?

[Ken Tanzer]

Well I partially take back my last question.  In the error message, I
missed the non-array / array part of "integer = text[]"

But I'm still confused.  My subselect returns an array.  If I cast it to a
text array, ANY is happy.  But if I don't do so, what exactly does Postgres
think my subquery has yielded?  And the error message still doesn't seem to
make sense...



On Mon, Sep 16, 2013 at 6:59 PM, Ken Tanzer  wrote:

> OK I tried that and see it works with the cast.  But now I'm confused
> about both what exactly is failing without the cast, and about the
> resulting error message.
>
> Is the query failing because PG doesn't understand the subquery is
> yielding an array?  Seems unlikely.  But if the problem is a type mismatch
> between 'test' (on the left) and my subquery, I'd expect the same error
> message as if I try to compare an int to a text array:
>
> SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','fail']));
> ERROR:  operator does not exist: integer = text[]
> LINE 1: SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','f...
>^
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
> Instead of the error message I actually got:
>
> ERROR:  array value must start with "{" or dimension information
>
> Thanks.
>
> Ken
>
>
>
>
>
> On Mon, Sep 16, 2013 at 6:42 PM, bricklen  wrote:
>
>>
>> On Mon, Sep 16, 2013 at 6:36 PM, Ken Tanzer  wrote:
>>
>>> Thanks for the explanation.  I think I at least understand what it's
>>> doing now.  I'm either surprised or confused though, as I was under the
>>> impression that you could substitute a subquery for a value pretty much
>>> anywhere, but I guess that's not the case?
>>>
>>> Cheers,
>>> Ken
>>>
>>>
>> Your subquery can also be explicitly casted to make it work. Note the
>> "::TEXT[]"
>>
>> SELECT 'found' WHERE 'test' = ANY( (SELECT
>> ARRAY['test','pass','fail'])::TEXT[] );
>>
>
>
>
> --
> AGENCY Software
> A data system that puts you in control
> 100% Free Software
> *http://agency-software.org/*
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing 
> list
>  to
> learn more about AGENCY or
> follow the discussion.
>



-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Why does this array query fail?

[David Johnston]

Ken Tanzer wrote
> Well I partially take back my last question.  In the error message, I
> missed the non-array / array part of "integer = text[]"
> 
> But I'm still confused.  My subselect returns an array.  If I cast it to a
> text array, ANY is happy.  But if I don't do so, what exactly does
> Postgres
> think my subquery has yielded?  And the error message still doesn't seem
> to
> make sense...
> 
> 
> 
> On Mon, Sep 16, 2013 at 6:59 PM, Ken Tanzer <

> ken.tanzer@

> > wrote:
> 
>> OK I tried that and see it works with the cast.  But now I'm confused
>> about both what exactly is failing without the cast, and about the
>> resulting error message.
>>
>> Is the query failing because PG doesn't understand the subquery is
>> yielding an array?  Seems unlikely.  But if the problem is a type
>> mismatch
>> between 'test' (on the left) and my subquery, I'd expect the same error
>> message as if I try to compare an int to a text array:
>>
>> SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','fail']));
>> ERROR:  operator does not exist: integer = text[]
>> LINE 1: SELECT 'found' WHERE 1 = ANY( (SELECT ARRAY['test','pass','f...
>>^
>> HINT:  No operator matches the given name and argument type(s). You might
>> need to add explicit type casts.
>>
>> Instead of the error message I actually got:
>>
>> ERROR:  array value must start with "{" or dimension information
>>
>> Thanks.
>>
>> Ken
>>
>>

>>> Your subquery can also be explicitly casted to make it work. Note the
>>> "::TEXT[]"
>>>
>>> SELECT 'found' WHERE 'test' = ANY( (SELECT
>>> ARRAY['test','pass','fail'])::TEXT[] );
>>>

Can we please follow list norms (or at least my example since I was the
first to respond) and bottom-post.

Sub-queries come in a few different flavors:

This:

ANY( (SELECT ARRAY[])::text[] )

works for the same reason this:

SELECT (SELECT col1 FROM (VALUES (ARRAY[4,5,6])) tbl (col1))::integer[]

works;

but this:

SELECT (SELECT col1 FROM (VALUES (ARRAY[1,2,3]),(ARRAY[4,5,6])) tbl
(col1))::integer[]

fails.

For this explanation I will simplify and state that there are two kinds of
sub-queries:

Scalar
Table-like (i.e., non-scalar)

A scalar sub-query must return, at most, one row and only a single column. 
A scalar sub-query can be used wherever a literal value is needed.

A table-like sub-query can return as many rows as desired and either one or
an unlimited number of columns - context depending.  In the case
"ANY(sub-query)" context it may only return a single column (but still
multiple rows).  In this context PostgreSQL goes looking for an operator -
e.g., equals(=) - with the right-side argument being of the type of the
sub-query column.  Since equals needs to have a matching type on the
left-side PostgreSQL presumes that whatever is on the left side must be of
the same type.  In this example you supplied an unadorned literal ('test')
which has no type information.  Thus PostgreSQL attempts to cast the unknown
literal to the type it requires (text[]) and fails since an array literal
must begin with '{'.  The attempt to cast 'test' failed.

WHERE 1 = ANY(SELECT ARRAY[]:text[])

This gives a different error because PostgreSQL knows that the number "1" is
an integer and thus has a known type - no cast is necessary.  However, since
the type "integer" does not match the needed type "text[]" a type mis-match
error is thrown or in this case no operator equals(integer, text[]) was
located.

It is the context of the use of the sub-query; not the form of the query
itself, that determines whether a particular sub-query will be treated as
scalar or table-like.  It is because "ANY(...)" can accept either a literal
or a table-like sub-query that this ambiguity arises.  

The example query just happens to only have a single row but nothing is
explicitly stopping it from generating more.  The work-around of casting the
sub-query to "text[]" works because the system knows that it must either get
a scalar result or the sub-query will throw an exception (as in my failing
example above).  The system cannot use the actual number of rows returned to
make the decision and so if you know that only one row (at most) can be
returned and you want a scalar interpretation you have to explicitly
indicate that in the query.

There is quite a bit more to this that can be gleaned by reading the
documentation for sub-queries.

Note that "(SELECT ARRAY[...])::text[]" is only a proper solution if you
cannot directly invoke the ARRAY[] syntax.  Where it comes in handy would be
something like:

WITH array_to_check (atc) AS ( VALUES (ARRAY[1,2,3]::integer[]) )
SELECT ...
FROM ...
WHERE 2 = ANY((SELECT atc FROM array_to_check)::integer[])

For the most part, however, forcing a non-scalar sub-query to become a
scalar sub-query is an indication that you are doing something wrong.

It may help to think of:

ANY(subquery) as meaning:

ANY(setof "whatever column type the subquery returns as its only column")

David J.




--
Vi

Re: [GENERAL] using Replace funcion in postgresql

[David Johnston]

karinos57 wrote
> SELECT 
>Volume, REPLACE(Volume,'.','')
>   FROM MyTable
> 
> The data in my table looks like this:
> 
> 88.97
> 448.58 and etc
> 
> i want to show like this with out the period:
> 
> 8897
> 44858
> 
> I have tried to use different ways but still getting the error i hope
> someone out there can help me. How can i achieve this? thanks

Solution provided elsewhere but make sure you cover the corner-case where
there are zeros in the decimal positions.  Depending on the how the number
column is defined those may be lost whereas I presume you want "00" to
appear at the end for integer amounts and "90" to appear to round-tenths.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/using-Replace-funcion-in-postgresql-tp5771164p5771184.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ¿Cómo comparar el resultado de dos consultas?

[Juan Daniel Santana Rodés]

Buenas...
Estoy desarrollando una tarea en la cual necesito saber cómo comparar el 
resultado de dos consultas...
He pensado en crear un procedimiento el cual reciba por parámetros ambas 
consultas respectivamente. Luego de alguna forma poder ejecutar las 
consultas y devolver si ambas tienen el mismo resultado. Como 
característica del problema, ambas consultas son de selección.

Acá dejo un pedazo del código de lo que quiero hacer.

create or replace function compare(sql1 character varying, sql2 
character varying) returns boolean as

$body$
Declare
Begin
 --acá de alguna manera poder ejecutar ambas consultas y luego conpararla
End;
$body$
language 'plpgsql';

He estado estudiando y me he encontrado de que existe EXECUTE pero para 
usarlo, antes se debe haber usado PREPARE, y en este caso los valores de 
los parámetros ya son consultas confeccionadas.

Por ejemplo la ejecución de la función sería algo como...

select compare('select * from table1', 'select * from table2');

Para este caso el resultado sería falso, pues las consultas son 
ejecutadas sobre tablas distintas.

Gracias de antemano.
Saludos cordiales desde Cuba.
__
"Todos el 12 de Septiembre con una Cinta Amarilla"
FIN A LA INJUSTICIA, LIBERENLOS YA!!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general