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
Sillvva7mo 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]
HotBBQSauce
HotBBQSauceOP7mo 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.
Want results from more Discord servers?
Add your server