Psalm 146:4 is usually translated in one of two ways.
(1) "When his breath departs, he returns to the earth; on that very day his plans perish." (NIV, NLT, ESV, BSB, NASB, CSB, HCSB, CEV, Good News Translation, ISV, NET Bible)
(2) "His breath goeth forth, he returneth to his earth; in that very day his thoughts perish." (ASV, KJV, YLT, WEB, NHEB, LSV, JPS, ERV, DRB, BST, NASB 1977 and 1995)
So which is correct? They are two very different translations of the same word[עֶשְׁתֹּנֹתָֽיו] that imply entirely different things... or do they?
A plan is defined as "an intention or decision about what one is going to do." In order to intend to make decisions in the first place, you need a mind in which to produce said intentions; you can't conjure up a plan without a conscious mind to do so. Rocks don't plan; neither do houses, or rivers, or trees(or even most animals. Some can, but nowhere near on par with the ability of human beings to do so). Why? Because they don't have a conscious mind. If the plans perished, it's because the conscious mind that created the plans in the first place perished; if the conscious mind perishes, subsequently so do the thoughts.
But either way, it's all the same to me. Whether plans or thoughts, both are technically correct, though "thoughts" is a much better translation. Why? Well, because "plans" is implied. Your plans perish because your thoughts perish, not the other way around. They are causally related. Though your plans perish, it is not certain that your thoughts perish, as plans and thoughts are not synonymous. If your thoughts perish, however, then it is certain that your plans perish, because plans themselves are thoughts. So, "thoughts" is a better translation for עֶשְׁתֹּנֹתָֽיו than "plans" is. In the event that the thoughts are destroyed, so too would the plans, and thus they are causally linked; there is no need for it to be translated as "plans", as thoughts and plans are not mutually exclusive, and plans are a subset of the set of all thoughts, thus it is much more practical to translate עֶשְׁתֹּנֹתָֽיו as thoughts. (And, as shown in Dottard's insightful answer, the translation of "thoughts" is much better supported than the translation of "plans")
Now, while Psalm 146:4 doesn't explicitly note the state of the dead, as the state of the dead is not the purpose/focus of the Psalm, it implicitly reveals details about the state of the dead. How so?
If, at the moment of death, your thoughts perish(as per the Psalm), what does that imply about the afterlife? Do you somehow regain your thoughts later on? What is there to suggest that your thoughts(and subsequently your consciousness) don't remain in a perished state after they perish at the moment of death?
Here is my inductive argument:
P1: The thoughts of humans perish at the moment of death(confirmed by Psalm 146:4).
P2: There is nothing to suggest that humans regain thoughts at any later moment in their state of death.
P3: A conscious mind necessarily constructs thoughts about itself and its surroundings.
C1: Those in a state of death do not have thoughts(this follows inductively from premises 1 and 2)
C2: Those in a state of death do not have a conscious mind, i.e. they are unconscious(this follows deductively from premise 3 and conclusion 1)
So, to answer your question. Yes, Psalm 146:4 does affirm that those in a state of death do not possess a conscious mind, i.e. that they are in an unconscious state.
Hope this helps. Have a good day! :)