Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
On Tue, Apr 29, 2014 at 2:40 PM, Dorian Hoxha dorian.ho...@gmail.com wrote: So : drop function alter type: add column create again function with new default argument in a transaction ? Yeah -- something like that. Try it out (carefully). AIUI, In most cases the function execution will be inlined so the performance hit should be minimal vs raw insert. 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] Altering array(composite-types) without breaking code when inserting them and similar questions
On Sun, Apr 27, 2014 at 4:57 PM, Dorian Hoxha dorian.ho...@gmail.com wrote: Since my alternative is using json, that is heavier (need to store keys in every row) than composite-types. Updating an element on a specific composite_type inside an array of them is done by UPDATE table SET composite[2].x = 24; So last standing question, is it possible to insert an array of composite_types by not specifying all of the columns for each composite_type ? So if i later add other columns to the composite_type, the insert query doesn't break ? One way to do it is via 'type constructor function'. postgres=# create type foo_t as (a int, b int); postgres=# create function foo_t(a int, b int) returns foo_t as $$ select row(a,b)::foo_t; $$ language sql stable; postgres=# create table bar(f foo_t); postgres=# insert into bar VALUES ((1,2)::foo_t); -- not tolerant to changes postgres=# insert into bar VALUES (foo_t(1,2)); -- works fine now, to extend the field, we can overload the function making sure to default the 3rd argument. alter type foo_t add attribute c text; drop function foo_t(int, int); -- must do this to make function unambiguous create function foo_t(a int, b int, c text = null) returns foo_t as $$ select row(a,b,c)::foo_t; $$ language sql stable; postgres=# INSERT INTO bar VALUES ((1,2)::foo_t); postgres=# insert into bar VALUES (foo_t(1,2,'test')); -- works fine This technique is somewhat dubious, but if for whatever reason you absolutely must preserve client sql in the face of server changes it might work. 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] Altering array(composite-types) without breaking code when inserting them and similar questions
So : 1. drop function 2. alter type: add column 3. create again function with new default argument in a transaction ? On Tue, Apr 29, 2014 at 4:22 PM, Merlin Moncure mmonc...@gmail.com wrote: On Sun, Apr 27, 2014 at 4:57 PM, Dorian Hoxha dorian.ho...@gmail.com wrote: Since my alternative is using json, that is heavier (need to store keys in every row) than composite-types. Updating an element on a specific composite_type inside an array of them is done by UPDATE table SET composite[2].x = 24; So last standing question, is it possible to insert an array of composite_types by not specifying all of the columns for each composite_type ? So if i later add other columns to the composite_type, the insert query doesn't break ? One way to do it is via 'type constructor function'. postgres=# create type foo_t as (a int, b int); postgres=# create function foo_t(a int, b int) returns foo_t as $$ select row(a,b)::foo_t; $$ language sql stable; postgres=# create table bar(f foo_t); postgres=# insert into bar VALUES ((1,2)::foo_t); -- not tolerant to changes postgres=# insert into bar VALUES (foo_t(1,2)); -- works fine now, to extend the field, we can overload the function making sure to default the 3rd argument. alter type foo_t add attribute c text; drop function foo_t(int, int); -- must do this to make function unambiguous create function foo_t(a int, b int, c text = null) returns foo_t as $$ select row(a,b,c)::foo_t; $$ language sql stable; postgres=# INSERT INTO bar VALUES ((1,2)::foo_t); postgres=# insert into bar VALUES (foo_t(1,2,'test')); -- works fine This technique is somewhat dubious, but if for whatever reason you absolutely must preserve client sql in the face of server changes it might work. merlin
Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
Since my alternative is using json, that is heavier (need to store keys in every row) than composite-types. Updating an element on a specific composite_type inside an array of them is done by UPDATE table SET composite[2].x = 24; So last standing question, is it possible to insert an array of composite_types by not specifying all of the columns for each composite_type ? So if i later add other columns to the composite_type, the insert query doesn't break ? Thanks On Mon, Apr 21, 2014 at 1:46 PM, Dorian Hoxha dorian.ho...@gmail.comwrote: Maybe the char array link is wrong ? I don't think an array of arrays is good for my case. I'll probably go for json or separate table since it looks it's not possible to use composite-types. On Mon, Apr 21, 2014 at 4:02 AM, Rob Sargentg robjsarg...@gmail.comwrote: Sorry, I should not have top-posted (Dang iPhone). Continued below: On 04/20/2014 05:54 PM, Dorian Hoxha wrote: Because i always query the whole row, and in the other way(many tables) i will always join + have other indexes. On Sun, Apr 20, 2014 at 8:56 PM, Rob Sargent robjsarg...@gmail.comwrote: Why do you think you need an array of theType v. a dependent table of theType. This tack is of course immune to to most future type changess. Sent from my iPhone Interesting. Of course any decent mapper will return the whole row. And would it be less disk intensive as an array of struct ( where struct is implemented as an array). From other threads [1] [2] I've come to understand the datatype overhead per native type will be applied per type instance per array element. [1] 30K floatshttp://postgresql.1045698.n5.nabble.com/Is-it-reasonable-to-store-double-arrays-of-30K-elements-td5790562.html [2] char arrayhttp://postgresql.1045698.n5.nabble.com/COPY-v-java-performance-comparison-tc5798389.html
Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
Maybe the char array link is wrong ? I don't think an array of arrays is good for my case. I'll probably go for json or separate table since it looks it's not possible to use composite-types. On Mon, Apr 21, 2014 at 4:02 AM, Rob Sargentg robjsarg...@gmail.com wrote: Sorry, I should not have top-posted (Dang iPhone). Continued below: On 04/20/2014 05:54 PM, Dorian Hoxha wrote: Because i always query the whole row, and in the other way(many tables) i will always join + have other indexes. On Sun, Apr 20, 2014 at 8:56 PM, Rob Sargent robjsarg...@gmail.comwrote: Why do you think you need an array of theType v. a dependent table of theType. This tack is of course immune to to most future type changess. Sent from my iPhone Interesting. Of course any decent mapper will return the whole row. And would it be less disk intensive as an array of struct ( where struct is implemented as an array). From other threads [1] [2] I've come to understand the datatype overhead per native type will be applied per type instance per array element. [1] 30K floatshttp://postgresql.1045698.n5.nabble.com/Is-it-reasonable-to-store-double-arrays-of-30K-elements-td5790562.html [2] char arrayhttp://postgresql.1045698.n5.nabble.com/COPY-v-java-performance-comparison-tc5798389.html
[GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
Hi list, I have a create type thetype(width integer, height integer); create table mytable(thetype thetype[]); How can i make an insert statement so if i later add fields to the composite type, the code/query doesn't break ? Maybe by specifying the fields of the composite type in the query ? This can be done for normal inserts(non arrays): CREATE TABLE mytable (t thetype); INSERT INTO mytable(t.width, t.height) VALUES (11,22); Also how to update an whole element of an array of composites ? Also, how to update an attribute in a specific element in an array of composites? (so when i add columns later to the composite, my old code doesn't break) How much overhead have the composite types beside the values and nulls? Thanks
Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
If you don't know the columns your type will have, you could consider using json or hstore if the data is unstructured. El 20/04/2014 14:04, Dorian Hoxha dorian.ho...@gmail.com escribió: Hi list, I have a create type thetype(width integer, height integer); create table mytable(thetype thetype[]); How can i make an insert statement so if i later add fields to the composite type, the code/query doesn't break ? Maybe by specifying the fields of the composite type in the query ? This can be done for normal inserts(non arrays): CREATE TABLE mytable (t thetype); INSERT INTO mytable(t.width, t.height) VALUES (11,22); Also how to update an whole element of an array of composites ? Also, how to update an attribute in a specific element in an array of composites? (so when i add columns later to the composite, my old code doesn't break) How much overhead have the composite types beside the values and nulls? Thanks
Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
Was just curious about the overhead. I know the columns, but i may need to add other columns in the future. Yeah, json is the alternative if this doesn't work. On Sun, Apr 20, 2014 at 7:54 PM, Fede Martinez federicoemarti...@gmail.comwrote: If you don't know the columns your type will have, you could consider using json or hstore if the data is unstructured. El 20/04/2014 14:04, Dorian Hoxha dorian.ho...@gmail.com escribió: Hi list, I have a create type thetype(width integer, height integer); create table mytable(thetype thetype[]); How can i make an insert statement so if i later add fields to the composite type, the code/query doesn't break ? Maybe by specifying the fields of the composite type in the query ? This can be done for normal inserts(non arrays): CREATE TABLE mytable (t thetype); INSERT INTO mytable(t.width, t.height) VALUES (11,22); Also how to update an whole element of an array of composites ? Also, how to update an attribute in a specific element in an array of composites? (so when i add columns later to the composite, my old code doesn't break) How much overhead have the composite types beside the values and nulls? Thanks
Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
Why do you think you need an array of theType v. a dependent table of theType. This tack is of course immune to to most future type changess. Sent from my iPhone On Apr 20, 2014, at 11:57 AM, Dorian Hoxha dorian.ho...@gmail.com wrote: Was just curious about the overhead. I know the columns, but i may need to add other columns in the future. Yeah, json is the alternative if this doesn't work. On Sun, Apr 20, 2014 at 7:54 PM, Fede Martinez federicoemarti...@gmail.com wrote: If you don't know the columns your type will have, you could consider using json or hstore if the data is unstructured. El 20/04/2014 14:04, Dorian Hoxha dorian.ho...@gmail.com escribió: Hi list, I have a create type thetype(width integer, height integer); create table mytable(thetype thetype[]); How can i make an insert statement so if i later add fields to the composite type, the code/query doesn't break ? Maybe by specifying the fields of the composite type in the query ? This can be done for normal inserts(non arrays): CREATE TABLE mytable (t thetype); INSERT INTO mytable(t.width, t.height) VALUES (11,22); Also how to update an whole element of an array of composites ? Also, how to update an attribute in a specific element in an array of composites? (so when i add columns later to the composite, my old code doesn't break) How much overhead have the composite types beside the values and nulls? Thanks
Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
Because i always query the whole row, and in the other way(many tables) i will always join + have other indexes. On Sun, Apr 20, 2014 at 8:56 PM, Rob Sargent robjsarg...@gmail.com wrote: Why do you think you need an array of theType v. a dependent table of theType. This tack is of course immune to to most future type changess. Sent from my iPhone On Apr 20, 2014, at 11:57 AM, Dorian Hoxha dorian.ho...@gmail.com wrote: Was just curious about the overhead. I know the columns, but i may need to add other columns in the future. Yeah, json is the alternative if this doesn't work. On Sun, Apr 20, 2014 at 7:54 PM, Fede Martinez federicoemarti...@gmail.com wrote: If you don't know the columns your type will have, you could consider using json or hstore if the data is unstructured. El 20/04/2014 14:04, Dorian Hoxha dorian.ho...@gmail.com escribió: Hi list, I have a create type thetype(width integer, height integer); create table mytable(thetype thetype[]); How can i make an insert statement so if i later add fields to the composite type, the code/query doesn't break ? Maybe by specifying the fields of the composite type in the query ? This can be done for normal inserts(non arrays): CREATE TABLE mytable (t thetype); INSERT INTO mytable(t.width, t.height) VALUES (11,22); Also how to update an whole element of an array of composites ? Also, how to update an attribute in a specific element in an array of composites? (so when i add columns later to the composite, my old code doesn't break) How much overhead have the composite types beside the values and nulls? Thanks
Re: [GENERAL] Altering array(composite-types) without breaking code when inserting them and similar questions
Sorry, I should not have top-posted (Dang iPhone). Continued below: On 04/20/2014 05:54 PM, Dorian Hoxha wrote: Because i always query the whole row, and in the other way(many tables) i will always join + have other indexes. On Sun, Apr 20, 2014 at 8:56 PM, Rob Sargent robjsarg...@gmail.com mailto:robjsarg...@gmail.com wrote: Why do you think you need an array of theType v. a dependent table of theType. This tack is of course immune to to most future type changess. Sent from my iPhone Interesting. Of course any decent mapper will return the whole row. And would it be less disk intensive as an array of struct ( where struct is implemented as an array). From other threads [1] [2] I've come to understand the datatype overhead per native type will be applied per type instance per array element. [1] 30K floats http://postgresql.1045698.n5.nabble.com/Is-it-reasonable-to-store-double-arrays-of-30K-elements-td5790562.html [2] char array http://postgresql.1045698.n5.nabble.com/COPY-v-java-performance-comparison-tc5798389.html
