Perhaps a possible new feature to a future PostgreSQL release
Hello Let me assume that there is a table T with columns a, b, c, d, e, f, g, and h. If one wants to select data from all the columns except d and e, then one has to write SELECT a, b, c, f, g, h FROM T; instead of writing SELECT ALL BUT (d, e) FROM T; or something similar (perhaps by using keywords EXCEPT or EXCLUDE). The more a table has columns, the more one has to write the column names. There are systems that support this kind of shorthand syntax in SQL: BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-modifiers<https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-modifiers> Databricks: https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select.html#syntax DuckDB: https://duckdb.org/docs/sql/query_syntax/select Snowflake:https://stephenallwright.com/select-columns-except-snowflake/ I think that such syntax would be useful and if more and more DBMS-s start to offer it, then perhaps one day it will be in the SQL standard as well. What do you think, is it something that could be added to PostgreSQL? People are interested of this feature. The following links are just some examples: http://www.postgresonline.com/journal/archives/41-How-to-SELECT-ALL-EXCEPT-some-columns-in-a-table.html https://stackoverflow.com/questions/729197/exclude-a-column-using-select-except-columna-from-tablea https://dba.stackexchange.com/questions/1957/sql-select-all-columns-except-some https://www.reddit.com/r/SQL/comments/15x97kw/sql_is_there_a_way_to_just_exclude_1_column_in/ Best regards Erki Eessaar
Re: Issues with Information_schema.views
Hello Thank you! I know that. DO INSTEAD NOTHING rules on updatable views could be used as a way to implement WITH READ ONLY constraint (one can define such constraint in Oracle). However, one could accidentally add such rule to non-updatable view as well. I tried to construct a system-catalog based query to find database rules that are unnecessary. Thus, for the testing purposes I added a DO INSTEAD NOTHING rule to already non-updatable view and was a bit surprised that INFORMATION_SCHEMA-based check showed that the view had become updatable. A possible reasoning is that I can update the view without getting an error. However, I still cannot change data in base tables. Secondly, the rule you demonstrated does not alone change IS_UPDATABLE value to YES. I have to create two rules: CREATE OR REPLACE RULE emps_update AS ON UPDATE TO Emps DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, deptno = NEW.deptno; CREATE OR REPLACE RULE emps_delete AS ON DELETE TO Emps DO INSTEAD DELETE FROM Emp WHERE empno=OLD.empno; My question is - is all of this the intended behaviour by the implementers? Best regards Erki Eessaar From: jian he Sent: Saturday, October 28, 2023 13:38 To: Erki Eessaar Cc: pgsql-hackers@lists.postgresql.org Subject: Re: Issues with Information_schema.views On Sat, Oct 28, 2023 at 5:27 PM Erki Eessaar wrote: > > Hello > > > /*After that: is_updatable=YES*/ > > 1. Indeed, now I can execute INSERT/UPDATE/DELETE against the view without > getting an error. However, I still cannot change the data in the database > through the views. https://www.postgresql.org/docs/current/sql-createview.html " A more complex view that does not satisfy all these conditions is read-only by default: the system will not allow an insert, update, or delete on the view. You can get the effect of an updatable view by creating INSTEAD OF triggers on the view, which must convert attempted inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE TRIGGER. Another possibility is to create rules (see CREATE RULE), but in practice triggers are easier to understand and use correctly. " You CAN get the effect of an updateable view. But you need to make the rule/triggers correct. the following RULE can get the expected result. CREATE OR REPLACE RULE emps_update AS ON UPDATE TO Emps DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, deptno = NEW.deptno; you can also look at src/test/regress/sql/triggers.sql, src/test/regress/sql/rules.sql for more test cases.
Issues with Information_schema.views
Hello The following was tested in a PostgreSQL (16) database. In my opinion queries based on Information_schema.views sometimes give unexpected results. CREATE TABLE Dept(deptno SMALLINT NOT NULL, dname VARCHAR(50) NOT NULL, CONSTRAINT pk_dept PRIMARY KEY (deptno)); CREATE TABLE Emp(empno INTEGER NOT NULL, ename VARCHAR(50) NOT NULL, deptno SMALLINT NOT NULL, CONSTRAINT pk_emp PRIMARY KEY (empno), CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES Dept(deptno) ON UPDATE CASCADE); CREATE VIEW emps AS SELECT * FROM Dept INNER JOIN Emp USING (deptno); UPDATE Emps SET ename=Upper(ename); /*ERROR: cannot update view "emps" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.*/ SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into FROM Information_schema.views WHERE table_name='emps'; /*is_updatable=NO and is_insertable_into=NO*/ CREATE OR REPLACE RULE emps_insert AS ON INSERT TO Emps DO INSTEAD NOTHING; /*After that: is_insertable_into=YES*/ CREATE OR REPLACE RULE emps_update AS ON UPDATE TO Emps DO INSTEAD NOTHING; /*After that: is_updatable=NO*/ CREATE OR REPLACE RULE emps_delete AS ON DELETE TO Emps DO INSTEAD NOTHING; /*After that: is_updatable=YES*/ 1. Indeed, now I can execute INSERT/UPDATE/DELETE against the view without getting an error. However, I still cannot change the data in the database through the views. 2. is_updatable=YES only after I add both UPDATE and DELETE DO INSTEAD NOTHING rules. My question is: are 1 and 2 the expected behaviour or is there a mistake in the implementation of the information_schema view? Best regards Erki Eessaar
Re: PostgreSQL domains and NOT NULL constraint
Hello Similarly, PostgreSQL does not enforce CHECK constraints of domains that try to enforce NOT NULL in the same situations where it does not enforce NOT NULL constraints - see example in the end. Thus, in my base tables can be rows that violate domain NOT NULL and CHECK constraints. For me, it is not a "feature", it is a bug. By the way, my small applications use domain NOT NULL constraints. This was the reason why I asked are there any other examples in addition to those that I provided that allow NULL's to NOT NULL columns. Best regards Erki Eessaar DROP TABLE IF EXISTS Product; DROP TABLE IF EXISTS Product_state_type; DROP DOMAIN IF EXISTS d_name; CREATE DOMAIN d_name VARCHAR(50) CONSTRAINT chk_d_name CHECK (VALUE IS NOT NULL); CREATE TABLE Product_state_type (product_state_type_code SMALLINT NOT NULL, name d_name, CONSTRAINT pk_product_state_type PRIMARY KEY (product_state_type_code), CONSTRAINT ak_product_state_type_name UNIQUE (name)); CREATE TABLE Product (product_code INTEGER NOT NULL, name d_name, product_state_type_code SMALLINT NOT NULL, CONSTRAINT pk_product PRIMARY KEY (product_code), CONSTRAINT fk_product_product_state_type FOREIGN KEY (product_state_type_code) REFERENCES Product_state_type(product_state_type_code) ON UPDATE CASCADE); INSERT INTO Product_state_type (product_state_type_code, name) VALUES (1, (SELECT name FROM Product_state_type WHERE FALSE)); /*Insertion succeeds, name is NULL!*/ INSERT INTO Product (product_code, name, product_state_type_code) SELECT 1 AS product_code, Product.name, 1 AS product_state_type_code FROM Product_state_type LEFT JOIN Product USING (product_state_type_code); /*Insertion succeeds, name is NULL!*/ DROP TABLE IF EXISTS Product; DROP TABLE IF EXISTS Product_state_type; DROP DOMAIN IF EXISTS d_name; CREATE DOMAIN d_name VARCHAR(50) CONSTRAINT chk_d_name CHECK (coalesce(VALUE,'')<>''); CREATE TABLE Product_state_type (product_state_type_code SMALLINT NOT NULL, name d_name, CONSTRAINT pk_product_state_type PRIMARY KEY (product_state_type_code), CONSTRAINT ak_product_state_type_name UNIQUE (name)); CREATE TABLE Product (product_code INTEGER NOT NULL, name d_name, product_state_type_code SMALLINT NOT NULL, CONSTRAINT pk_product PRIMARY KEY (product_code), CONSTRAINT fk_product_product_state_type FOREIGN KEY (product_state_type_code) REFERENCES Product_state_type(product_state_type_code) ON UPDATE CASCADE); INSERT INTO Product_state_type (product_state_type_code, name) VALUES (1, (SELECT name FROM Product_state_type WHERE FALSE)); /*Insertion succeeds, name is NULL!*/ INSERT INTO Product (product_code, name, product_state_type_code) SELECT 1 AS product_code, Product.name, 1 AS product_state_type_code FROM Product_state_type LEFT JOIN Product USING (product_state_type_code); /*Insertion succeeds, name is NULL!*/ From: Tom Lane Sent: Saturday, October 14, 2023 19:09 To: Vik Fearing Cc: Erki Eessaar ; pgsql-hackers@lists.postgresql.org Subject: Re: PostgreSQL domains and NOT NULL constraint Vik Fearing writes: > On 10/13/23 06:37, Tom Lane wrote: >> Hmph. The really basic problem here, I think, is that the spec >> wants to claim that a domain is a data type, but then it backs >> off and limits where the domain's constraints need to hold. > I don't think that is an accurate depiction of domains. > First of all, I am not seeing where it says that a domain is a data > type. It allows domains to be used in some places where a data type is > used, but that is not equivalent to a domain /being/ a data type. Hmm, you are right. This is something I'd never paid attention to before, but they do seem to exclude domains from being the declared type of any expression. Most notably, not even a CAST to a domain type produces the domain type. Per SQL:2021 6.13 syntax rules: 1) Case: a) If a is specified, then let TD be the data type of the specified domain. b) If a is specified, then let TD be the data type identified by . shall not contain a . 2) The declared type of the result of the is TD. Even more amusingly for our current purposes, CAST does not enforce NOT NULL. general rule 2: 2) Case: a) If the specifies NULL, then the result of CS is the null value and no further General Rules of this Subclause are applied. b) If the specifies an , then the result of CS is an empty collection of declared type TD and no further General Rules of this Subclause are applied. c) If SV is the null value, then the result of CS is the null value and no further General Rules of this Subclause are applied. So for a null value the spec never reaches GR 23 that says to apply the domain's constraints. This is already a sufficient intellectual muddle that I'm not sure we want to follow it slavishly. I
Re: PostgreSQL domains and NOT NULL constraint
>I doubt we'd consider doing anything about that. >The whole business of domains with NOT NULL constraints >is arguably a defect of the SQL standard, because >there are multiple ways to produce a value that >is NULL and yet must be considered to be of the domain type. In my opinion it is inconsistent and illogical if a type sometimes contains a value and sometimes not. CREATE DOMAIN d_int INTEGER NOT NULL; All the following statements fail (and correctly so in my opinion). SELECT (NULL)::d_int; /*ERROR: domain d_int does not allow null values*/ SELECT Cast(NULL AS d_int); /*ERROR: domain d_int does not allow null values*/ WITH val (v) AS (VALUES (1), (NULL)) SELECT Cast(v AS d_int) AS v FROM Val; /*ERROR: domain d_int does not allow null values*/ In my opinion the confusion and related problems arise from the widespread practice of sometimes treating a domain as a type (which it is not) and sometimes treating NULL as a value (which it is not). Best regards Erki Eessaar
Re: PostgreSQL domains and NOT NULL constraint
Hello Equaling a domain with a type is really confusing because why, for instance, in this case the following is possible without defining any additional operators. CREATE DOMAIN d_name VARCHAR(50) NOT NULL; CREATE DOMAIN d_description VARCHAR(1000) NOT NULL; CREATE TABLE x(name d_name, description d_description); SELECT * FROM x WHERE name=description; Isn't it so that domains are not types and for this reason there are separate CREATE DOMAIN and CREATE TYPE statements?! In my opinion the Notes section of CREATE DOMAIN documentation should offer better examples. The two examples that I provided in my demonstration seemed very far fetched and artificial. Frankly, I have difficulties in imagining why someone would like to write statements like that in a production environment and how the proper enforcement of NOT NULL constraints of domains could break things. Lets say I have a column that I have declared mandatory by using a domain, but somehow I have added NULLs to the column, and if it is not possible any more, then things break down. If I want to permit NULLs, then ALTER DOMAIN d DROP NOT NULL; will fix it with one stroke. If I do not want to permit NULLs but I have registered NULLs, then this is a data quality issue that has to be addressed. Currently there is a feature (NOT NULL of domain) that the documentation explicitly suggests not to use. Isn't it in this case better to remove this feature completely?! If this would break something, then it would mean that systems actually rely on this constraint. Best regards Erki Eessaar From: Tom Lane Sent: Friday, October 13, 2023 08:37 To: Vik Fearing Cc: Erki Eessaar ; pgsql-hackers@lists.postgresql.org Subject: Re: PostgreSQL domains and NOT NULL constraint Vik Fearing writes: > Regardless of what the spec may or may not say about v1.d, it still > remains that nulls should not be allowed in a *base table* if the domain > says nulls are not allowed. Not mentioned in this thread but the > constraints are also applied when CASTing to the domain. Hmph. The really basic problem here, I think, is that the spec wants to claim that a domain is a data type, but then it backs off and limits where the domain's constraints need to hold. That's fundamentally inconsistent. It's like claiming that 'foobarbaz' is a valid value of type numeric as long as it's only in flight within a query and you haven't tried to store it into a table. Practical problems with this include: * If a function declares its argument as being of a domain type, can it expect that the passed value obeys the constraints? * If a function declares its result as being of a domain type, is it required to return a result that obeys the constraints? (This has particular force for RETURNS NULL ON NULL INPUT functions, for which we just automatically return NULL given a NULL input without any consideration of whether the result type nominally prohibits that.) * If a plpgsql function has a variable that is declared to be of domain type, do we enforce the domain's constraints when assigning? * If a composite type has a column of a domain type, do we enforce the domain's constraints when assigning or casting to that? AFAICS, the spec's position leaves all of these as judgment calls, or else you might claim that none of the above cases are even allowed to be declared per spec. I don't find either of those satisfactory, so I reiterate my position that the committee hasn't thought this through. > As you know, I am more than happy to (try to) amend the spec where > needed, but Erki's complaint of a null value being allowed in a base > table is clearly a bug in our implementation regardless of what we do > with views. I agree it's not a good behavior, but I still say it's traceable to schizophenia in the spec. If the result of a sub-select is nominally of a domain type, we should not have to recheck the domain constraints in order to assign it to a domain-typed target. If it's not nominally of a domain type, please cite chapter and verse that says it isn't. regards, tom lane
PostgreSQL domains and NOT NULL constraint
Hello
PostgreSQL's CREATE DOMAIN documentation (section Notes) describes a way how
one can add NULL's to a column that has a domain with the NOT NULL constraint.
https://www.postgresql.org/docs/current/sql-createdomain.html
To me it seems very strange and amounts to a bug because it defeats the purpose
of domains (to be a reusable assets) and constraints (to avoid any bypassing of
these).
Oracle 23c added the support of domains
(https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/create-domain.html).
I tested the same scenario both in PostgreSQL and Oracle
(https://www.oracle.com/database/free/) and found out that in these situations
Oracle does not allow NULL's to be added to the column. I do not know as to
whether the behaviour that is implemented in PostgreSQL is specified by the
standard. However, if it is not the case, then how it could be that Oracle can
but PostgreSQL cannot.
Best regards
Erki Eessaar
The scenario that I tested both in PostgreSQL (16) and Oracle (23c).
***
/*PostgreSQL 16*/
CREATE DOMAIN d_name VARCHAR(50) NOT NULL;
CREATE TABLE Product_state_type (product_state_type_code SMALLINT NOT NULL,
name d_name,
CONSTRAINT pk_product_state_type PRIMARY KEY (product_state_type_code),
CONSTRAINT ak_product_state_type_name UNIQUE (name));
CREATE TABLE Product (product_code INTEGER NOT NULL,
name d_name,
product_state_type_code SMALLINT NOT NULL,
CONSTRAINT pk_product PRIMARY KEY (product_code),
CONSTRAINT fk_product_product_state_type FOREIGN KEY (product_state_type_code)
REFERENCES Product_state_type(product_state_type_code) ON UPDATE CASCADE);
INSERT INTO Product_state_type (product_state_type_code, name)
VALUES (1, (SELECT name FROM Product_state_type WHERE FALSE));
/*Insertion succeeds, name is NULL!*/
INSERT INTO Product (product_code, name, product_state_type_code)
SELECT 1 AS product_code, Product.name, 1 AS product_state_type_code
FROM Product_state_type LEFT JOIN Product USING (product_state_type_code);
/*Insertion succeeds, name is NULL!*/
/*Oracle 23c*/
CREATE DOMAIN d_name AS VARCHAR2(50) NOT NULL;
CREATE TABLE Product_state_type (product_state_type_code NUMBER(4) NOT NULL,
name d_name,
CONSTRAINT pk_product_state_type PRIMARY KEY (product_state_type_code),
CONSTRAINT ak_product_state_type_name UNIQUE (name));
CREATE TABLE Product (product_code NUMBER(8) NOT NULL,
name d_name,
product_state_type_code NUMBER(4) NOT NULL,
CONSTRAINT pk_product PRIMARY KEY (product_code),
CONSTRAINT fk_product_product_state_type FOREIGN KEY (product_state_type_code)
REFERENCES Product_state_type(product_state_type_code));
INSERT INTO Product_state_type (product_state_type_code, name)
VALUES (1, (SELECT name FROM Product_state_type WHERE FALSE));
/*Fails.
Error report -
SQL Error: ORA-01400: cannot insert NULL into
("SYSTEM"."PRODUCT_STATE_TYPE"."NAME")
Help: https://docs.oracle.com/error-help/db/ora-01400/
01400. 0 - "cannot insert NULL into (%s)"
*Cause:An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.*/
INSERT INTO Product_state_type (product_state_type_code, name)
VALUES (1, 'Active');
INSERT INTO Product (product_code, name, product_state_type_code)
SELECT 1 AS product_code, Product.name, 1 AS product_state_type_code
FROM Product_state_type LEFT JOIN Product USING (product_state_type_code);
/*Fails.
SQL Error: ORA-01400: cannot insert NULL into
("SYSTEM"."PRODUCT"."NAME")
Help: https://docs.oracle.com/error-help/db/ora-01400/
01400. 0 - "cannot insert NULL into (%s)"
*Cause:An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.*/
