I am trying to an insert query of bird family data below ( using function insertIntoB_familiestbl) related to bird families into bird families table. But I am getting an error relating to the expressions not matching the number of columns.
Here is create table function and insert table function using pg-format:
function createBirdFamilies_rel(){//bird_families table
return db.query(`
CREATE TABLE bird_families (
family_id SERIAL PRIMARY KEY,
scientific_fam_name VARCHAR(55) UNIQUE NOT NULL,
f_description TEXT,
clutch_size int4range,
habitats text ARRAY,
predators text ARRAY,
o_id INT REFERENCES bird_orders(order_id)
)
`)
}
function insertIntoB_familiestbl(swappedData){//insert into bird_families
const formattedData = parseObjToNestedArr(swappedData)
const queryStr = format(`
INSERT INTO bird_families
(scientific_fam_name,f_description,clutch_size,habitats,predators,o_id)
VALUES
%L
RETURNING *;
`,formattedData
)
return db.query(queryStr)
}
I am using a promise chain of then() functions and a ref making functions as the tables involved in this database have foreign keys. SO the bird families table is related to the bird orders table. When include this code it fails at the insert function (noted in CAPITAL LETTERS; scroll down). The createRef functions swap out the field value for the ID in the parent table (so for example the createWingsRef function swaps out the wing shape_name for the wing_id that then can be used in the bird_orders table) for creating the dependency but this is not related to the error.
const seeder = ({wing_shapeData}) =>{
...etc (previous code that works)
.then(()=>{
return insertIntoW_shapetbl(wing_shapeData)
})
.then(({rows})=>{
const ref = createWingsRef(rows)
const swappedData = addWIdToOrders(birdsOrdersData,ref)
//console.log(swappedData)
return insertIntoB_orderstbl(swappedData)
//Whoopee works now!!
}).then(({rows})=>{
const ref = createOrdersRef(rows)
const swappedData = addOIdToFamilies(birdsFamiliesData,ref)
return insertIntoB_familiestbl(swappedData) //FAILS HERE AND GIVES ERROR
}).catch((err)=>{
console.log(err)
})
}
Error I receive is:
error: INSERT has more expressions than target columns
at /home/mba3s5a1/Program/programs/projects/bird_rookery_project/node_modules/pg-pool/index.js:45:11
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
length: 116,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '368',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'analyze.c',
line: '974',
routine: 'transformInsertRow'
}
Here is some array data I have related to bird families
const bird_familesTD = [
{
scientific_fam_name: "Mesitornithidae",
f_description: "These are a family of small near-flightless birds endemic to Madagascar. Every species in this family (white, brown and sub-desert) is threatened. They are vocal birds that produce passerine-like sounds for territorial defence",
clutch_size : '[2,3]',
habitats: ["dry forests","woodlands"],
predators:["reptiles","larger mammals"],
order:"Mesitornithiformes"
},
{
scientific_fam_name: "Strigidae",
f_description: "This is a large family that includes the True Owl found on all continents except Antarctica. 95% are forest-dwelling but most are non-migratory with rest migrating depending on the seasons. These owls have large heads, elongated eyes and a short hooked bill that point downwards. Many species have ear tufts that are suggested to be for behavioural functions. They have talons that are sharp and hooked and with a reversible fourth toes (zygodactyly). Like most owls they hunt in low-light conditions. When confronted with danger they will crouch down, lower their heads, drop their wings and ruffle their feathers.",
clutch_size : '[3,4]',
habitats: ["forests","tundras","deserts"],
predators:["none"],
order:"Strigiformes"
}
]