Can not open table view in LibreOffice Base with Postgresql 12 DB

[GerardCarbo]

When trying to open a view using LibreOffice 6.2.8 to a Postgresql 12.0 table using PostgeSQL connector it returns the error (both on Windows 10):

Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERROR: column pg_attrdef.adsrc does not exist

LINE 1: …attnotnull,
pg_type.typdefault, pg_type.typtype,
pg_attrdef…
(caused by statement ‘SELECT
pg_namespace.nspname,
pg_class.relname,
pg_attribute.attname, pg_type.typname,
pg_attribute.atttypmod,
pg_attribute.attnotnull,
pg_type.typdefault, pg_type.typtype,
pg_attrdef.adsrc,
pg_description.description,
pg_type.typbasetype,
pg_attribute.attnum FROM pg_class,
pg_attribute LEFT JOIN pg_attrdef ON
pg_attribute.attrelid =
pg_attrdef.adrelid AND
pg_attribute.attnum = pg_attrdef.adnum
LEFT JOIN pg_description ON
pg_attribute.attrelid =
pg_description.objoid AND
pg_attribute.attnum=pg_description.objsubid,
pg_type, pg_namespace WHERE
pg_attribute.attrelid = pg_class.oid
AND pg_attribute.atttypid =
pg_type.oid AND pg_class.relnamespace
= pg_namespace.oid AND NOT pg_attribute.attisdropped AND
pg_namespace.nspname LIKE
‘information_schema’ AND
pg_class.relname LIKE ‘collations’ AND
pg_attribute.attname LIKE ‘%’ ORDER BY
pg_namespace.nspname,
pg_class.relname,
pg_attribute.attnum’)

For example from LibreOffice Base when trying to open a table view:

Creating a pivot table in LO Calc gives the error:

I guess this is dued to the lack of support for the column adsrc in PG 12.

Any idea on how to solve it?

[Ratslinger]

Hello,

You do not state the specific LO version used, the OS or what connector type is used.

[Ratslinger]

@GerardCarbo,

You have now added to the question something about Calc and it has nothing to do with the original question. Please separate this into a new question with an explanation. public.metrics in not in the SQL in the question.

Resolution has been provided for the adsrc error in the answer.

[GerardCarbo]

‘public.metrics’ is the table (of my own database) I’m trying to connect to (using LibreOffice Base or LibreOffice Calc). The error I get is distinct depending if I try from Base or Calc. I guess that the problem is that the current implementation of the libreoffice-sdbc-postgresql driver does not take into account that the pg_attrdef.adsrc field has been removed from PG 12.0

[GerardCarbo]

Confirmed: connecting to a PG 11.x is working properly with the same LO version.

[Ratslinger]

@GerardCarbo,

There is nothing more that can be answered. Based upon the question, the fix is the SQL in the answer. Yes, adsrc has been deleted from PostgreSQL. This has nothing to do with the driver. It is the SQL which creates your Table View. That SQL needs to be changed. Link to do that and the actual SQL is in the answer. Do not know how else to help you. You must make this change on your system with the information provided.

[Ratslinger]

Hello,

After deciphering your question, was able to run the SQL without errors on Ubuntu 18.04 with LO v6.3.3.2 using the SDBC (PostgreSQL) connector. This is using PostgreSQL v10.10 (Ubuntu):

Since pg_attrdef.adsrc was removed as obsolete, simply eliminate it from the query:

SELECT pg_namespace.nspname,
       pg_class.relname,
       pg_attribute.attname,
       pg_type.typname,
       pg_attribute.atttypmod,
       pg_attribute.attnotnull,
       pg_type.typdefault,
       pg_type.typtype,
       pg_description.description,
       pg_type.typbasetype,
       pg_attribute.attnum
FROM pg_class,
     pg_attribute
  LEFT JOIN pg_attrdef
         ON pg_attribute.attrelid = pg_attrdef.adrelid
        AND pg_attribute.attnum = pg_attrdef.adnum
  LEFT JOIN pg_description
         ON pg_attribute.attrelid = pg_description.objoid
        AND pg_attribute.attnum = pg_description.objsubid,pg_type,pg_namespace
WHERE pg_attribute.attrelid = pg_class.oid
AND   pg_attribute.atttypid = pg_type.oid
AND   pg_class.relnamespace = pg_namespace.oid
AND   NOT pg_attribute.attisdropped
AND   pg_namespace.nspname LIKE 'information_schema'
AND   pg_class.relname LIKE 'collations'
AND   pg_attribute.attname LIKE '%'
ORDER BY pg_namespace.nspname,
         pg_class.relname,
         pg_attribute.attnum;

Edit 2019-11-18:

Not certain you understand that table view is basically a query. So with the error you need to replace what you have. Correct SQL is presented above. To replace see → CREATE VIEW. Easiest method I used is to delete the old and just re-create as new view. Your choice.

[Ratslinger]

Please only ask questions which are related to LO. This has nothing to do with LO or Base.