Compare with default value?

On Saturday, March 13, 2021, Ulrich Goebel <ml@fam-goebel.de> wrote:

I would like to get the rows, where a column has the default value,
similar to:

select id fromt tbl where col = default

If the default is a simple constant then why go through the trouble instead
of just writing col = ‘constant’ ?

David J.

Hi,

o.k. I have to give some more information...

Am 13.03.21 um 19:39 schrieb David G. Johnston:

On Saturday, March 13, 2021, Ulrich Goebel <ml@fam-goebel.de
<mailto:ml@fam-goebel.de>> wrote:

I would like to get the rows, where a column has the default value,
similar to:

select id fromt tbl where col = default

If the default is a simple constant then why go through the trouble
instead of just writing col = ‘constant’ ?

What I want to build is a more ore less generic app, completes a row
(orig) in an table with data from another row (complete). That only for
the cols where the orig holds the default value. The app shouldn't know
to much about the table structure. It should work like that:

for col in columns
if orig.col is not the default
orig.col = complete.col
update orig in the database
delete complete

It is somewhat unclear for me whicht part of the problem is do be coded
in a SQL-Statement and which part in my programming language (Python3).
But it could help to get a reference to the default value.

Ulrich

--
Ulrich Goebel
Am Büchel 57, 53173 Bonn

On Sat, Mar 13, 2021 at 1:41 PM Ulrich Goebel <ml@fam-goebel.de> wrote:

But it could help to get a reference to the default value.

There is no such thing as a "default value". There is a "default
expression" though. It should be available in the system catalogs as part
of the definition of a table. But I'm of the general impression that
attributing some special significance to a value in the database because it
happens to have been computed by a default expression instead of some
explicitly supplied value is generally a wrong approach to take. The data
should be interpreted in an internally self-sufficient way.

David J.

On 3/13/21 1:05 PM, Ulrich Goebel wrote:

I would like to get the rows, where a column has the default value,
similar to:

select id fromt tbl where col = default

Is there a chance?

It isn't pretty, and not all that practical, but for example:

8<--------------------
create table def(id int, f1 text not null default 'foo');

insert into def values(1,'hello'),(2,default),(3,default),(4,'bar');

SELECT *
FROM def
WHERE $$'$$ || f1 || $$'::text$$ =
(SELECT pg_get_expr(d.adbin, d.adrelid, true)
FROM pg_attrdef d
JOIN pg_attribute a on d.adrelid = a.attrelid
AND d.adnum = a.attnum
WHERE a.attrelid = 'def'::regclass
AND a.attname = 'f1');
id | f1
----+-----
2 | foo
3 | foo
(2 rows)
8<--------------------

Hope this helps,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On Mar 13, 2021, at 1:51 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:



On Sat, Mar 13, 2021 at 1:41 PM Ulrich Goebel <ml@fam-goebel.de> wrote:
But it could help to get a reference to the default value.

There is no such thing as a "default value". There is a "default expression" though. It should be available in the system catalogs as part of the definition of a table. But I'm of the general impression that attributing some special significance to a value in the database because it happens to have been computed by a default expression instead of some explicitly supplied value is generally a wrong approach to take. The data should be interpreted in an internally self-sufficient way.

David J.

Can you distinguish the case of the actual value matching the (computed) default?

Ulrich Goebel <ml@fam-goebel.de> writes:

Hi,

o.k. I have to give some more information...

Am 13.03.21 um 19:39 schrieb David G. Johnston:

On Saturday, March 13, 2021, Ulrich Goebel <ml@fam-goebel.de
<mailto:ml@fam-goebel.de>> wrote:

I would like to get the rows, where a column has the default value,
similar to:
select id fromt tbl where col = default

If the default is a simple constant then why go through the trouble instead of
just writing col = ‘constant’ ?

I think you may need to re-think your design or at least come at it from
a different perspective. As shown by another post in the thread, at some
level, this is 'sort of' possible, but it will be ugly and fragile.

Possibly one of the issues you have is on one hand, you want the
solution to be as generic as possible, but on the other, you require it
to have specific knowledge about the definition of your table. i.e.
which column(s) have a default value. Furthermore, your 'completion'
table will need to be defined based on this information i.e. potentially
multiple columns with multiple different data types etc.

While the additional information you provided does give some increased
understanding of what your trying to do, it is still describing your
desired solution implementation. It might be better if you provide more
high level details of what your attempting to do so that we can better
understand how you arrived at the proposed solution and whether there
may be better alternatives available.

Hi,

Am 13.03.21 um 22:21 schrieb Tim Cross:

Ulrich Goebel <ml@fam-goebel.de> writes:

Hi,

o.k. I have to give some more information...

Am 13.03.21 um 19:39 schrieb David G. Johnston:

On Saturday, March 13, 2021, Ulrich Goebel <ml@fam-goebel.de
<mailto:ml@fam-goebel.de>> wrote:

I would like to get the rows, where a column has the default value,
similar to:
select id fromt tbl where col = default

If the default is a simple constant then why go through the trouble instead of
just writing col = ‘constant’ ?

I think you may need to re-think your design or at least come at it from
a different perspective. As shown by another post in the thread, at some
level, this is 'sort of' possible, but it will be ugly and fragile.

Yes, I am re-thinking allredy...

Possibly one of the issues you have is on one hand, you want the
solution to be as generic as possible, but on the other, you require it
to have specific knowledge about the definition of your table. i.e.
which column(s) have a default value. Furthermore, your 'completion'
table will need to be defined based on this information i.e. potentially
multiple columns with multiple different data types etc.

While the additional information you provided does give some increased
understanding of what your trying to do, it is still describing your
desired solution implementation. It might be better if you provide more
high level details of what your attempting to do so that we can better
understand how you arrived at the proposed solution and whether there
may be better alternatives available.

o.k.: For a conference I have a tbl_person which holds all peoble which
are involved: participants and people which provide private lodgins for
other participants. (There are much more roles, but for illustration
these two should be enough.) Of course each person can have one or more
roles at the conference, a n-n-relation models that. Now our workflow
allows that one person find it way in the tbl_person twice (ore even
more often): for example the conference office generates a row for Tom,
because he provides private lodgin. Later on Tom decides to participate
an fills the online registration formular. These data generate the
second row for Tom. Both rows hold significant information which the
other doesn't hold. Let's say the online register gave the birthday, the
other holds information about the lodgin (bed with or without
breakfast). The next step then is that the conference office get notice
of the doubled person an should make one row out of the existing two
rows. The office decide which of the two rows should be completed with
data from the other row. Therefore I would like to pick the columns in
the first row where we have default values and replace it by the value
from the second row.

There are more ways to end with two or even more rows per person. May be
it would have been much better to avoid these possibilities. But for the
moment I have a given database structure running in productive mode, so
it is not easy to re-structure the structure or even the workflows...

Thanks for patience reading all that!

--
Ulrich Goebel
Am Büchel 57, 53173 Bonn

On 3/14/21 6:30 AM, Ulrich Goebel wrote:

Hi,

Am 13.03.21 um 22:21 schrieb Tim Cross:

I think you may need to re-think your design or at least come at it from
a different perspective. As shown by another post in the thread, at some
level, this is 'sort of' possible, but it will be ugly and fragile.

Yes, I am re-thinking allredy...

o.k.: For a conference I have a tbl_person which holds all peoble which
are involved: participants and people which provide private lodgins for
other participants. (There are much more roles, but for illustration
these two should be enough.) Of course each person can have one or more
roles at the conference, a n-n-relation models that. Now our workflow
allows that one person find it way in the tbl_person twice (ore even
more often): for example the conference office generates a row for Tom,
because he provides private lodgin. Later on Tom decides to participate
an fills the online registration formular. These data generate the
second row for Tom. Both rows hold significant information which the
other doesn't hold. Let's say the online register gave the birthday, the
other holds information about the lodgin (bed with or without
breakfast). The next step then is that the conference office get notice
of the doubled person an should make one row out of the existing two
rows. The office decide which of the two rows should be completed with
data from the other row. Therefore I would like to pick the columns in
the first row where we have default values and replace it by the value
from the second row.

This is just part of a bigger issue, which value to believe:

col_1 col_2 col_3 col_4
row 1 default some_val default some_val
row 2 other_val default default other_val

How do you arrive at assumption that row 2(or any other row) has
precedence over row 1?

There are more ways to end with two or even more rows per person. May be
it would have been much better to avoid these possibilities. But for the
moment I have a given database structure running in productive mode, so
it is not easy to re-structure the structure or even the workflows...

Thanks for patience reading all that!

--
Adrian Klaver
adrian.klaver@aklaver.com

Am 14.03.21 um 18:13 schrieb Adrian Klaver:

On 3/14/21 6:30 AM, Ulrich Goebel wrote:

Hi,

Am 13.03.21 um 22:21 schrieb Tim Cross:

I think you may need to re-think your design or at least come at it from
a different perspective. As shown by another post in the thread, at some
level, this is 'sort of' possible, but it will be ugly and fragile.

Yes, I am re-thinking allredy...

o.k.: For a conference I have a tbl_person which holds all peoble
which are involved: participants and people which provide private
lodgins for other participants. (There are much more roles, but for
illustration these two should be enough.) Of course each person can
have one or more roles at the conference, a n-n-relation models that.
Now our workflow allows that one person find it way in the tbl_person
twice (ore even more often): for example the conference office
generates a row for Tom, because he provides private lodgin. Later on
Tom decides to participate an fills the online registration formular.
These data generate the second row for Tom. Both rows hold significant
information which the other doesn't hold. Let's say the online
register gave the birthday, the other holds information about the
lodgin (bed with or without breakfast). The next step then is that the
conference office get notice of the doubled person an should make one
row out of the existing two rows. The office decide which of the two
rows should be completed with data from the other row. Therefore I
would like to pick the columns in the first row where we have default
values and replace it by the value from the second row.

This is just part of a bigger issue, which value to believe:

    col_1         col_2          col_3      col_4
row 1    default      some_val    default      some_val
row 2    other_val       default         default   other_val

How do you arrive at assumption that row 2(or any other row) has
precedence over row 1?

That goes in two steps:
1. The conference office will decide, which of the two rows will be
completed by the other
2. My function will do the job, but only for the columns, where it would
overwrite the default value.

By the way: the discussion here brought me to give up the completely
automated (generic) way. Now in my python code for step 2. I go through
all of the columns and decide for each of them what to do:
- either to overwrite a default value (which I define in python rather
then take it from the database)
- or put the values together (for examples in the case of "remarks",
which could be significant in both rows
- or even other handling...

Thank You all for creative thinking on my issue!

There are more ways to end with two or even more rows per person. May
be it would have been much better to avoid these possibilities. But
for the moment I have a given database structure running in productive
mode, so it is not easy to re-structure the structure or even the
workflows...

Thanks for patience reading all that!

--
Ulrich Goebel
Am Büchel 57, 53173 Bonn

Ulrich Goebel <ml@fam-goebel.de> writes:

Hi,

Am 13.03.21 um 22:21 schrieb Tim Cross:

Ulrich Goebel <ml@fam-goebel.de> writes:

Hi,

o.k. I have to give some more information...

Am 13.03.21 um 19:39 schrieb David G. Johnston:

On Saturday, March 13, 2021, Ulrich Goebel <ml@fam-goebel.de
<mailto:ml@fam-goebel.de>> wrote:

I would like to get the rows, where a column has the default value,
similar to:
select id fromt tbl where col = default

If the default is a simple constant then why go through the trouble instead of
just writing col = ‘constant’ ?

I think you may need to re-think your design or at least come at it from
a different perspective. As shown by another post in the thread, at some
level, this is 'sort of' possible, but it will be ugly and fragile.

Yes, I am re-thinking allredy...

Possibly one of the issues you have is on one hand, you want the
solution to be as generic as possible, but on the other, you require it
to have specific knowledge about the definition of your table. i.e.
which column(s) have a default value. Furthermore, your 'completion'
table will need to be defined based on this information i.e. potentially
multiple columns with multiple different data types etc.
While the additional information you provided does give some increased
understanding of what your trying to do, it is still describing your
desired solution implementation. It might be better if you provide more
high level details of what your attempting to do so that we can better
understand how you arrived at the proposed solution and whether there
may be better alternatives available.

o.k.: For a conference I have a tbl_person which holds all peoble which are
involved: participants and people which provide private lodgins for other
participants. (There are much more roles, but for illustration these two should
be enough.) Of course each person can have one or more roles at the conference,
a n-n-relation models that. Now our workflow allows that one person find it way
in the tbl_person twice (ore even more often): for example the conference office
generates a row for Tom, because he provides private lodgin. Later on Tom
decides to participate an fills the online registration formular. These data
generate the second row for Tom. Both rows hold significant information which
the other doesn't hold. Let's say the online register gave the birthday, the
other holds information about the lodgin (bed with or without breakfast). The
next step then is that the conference office get notice of the doubled person an
should make one row out of the existing two rows. The office decide which of the
two rows should be completed with data from the other row. Therefore I would
like to pick the columns in the first row where we have default values and
replace it by the value from the second row.

There are more ways to end with two or even more rows per person. May be it
would have been much better to avoid these possibilities. But for the moment I
have a given database structure running in productive mode, so it is not easy to
re-structure the structure or even the workflows...

I really hate to say this, but I think you have a major fundamental flaw
in your database design. As soon as I read

Now our workflow allows that one person find it way
in the tbl_person twice (ore even more often): for example the conference office
generates a row for Tom, because he provides private lodgin. Later on Tom
decides to participate an fills the online registration formular. These data
generate the second row for Tom. Both rows hold significant
information....

I thought - "oh dear, this is going to be a problem".

Based on this and your previous post, I suspect your now beginning to
run into maintenance problems with your application database and are
trying to find ways to deal with the issues your encountering. The good
news, it can be fixed. The bad news, it will take a fair amount of work.
The really really bad news is while you may be able to work around some
of the issues, things are just going to snowball and get worse,
requiring increasing amounts of maintenance and increasing amounts of
effort to fix.

Although you have said there is an existing structure in production and
changing it will not be easy, bottom line is that it really is your only
sustainable course of action. Anything else you do will at best delay
the inevitable and at worst could result in far worse consequences (such
as major data loss or curruption resulting from efforts to maintain a
poor data model design). What you need to do is re-examine your data
model e.g. tables, columns, views etc and make sure you are at least
meeting 3rd normal form
(https://www.guru99.com/database-normalization.html).

This is really your only sane solution. There are things you can do with
views and database functions which can isolate/reduce the impact of changing
your underlying database structure on your front end application and
workflows to buy you some time, but these will likely need to be
re-worked eventually as well.

I know this seems like a daunting task which will have significant
impact and is unlikely to be welcomed by senior management, but you
don't have much choice. Having the wrong database design is like trying
to write software with the wrong data abstractions. As things evolve,
adding new functionality and maintaining existing functionality will
become harder and harder. Increasing amounts of time will be spent in
diagnosing and fixing data anomalies and management/clients will become
increasingly frustrated at how long it takes to add/update/extend
functionality. The sooner you bite the bullet and commit to fixing the
underlying database model, the better. The longer you delay, the harder
it will become.

--
Tim Cross