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.