Thread: Visibility of data from table inherits function

Recipients,

A table was created:

CREATE TABLE exampletable (
name varchar(200)
);

Table 'exampletable' _already_ contains data, e.g. 'Jane Bloggs'

A new table was created with inheritance of 'exampletable':

CREATE TABLE exampletablechild (dates DATE) INHERITS (exampletable);
SET DATESTYLE TO 'SQL, EUROPEAN';

\d exampletablechild

"
...
Inherits: exampletable
"

SELECT name FROM exampletablechild;
"
name
------
(0 rows)
"

SELECT name FROM exampletable;

"
name
--------------
 jane bloggs
"

Please could someone explain why the data in the table 'exampletable' is not visible from the query using the child
table?

Does not 'INHERIT' function apply to the data of the precedent parent table?

Please could someone explain why the data in the table 'exampletable' is not visible from the query using the child table?

Does not 'INHERIT' function apply to the data of the precedent parent table?

On Sun, 2 Jan 2022 at 13:23, ourdiaspora <ourdiaspora@protonmail.com> wrote:
> CREATE TABLE exampletable (
> name varchar(200)
> );
...
> CREATE TABLE exampletablechild (dates DATE) INHERITS (exampletable);
...
> Please could someone explain why the data in the table 'exampletable' is not visible from the query using the child
table?
> Does not 'INHERIT' function apply to the data of the precedent parent table?

No, it works the other way round, somehow like OO inheritance.

When you query a table you will see its data and all of the tables
that inherit from it.

Take a look at https://www.postgresql.org/docs/14/tutorial-inheritance.html
. Ii is not shown there, but you canot see cities through capitals
because they do not have state, so they do not match the structure,
but you can see capitals through cities.

Inherits does not copy things. LIKE on creation does, but AFAIK it can
copy nearly everything except data.

Francisco Olarte.

On Sunday, January 2nd, 2022 at 3:54 PM, Francisco Olarte <folarte@peoplecall.com> wrote:
>
> Inherits does not copy things. LIKE on creation does, but AFAIK it can
>
> copy nearly everything except data.
>

Thanks, had read that part of the documentation.

So far it seems that functions 'joins' may be used to view data, whilst the functions 'views' is unable to be used with
dataentry. 

Please, any suggestions of an alternative function to use?

On 1/2/22 11:41, ourdiaspora wrote:
> On Sunday, January 2nd, 2022 at 3:54 PM, Francisco Olarte <folarte@peoplecall.com> wrote:
>>
>> Inherits does not copy things. LIKE on creation does, but AFAIK it can
>>
>> copy nearly everything except data.
>>
> 
> Thanks, had read that part of the documentation.
> 
> So far it seems that functions 'joins' may be used to view data, whilst the functions 'views' is unable to be used
withdata entry.
 

They are not functions. A join is a clause in a query. A view is an 
object that can be created and then used like a table.

Views can be used for data entry, see:

https://www.postgresql.org/docs/current/sql-createview.html

Search for section:

Updatable Views

> 
> Please, any suggestions of an alternative function to use?
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Στις 2/1/22 2:23 μ.μ., ο/η ourdiaspora έγραψε:

> Recipients,
>
> A table was created:
>
> CREATE TABLE exampletable (
> name varchar(200)
> );
>
> Table 'exampletable' _already_ contains data, e.g. 'Jane Bloggs'
>
> A new table was created with inheritance of 'exampletable':
>
>
> Please could someone explain why the data in the table 'exampletable' is not visible from the query using the child
table?
>
> Does not 'INHERIT' function apply to the data of the precedent parent table?
Think of it as a list of Numbers in java. Assume you are looking for 
Double's, then you transverse the list looking for only Doubles, then 
for Integers and you get only Integers, then for Numbers and you get the 
whole list. This is the same concept in pgsql inheritance.  A row of 
"type"  exampletablechild is a row of "type" exampletable, but not vice 
versa: A row of exampletable is NOT a row of exampletablechild. 
Selecting directly from exampletablechild it should return to you only 
exampletablechild and below.
>
>
>

On Sun, 2 Jan 2022 at 20:42, ourdiaspora <ourdiaspora@protonmail.com> wrote:
> On Sunday, January 2nd, 2022 at 3:54 PM, Francisco Olarte <folarte@peoplecall.com> wrote:
> > Inherits does not copy things. LIKE on creation does, but AFAIK it can
> > copy nearly everything except data.
> Thanks, had read that part of the documentation.
> So far it seems that functions 'joins' may be used to view data, whilst the functions 'views' is unable to be used
withdata entry.
 
> Please, any suggestions of an alternative function to use?

I would first suggest you try to describe what you are trying to
achieve, just to avoid https://xyproblem.info.

If what you want to achieve is copying what is in table PARENT when
you create table CHILD the solution is easy, providing all the extra
columns have DEFAULT values, just use a CREATE LIKE followed by an
INSERT INTO CHILD SELECT * FROM PARENT, it is a simple two liner that
you can wrap in a function if you need to use it a lot in your work
flow.

If you want to see the rows of BOTH when you select from CHILD you
need a VIEW, as PARENT has missing columns.

As you have not stated what you want to achieve, even in the examples,
not much can be suggested, hence the cite of XYproblem.

Francisco Olarte.