How to recover data from 9.3 data directory

[Andrus]

Hi!

There is data directory from Postgres 9.3 server from old Centos.

Centos server is not running anymore and there is window 10 workstation 
to recover.


1. Tried to recover data from it installing 9.3 and 13 servers in 
windows 10 and using


"C:\Program Files\PostgreSQL\13\bin\pg_upgrade.exe" ^
--old-datadir "D:/Centos93Data/data" ^
--new-datadir "C:/Program Files/PostgreSQL/13/data" ^
--old-bindir "C:/Program Files/PostgreSQL/9.3/bin" ^
--new-bindir "C:/Program Files/PostgreSQL/13/bin"

It complained that postmaster seems working. I deleted postmaster.pid 
file. Now it throws  error


Performing Consistency Checks
Checking cluster versions ok
The source cluster was not shut down cleanly.
Failure, exiting

2. Tried to run old server using

C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" start -D 
"D:/Centos93Data/data"


got error

FATAL:  database files are incompatible with server

DETAIL:  The database cluster was initialized with USE_FLOAT8_BYVAL but 
the server was compiled without USE_FLOAT8_BYVAL.

HINT:  It looks like you need to recompile or initdb.

I tried both 32 and 64 bit 9.3 servers in windows but error is the same.

How to recover data from 9.3 linux directory ?

Andrus.

Re: pg_upgrade and wraparound

[Jan Wieck]

On 3/12/21 8:30 PM, Michael Paquier wrote:

Hi Jan,

On Fri, Mar 12, 2021 at 06:13:33PM -0500, Jan Wieck wrote:

One of the things in my way is that when using pg_resetwal to put the
NextXID way into the future (to push the old cluster close to wraparound for
example), the postmaster won't start because it doesn't have the pg_xact
files for that around. Should pg_resetwal create the files in the gap
between the old NextXID and the new one?


I think that you should add this patch to the next commit fest to
track it properly:
https://commitfest.postgresql.org/33/
--
Michael



Actually this is the wrong patch (this one is for PG-12, not for HEAD). 
Will update later today.


But yes, putting it into the next commitfest after initial discussion is 
the plan.



Regards, Jan

--
Jan Wieck
Principle Database Engineer
Amazon Web Services

Re: How to recover data from 9.3 data directory

[Julien Rouhaud]

Le sam. 13 mars 2021 à 21:29, Andrus  a écrit :

> 2. Tried to run old server using
>
C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" start -D
> "D:/Centos93Data/data"
> got error
>
> FATAL:  database files are incompatible with server
> DETAIL:  The database cluster was initialized with USE_FLOAT8_BYVAL but
> the server was compiled without USE_FLOAT8_BYVAL.
> HINT:  It looks like you need to recompile or initdb.
>
> I tried both 32 and 64 bit 9.3 servers in windows but error is the same.
>
> How to recover data from 9.3 linux directory ?
>
that won't work on windows. pg_upgrade only works if source and target
environment are binary compatible, which isn't the case for windows / GNU
Linux.

>

Re: How to recover data from 9.3 data directory

[Andrus]

Hi!

> that won't work on windows. pg_upgrade only works if source and 
target environment are binary compatible, which isn't the case for 
windows / GNU Linux.


wal archiving works between Linux and Windows. So I expected those 
commands to work also.


How to determine is this data from 32 or 64 bit server ?

Should I create virtual machine and install Linux with 9.3 into it or is 
there simpler solution?


Will data from 32 bit server require 32 bit Linux with 32 bit postgres 
or will it work with 64 bit Linux also ?


Andrus.

Re: How to recover data from 9.3 data directory

[Julien Rouhaud]

Le sam. 13 mars 2021 à 22:02, Andrus  a écrit :

> Hi!
>
> > that won't work on windows. pg_upgrade only works if source and target
> environment are binary compatible, which isn't the case for windows / GNU
> Linux.
>
> wal archiving works between Linux and Windows. So I expected those
> commands to work also.
>

WAL archiving is only a file copy, you can copy them anywhere without
restrictions on the architecture.

> How to determine is this data from 32 or 64 bit server ?
>
it's not really a question of the binary pointer size but the target
hardware specificity.

> Should I create virtual machine and install Linux with 9.3 into it or is
> there simpler solution?
>
that's the only option, unless you already have some compatible server
around.

> Will data from 32 bit server require 32 bit Linux with 32 bit postgres or
> will it work with 64 bit Linux also ?
>
I think that it won't matter and a 64 bits server will work just fine, but
only testing will give a definite answer.

>

Re: How to recover data from 9.3 data directory

[Andrus]

   Should I create virtual machine and install Linux with 9.3 into it
   or is there simpler solution?

that's the only option, unless you already have some compatible server 
around.


I have new Debian virtual machine with Postgres 12  .

Debian does not offer to install Postgres 9.3 into it. How to install 
postgres 9.3 in new Debian ?


Andrus.

Re: How to recover data from 9.3 data directory

[Guillaume Lelarge]

Le sam. 13 mars 2021 à 15:18, Andrus  a écrit :

> Should I create virtual machine and install Linux with 9.3 into it or is
>> there simpler solution?
>>
> that's the only option, unless you already have some compatible server
> around.
>
> I have new Debian virtual machine with Postgres 12  .
>
> Debian does not offer to install Postgres 9.3 into it. How to install
> postgres 9.3 in new Debian ?
>

You'll have to compile it.


-- 
Guillaume.

Re: How to recover data from 9.3 data directory

[Andrus]

Hi!

>Debian does not offer to install Postgres 9.3 into it. How to install 
postgres 9.3 in new Debian ?




>You'll have to compile it.


Enterpisedb offers 9.3 download for Linux in 
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads


Maybe it is possible to install it in new Debian ?

Or is it possible to download ISO image with Postgres 9.3 installed ?

Or should I try to find old Debian ISO image and install it into this?

Andrus.

how to set permission, so I can run pg_dumd in a cron job

[robert rottermann]

Hi Friends

I would like to have a cronjob creating a dump of a db.

I am on a ubuntu 18 lts, potgres v10.

I have a user robert with superuser db permission.

a database "mydb" of which I would like to to a nightly dump.


I tried to set thing in hba_conf like this:

# Database administrative login by Unix domain socket
local   all postgres peer

# TYPE  DATABASE    USER    ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
host    all all 127.0.0.1/32 trust

and I create a /root/.pgpass file with this content


hostname:port:database:username:password
localhost:5432:mydb:robert:



but still I get:

root@elfero:~# pg_dump  -U robert -d mydb > dumps/mydb.sql
pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL:  Peer 
authentication failed for user "robert"



can you please give me a hand

thanks

robert

Re: How to recover data from 9.3 data directory

[Adrian Klaver]

On 3/13/21 7:25 AM, Andrus wrote:

Hi!

 >Debian does not offer to install Postgres 9.3 into it. How to install 
postgres 9.3 in new Debian ?




>You'll have to compile it.


Enterpisedb offers 9.3 download for Linux in 
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads


Maybe it is possible to install it in new Debian ?

Or is it possible to download ISO image with Postgres 9.3 installed ?

Or should I try to find old Debian ISO image and install it into this?


Or you could just use the the Postgres APT repo, as example:

https://apt.postgresql.org/pub/repos/apt/dists/stretch-pgdg/9.3/




Andrus.



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

Re: How to recover data from 9.3 data directory

[Julien Rouhaud]

Le dim. 14 mars 2021 à 00:17, Adrian Klaver  a
écrit :

> On 3/13/21 7:25 AM, Andrus wrote:
> > Hi!
> >
> >  >Debian does not offer to install Postgres 9.3 into it. How to install
> > postgres 9.3 in new Debian ?
> >
> >>
> >> >You'll have to compile it.
> >
> > Enterpisedb offers 9.3 download for Linux in
> > https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
> >
> > Maybe it is possible to install it in new Debian ?
> >
> > Or is it possible to download ISO image with Postgres 9.3 installed ?
> >
> > Or should I try to find old Debian ISO image and install it into this?
>
> Or you could just use the the Postgres APT repo, as example:
>
> https://apt.postgresql.org/pub/repos/apt/dists/stretch-pgdg/9.3/


see also
https://wiki.postgresql.org/wiki/Apt/FAQ#Where_are_older_versions_of_the_packages.3F

>
>

Re: how to set permission, so I can run pg_dumd in a cron job

[Adrian Klaver]

On 3/13/21 8:16 AM, robert rottermann wrote:

Hi Friends

I would like to have a cronjob creating a dump of a db.

I am on a ubuntu 18 lts, potgres v10.

I have a user robert with superuser db permission.

a database "mydb" of which I would like to to a nightly dump.


I tried to set thing in hba_conf like this:

# Database administrative login by Unix domain socket
local   all postgres peer

# TYPE  DATABASE    USER    ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
host    all all 127.0.0.1/32 trust

and I create a /root/.pgpass file with this content


hostname:port:database:username:password
localhost:5432:mydb:robert:



but still I get:

root@elfero:~# pg_dump  -U robert -d mydb > dumps/mydb.sql
pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL:  
Peer authentication failed for user "robert"



can you please give me a hand


1) Did you have the server reload the conf files after making the changes?

2) Is there an entry for something like:

local   all all  peer

before the lines you show above?




thanks

robert






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

Re: how to set permission, so I can run pg_dumd in a cron job

[robert rottermann]

Adrin, thanks

On 13.03.21 17:23, Adrian Klaver wrote:

On 3/13/21 8:16 AM, robert rottermann wrote:

Hi Friends

I would like to have a cronjob creating a dump of a db.

I am on a ubuntu 18 lts, potgres v10.

I have a user robert with superuser db permission.

a database "mydb" of which I would like to to a nightly dump.


I tried to set thing in hba_conf like this:

# Database administrative login by Unix domain socket
local   all postgres peer

# TYPE  DATABASE    USER    ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
host    all all 127.0.0.1/32 trust

and I create a /root/.pgpass file with this content


hostname:port:database:username:password
localhost:5432:mydb:robert:



but still I get:

root@elfero:~# pg_dump  -U robert -d mydb > dumps/mydb.sql
pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL:  Peer 
authentication failed for user "robert"



can you please give me a hand


1) Did you have the server reload the conf files after making the changes?

yes I did


2) Is there an entry for something like:

local   all all  peer

before the lines you show above?


no

I added the whol pg_conf at the end.

There is one thing particulare with the installation.

I have postgresql 9.5 and 10.0 on the box.

now when I check

pg_dump -V
pg_dump (PostgreSQL) 9.5.24

root@elfero:~# ll /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37 Nov 13  2019 /usr/bin/pg_dump -> 
../share/postgresql-common/pg_wrapper*


when I switch to user postgres, and the us psql, I get the following:

root@elfero:~# su postgres
postgres@elfero:/root$ psql -d  elfero
could not change directory to "/root": Permission denied
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1), server 9.5.24)
Type "help" for help.

looks, as if I have a mess with this two instances.


How can I best clean that up.

I need only PostgreSQL 10

thanks again

robert



# PostgreSQL Client Authentication Configuration File
# ===
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file. A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof. In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches. It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask. A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts. Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE. The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted. Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the server receives

Re: how to set permission, so I can run pg_dumd in a cron job

[Adrian Klaver]

On 3/13/21 9:25 AM, robert rottermann wrote:


Adrin, thanks




can you please give me a hand


1) Did you have the server reload the conf files after making the 
changes?

yes I did


2) Is there an entry for something like:

local   all all  peer

before the lines you show above?


no

I added the whol pg_conf at the end.

There is one thing particulare with the installation.

I have postgresql 9.5 and 10.0 on the box.

now when I check

pg_dump -V
pg_dump (PostgreSQL) 9.5.24

root@elfero:~# ll /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37 Nov 13  2019 /usr/bin/pg_dump -> 
../share/postgresql-common/pg_wrapper*


when I switch to user postgres, and the us psql, I get the following:

root@elfero:~# su postgres
postgres@elfero:/root$ psql -d  elfero
could not change directory to "/root": Permission denied
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1), server 9.5.24)
Type "help" for help.

looks, as if I have a mess with this two instances.


How can I best clean that up.

I need only PostgreSQL 10


From command line what does pg_lsclusters show?

Which pg_hba.conf file did you change?  The one in:

/etc/postgresql/9.5/main

or

/etc/postgresql/10/main


To specify a version of pg_dump to use do something like:

pg_dump --cluster 10/main -d some_db -U some_user -p some_port

The important part is the some_port. You will need to use the one 
specified for the instance(cluster) as returned by the pg_lscluster command.




thanks again

robert





# Database administrative login by Unix domain socket
#local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
q# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5



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

Compare with default value?

[Ulrich Goebel]

Hi,

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?

Thank's
Ulrich

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

solved: Re: how to set permission, so I can run pg_dumd in a cron job

[robert rottermann]

Thanks again Adrian


On 13.03.21 18:37, Adrian Klaver wrote:

On 3/13/21 9:25 AM, robert rottermann wrote:


Adrin, thanks




can you please give me a hand


1) Did you have the server reload the conf files after making the changes?

yes I did


2) Is there an entry for something like:

local   all all  peer

before the lines you show above?


no

I added the whol pg_conf at the end.

There is one thing particulare with the installation.

I have postgresql 9.5 and 10.0 on the box.

now when I check

pg_dump -V
pg_dump (PostgreSQL) 9.5.24

root@elfero:~# ll /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37 Nov 13  2019 /usr/bin/pg_dump -> 
../share/postgresql-common/pg_wrapper*


when I switch to user postgres, and the us psql, I get the following:

root@elfero:~# su postgres
postgres@elfero:/root$ psql -d  elfero
could not change directory to "/root": Permission denied
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1), server 9.5.24)
Type "help" for help.

looks, as if I have a mess with this two instances.


How can I best clean that up.

I need only PostgreSQL 10


From command line what does pg_lsclusters show?

root@elfero:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory Log 
file
9.5 main    5432 online postgres /mnt/HC_Volume_8755337/postgresql/9.5/main 
/var/log/postgresql/postgresql-9.5-main.log
10  main    5433 down   postgres /var/lib/postgresql/10/main 
/var/log/postgresql/postgresql-10-main.log





Which pg_hba.conf file did you change?  The one in:

/etc/postgresql/9.5/main

or

/etc/postgresql/10/main


To specify a version of pg_dump to use do something like:

pg_dump --cluster 10/main -d some_db -U some_user -p some_port

The important part is the some_port. You will need to use the one specified 
for the instance(cluster) as returned by the pg_lscluster command.


I did in deed change the wrong file

now, I need remove one of the two postgresql versions, but for this I will open 
a new question



thanks and have a nice weekend

Robert





thanks again

robert





# Database administrative login by Unix domain socket
#local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
q# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5

Re: Compare with default value?

[David G. Johnston]

On Saturday, March 13, 2021, 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
>

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

David J.

how to best remove version 10 (and keep version 9.5)

[robert rottermann]

Hi folks

I am running an Ubuntu 18.04 lts remote box.

For some (complicated) reasons I did install PostgreSQL 9.5 alongside the 
already installed V10 instance.


I did set the data_directory to point some disk  “outside” the standard 
/etc/postgresql/9.5/main structure.


Now, what I would like to do is to get rid of one of the two installed 
PostgreSQL versions.


Also there are aliasings like:     /usr/bin/pg_dump -> 
../share/postgresql-common/pg_wrapper*


I would like to avoid them if possible.

Things are running now with version 9.5 so I tend to prefer to keep it.

The whole setup is kind of temporary and will be migrated to the “real thing" 
during this year. So it is not a problem that the PostgreSQL version is dated.



I would be grateful for your help

robert

Re: how to best remove version 10 (and keep version 9.5)

[Adrian Klaver]

On 3/13/21 11:00 AM, robert rottermann wrote:

Hi folks

I am running an Ubuntu 18.04 lts remote box.

For some (complicated) reasons I did install PostgreSQL 9.5 alongside 
the already installed V10 instance.


I did set the data_directory to point some disk  “outside” the standard 
/etc/postgresql/9.5/main structure.


Now, what I would like to do is to get rid of one of the two installed 
PostgreSQL versions.


Also there are aliasings like:     /usr/bin/pg_dump -> 
../share/postgresql-common/pg_wrapper*


I would like to avoid them if possible.


You are using the Debian/Ubuntu packaging and the above is part of that. 
Also they are very helpful so you don't want to get rid of them.




Things are running now with version 9.5 so I tend to prefer to keep it.


From your previous post:

root@elfero:~# pg_lsclusters
Ver Cluster Port Status OwnerData directory 
Log file
9.5 main5432 online postgres 
/mnt/HC_Volume_8755337/postgresql/9.5/main 
/var/log/postgresql/postgresql-9.5-main.log
10  main5433 down   postgres /var/lib/postgresql/10/main 
/var/log/postgresql/postgresql-10-main.log



So, the 10 instance is not running and the 9.5 instance is listening on 
the default port. At this point I would leave things as they are.




The whole setup is kind of temporary and will be migrated to the “real 
thing" during this year. So it is not a problem that the PostgreSQL 
version is dated.



I would be grateful for your help

robert






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

Re: How to recover data from 9.3 data directory

[Andrus]

Or you could just use the the Postgres APT repo, as example:

https://apt.postgresql.org/pub/repos/apt/dists/stretch-pgdg/9.3/



see also 
https://wiki.postgresql.org/wiki/Apt/FAQ#Where_are_older_versions_of_the_packages.3F 





I discovered that Debian allows to install 9.3 side-by side with 12

apt-get install postgresql-9.3

Andrus.

Re: How to recover data from 9.3 data directory

[Adrian Klaver]

On 3/13/21 12:04 PM, Andrus wrote:



Or you could just use the the Postgres APT repo, as example:

https://apt.postgresql.org/pub/repos/apt/dists/stretch-pgdg/9.3/



see also 
https://wiki.postgresql.org/wiki/Apt/FAQ#Where_are_older_versions_of_the_packages.3F 





I discovered that Debian allows to install 9.3 side-by side with 12


As long as you are using the PGDG(Postgres) repos and Debian versions 
listed here:


https://www.postgresql.org/download/linux/debian/#apt



apt-get install postgresql-9.3

Andrus.




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

Re: Compare with default value?

[Ulrich Goebel]

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 > 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

Re: Compare with default value?

[David G. Johnston]

On Sat, Mar 13, 2021 at 1:41 PM Ulrich Goebel  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.

Re: Compare with default value?

[Joe Conway]

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

Re: Compare with default value?

[Rob Sargent]

> On Mar 13, 2021, at 1:51 PM, David G. Johnston  
> wrote:
> 
> 
>> On Sat, Mar 13, 2021 at 1:41 PM Ulrich Goebel  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?

Re: Compare with default value?

[Tim Cross]

Ulrich Goebel  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 > > 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.