Postgres Pro
Downloads
Home   >   mailing lists

Thread: partitoning expert : Partitonning with specialization of one column type

partitoning expert : Partitonning with specialization of one column type

From
Rémi Cura
Date:
Hey, postgres 9.3 here.
for partitionning expert.

I have several table child with columns (gid serial, patch pcpatch(n))
where n may change depending on the tables, and pcpatch is a type from pgpointcloud extension
(here is the definition)
-----
CREATE TYPE pcpatch
   (INPUT=pcpatch_in,
       OUTPUT=pcpatch_out,
       RECEIVE=-,
       SEND=-,
       TYPMOD_IN=pc_typmod_in,
       TYPMOD_OUT=pc_typmod_out,
       ANALYZE=-,
       CATEGORY='U', DEFAULT='',
       INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=MAIN);
-----

The question is, how do I create an inheritance scheme?

If the father table is

CREATE TABLE father(
gid serial,
patch pcpatch
) ;

trying to put child_1, child_2 ... to inherit father raise an error

----
child table "test_child_1" has different type for column "patch"
----

So my question is, how would it be possible (if at all) to inherit of father table, while specializing the type of father table in child table?

Thanks,
Cheers,
Rémi-C

Re: partitoning expert : Partitonning with specialization of one column type

From
Steven Erickson
Date:

Your child could be:

 

CREATE TABLE child-1(

patchn pcpatchn

) INHERITS (father);

 

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rémi Cura
Sent: Wednesday, April 01, 2015 8:41 AM
To: PostgreSQL General
Subject: [GENERAL] partitoning expert : Partitonning with specialization of one column type

 

Hey, postgres 9.3 here.

for partitionning expert.

I have several table child with columns (gid serial, patch pcpatch(n))

where n may change depending on the tables, and pcpatch is a type from pgpointcloud extension

(here is the definition)

-----
CREATE TYPE pcpatch
   (INPUT=pcpatch_in,
       OUTPUT=pcpatch_out,
       RECEIVE=-,
       SEND=-,
       TYPMOD_IN=pc_typmod_in,
       TYPMOD_OUT=pc_typmod_out,
       ANALYZE=-,
       CATEGORY='U', DEFAULT='',
       INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=MAIN);
-----

The question is, how do I create an inheritance scheme?

If the father table is

CREATE TABLE father(

gid serial,

patch pcpatch

) ;

trying to put child_1, child_2 ... to inherit father raise an error

----
child table "test_child_1" has different type for column "patch"
----

So my question is, how would it be possible (if at all) to inherit of father table, while specializing the type of father table in child table?

Thanks,

Cheers,

Rémi-C

NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.

Re: partitoning expert : Partitonning with specialization of one column type

From
Rémi Cura
Date:
Hm sorry I wasn't very clear

child table already have type pcpatch(3) , pcpatch(4), etc.
This is the normal behaviour and can't be changed.

pcpatch(n) are specialization of pcpatch type.

pcpatch(3) is widely different from pcpatch(4)

For those who know PostGIS pcpatch is like "geometry" and pcpatch(3) like "geometry(point)" and pcpatch(5) like "geometry(polygon)",
for instance.

Cheers,
Rémi-C


2015-04-01 16:21 GMT+02:00 Steven Erickson <Steven.Erickson@telventdtn.com>:

Your child could be:

 

CREATE TABLE child-1(

patchn pcpatchn

) INHERITS (father);

 

 

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rémi Cura
Sent: Wednesday, April 01, 2015 8:41 AM
To: PostgreSQL General
Subject: [GENERAL] partitoning expert : Partitonning with specialization of one column type

 

Hey, postgres 9.3 here.

for partitionning expert.

I have several table child with columns (gid serial, patch pcpatch(n))

where n may change depending on the tables, and pcpatch is a type from pgpointcloud extension

(here is the definition)

-----
CREATE TYPE pcpatch
   (INPUT=pcpatch_in,
       OUTPUT=pcpatch_out,
       RECEIVE=-,
       SEND=-,
       TYPMOD_IN=pc_typmod_in,
       TYPMOD_OUT=pc_typmod_out,
       ANALYZE=-,
       CATEGORY='U', DEFAULT='',
       INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=MAIN);
-----

The question is, how do I create an inheritance scheme?

If the father table is

CREATE TABLE father(

gid serial,

patch pcpatch

) ;

trying to put child_1, child_2 ... to inherit father raise an error

----
child table "test_child_1" has different type for column "patch"
----

So my question is, how would it be possible (if at all) to inherit of father table, while specializing the type of father table in child table?

Thanks,

Cheers,

Rémi-C

NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.


Re: partitoning expert : Partitonning with specialization of one column type

From
"David G. Johnston"
Date:
On Wed, Apr 1, 2015 at 7:26 AM, Rémi Cura <remi.cura@gmail.com> wrote:
pcpatch(n) are specialization of pcpatch type.

​While this may be true PostgreSQL doesn't understand "specialization" of data types.  If you cannot do as Steven suggested you do not get to leverage inheritance directly and will need to devise your own work-arounds for the capabilities that you require.

I'm somewhat surprised that what you describe works at all - mainly given that functions do not allow typemod specifications to pass through the function invocation...but alas I'm not all that familiar with PostGIS and whatever is being done does indeed seem to be working...

David J.
​

Re: partitoning expert : Partitonning with specialization of one column type

From
Rémi Cura
Date:
Okay,
thanks for the answer.
I take it that it is "do it differently then".

I might have an idea but it is not working yet, so I'll post another message.

Thanks dear co-list user ^^
Cheers,
Rémi-C
 

2015-04-01 16:56 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>:
On Wed, Apr 1, 2015 at 7:26 AM, Rémi Cura <remi.cura@gmail.com> wrote:
pcpatch(n) are specialization of pcpatch type.

​While this may be true PostgreSQL doesn't understand "specialization" of data types.  If you cannot do as Steven suggested you do not get to leverage inheritance directly and will need to devise your own work-arounds for the capabilities that you require.

I'm somewhat surprised that what you describe works at all - mainly given that functions do not allow typemod specifications to pass through the function invocation...but alas I'm not all that familiar with PostGIS and whatever is being done does indeed seem to be working...

David J.
​