Drizzle Team•8mo ago

I cant append new integers in a integer array

          platforms: sql`ARRAY_CAT(${cryptoToken.platforms}, ARRAY['${coin.platform.id}'])`,

          platforms: sql`ARRAY_CAT(${cryptoToken.platforms}, ARRAY['${coin.platform.id}'])`,

i have tried 1000000 ways but i want to append it if possible?! here is the schema:

export const cryptoToken = pgTable("crypto_token", {
  id: integer("id").primaryKey(),
  rank: integer("rank"),
  name: text("name").notNull(),
  symbol: text("symbol").notNull(),
  slug: text("slug").notNull(),
  is_active: integer("is_active").notNull(),
  first_historical_data: text("first_historical_data"),
  last_historical_data: text("last_historical_data"),
  platforms: integer("platforms")
    .array()
    .notNull()
    .default(sql`ARRAY[]::integer[]`),
});

export const cryptoToken = pgTable("crypto_token", {
  id: integer("id").primaryKey(),
  rank: integer("rank"),
  name: text("name").notNull(),
  symbol: text("symbol").notNull(),
  slug: text("slug").notNull(),
  is_active: integer("is_active").notNull(),
  first_historical_data: text("first_historical_data"),
  last_historical_data: text("last_historical_data"),
  platforms: integer("platforms")
    .array()
    .notNull()
    .default(sql`ARRAY[]::integer[]`),
});

i just want to append integers to the array when available, this errors:

const insertResult = await db
        .insert(cryptoToken)
        .values({
          name: coin.name,
          symbol: coin.symbol,
          slug: coin.slug,
          is_active: 1,
          first_historical_data: coin.first_historical_data,
          last_historical_data: coin.last_historical_data,
          id: coin.id,
          platforms: sql`ARRAY_CAT(${cryptoToken.platforms}, ARRAY['${coin.platform.id}'])`,
          rank: coin.rank,
        })
        .onConflictDoUpdate({
          target: cryptoToken.id,
          set: {
            name: coin.name,
            symbol: coin.symbol,
            slug: coin.slug,
            is_active: 1,
            first_historical_data: coin.first_historical_data,
            last_historical_data: coin.last_historical_data,
            platforms: sql`ARRAY_CAT(${cryptoToken.platforms}, ARRAY['${coin.platform.id}'])`,
            rank: coin.rank,
          },
        });

const insertResult = await db
        .insert(cryptoToken)
        .values({
          name: coin.name,
          symbol: coin.symbol,
          slug: coin.slug,
          is_active: 1,
          first_historical_data: coin.first_historical_data,
          last_historical_data: coin.last_historical_data,
          id: coin.id,
          platforms: sql`ARRAY_CAT(${cryptoToken.platforms}, ARRAY['${coin.platform.id}'])`,
          rank: coin.rank,
        })
        .onConflictDoUpdate({
          target: cryptoToken.id,
          set: {
            name: coin.name,
            symbol: coin.symbol,
            slug: coin.slug,
            is_active: 1,
            first_historical_data: coin.first_historical_data,
            last_historical_data: coin.last_historical_data,
            platforms: sql`ARRAY_CAT(${cryptoToken.platforms}, ARRAY['${coin.platform.id}'])`,
            rank: coin.rank,
          },
        });

2 Replies

Sillvva•8mo ago

Not an expert on arrays, but could it be because you're wrapping coin.platform.id in single quotes making it a string instead of an integer? When I test this

SELECT array_cat(ARRAY[1], ARRAY['2'])
-- array_cat(integer[], text[]) -- No function matches the given name and argument types. You might need to add explicit type casts.
SELECT array_cat(ARRAY['1']::integer[], ARRAY[2])
SELECT array_cat(ARRAY[1], ARRAY[2])
-- [1, 2]

SELECT array_cat(ARRAY[1], ARRAY['2'])
-- array_cat(integer[], text[]) -- No function matches the given name and argument types. You might need to add explicit type casts.
SELECT array_cat(ARRAY['1']::integer[], ARRAY[2])
SELECT array_cat(ARRAY[1], ARRAY[2])
-- [1, 2]

HotBBQSauceOP•8mo ago

in the meanwhile i have updated the schema but still haven't found a solution, lets take the schema first as starting point i have updated the schema to look like this:

export const cryptoToken = pgTable("crypto_token", {
  id: integer("id").primaryKey(),
  rank: integer("rank"),
  name: text("name").notNull(),
  symbol: text("symbol").notNull(),
  slug: text("slug").notNull(),
  is_active: integer("is_active").notNull(),
  first_historical_data: text("first_historical_data"),
  last_historical_data: text("last_historical_data"),
});

type TokenPlatform = {
  id: number;
  contract_address: string;
};

export const platform = pgTable("platform", {
  id: integer("id").unique().primaryKey(),
  name: text("name"),
  slug: text("slug"),
  symbol: text("symbol"),
  tokens: jsonb("tokens").$type<TokenPlatform[]>(),
});

export const cryptoToken = pgTable("crypto_token", {
  id: integer("id").primaryKey(),
  rank: integer("rank"),
  name: text("name").notNull(),
  symbol: text("symbol").notNull(),
  slug: text("slug").notNull(),
  is_active: integer("is_active").notNull(),
  first_historical_data: text("first_historical_data"),
  last_historical_data: text("last_historical_data"),
});

type TokenPlatform = {
  id: number;
  contract_address: string;
};

export const platform = pgTable("platform", {
  id: integer("id").unique().primaryKey(),
  name: text("name"),
  slug: text("slug"),
  symbol: text("symbol"),
  tokens: jsonb("tokens").$type<TokenPlatform[]>(),
});

below is the actual return type of the API:

export type CMCCryptoMap = {
  id: number;
  rank: number;
  name: string;
  symbol: string;
  slug: string;
  is_active: number;
  first_historical_data: string;
  last_historical_data: string;
  platform?: Platform;
};

export type Platform = {
  id: number;
  name: string;
  symbol: string;
  slug: string;
  token_address: string;
  tokenId: number;
};

export type CMCCryptoMap = {
  id: number;
  rank: number;
  name: string;
  symbol: string;
  slug: string;
  is_active: number;
  first_historical_data: string;
  last_historical_data: string;
  platform?: Platform;
};

export type Platform = {
  id: number;
  name: string;
  symbol: string;
  slug: string;
  token_address: string;
  tokenId: number;
};

Oke now Platform really has alot of redundant data, basically al 8K rows could be wrapped into like 5-6 rows, im not sure what the best way would be to 'link' these together, pushing a single integer into a array that tells what token belongs to "this platform" was the easiest way i could come up with but it doesnt work.

Gaming

Programming

I cant append new integers in a integer array