Can get data from node, but not command line using postgres

Question

I created a table called alpha and a table within alpha named 'event'. I can add a row to the event table, and retrieve all rows from the event table in node. However when I try in the command line I am getting a 'blank' when I select all.

Command line (not seeing any rows):

(master U:1 ✗) happy psql alpha
psql (9.4.4)
Type "help" for help.

alpha=# psql \dt
          List of relations
 Schema | Name  | Type  |   Owner    
--------+-------+-------+------------
 public | event | table | pgOwner
(1 row)

alpha-# select * from event
alpha-#

Node code (shows rows):

let promise = require('bluebird');
let moment = require('moment');

let options = {
  // Initialization Options
  promiseLib: promise
};

let pgp = require('pg-promise')(options);
let connectionString = 'postgres://localhost:5432/alpha';
let db = pgp(connectionString);

// add query functions
function getAllEvents(req, res, next) {
  db.any('select * from event')
    .then(function (data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved ALL events'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

function addEvent(req, res, next) {
  // let date = moment(new Date()).format('MMMM Do YYYY, h:mm A');
  db.none('insert into event(firstName, lastName, eventDate, emotion, answer)' +
      'values(${firstName}, ${lastName}, ${emotion}, ${emotion}, ${answer})',
    req.body)
    .then(function () {
      res.status(200)
        .json({
          status: 'success',
          message: 'Inserted one event'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}


module.exports = {
  getAllEvents: getAllEvents,
  addEvent: addEvent,
};

You need to end the statement with ; in psql: select * from event; — user330315
– user330315, Commented Sep 28, 2016 at 13:44
@a_horse_with_no_name works! can you write it as an answer so I can mark it as correct? — Ron I
– Ron I, Commented Sep 28, 2016 at 14:00

vitaly-t · Accepted Answer · 2016-09-28 14:58:02Z

5

As documented in the manual

At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command.

In other words, you have to end every statement with a ;, in contrast to executing queries via a driver, which only requires ; when you execute more than one query with a single request (you normally wouldn't do it).

edited Sep 28, 2016 at 14:58

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

answered Sep 28, 2016 at 14:09

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

@vitaly-t: your addition is only partly true. e.g. the JDBC driver does allow sending multiple statements delimited with ;

Collectives™ on Stack Overflow

Can get data from node, but not command line using postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related