mysql - 在将此插入写入查询时需要一些帮助，什么是有效的方法？

Question

我正在组装一个菜谱应用程序，但这次我使用 PostgreSQL 作为数据库，而不是 MEAN 堆栈。一切都很顺利，直到我编写 INSERT INTO 的时候，一切都在走下坡路，我用几种不同的方式编写了这段代码，这些是我收到的错误:

Server Stareted On Port 3000
events.js:141
      throw er; // Unhandled 'error' event
      ^

error: null value in column "id" violates not-null constraint

尝试了其他方法并得到:

Server Stareted On Port 3000
events.js:141
      throw er; // Unhandled 'error' event
      ^

error: syntax error at or near "WHERE"

上面是我认为通过添加 where id = 就可以工作的时候的内容

Server Stareted On Port 3000
error running query { [error: there is no parameter $1]
  name: 'error',
  length: 87,
  severity: 'ERROR',
  code: '42P02',
  detail: undefined,
  hint: undefined,
  position: '59',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_expr.c',
  line: '824',
  routine: 'transformParamRef' }

尝试了其他方法并得到:

events.js:141
      throw er; // Unhandled 'error' event
      ^

error: INSERT has more expressions than target columns

我不知道发生了什么或如何重新安排我的代码，我的意思是我认为这就像主 ID 缺少某些东西一样简单，但在尝试了上述所有操作之后，坦白地说，我已经花了很多钱，不知道从这里去哪里，是的，我什至看过这里的其他帖子。

这是views/app.js的代码:

var express = require('express'),
    path = require('path'),
    bodyParser = require('body-parser'),
    cons = require('consolidate'),
    dust = require('dustjs-helpers'),
    pg = require('pg'),
    app = express();

// create a config to configure both pooling behavior
// and client options
// note: all config is optional and the environment variables
// will be read if the config is not present
var config = {
  user: 'aleatoire', //env var: PGUSER
  database: 'recipebookdb', //env var: PGDATABASE
  password: '', //env var: PGPASSWORD
  port: 5432, //env var: PGPORT
  max: 10, // max number of clients in the pool
  idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed
};


//this initializes a connection pool
//it will keep idle connections open for 30 seconds
//and set a limit of maximum 10 idle clients
var pool = new pg.Pool(config);


// Assign Dust Engine To .dust Files
app.engine('dust', cons.dust);

// Set Default Ext .dust
app.set('view engine', 'dust');
app.set('views', __dirname + '/views');

// Set Public Folder
app.use(express.static(path.join(__dirname, 'public')));

// Body Parser Middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: false}));

app.get('/', function(req, res){
    // to run a query we can acquire a client from the pool,
    // run a query on the client, and then return the client to the pool
    pool.connect(function(err, client, done) {
      if(err) {
        return console.error('error fetching client from pool', err);
      }
      client.query('SELECT * FROM recipes', function(err, result) {

        if(err) {
          return console.error('error running query', err);
        }
        res.render('index', {recipes: result.rows});
        //call `done()` to release the client back to the pool
        done();

      });
    });

});

app.post('/add', function(req, res){
    // to run a query we can acquire a client from the pool,
    // run a query on the client, and then return the client to the pool
    pool.connect(function(err, client, done) {
      if(err) {
        return console.error('error fetching client from pool', err);
      }
      client.query('INSERT INTO recipes(name, ingredients, directions) VALUES($1, $2, $3, $4)', [req.body.name, req.body.ingredients, 
        req.body.directions]);

        done();
        res.redirect('/');
    });
});

// Server
app.listen(3000, function(){
    console.log('Server Stareted On Port 3000');
});

这是 package.json 文件:

{
  "name": "recipebook",
  "version": "1.0.0",
  "description": "A recipe web app with Nodejs and PostgreSQL",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "Daniel Cortes",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.15.2",
    "consolidate": "*",
    "dust": "*",
    "dustjs-helpers": "*",
    "dustjs-linkedin": "*",
    "express": "*",
    "pg": "*"
  }
}

这是views/layout.dust:

  <!DOCTYPE html>
    <html>
    <head>
        <title>RecipeBook</title>
        <link rel="stylesheet" href="/css/bootstrap.css">
        <link rel ="stylesheet" href="/css/style.css">
    </head>
    <body>
        <div class="container">
            <div class="row">
                <div class="col-md-offset-2 col-md-7">
                    {+body /}
                </div>
            </div>
        </div>
        <script src="https://code.jquery.com/jquery-1.12.4.min.js" integrity="sha256-ZosEbRLbNQzLpnKIkEdrPv7lOy9C27hHQ+Xp8a4MxAQ="   crossorigin="anonymous"></script>
        <script src="/js/bootstrap.js"></script>
    </body>
    </html>

这是views/index.dust:

{>"layout" /}

{<body}
    <button  type="button" class="btn btn-primary btn-block" data-toggle="modal" data-target="#formModal">
        Add Recipe
    </button>

    <br />

    {#recipes}
        <div class="well">
            <h4>{name} 
            <button class="btn btn-default pull-right" data-toggle="collapse" href="#recipe_{id}" aria-expanded="false" aria-controls="recipe_{id}">
                <span class="glyphicon glyphicon-triangle-bottom" aria-hidden="true"></span>
            </button></h4>

            <div class="collapse" id="recipe_{id}">
              <br />
              <br />
              <p><strong>Ingredients: </strong>{ingredients}</p>
              <p><strong>Directions: </strong>{directions}</p>
              <br />
              <hr />
              <button class="btn btn-default edit-recipe">
                <span class="glyphicon glyphicon-edit" aria-hidden="true"></span>
              </button>
              <button class="btn btn-danger delete-recipe">
                <span class="glyphicon glyphicon-remove" aria-hidden="true"></span>
              </button>
            </div>
        </div>
    {/recipes}

    <!-- Add Form Modal -->
    <div class="modal fade" id="formModal" tabindex="-1" role="dialog" aria-labelledby="formModalLabel">
      <div class="modal-dialog" role="document">
        <div class="modal-content">
            <form method="post" action="/add">
              <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                <h4 class="modal-title" id="myModalLabel">Add Recipe</h4>
              </div>
              <div class="modal-body">
                <div class="form-group">
                    <label>Recipe Name</label>
                    <input type="text" class="form-control" name="name" />
                </div>
                <div class="form-group">
                    <label>Ingredients</label>
                    <textarea name="ingredients" class="form-control"></textarea>
                </div>
                <div class="form-group">
                    <label>Directions</label>
                    <textarea class="form-control" name="directions"></textarea>
                </div>
              </div>
              <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                <input type="submit" class="btn btn-primary" value="Save" />
              </div>
          </form>
        </div>
      </div>
    </div>
{/body}

Answer 1

根据给出的代码，表结构看起来像

CREATE TABLE public.recipes(
id integer NOT NULL, --(Actually NOT NULL is not required here, as it is Primary key)
name character(255), 
ingredients text, 
directions text, 
CONSTRAINT recipes_pkey PRIMARY KEY (id));

这里id是主键，你希望它自动递增。

有两种方法可以实现这一目标

1) 将 id 数据类型从 integer 更改为 SERIAL

CREATE TABLE public.recipes(
id serial,
name character varying(255), 
ingredients text, 
directions text, 
CONSTRAINT recipes_pkey PRIMARY KEY (id));

您可以使用 3 个参数调用 insert。

INSERT INTO recipes(name, ingredients, directions) VALUES($1, $2, $3)', [req.body.name, req.body.ingredients, 
        req.body.directions]);

2) 使用序列

在你的情况下SERIAL会很好。

更改id列的数据类型并希望它能起作用。