SQLite doesn’t have a SHOW TABLES statement like MySQL and MariaDB have, but it does have a similar command.
In SQLite, you can use the .tables command to show a list of tables. You can alternatively use the table_list pragma to do the job.
Author: Ian
SQLite Introduces the UNIXEPOCH() Function
SQLite 3.38.0 was released on 2nd February 2022. With it came some enhancement to the date and time functions.
One of those enhancements is the addition of the UNIXEPOCH() function. This function returns a unix timestamp. That is, the number of seconds since 1970-01-01 00:00:00 UTC.
4 Ways to Select Duplicate Rows in MariaDB
If you have a table that you suspect has duplicate rows in your MariaDB database, you can use any of the following queries to get an idea of how many rows are duplicates.
Continue readingReturn the End of the Month in SQLite
In SQLite, we can use the following method to calculate the last day of a given month.
We can base this either on the current date, or on some other specific date.
Continue readingFix ‘ERROR:Â column “colname” does not exist’ in PostgreSQL when using UNION, EXCEPT, or INTERSECT
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.
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.