0

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"
    }
]
9
  • That is the SQLSTATE for "syntax error". Figure out the generated SQL statement, then you can figure out where the syntax error is. Commented Feb 14, 2024 at 13:01
  • @LaurenzAlbe Syntax error yes, I had a look online for that 42601 PostgreSQL error; so could it be as something small as a spelling mistake or misplaced comma or an issue with my original array data as it works up to second const swappedData variable in the promise chain. Commented Feb 14, 2024 at 13:15
  • Figure out the generated SQL statement and add it to the question. Commented Feb 14, 2024 at 13:33
  • @LaurenzAlbe I am not sure I am understanding you. I run a npm command to create the database and then I do npm run seed and it gives the error shown above; there is nothing else generated in the console of my visual studio code. Commented Feb 14, 2024 at 13:47
  • 1
    You say that you are trying to insert something in a table. The only way you can do that is to execute an SQL statement. No matter if you write that statement yourself or if you have your code generate it, you have to figure out that statement. Otherwise you are beyond help. Commented Feb 14, 2024 at 14:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.