Coder Social home page Coder Social logo

node-pg-format's People

Contributors

tanzim avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar

node-pg-format's Issues

Numeric fields returned as text - format

const query = format('INSERT INTO users (%1$I) VALUES (%2$L)', fields, values)

values is an array containing number types but the format function called with the L parameter returns everything as a string.

i.e. the format function is adding quotes (' ') around numeric types

Space after escape

In PSQL, escaped strings are prefixed with an E. When using

let query1 = format('INSERT INTO survey (%s) VALUES %L ON CONFLICT DO NOTHING RETURNING id', headers, values);

I'm getting

...
VALUES
  (
    E 'ul. Bema 99\\113',
  ) ON CONFLICT DO NOTHING RETURNING id

It would seem that the space between E and the quoted string is a mistake. Reading the source code seems like it is not responsible for inserting this space. Any clue what's going on?

update SQL keyword list

9.4 was released recently. Update the reserved word list with SQL keyword additions for 9.4 such as LATERAL.

Usage for "dynamic" UPDATE queries?

I understand that this function allows me to write "dynamic" insert queries, given an array of column names and one with the corresponding values:

let query = format(INSERT INTO sometable(%I) VALUES(%L);, arrColumn, arrValue);

However, how can I do the same with an UPDATE query where the syntax uses pairs (... SET column1=value1, column2=value2, ...)?

I'm sure there must be a way but didn't find yet. Thanks in advance for your help.

Triple nested array breaks format

Trying to submit multiple rows into postgres where one of the columns is an array. Any nests beyond two are not handled correctly.

For example:

format('%L', ['a', 'b', ['c', ['d', 'e']]])

this returns 'a','b', ('c', 'd','e') instead of 'a','b', ('c', ('d', 'e')).

Is this intended? Is there a way to work around this?

Feature request: positional arguments

Hello,

I really like this project, but there's one thing I'm kind of missing; being able to specify positional arguments.

This is how I would imagine it:

const sql = format(`
    SELECT x
    FROM table_a
    WHERE foo = %1$L

    UNION

    SELECT x
    FROM table_b
    WHERE foo = %1$L`,
    'bar'
);

The optional n$ embedded in the %L (or other placeholders) would refer to the nth given argument.

Is this something you could easily add? If not, I'm willing to give it a shot and send a PR, but you're more familiar with the code naturally.

Thanks!

handle invalid Unicode code point in JSON string

In some cases some Unicode points that are not valid in string appeared in JSON like:

{
    "model" : "Canon EOS 5D Mark II\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000",
    ....
}

Format this JSON directly could cause an error: unsupported Unicode escape sequence thrown by PostgreSQL. Otherwise, if I stringify it manually without the explicit cast ::jsonb, then PostgreSQL will accept the string.

I think we could add a method after the sentence literal = JSON.stringify(value); to sanitize the string, like str.replace(/\\0000/g, '') maybe.

issues when query by record type

Hi, Here is my test code

var pg = require('pg')
pg.format = require('pg-format')
var conString = "...";
pg.connect(conString, function(err, client) {
  var query = `
    SELECT * FROM myTable
    WHERE (id, name) = ANY(ARRAY[%L])
    ;`
  var params = [[1, 'Jack'], [2, 'Tom']]
  console.log(pg.format(query, params))
  client.query(pg.format(query, params), (err, data) => {
    console.log(err)
  })
})

The formatted query is

    SELECT * FROM myTable
    WHERE (id, name) = ANY(ARRAY[('1', 'Jack'), ('2', 'Tom')])

Here are may 2 issues:

  1. int type changed to varchar
  2. it throw “cannot compare dissimilar column types integer and unknown at record column 1”

If change the query to

    SELECT * FROM myTable
    WHERE (id, name) = ANY(ARRAY[('1'::int, 'Jack'::varchar), ('2'::int, 'Tom'::varchar)])

it could get right result.

Thanks

Create multi upsert query

i want to create method to do upsert using library pg-format.

Example, the id is serial field.

// Example data to save:
const data = [
    {id: null, name:'a', desc: 'b'},
    {id: 20, name: 'aa', desc: 'bb'}
]

const sql = 'INSERT INTO table_name(id, a, b)
    VALUES(%L)
    ON CONFLICT (id)
    DO UPDATE SET (a,b) = 
    (EXCLUDED.a, EXCLUDED.b)';

const params = []
for (obj of data) {
    const id = obj.id ?? 'DEFAULT';
    params.push([id, obj.name, obj.desc])
}

format(sql, params); 
// Result: 
//  INSERT INTO table_name(id, a, b)
//  VALUES('DEFAULT', 'a', b), ('20', 'aa', 'bb')
//  ON CONFLICT (id)
//  DO UPDATE SET (a,b) = 
//  (EXCLUDED.a, EXCLUDED.b)

So I got an error because "DEFAULT" is in quotation marks, so it is a string and not the keyword postgres. I try using %I but it also not work. The only way i found was to use %s but it is not esceped so it's unsave.
I also try to make combination of this marks like this VALUES(%s, %L) or VALUES(%I, %L) but i i can have both id as a number and as a null so it's a incorrect way.

Mayby is diffret way to create upsert query or to bind this?

How to implement db functions into query?

Hello and thank you for your work! :)

Please guide on using db functions such as newid() or now() in pg-format queries with arrays. How to call them in values in INSERT INTO?

Highly appreciated! :)
Thank you!

How to insert GeoJSON object with multiple features into PostgreSQL using pg-format

I Have following geojson object with 2 features: My question is: How do I go about inserting this 2 features in a Postgresql Table with pg-format?

{
  "type": "FeatureCollection",
  "crs": {
    "type": "name",
    "properties": {
      "name": "EPSG:4326"
    }
  },
  "features": [
    {
      "type": "Feature",
      "id": 6195898,
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              19.75889040800007,
              -31.466254463
            ],
            [
              19.75889586500006,
              -31.46648844699997
            ],
            [
              19.759105202000057,
              -31.46648545899996
            ],
            [
              19.759100205000035,
              -31.466250463999966
            ],
            [
              19.75889040800007,
              -31.466254463
            ]
          ]
        ]
      },
      "properties": {
        "OBJECTID": 6195898,
        "GID": 2168525,
        "PRCL_KEY": "N065C015000200003929000001",
        "PRCL_TYPE": "E",
        "LSTATUS": "R",
        "WSTATUS": "C",
        "GEOM_AREA": 518.0799,
        "COMMENTS": " ",
        "TAG_X": 19.758913,
        "TAG_Y": -31.46638,
        "TAG_VALUE": "RE/3929",
        "TAG_SIZE": 0.000026,
        "TAG_ANGLE": 0.002022,
        "TAG_JUST": "L",
        "ID": "C01500020000392900000",
        "DATE_STAMP": 1517529600000,
        "DATE_PROCESSED": 1635015671000,
        "SHAPE_Length": 0.000888289181740557,
        "SHAPE_Area": 4.915952156191992e-8,
        "PROVINCE": "NORTHERN CAPE",
        "MAJ_REGION": "CALVINIA",
        "MAJ_CODE": "C0150000",
        "MIN_REGION": "CALVINIA",
        "MIN_CODE": "C0150002",
        "PARCEL_NO": 3929,
        "PORTION": 0,
        "SS_NAME": " ",
        "DSG_NO": " ",
        "SR_NO": " ",
        "SS_NO": " "
      }
    },
    {
      "type": "Feature",
      "id": 6195899,
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              23.068871573000024,
              -28.336274698999947
            ],
            [
              23.069115587000056,
              -28.336471700999937
            ],
            [
              23.069310598000072,
              -28.336282697999923
            ],
            [
              23.069308603000028,
              -28.336240698999973
            ],
            [
              23.069092589000036,
              -28.33606670399996
            ],
            [
              23.068871573000024,
              -28.336274698999947
            ]
          ]
        ]
      },
      "properties": {
        "OBJECTID": 6195899,
        "GID": 2168614,
        "PRCL_KEY": "N085C031000300000638000000",
        "PRCL_TYPE": "E",
        "LSTATUS": "R",
        "WSTATUS": "C",
        "GEOM_AREA": 1013.394276,
        "COMMENTS": " ",
        "TAG_X": 23.069061,
        "TAG_Y": -28.336332,
        "TAG_VALUE": "638",
        "TAG_SIZE": 0.000031,
        "TAG_ANGLE": 6.269028,
        "TAG_JUST": "L",
        "ID": "C03100030000063800000",
        "DATE_STAMP": 1591315200000,
        "DATE_PROCESSED": 1635015671000,
        "SHAPE_Length": 0.0012081009337222054,
        "SHAPE_Area": 9.317254943786774e-8,
        "PROVINCE": "NORTHERN CAPE",
        "MAJ_REGION": "HAY",
        "MAJ_CODE": "C0310000",
        "MIN_REGION": "POSTMASBURG",
        "MIN_CODE": "C0310003",
        "PARCEL_NO": 638,
        "PORTION": 0,
        "SS_NAME": " ",
        "DSG_NO": " ",
        "SR_NO": " ",
        "SS_NO": " "
      }
    }
  ]
}

how to stream multi insert into table

Hi,
first of all great npm package
second how can i insert multiple insert statements into postgres
like for example

pg.connect(connectionString,function(err, client, done) {
if(err) 
  {
      console.log(err)
  }


var basescrp=format('INSERT INTO srt (starttime, endtime,content,showname,season,ep,createdat,updatedat) VALUES %L', data)



var stream = client.query(copyFrom('COPY srt FROM STDIN'));

var rs = new QueryStream(basescrp)

rs.pipe(stream).on('finish', done).on('error', function (err) {
  console.log(err);
  done();
});

})

sadly the above does not work
any pointers, suggestions on how to stream multiple insert into postgres !

Empty arrays get serialised as empty string

This causes a parse error in my sql query. Consider the following example:

CREATE TABLE thing(
  tags string[] NOT NULL
)
const format = require('pg-format')

format('INSERT INTO %I (%I) VALUES(%L)', 'thing', 'tags', [])

Now this get's formatted into the following query:

INSERT INTO thing (tags) VALUES()

Which gives the following error:

error: syntax error at or near ")"

I believe that the correct query output would have been:

INSERT INTO thing (tags) VALUES('{}')

A workaround is to send in the string '{}' to the format function, but that is very ugly and breaks my validation of data since it expects an array of strings...

How to handle arrays as records for INSERTS and UPDATES

How would I best use pg-format to handle the insertion of arrays into columns with an array type.

Imagine a table with a column 'roles' of type text[].

To insert into that table the statement would look something like

INSERT INTO table (name, roles) VALUES ("Josh", ARRAY["role1", "role2"])
or
INSERT INTO table(name, roles) VALUES ("Josh", {"role1", "role2"})

However, when pg-format encounters an array it formats as a literal like this:

INSERT INTO table(name, roles) VALUES ("Josh", ("role1", "role2")) which is invalid. That format ("role1", "role2") works great for IN statements so clearly is the correct handling for arrays in some circumstances.

Recommendations for UPDATES and INSERTS where the value being written is an Array?

Bulk insert with JSON column type

Hi,

I have a script that uses a bulk insert using a t set.

Basically, what we do is loop over an excel spreadsheet, create an array of all the object we wish to insert and then pass it over to pgformt, if there is no entry in the field we pass over an empty array so the variable is set to [] if there are no values.

The query is

format(`update attendee_data as t set
                                fname = c.attendee_fname,
                                lname = c.attendee_lname,
                                title = c.attendee_title,
                                email = c.attendee_email,
                                phone = c.attendee_phone,
                                company = c.attendee_company,
                                description = c.attendee_des,
                                links = CAST (c.attendee_links AS JSON),
                                grouplistid = c.attendee_groups,
                                attendeeonly = c.attendee_atonly
                               
                            
                             from (values
                            %L
                             ) as c(attendee_fname,attendee_lname,attendee_title,attendee_email,attendee_phone,attendee_company,attendee_des,attendee_links,attendee_groups,attendee_atonly,attendee_id)
                             where CAST (c.attendee_id AS bigint) = CAST (t.sid AS bigint) AND aid = ${req.session.AccountID} AND cid= ${req.session.cID}`, attendeeUpdate, []));

This normally works great, however one of the columns is of type JSON. When trying to run the above code it throws and error

error: syntax error at or near ","

Is there a way to have the bulk insert work when there is a column that may be an array?

I saw a similar question posted on here but it does not look like there was ever a resolution to it, especially for a bulk insert.

Thanks!

Inserting into jsonb column?

Is there anyway to insert an object into a column of type jsonb? Because I see you do a JSON stringify now on objects.

I want to create 1 update statement that updates multiple rows and created the following:
format("UPDATE stores AS s SET details = t.details FROM (VALUES %L) AS t(placeid, details) WHERE s.placeid = t.placeid", stores)
Details is a column of type jsonb.
The stores array in Javascript is a double array with the first column (placeid) being a string and the second column (details) is JSON.

Build query using pg-format

Hi

Is it possible to construct my query dynamically depending upon what parameters are passed through, so if I pass this Object to the below method

QueryObject {
  day: 'tomorrow',
  leagueName: 'England Premier League',
  homeTeam:
    { columnOne: 'column_1',
      columnOneValue: '80' },
 awayTeam:
   { columnOne: 'column_2',
     columnOneValue: '100' } 
}

function buildConditions(queryObject) {
  const columns = [];
  const conditions = [];
  const values = [];

  if (typeof queryObject.homeTeam.columnOne !== 'undefined') {
    columns.push(queryObject.homeTeam.columnOne);
    conditions.push('%I = $1');
    values.push(queryObject.homeTeam.columnOneValue);
  }

  if (typeof queryObject.awayTeam.columnOne !== 'undefined') {
    columns.push(queryObject.awayTeam.columnOne);
    conditions.push('%I = $2');
    values.push(queryObject.awayTeam.columnOneValue);
  }

  return {
    where: conditions.length ? conditions.join(' AND ') : '1',
    values,
    columns,
  };
}

It seems like I am pretty close but i get the error Error: too few arguments

const conditions = buildConditions(queryObject);
console.log(conditions);
// { where: '%I = $1 AND %I = $2, values: [ '100', '100' ], columns: [ 'column_1', 'column_2' ] }
const string = 'SELECT * FROM fixtures WHERE ' + conditions.where + ', ' + conditions.columns.join(', ');
console.log(string)
// SELECT * FROM fixtures WHERE %I = $1 AND %I = $2, column_1, column_2
sql = format(string);

Seems I get the error when passing the string into format

What I don't understand at this point is if I was to construct the query as below, it works

sql = format("SELECT * FROM fixtures WHERE %I = $1 AND %I = $2", queryObject.homeTeam.columnOne, queryObject.awayTeam.columnOne);

Deno Port

Now that demo is fast approaching v1.0, I was looking to see if the modules I use will work on deno.

The pg-format module is not natively supported in deno because it used the CJS require and module.exports statements.

Is there a chance of either moving pg-format to the now supported ESM format, or building an ESM version?

I was thinking about forking this project to migrate it to import/export however it would be better to keep it here and add the support for ESM directly.

How to implement "On conflict" with arrays?

Greetings! :)

How can I implement parameter from nested array in query in "on conflict" clause?

For example:

let arr = [ [1,2], [5,6] ]

pgformat('insert into sample_table (foo,bar) values %L on conflict (foo,bar) do update set foo=???, bar=??? ; ', arr)

Thanks beforehands

Insert multiple records in multiple tables.

Hi,

We can save multiple records into one table using pg-format. like
const roleQuery = format('INSERT INTO user_roles (user_id, practice_id, role_id) VALUES %L;',[practiceArray]);

But what if we need to add multiple records in multiple tables? I used following login but it didn't work. it's returning 'Error: too few arguments'

const roleQuery = format( 'with new_practice as(INSERT INTO gp_checklist.user_practices (user_id, practice_id) VALUES %L) INSERT INTO user_roles (user_id, practice_id, role_id) VALUES %L;', [practiceArray, practiceRoleArray]);

Anyone? who has done this kind of work?

dynamicRequire Error when bundling with rollup

The following code in index.js causes files built using rollup to throw error during runtime.
(Error: Could not dynamically require "/build/reserved.js". ).

// index.js
var reservedMap = require(__dirname + '/reserved.js'); 

For now I've used dynamicRequireTargets with @rollup/plugin-commonjs plugin to resolve this issue.

plugins:[...,
commonjs({
      dynamicRequireTargets: ["node_modules/pg-format/lib/*.js"],
    })
],

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.