My question is about PostgreSQL. I found similar questions for MS SQL server but I don't know if the answers apply here. My table looks like this: scores ====== | ID | UserID | ValidFrom | ValidUntil | ScorePoints | +----+--------+------------+------------+-------------| | 1 | 1 | 2025-08-01 | 2025-08-02 | 80 | | 2 | 1 | 2025-08-02 | NULL | 85 | There will be a lot of queries to find the currently valid score for a given UserID (= the row for UserID = ? AND ValidUntil IS NULL). I have a unique index like this: CREATE UNIQUE INDEX ix_uq_scores ON scores ( userid ) WHERE validuntil IS NULL; Or should it be: CREATE UNIQUE INDEX ix_uq_scores ON scores ( userid, validuntil ) WHERE validuntil IS NULL; A query might look like SELECT u.id, u.username, s.scorepoints FROM users u INNER JOIN scores s ON s.userid = u.id AND s.validuntil IS NULL WHERE u.id = 123; My filtered index will only include rows where validuntil **IS** NULL. So do I have to add this constant NULL value to the index tuple?

[The docs](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver16#comparing-coalesce-and-isnull) insist that ISNULL is a function, but COALESCE is not. Specifically, they say > The ISNULL function and the COALESCE expression If I put on my Lisp hat, I can think of COALESCE as a macro and everything makes sense. However, I've never needed such thinking in T-SQL. So, I must ask. In T-SQL: 1. What is the definition of a function? 2. What is the definition of an expression? 3. How can I tell the difference between a function and an expression?

I have a PostgreSQL table with the following schema and indexes

+---------+---------+-----------+----------+--------------+-------------+
| Column  | Type    | Modifiers | Storage  | Stats target | Description |
|---------+---------+-----------+----------+--------------+-------------|
| number1 | integer |  not null | plain    |        |       |
| number2 | integer |  not null | plain    |        |       |
| account | bytea   |           | extended |        |       |
+---------+---------+-----------+----------+--------------+-------------+
Indexes:
    "test_pkey" PRIMARY KEY, btree (number1, number2)
    "test_account_idx" btree (account, number1, number2)
Has OIDs: no

When I query filtering WHERE "account" IS NULL

EXPLAIN
SELECT *
FROM "test"
WHERE "account" IS NULL
ORDER BY
  "number1" ASC,
  "number2" ASC
;

The query plan does not use the optimal index "test_account_idx"

+-------------------------------------------------------------------------------+
| QUERY PLAN                                                                    |
|-------------------------------------------------------------------------------|
| Index Scan using test_pkey on test  (cost=0.29..4672.97 rows=106319 width=29) |
|   Filter: (account IS NULL)                                                   |
+-------------------------------------------------------------------------------+

However, when I filter on WHERE "account" = instead of IS NULL

EXPLAIN
SELECT *
FROM "test"
WHERE "account" = '\x00'
ORDER BY
  "number1" ASC,
  "number2" ASC
;

The query plan DOES use the optimal index

+-----------------------------------------------------------------------------------+
| QUERY PLAN                                                                        |
|-----------------------------------------------------------------------------------|
| Index Only Scan using test_account_idx on test  (cost=0.42..4.50 rows=5 width=29) |
|   Index Cond: (account = '\x00'::bytea)                                           |
+-----------------------------------------------------------------------------------+

Why does filtering on NULL instead of a value make the query so much less efficient?

When I run this query it returns me a count of null values of the column country (select sum(case when (country IS NULL) then 1 else 0 END) as 'null_flag' from city) whereas when I try to add the same code in a procedure like this DELIMITER $$ CREATE PROCEDURE get_null_flags (col_name VARCHAR(40)) BEGIN (select sum(case when ',[' + col_name + ']' IS NULL then 1 else 0 END) as 'null_flag' from city) END $$ DELIMITER ; It throws an error saying Query 1 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 10 Note: I am using MySQL Ver 8.0.28 I have installed this version via Homebrew. I also have an older version of Mysql ie: 5.7 and I switch the versions using this documentation - https://jeanleem6.github.io/2019/05/08/Multiple-MySQL-Versions-with-Homebrew/

I would like to count the number of null's present per row in a table without enumerating column names. For example: WITH t as (VALUES (NULL ,'hi',2,NULL,'null'), ('' ,'hi',2,3,'test'), (NULL ,'hi',2,3,'null') ) SELECT countnulls(t) FROM t; Would result in: numnulls 2 0 1 The closest I can get is with the following hack of row_to_json(): select (CHAR_LENGTH(row_to_json(t)::text) - CHAR_LENGTH(REPLACE(row_to_json(t)::text, 'null', '')))/4 from t; Which is... quite the hack (not in a good way). It works, sort of, but it counts the string 'null' as a NULL when it is present in the actual data or in the column names. So it is incorrect in the above case.

Beginner with SQL and trying to improve this using ISNULL instead of the longer statement?

CASE WHEN c.FirstName IS NULL THEN '' ELSE c.FirstName END

I have a database that collects statistics from Usenet. When I do a basic select such as:

select day, count(day) from statistics where month = 12 and year = 2020 group by day;

I get the result

+------+------------+
| day  | count(day) |
+------+------------+
|    1 |         72 |
|    2 |         82 |
|    3 |         94 |
|    4 |         73 |
|    5 |        121 |
|    6 |         42 |
|    7 |         27 |
|    8 |         26 |
|    9 |          6 |
|   14 |          6 |
|   15 |         18 |
|   16 |         23 |
|   19 |         16 |
|   18 |         90 |
|   17 |         78 |
|   20 |         42 |
|   21 |         77 |
|   22 |         57 |
|   23 |        155 |
|   24 |         85 |
|   25 |         21 |
|   26 |         40 |
|   27 |         63 |
|   28 |         90 |
|   29 |         68 |
|   30 |         49 |
|   31 |         50 |
+------+------------+

Some of the fields are where days have not been counted (10,11,12,13), so are NULL in the d'base. How do I modify my SELECT statement so that it displays this missing info like:

|   10 |         0 |
|   11 |         0 |
|   12 |         0 |
|   13 |         0 |

to give a complete result of:

+------+------------+
| day  | count(day) |
+------+------------+
|    1 |         72 |
|    2 |         82 |
|    3 |         94 |
|    4 |         73 |
|    5 |        121 |
|    6 |         42 |
|    7 |         27 |
|    8 |         26 |
|    9 |          6 |
|   10 |          0 |
|   11 |          0 |
|   12 |          0 |
|   13 |          0 |
|   14 |          6 |
|   15 |         18 |
|   16 |         23 |
|   19 |         16 |
|   18 |         90 |
|   17 |         78 |
|   20 |         42 |
|   21 |         77 |
|   22 |         57 |
|   23 |        155 |
|   24 |         85 |
|   25 |         21 |
|   26 |         40 |
|   27 |         63 |
|   28 |         90 |
|   29 |         68 |
|   30 |         49 |
|   31 |         50 |
+------+------------+

I'm not sure if there's something to do with IFNULL or it's more complicated (I am not an expert :-)) using a JOIN.

How do I get try_convert datetime to display NULL when the input is space? I am getting two different results here. My intention is to show 'space' number query as Null also, just like first query. select try_convert(datetime,'abcd') ---> Null select try_convert(datetime,'') ---> '1/1/1900'

Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve I'm trying to make a query to bring up ID's that have ALL null values and are located in another table. DB looks like: Table 1 BOOK ID| User ID 1 | 1 1 | 2 2 | 17 2 | 16 3 | 17 3 | 16 4 | 3 4 | 16 Table 2 USER ID | Name 1 | Jeff 2 | Bill 3 | Fred The desired outcome would be to bring up Book IDs 2 and 3 as both IDs only have users that cannot be found in Table 2.

I have a derived VARCHAR date field that I need to use for deriving another field, based off of whether or not the date field has a value. The derived date field is called "AmortDate" and I'm attempting the following: CASE WHEN 'AmortDate' IS NOT NULL THEN 1 ELSE 0 END AS "IsAmortizing?' I've tried several different variations ( NULL, '', IS NOT NULL) CASE WHEN ln.PAYMENT_SCHEDULE_TYPE = '0' THEN '' WHEN ln.PAYMENT_SCHEDULE_TYPE = '1' THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120) WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '0' AND ln.PAYMENT_SCHEDULE_NBR = '1' AND lpsf.psDatePmtDueFirst '' THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120) WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '0' AND ln.PAYMENT_SCHEDULE_NBR = '1' AND lpsf.psDatePmtDueFirst = '' THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120) WHEN lpsf.psNbrPmtsThisSchedule = '999' AND ln.TERM_IN_MONTHS = '999' AND ln.PAYMENT_SCHEDULE_NBR = '1' THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120) WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE = '0' AND ln.PAYMENT_SCHEDULE_AMOUNT = '0' THEN CONVERT(VARCHAR(10),ln.DATE_EXPECT_PAYOFF,120) WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE IN('1','2','3') AND ln.PAYMENT_SCHEDULE_AMOUNT = '0' THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120) WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE IN('1','2','3') AND ln.PAYMENT_SCHEDULE_AMOUNT > 0 THEN CONVERT(VARCHAR(10),lpsf.psDatePmtDueFirst,120) WHEN lpsf.psNbrPmtsThisSchedule NOT IN('1','999') AND ln.PAYMENT_SCHEDULE_TYPE = '6' AND ln.PAYMENT_SCHEDULE_AMOUNT = '0' THEN CONVERT(VARCHAR(10),ln.DATE_MATURITY_NEXT,120) ELSE '' END AS AmortDate Based off of the results of the derived AmortDate field above, I need to create another calculated field: CASE WHEN AmortDate IS NOT NULL THEN 1 ELSE 0 END AS 'IsAmortizing'

There is a table 'test' with ~ 10M records with field 'finish'. select count(*) from test where finish is null; count --------- 2485009 (1 row) I'd like to create index to speed up queries like select * from test where finish is null; Just for testing purpose the following indexes were created: create index idx_t_0 on test(finish); create index idx_t_1 on test((finish is null)); create index idx_t_2 on test(id) where finish is null; gist=# set track_io_timing=on; [16/1920] SET gist=# EXPLAIN (ANALYZE, BUFFERS) select * from test where finish is null; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..2829257.75 rows=2493440 width=1249) (actual time=0.031..23673.804 rows=2485009 loops=1) Filter: (finish IS NULL) Rows Removed by Filter: 6686627 Buffers: shared hit=129944 read=2606747 I/O Timings: read=15210.163 Planning time: 2.294 ms Execution time: 23847.759 ms (7 rows) gist=# set enable_seqscan=off; SET gist=# EXPLAIN (ANALYZE, BUFFERS) select * from test where finish is null; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- -------- Bitmap Heap Scan on test (cost=40312.83..2851483.71 rows=2493440 width=1249) (actual time=362.433..43436.427 rows=2485009 l oops=1) Recheck Cond: (finish IS NULL) Rows Removed by Index Recheck: 3622568 Heap Blocks: exact=14958 lossy=891618 Buffers: shared read=913368 I/O Timings: read=23970.878 -> Bitmap Index Scan on idx_t_2 (cost=0.00..39689.47 rows=2493440 width=0) (actual time=357.046..357.046 rows=2485009 loops=1 ) Buffers: shared read=6792 I/O Timings: read=50.203 Planning time: 0.256 ms Execution time: 43688.891 ms (11 rows) gist=# set enable_bitmapscan = off; SET gist=# EXPLAIN (ANALYZE, BUFFERS) select * from test where finish is null; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- ---------- Index Scan using idx_t_1 on test (cost=0.43..5670318.77 rows=2493440 width=1249) (actual time=2.049..14702.455 rows=2485009 loops=1) Index Cond: ((finish IS NULL) = true) Filter: (finish IS NULL) Buffers: shared read=913369 I/O Timings: read=9994.256 Planning time: 0.227 ms Execution time: 14875.190 ms (7 rows) **Config** listen_addresses ='*' port=5432 max_connections = 100 work_mem = 13107kB shared_buffers = 2GB effective_cache_size = 6GB maintenance_work_mem = 1GB # checkpoint_timeout = 300 # checkpoint_warning = 300 checkpoint_completion_target = 0.9 synchronous_commit = off default_statistics_target = 500 random_page_cost = 4 effective_io_concurrency = 2 wal_buffers = 16MB min_wal_size = 4GB max_wal_size = 8GB max_worker_processes = 4 max_parallel_workers_per_gather = 2 max_parallel_workers = 4 max_files_per_process=500

I want the list of unique columns with not null constraint

Here is my temporary table: CREATE TABLE #test123( c1 float, c2 float, c3 float, c4 float, c5 float ); INSERT #test123(c1,c2,c3,c4,c5) VALUES (2,3,NULL,1,2), (2,NULL,NULL,1,2), (2,3,NULL,NULL,2), (NULL,3,NULL,1,NULL), (2,3,NULL,1,2); When I run the following querys to replace all NULLs in the first column INSERT INTO #test123 (c1) VALUES (ISNULL(c1,0)); or INSERT INTO #test123 (c1) SELECT CASE WHEN c1 IS NULL THEN 0 END AS c1; I receive both time the error "Invalid column name 'c1'." The Syntax (source ) of INSERT INTO is: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

I am working with a SQL Server database, which includes a lot of NULLs. To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2). My database is similar to this structure: c1 c2 c3 c4 c5 ----------------------------------------------------- 2 3 NULL 1 2 2 NULL NULL 1 2 2 3 NULL NULL 2 NULL 3 NULL 1 NULL 2 3 NULL 1 2 I tried the query, which doesn't return an error, but no rows are selected: SELECT * FROM test123 WHERE ((ISNULL(c1,1) + ISNULL(c2,1) + ISNULL(c3,1) + ISNULL(c4,1) + ISNULL(c5,1)) < 2); I expect this query to return the 1st and the fifth row, but the result contains 0 rows. --------------------------- I can't test the following code, because I don't have the rights to write on the database, but here is a (pseudo-) code for creating a table like mine: CREATE TABLE test123( c1 float, c2 float, c3 float, c4 float, c5 float ) GO INSERT test123(c1,c2,c3,c4,c5) VALUES (2,3,NULL,1,2), (2,NULL,NULL,1,2), (2,3,NULL,NULL,2), (NULL,3,NULL,1,NULL), (2,3,NULL,1,2);

I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table. Some thing like: select 'all columns with NULL' from table_xyz where primary_key='pk_row2'; I use SQL Server 2012. Please help achieve this.

I am very new to Teradata and SQL environment. I am trying to remove all the null values from sql table in teradata using ISNULL(COLUMNNAME,0). But it gives an error that "the data type does not match the defined data type name". Also, would like to know if there is any way of removing all NULL at once rather than column wise writing the above query again and again.

I'm seeing a lot of where NULL IS NULL type conditions in code and I'm curious about the overhead that may be involved in this. So given SELECT * FROM table WHERE field = 'TERM' AND (NULL IS NULL OR anotherField = NULL) on a table > 10+ million records. Would there be enough concern to enforce the removal of the NULL IS NULL condition or the difference is negligible? Developers are using this syntax to write generic SQL that handles optional query parameters. Personally I feel it should be left off the query if the parameter is not provided as it seems redundant. I've done a little bit of Googling on this and I can't seem to find the answer, maybe my search terms are off as it seems common.

Is there a way, to optimize this query, where several IsNull in relation with dates are used? Unfortunately the query is a way slow and actually I have no glue, how to make it way faster. case when IsNull(A.TD, '1900-01-01') > IsNull(B.TD, '1900-01-01') then IsNull(A.TD, '1900-01-01') when IsNull(A.TD, '1900-01-01') < IsNull(B.TD, '1900-01-01') then IsNull(B.TD, '1900-01-01') when IsNull(A.TD, '1900-01-01') = IsNull(B.TD, '1900-01-01') then S.TE end as DZ Thank you.

It's my impression that comparing to null limits the use of an index. We had a DBA come in and that statement was part of his notes, however no better example was given. If that statement is true, what is a better way to write the following? SELECT * FROM dbo.DpdRoute WHERE DestinationCountry =@COUNTRY AND ( (BeginPostCode IS NULL AND ENDPOSTCODE IS NULL) OR (BeginPostCode = @POSTCODE AND ENDPOSTCODE IS NULL) OR (BeginPostCode IS NULL AND ENDPOSTCODE = @POSTCODE) OR (BeginPostCode = @POSTCODE)) I'm looking for a list of routes, for a given country where a postcode is either within a range of postcodes, equal to the min or max post code, or the min or max post codes are both null. How does one "not compare to null" when the comparison is specifically about "nulls"?

I have the below query (showing the last part of the query). In the first example I get a result (which is not exactly as desired) but the results arrive within 10min. The second version results in an error: snapshot too old: rollback segment number. Basically I'm joining in a second table with one key, and add a second key if available, and a third key if available. If key #2 and #3 are null then the join should only happen on the first key. My research has shown that the snapshot too old error message originates from the fact that the query takes too much resources. That's why I would like to understand in how far NVL is more efficient than IS NULL and what I can do to adjust my query. quick but result is not as desired: ..……. left outer join tlea on ( dset_cob_dt = tlea_cob_dt and meas_data.tlsn_trd_id = tlea.tlsn_trd_id and nvl (meas_data.tlsn_vrsn_num, -1) = nvl (tlea.tlsn_vrsn_num, -1) and meas_data.tlsn_leg_id = tlea.tlsn_leg_id); works correctly but getting errors: ……… LEFT OUTER JOIN tlea ON (dset_cob_dt = tlea_cob_dt AND meas_data.tlsn_trd_id = tlea.tlsn_trd_id AND (meas_data.tlsn_leg_id = tlea.tlsn_leg_id OR tlea.tlsn_leg_id IS NULL) AND (meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num OR tlea.tlsn_vrsn_num IS NULL) ); ORA-12801: error signaled in parallel query server P034 ORA-01555: snapshot too old: rollback segment number 240 with name "_SYSSMU240_2257379025$" too small How can I make the second quefry more efficient so I can avoid the error? Is there a way to achieve the same with NVL? UPDATE: The query should always join on tlsn_trd_id and if available (not null) additionally on tlsn_leg_id and if available (not null) additional on tlsn_vrsn_num as well