Tag: postgresql-10

Why am I getting a cross-reference error with my insert/select statement in single database

I have the following INSERT statement, and it works fine without the WHERE clause, but will not work with it. There is a separate database that I have connected to via a dblink connection, and I have created several views that contain data I will need for the database I’m working in. I have the following statement: The error received

Function to convert char(255) non standard dates into timestamps

postgresql-10 sql sql-function

I have database with a column of date strings that are in the format YYYY-MM-DDThh:mm:ss.3nZ I would like to transform them into TIMESTAMP so that I can then apply the BETWEEN function to identify rows that fall in a given time window (this being the ultimate goal of the exercise). I can’t just change the nature of the column unfortunately.

How to automatically create a comment on CREATE in Postgres?

plpgsql postgresql postgresql-10 sql triggers

I would like to create a trigger which gets fired once a table is created in a schema. This trigger should add a comment to the newly created table, something like: With add_comment doing somthibg like: However, it seems I can only add triggers on table level. How could I achieve to add comments to newly created tables in pure

PostgreSQL11 xpath query not working properly

postgresql postgresql-10 postgresql-11 sql xpath

When I execute below query in Postgres 10.12, it works properly. Output: But When I execute same query in Postgres 11.7, it is not working. What is the solution to fix this issue? Answer This is caused by this change: Correctly handle relative path expressions in xmltable(), xpath(), and other XML-handling functions (Markus Winand) Per the SQL standard, relative paths

PostgreSQL-10: query JSONB property with multiple types

json jsonb postgresql postgresql-10 sql

Assume a table json_table with a column data (jsonb). A sample value would be {“a”: [{“b”:{“c”: “xxx”, “d”: 1}},{“b”:{“c”: “xxx”, “d”: 2}}]} I used to run SQL queries like the following: SELECT …

PostgreSQL: exclude complete jsonb array if one element fails the WHERE clause

json jsonb postgresql postgresql-10 sql

Assume a table json_table with columns id (int), data (jsonb). A sample jsonb value would be When I use an SQL statement like the following: … the two array elements are unnested and the one that qualifies the WHERE clause is still returned. This makes sense since each array element is considered individually. In this example I will get back