All Questions
Tagged with ansi-sql-standard or sql-standard
123 questions
0
votes
1
answer
45
views
Why does the PostgreSQL mode() aggregate use WITHIN GROUP?
The mode() aggregate function selects the statistical mode. Some examples:
coredata_api=# create table t1 (pet text, meme int);
coredata_api=# insert into t1 (pet, meme) values ('dog', 6), ('dog', 6),...
- 719
-1
votes
1
answer
146
views
Find an authoritative statement that MariaDB fully supports the SQL:2016 JSON feature set
A software vendor refuses to support MariaDB on the grounds that they need JSON support, but that "JSON is not inherently included in the basic SQL standard, but it is part of the SQL:2016 ...
- 119
0
votes
4
answers
570
views
Why is LIMIT disallowed in IN subqueries in MySQL?
Please read carefully. This question is not about workarounds or alternative ways to achieve LIMIT in IN subqueries!
It’s a well-known and well-documented that there are a number of restrictions on ...
1
vote
1
answer
209
views
ANSI SQL - Generate a sequence and get values
wondering if the ANSI SQL standard has a portable way to create a sequence object and get values from it?
I can't find a portable way, but search engines seem to confuse ANSI sql with MS SQL server, ...
- 133
1
vote
1
answer
139
views
Since SQL-92, what new kinds of RDBMS constraints have been invented and which RDBMS implement them?
Backstory: I'm having to do some computer archeology for a data integration project, involving getting data out a multiple-decades-old old Progress, now OpenEdge, database application for AS/400.
...
- 692
0
votes
1
answer
196
views
Is UPDATE FROM still not in the ANSI standard?
This article fascinated me it claims that FROM clauses in UPDATE are not part of the ANSI standard. Unfortunately, it is very old and I don't care to buy a copy of the latest ANSI standard for SQL. Is ...
- 1,340
0
votes
0
answers
161
views
Should/can COMMIT/ROLLBACK emit an error when no transaction is active?
What does ISO/IEC 9075-1:2023 standard say about COMMIT/ROLLBACK queries? Should (or can) they emit an error/exception when executed outside an active transaction?
I am asking because of https://jira....
- 428
0
votes
0
answers
176
views
Are there guarantees that ANY_VALUE in aggregation and HAVING will use the same value?
I would assume this to be the case, since it seems like an easy optimization and to make things more predictable. Given:
SELECT ANY_VALUE(country) AS country
FROM countries
HAVING ANY_VALUE(country) = ...
- 363
2
votes
1
answer
1k
views
Why are backslashes doubled in SQL's string encoding?
The Postgres docs state:
While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string contains many single quotes or ...
- 139
6
votes
1
answer
165
views
What are these single letter SQL keywords?
I am looking at list of SQL keywords here https://www.postgresql.org/docs/current/sql-keywords-appendix.html
There are some single letter keywords there. I cannot find any information about them.
...
- 162
6
votes
3
answers
1k
views
Why are unquoted identifiers upper-cased per SQL-92?
Section 5.6 of the SQL-92 standard contains rules 10...13 per which unquoted identifiers should be upper-cased, so foo becomes FOO but "foo" remains foo.
These rules are respected by Oracle, ...
- 161
1
vote
2
answers
527
views
What is the equivalent of LIKE with wildcard the end 'abc%' using greater/less than operators?
I just found out that some specific patterns when used with LIKE text operator uses BTREE indexes to speed up queries, such as:
'abc123' (text with no wildcard)
'abc%' (text with only one wildcard in ...
0
votes
1
answer
1k
views
What is the standard SQL way to rename a column?
So far I've seen ALTER TABLE RENAME, ALTER TABLE CHANGE and ALTER TABLE MODIFY.
What I'm trying to achieve is something like ALTER TABLE my_table RENAME col_old_name TO col_new_name, which can be a ...
- 135
1
vote
2
answers
182
views
Efficient generation of quantiles for indexed column
I am trying to build a query partitioner for a distributed processing system. This system would work with databases that support JDBC.
The input of this partitioner would be the name of a table, and a ...
- 113
0
votes
1
answer
239
views
What is the formal SQL:2016 definition of a SQL identifier?
What is the formal definition of a SQL identifier in the ISO/IEC 9075:2016 standard?
Most specifically, in the case of both regular and delimited identifiers, what limitations exist on:
Character set ...
- 101
