Git Product home page Git Product logo

Comments (3)

phiresky avatar phiresky commented on June 4, 2024

It might be needed to create a temporary type. Example:

instead of

select * from (values (100190, true), (100125, true)) as foo(id, exists)

do this

create type temp as (id int, exists boolean);
select * from (values (23, true), (56, true)) as test;

from ts-typed-sql.

phiresky avatar phiresky commented on June 4, 2024

another example:

query

const existingMeals = await db.exec(
            sql.from(chkTable)
                .innerJoin(menu.meal)
                .on({ _mensa: chkTable._mensa,
                    _line: chkTable._line,
                    _date: chkTable._date.cast(tDate), // TODOHEDIET
                    _mealIndex: chkTable._mealindex//.cast(sql.tInteger)
                })
                .select(menu.meal.$all)

generated sql:

  sql SELECT "menu"."meal".* FROM (VALUES ($1, $2, $3, $4), ($5, $6, $7, $8))
AS "temp"(_mensa, _line, _date, _mealindex) 
JOIN "menu"."meal"
ON "menu"."meal"."_mensa" = "temp"."_mensa"
AND "menu"."meal"."_line" = "temp"."_line"
AND "menu"."meal"."_date" = "temp"."_date"::date
AND "menu"."meal"."_mealIndex" = "temp"."_mealindex"

 [ 'adenauerring',
  'l1',
  2014-12-10T23:00:00.000Z,
  0,
  'adenauerring',
  'l1',
  2014-12-10T23:00:00.000Z,
  1 ]

error:

  name: 'error',
  length: 204,
  severity: 'ERROR',
  code: '42883',
  detail: undefined,
  hint: 'No operator matches the given name and argument type(s). You might need to add explicit type casts.',
  position: '15780',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_oper.c',
  line: '726',
  routine: 'op_error' }

from ts-typed-sql.

phiresky avatar phiresky commented on June 4, 2024

An interesting alternative for inserting / updating an unbounded amount of values in general is

await pool.query("insert into test (param1, param2) values (unnest($1::int[]), unnest($2::text[]))", [[1, 2], ["foo", "bar"]]);

General variant:

select * from unnest('{1,2,3}'::int[], '{a,b,c}'::text[]) as x(a,b);

Which results in a much better prepared query. Might not be applicable in all cases though.

from ts-typed-sql.

Related Issues (20)

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.