If you’re getting “ERROR: column “colname” does not exist’” in PostgreSQL when using an operator such as UNION, EXCEPT, or INTERSECT, it may be that you’re trying to reference an aliased column by its column name.
Category: DBMS
Database Management Systems
Return the Start of the Month in SQLite
SQLite gives us the ability to return the date of the beginning of the month, based on a given date.
This means we can return the date of the first day of the current month, or the first day of the month based on a date that we specify.
This allows us to perform further calculations on the resulting date, like adding a given number of days to it.
Continue readingFix “ERROR 1054 (42S22): Unknown column ‘…’ in ‘on clause'” in MariaDB
If you’re getting an error that reads something like “ERROR 1054 (42S22): Unknown column ‘tab.ColName’ in ‘on clause”” in MariaDB, here are three likely causes:
- The column doesn’t exist.
- You’re trying to reference an aliased column by its column name.
- Or it could be the other way around. You could be referencing the column with an alias that was never declared.
How to Return Only Numeric Values in SQL Server
In SQL Server, we can use the ISNUMERIC() function to return numeric values from a column.
We can alternatively run a separate query to return all values that contain numeric data.
Continue readingFix “ERROR: each EXCEPT query must have the same number of columns” in PostgreSQL
When using PostgreSQL’s EXCEPT operator, if you encounter an error that reads “ERROR: each EXCEPT query must have the same number of columns“, it’s because there’s a mismatch in the number of columns returned by the queries on either side of the EXCEPT operator.
The way to fix this is to ensure that both SELECT statements return the same number of columns.
Subtract Years from a Date in SQLite
In SQLite, we can use the DATE() function to subtract one or more years from a date.
For datetime values, we can use the DATETIME() function.
Fix “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in global ORDER clause” when using UNION in MySQL
If you’re getting an error that reads “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in global ORDER clause” when using the UNION clause in a MySQL query, it’s probably because you’re qualifying a column name with its table name.
This doesn’t work in MySQL.
To fix this issue, either remove the table name or use a column alias.
Continue readingAdd Days to a Date in PostgreSQL
The + operator allows us to add one or more days to a given date in PostgreSQL. We have a few options when it comes to specifying the actual number of days.
11 Ways to Find Duplicate Rows that have a Primary Key in Oracle
Here are eleven options for returning duplicate rows in Oracle Database when those rows have a primary key or some other unique identifier column and you want to ignore it.
Continue readingReturn Rows that Contain Non-Alphanumeric Characters in SQL Server
Here are examples of returning rows that contain non-alphanumeric characters in SQL Server.
Non-alphanumeric characters include punctuation characters like !@#&()–[{}]:;',?/* and symbols like `~$^+=<>“, as well as whitespace characters like the space or tab characters.