257 questions
2
votes
1
answer
102
views
Postgres not using indexes when checking for nulls
I have two tables. They each have around 2.3 million rows in them.
They are running on PostgreSQL 17.4
CREATE TABLE keyeddata (
category text NULL,
key1 text NULL,
key2 text NULL,
key3 ...
- 41.3k
1
vote
2
answers
217
views
Mutually exclusive columns and violating 3NF [closed]
CREATE TABLE foo
(
id SERIAL PRIMARY KEY,
a TEXT,
b TEXT
)
Column a can take a value only when column b is empty and vice versa:
id | a | b
---+-------+------
1 | NULL | 'abc'
2 | '...
- 436
1
vote
1
answer
95
views
Can the Oracle PHP function oci_bind_array_by_name() handle nulls?
When I use the Oracle PHP function oci_bind_array_by_name(), it converts all nulls in my array to 0s for any number fields and 0-length strings for varchar2s. The official documentation does not ...
- 33
-1
votes
1
answer
25
views
When querying a simple table in MySql, the row values appear as NULL. How do I get my query to show the row values?
I created a table called "Author" and inserted row values into it in MySql. When I query the table with SELECT * FROM Author; the result will show the columns but not row values.
I assume I ...
- 9
1
vote
3
answers
82
views
How to find the average of two columns but if one value is null, then only show me the other value?
I have a table where I want to find the average value of column1 and column2, but in some cases there may be null values in either columns. In that case, I just want to be shown the value that is NOT ...
- 13
-1
votes
1
answer
89
views
How to Select all Columns from a Table for a Record - but to exclude a Column if all entries for that Record in said Column are null?
I have a schema in a read-only Oracle database called MAXSCHEMA and a table in it called WORKORDER. This table has a column called WONUM.
If I use this SQL statement:
SELECT *
FROM MAXSCHEMA.WORKORDER
...
- 35
0
votes
0
answers
77
views
Passing SQL update query string to SQL Server from other software where update variable values may or not be NULL
I am creating an update query string in Bizagi, that will be passed to SQL Server to be executed.
The update values are taken from a Bizagi form, and may or not be NULL. The string includes boolean, ...
- 1
0
votes
1
answer
176
views
How to get a table with NULL values from a PostgreSQL INNER JOIN and UNION ALL query?
I have a PostgreSQL query for a time series graph with an inner join. A list of tags is used as a where condition for the inner join. I use union all to get a result table with data from two cases: a ...
- 936
0
votes
1
answer
199
views
Oracle SQL - Months between dates and null values
I am currently trying to get the number of months between 2 dates in Oracle SQL, however when the end date is null then it comes up with null, is there a way to use todays date if end date is null in ...
- 7
0
votes
1
answer
84
views
PostgreSQL NULL check after adding attribute on UDT
I have a problem that in Postgres 14 a NULL check on my UDT does no longer work after I modify the type.
My composite UDT that has two attributes.
create type A as (
foo text
);
create type B as (
...
- 335
0
votes
1
answer
56
views
How to SORT with Null and YEAR?
i want to sort with SQLITE so my data will show like this:
First sort by year asc, then alphabetic
If year is null then alphabetic desc
My ideas was :
ORDER by CASE WHEN year = NULL then Name Else ...
- 75
0
votes
1
answer
133
views
Can not properly insert NULL value in mysql database using python
I use json format to send the data, here is the fragment of my code which returns an error
def blocks_files(self, db, order_id, op_id, file_ids, images):
"""Creating record ...
-1
votes
1
answer
82
views
Adding a table that value isn't in - Oracle SQL Developer
I am trying to pull store data for locations in the US and around the world.
select
s.store,
s.store_name,
s.store_city,
s.state,
st.description as "STATE_DESC",
s....
- 9
-3
votes
1
answer
64
views
SQL Server 2022: Latest Update CU6 - Why does Last_Value does not work when there is no Partition? [duplicate]
Can anyone explain why Last_Value does not take the last value (which is last as per order by) for all rows ?
select * from mytableone
Initial Data
(Please click on link)
Query Used:
select t.*,
...
- 1
0
votes
1
answer
59
views
SQLAlchemy ORM: translate between None in SQLAchemy ORM instance attributes to a specific database column value?
SQLAlchemy ORM generally turns instance attributes that are None into NULL column values in the database (and vice-versa), but I'd like SQLAlchemy to convert between None values in the ORM and a ...
- 6,597
1
vote
1
answer
206
views
Generating a flag with respect to a change in consecutive records within partition
I have a snowflake table (called as original_table) with following fields case_num, code, project_name, sp_id, updated_date. For each case_num the combination of code, project_name, sp_id will be ...
- 89
0
votes
1
answer
111
views
How to call a possibly null returning sql function from C extension in PostgresQL
I'm writing a PostgresQL C extension and I'd like to call a sql function that can possibly return null value. I cannot use DirectFunctionCall family of functions, because they raise an error when ...
- 92
0
votes
2
answers
70
views
NOT Combination with AND
I am trying to query a list of rows from the database.
It has 7 fields (columns) (A,B,C,VA,VB,VC,LISTED).
SELECT * FROM datas WHERE
((A=1 OR A IS NULL) AND (B=1 OR B IS NULL) AND (C=1 OR C IS ...
- 572
0
votes
1
answer
55
views
I need to join 2 Tables based on the NULL values
My Tables are able follows
Table name: Student
NAME
STATUS
Jason
X
John
NULL
Table name: Status
STATUS
DESCRIPTION
X
Active
NULL
Inactive
I cannot get the expected results when I join the 2 tables ...
-2
votes
1
answer
352
views
How can I Replace an Artists' Last Name in SQL with the IN Clause?
I am currently learning SQL and I am doing a SQL assignment with the following scenario:
Select the FirstName and LastName for Artists who were born in Canada or the UK. Replace any artists last name ...
user20166753
0
votes
2
answers
2k
views
Null result from multiple left Join in postgresql
I have 3 tables in a database A, B and C which share the same column "name".
B has an attribute "title" and C has an attribute "age".
I am looking to write a SQL query ...
- 171
0
votes
1
answer
50
views
How to find the nearest numeric values (also in decimal places) in SQL same as LIKE function
I want to find the nearest coordinates to validate the place name as the format type is FLOAT, then I cannot use LIKE function as it is only applicable for STRING format.
situation example;
my ...
1
vote
1
answer
46
views
Understanding how NULL is treated in GROUP BY versus the WHERE clause?
While trying to isolate the 7 rows in the first query below, I got results that, as a novice, I was not expecting. I read over this SQLite document but do not understand why the 7 rows which are NULL ...
- 3,224
0
votes
0
answers
134
views
Treating null filter values as "any" when filtering on multiple columns in SQLAlchemy
We are trying to implement the ability to search across multiple (or all) columns on joined tables in our database, but with the requirement that an arbitrary number of columns can actually be left &...
- 1
3
votes
3
answers
116
views
Get the percentage of row with TEXT type
I am trying to calculate the percentage of the row events when the value is null. I have a table with 2 rows (zip_code, events).
TABLE weather
events TEXT
zip_code INTEGER
This is the query I ...
- 59
0
votes
1
answer
28
views
IFNULL around a complicated Case Function select column
How do I fix my IFNULL so that it doesn't break the below? If I remove the IFNULL than it works fine, but I need to calculate data off that column and need to get rid of the null values and replace ...
1
vote
1
answer
145
views
SQL checking equality inside a case statement inside where clause?
Here is the code:
WHERE 1=1
AND TU.Auction_I IN (41, 42)
AND @StatePickupID = CASE
WHEN @StatePickupID IS NOT NULL
THEN (UP.TransportStateID = @StatePickupID)
END
...
- 31
-1
votes
1
answer
45
views
select columns from SQL table with more than X number of NON-NULL values
I have a LOT of junk columns in my table containing thousands of null values and maybe one or two real values. I would like to select only the columns in this table containing more than 10 non-null ...
- 1
0
votes
0
answers
203
views
Handling nulls with df.to_sql() in postgres
I have data that I am using df.to_sql() on to get into my postgres warehouse. I have used the following answer to handle the same error, but now all nulls are inputted as nan in my tables. Below is ...
- 544
0
votes
1
answer
83
views
SQL - Formatting a result set and replacing text - code and pictures included
I'm having trouble formatting this result set. The columns vendor_address1 and vendor_address2 have the text NULL in them. I need to format the output like the picture. I have tried various case ...
- 23
1
vote
1
answer
186
views
SQL - Adding a % sign to NVL output/result set - pictures and code included
I am having trouble adding a percent sign to the output of the NVL() function. If you look in the desired output picture you will see that the commission needs to have a % added to it. I've tried ...
- 23
1
vote
1
answer
682
views
Does PostREST have a null-safe not-equal operator, like IS DISTINCT FROM?
I'm making some simple PostgREST queries on a table with network device properties. It seems that eq and not.eq both exclude null values. I've learned that that's a “feature” of PostgreSQL, and can be ...
- 763
3
votes
1
answer
671
views
How do I add conditional check after AND statement in SQL?
I want to check if certain column exists after AND statement and if it does, add another AND statement. What would be the best way to achieve this?
For example:
SELECT
*
FROM
db.table AS t
...
0
votes
1
answer
921
views
Referenced CTE in CASE expression is returning null values. My CTE works correctly as standalone query
I have a query that works as intended, and I need to build upon it to include a new column that has conditional values. The new column will result with either a date or a string 'na'. To accomplish ...
0
votes
1
answer
160
views
cast null value from database
I have a form that takes data from a database, I want to convert the data from null to a value of 0 in case there is no row in the database, I tried a lot but did not find the solution
{
con....
0
votes
1
answer
90
views
How to count in LEFT JOIN table
SELECT emp.`emp_id`,cio.`pin`,cio.`checktime` FROM payroll.employees AS emp
LEFT JOIN (SELECT * FROM zkteco_biotime.checkinout WHERE checktime > "2022-09-20 23:59:59") AS cio ON emp....
-1
votes
1
answer
72
views
script to omit null data
I have two php files to bring up a database in a particular order, but what I need is for the columns that are empty to be omitted.
the file on the web page is:
<?php
if(is_array($fetchData)){ ...
- 1
1
vote
2
answers
2k
views
Select rows which are there in one table and not there in another table by matching two columns values
I have two tables as shown below
t1:
cust_id product score
1 bat 0.8
2 ball 0.3
2 phone 0.6
3 tv 1.0
2 bat ...
- 2,911
0
votes
0
answers
511
views
Spring Boot @PrePersist does not work properly
I have a Spring Boot application with Kotlin and want to store the creation timestamp and the last updated timestamp. The following code should do the trick:
@Entity
data class Entity(
// ...
) {
...
- 1,250
1
vote
2
answers
69
views
Update NULL values in column with known values SQL
If I have the following table
name
nationality
AAA
french
BBB
english
CCC
spanish
DDD
dutch
BBB
NULL
AAA
NULL
How do I update the NULL values with 'english' and 'french' respectively
I tried the ...
- 406
1
vote
1
answer
517
views
Hive is not handling integer values properly when loading text data into table
I was loading some text data into Apache Hive containing int columns. It was storing null values at unexpected places. So, I ran some tests:
create table testdata (c1 INT, c2 FLOAT) row format ...
- 3,434
0
votes
2
answers
2k
views
PostgreSQL NULL value cannot be found [duplicate]
select * from test;
select * from test where name not in ('amtf');
Why?
- 75
2
votes
2
answers
99
views
Need a SQL select statement to return rows that have the same id in one column and distinct value in another column
I have a table that contains a group number column and a data column:
GROUP
DataColumn
1
NULL
1
NULL
1
"hello"
1
NULL
2
"bye"
2
"sorry"
3
NULL
3
NULL
3
NULL
I want to ...
1
vote
3
answers
139
views
Eliminating null values in union
I'm doing a query across databases with an identical structure, to show a mapping from a source value to a target value.
Every one of my databases has a table with two columns: source and target
DB1
...
- 13.9k
2
votes
0
answers
2k
views
Behavior of "created on" and "updated on" database fields
In general, it is a good practice to use "created on" and "updated on" columns for SQL database tables for traceability reasons, exact we you have some other way of tracing the ...
- 2,021
1
vote
1
answer
98
views
Why does the SQL standard define JOIN USING via COALESCE of corresponding join column values given they can't be NULL?
The SQL standard (SQL:2011 draft, p. 363) says that when two tables are joined with USING, the result has only one column named c for each c (called a corresponding join column) specified in <join ...
- 1,201
0
votes
2
answers
2k
views
Data being entered as NULL in database through Spring Application
I am trying to enter data into my database, but some fields are being entered as NULL
Here is fields of my Model class on Spring
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
...
- 342
0
votes
1
answer
2k
views
column contains null values
I added Person as a foreign key to the CareerHistory table.
person = models.ForeignKey(
Person, on_delete=models.PROTECT, default=None, related_name='careerist')
I set default=None thinking ...
- 2,073
-3
votes
3
answers
2k
views
How to do LEFT JOIN a ON b = c, but if joined table is NULL, join on x instead in (Postgres) SQL?
Given I have two SQL tables:
movies
----------------
| id | title |
----------------
| 1 | Matrix |
| 2 | Titanic |
----------------
directors
-----------------------
| id | name | ...
- 12.4k
4
votes
2
answers
3k
views
In Postgres, field != 'N' fails to include NULL value [duplicate]
We have a case in Postgres where
where p.is_ready_for_submission != 'N'
is failing to include NULL as a satisfying value for this condition. The column is_ready_for_submission can be NULL, Y, N. The ...
- 12.7k