postgresql - Determine next auto_increment value before an INSERT in Postgres -


i in process of switching mariadb postgres , have run small issue. there times when need establish next auto_increment value prior making insert. because insert has impact on few other tables quite messy repair if done post insert itself. in mysql/mariadb easy. did

"select auto_increment   information_schema.tables   table_name = 'users'         , table_schema = database( ) ;";

and used returned value pre-correct other tables prior making actual insert. aware pgsql 1 can use returningwith select,insert , update statements. however, require post-insert correction other tables in turn involve breaking code has been tested , proven work. imagine there way find next auto_increment have been unable find it. amongst other things tried nextval('users_id_seq') did not useful. hopefully, here able help.

to port original mariadb schema on postgres edited sql emitted adminer mariadb version ensure works postgres. involved changing int(11) integer, tinyint(3) small int, varchar character varying etc. auto-increment columns read bit , concluded needed use serial instead. typical sql fed postgres this

create table "users"  (  "id" serial not null,  "bid" integer  not null default 0,  "gid" integer  not null default 0,  "sid" integer  not null default 0,  "s1" character varying(64)not null,  "s2" character varying(64)not null,  "name" character varying(64)not null,  "apik" character varying(128)not null,  "email" character varying(192)not null,  "gsm" character varying(64)not null,  "rights" character varying(64)not null,  "managed" character varying(256)not null default   'm_bephjxalyplyojhxvgwjnlamqxv0knenmcya,,',   "senior" smallint  not null default 0,   "refs" integer  not null default 0,   "verified" smallint  not null default 0,   "vkey" character varying(64)not null,   "lang" smallint  not null default 0,   "leader" integer  not null  );

this sql run adminer works correctly. however, when try adminer export new users table in postgres gives me

create table "public"."users"  (  "id" integer default nextval('users_id_seq') not null,  "bid" integer default 0 not null,

it perhaps possible have gone things incorrectly when porting on auto_increment columns - in case there still time correct error.

as documented in manual serial not "real" data type, it's shortcut column takes default value sequence.

if need generated value in code before inserting, use nextval() use value got in insert statement:

in pl/pgsql following. exact syntax depends on programming language use:

declare   l_userid integer; begin   l_userid := nextval('users_id_seq');   -- value   insert users (id, ...)   values (l_userid, ...); end;

it important never pass value insert statement not generated sequence. postgres not automagically sync sequence values "manually" provided values.


Comments

Post a Comment

Popular posts from this blog

angular - Ionic slides - dynamically add slides before and after -

hi i'm using ngfor create set of 3 slides while starting in middle i'm guaranteed able slide left or right on start. when slide right can simple listen reachedend , push slide array i'm looping. but have problem adding slide beginning. if same above , use e.g. array.unshift() or spread add item beginning, view think it's on position 0 , snaps view new slide. the code below work animates slide change index 1. slide = [0,1,2] //example loop slidechanged(event) { if(this.slides.isbeginning()){ this.slide = [this.slide[0]-1, ...this.slide]; this.slides.update(); this.slides.slideto(1) } } <ion-slides [initialslide]="1" (ionslidedidchange)="slidechanged($event)"> <ion-slide *ngfor="let item of slide"> <h1>slide {{item}}</h1> </ion-slide> </ion-slides> any appreciated! you can using ionslidenextend , ionslideprevend events slides . please
Read more

minify - Minimizing css files -

i want know how css files can minimized. currently, using external sites css minifier compress css files. however, after removing whitespaces, comments, etc, file sizes reduced 10-15%. how frameworks bootstrap manage make minimized versions kilobytes. as example, css is: body { background-color: #ff0; } the reduction goes 28 bytes 22 bytes other css files have lot more characters still smaller this. actually minifying code means it's remove thing code white space characters new line characters comments block delimiters it reduces amount of code has transferred on web , speed site load fastly.but unreadable format.
Read more

Add a dynamic header in angular 2 http provider -

i have http interceptor built via tutorial https://scotch.io/@kashyapmukkamala/using-http-interceptor-with-angular2 the problem need have dynamic header changes automatically once changes 'mode' in app. service able tell interceptor change header, or have interceptor pull header (which string)from service each request. able throwing away patch method, call http.patch('new header string') set header string. call in service controls 'mode' setting. wondering if there way interceptor pull recent value service each request or if can add method http provider via interceptor? i've tried call service in interceptor each request returns undefined. because creating new instance of service or copying value before service fired , loaded/changed data. i don't want pass header string every http provider call because defeats purpose of having interceptor, id able set once changes or have provider fetch service before makes request. tl;dr : need add autom
Read more