You are conflating Apple Pay and Apple Card. They are two very different things.
Apple Card is a Goldman Sachs Master Card. It doesn't work at Costco because Costco has an exclusivity deal with VISA, so they don't accept Master Card, Discover, American Express, Diner's, and whatever else is out there.
Apple Pay is a method of payment that can be tied to many things like credit cards, debit cards, and prepaid accounts like Apple Cash. Costco IS compatible with Apple Pay, but if you using a credit card then the card must be a VISA. If it is not a credit card, you are fine, for example debit cards and I think Apple Cash.
I think this is a really good explanation—the payment network/card vs. the mechanism for passing the card information. It would be similar if a place had a EMV (chip) reader, but didn't accept a particular card network (or in Costco's case, Visa only)—the hardware can do it, but the payment processor/policies don't allow it.
Since Costco's terminals work with contactless, Apple Pay works, but the same card network restrictions apply. When the card is read, it's still seen as a Mastercard, regardless of
how it's read (physical card, iPhone, Watch, etc.)
On the debit side, there's a bit of screwiness that they seem to still be ironing out with contactless (and Apple Pay)—Visa ones seem to work and are captured as PIN-based debit, while some people have had a little bit of trouble with some debit Discover and debit Mastercards being recognized. It's a software issue with the way they have contactless set up (others more familiar can probably fill in the gaps), but the physical cards read fine when swiped or inserted. This could cause problems with Apple Cash (it's a Discover debit card behind the scenes).
I was in Canada a few months ago and their exclusivity deal is with Mastercard (and their store card is a Capital One-issued Mastercard, unlike our Citi-issued Visa in the US), so the Apple Card worked fine up there.