postgresql,sql-update,auto-increment , PostgreSQL increment serial column on update
PostgreSQL increment serial column on update
Question:
Tag: postgresql,sql-update,auto-increment
I am trying to increment a serial column after an update. I have written a trigger function to help.
CREATE FUNCTION "public"."update_transaction_id" () RETURNS trigger AS
$BODY$
DECLARE
curr_id integer;
BEGIN
curr_id = nextval(pg_get_serial_sequence('current_table', 'transaction_id'));
NEW.transaction_id = curr_id;
PERFORM SETVAL((SELECT pg_get_serial_sequence('current_table', 'transaction_id')), curr_id + 1, false);
RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TRIGGER "trg_update_transaction_id" AFTER UPDATE
ON "current_table" FOR EACH ROW
EXECUTE PROCEDURE "public"."update_transaction_id"();
So the column is transaction_id in the table current_table. The query executes fine and the my update function does run through this trigger. However, the transaction_id column remains the same value. Is there something wrong with this procedure?
Answer:
There are three errors in your code. First, you use the equality operator = instead of the assignment operator :=. Second, you should not SETVAL the sequence; it knows perfectly well how to maintain itself. Third, this function should be called by a BEFORE UPDATE trigger or the assignment to transaction_id will not persist.
So why not simply:
CREATE FUNCTION update_transaction_id() RETURNS trigger AS $BODY$
BEGIN
NEW.transaction_id := nextval('seq_name');
RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql';
You can trivially retrieve the immutable name of the sequence from the table definition, so plug that in straightaway.
Related:
c++,sql,database,postgresql,odbc
In postgreSQL we have option to import the file into database using lo_import method, which returns a OID using that we can export the file from database to the filesystem. eg: describe test table name text, file_id oid insert into test values('arul_test',lo_import('/home/arul/test.txt')); prepared statement would be insert into test values(?,?);...
sql,postgresql,postgresql-9.4,jsonb,set-returning-functions
I am (still) new to postgresql and jsonb. I am trying to select some records from a subquery and am stuck. My data column looks like this (jsonb): {"people": [{"age": "50", "name": "Bob"}], "another_key": "no"} {"people": [{"age": "73", "name": "Bob"}], "another_key": "yes"} And here is my query. I want to...
sql,postgresql,if-statement
I need to implement a conditional SELECT statement based on the result of another statement. I don't want to create a function for this purpose, but simply using conditional SELECT. My unsuccessful approach looks as below: DO $do$ BEGIN IF SELECT count(*) FROM table1 < 1 THEN SELECT * FROM...
sql,oracle11g,sql-update
I need to update all gmail.com email id in the table emails to yahoo.com. I have this below query which doesn't works. Can someone help? update emails set email = (select CONCAT(email, '@yahoo.com ') "email" from emails ) where email like '%@gmail.com%' ...
sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...
postgresql,constraints
I have a table definition in Postgres. I would like to add a constraint to a column that is of Character datatype to have only 3 allowed values: CREATE TABLE my_table ( id character varying(255) NOT NULL, uid character varying(255) NOT NULL, my_text text NOT NULL, is_enabled boolean NOT NULL...
postgresql,replication,postgresql-9.4
I have replication slot which I want to delete but when I do delete I got an error that I can't delete from view. Any ideas? postgres=# SELECT * FROM pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn --------------+--------------+-----------+--------+----------+--------+------+--------------+-------------...
postgresql,postgresql-9.3
I would like to optimize the following query in postgres SELECT(MIN("products"."shipping") AS minimal FROM "products" WHERE "products"."tag_id" IN (?) with an index like CREATE INDEX my_index ON products (tag_id, shipping DESC); Unfortunately this one is only used when it's just one tag. Almost alwayst it is queried for a handful...
java,hibernate,postgresql,jpa
I am having a rather odd problem. I have native query which runs perfectly when executed on sql server: SELECT date_time, GREATEST(sum(count_up - count_down) OVER (PARTITION BY date_trunc('day', result.date_time) ORDER BY date_time),0) AS cum_amt FROM peoplecounting.result order BY date_time; However, using this query as native query in JPA results in...
sql-server,tsql,sql-update,compare
I have 2 tables and want to compare them and modify tableA (set NameMod = 1) if it has different rows. To compare tables I use: select Id, Name from tableB except select Id, Name from tableA And then I want to modify tableA: update tableA Set NameMod = 1...
postgresql
I have a syntax error in this query: CREATE TABLE test (LIKE original_table INCLUDING INDEXES); ERROR : syntax error at or near "INDEXES" I am using PostgreSQL 8.1 version. Where is the issue in my query?...
sql,postgresql,group-by
I want group my data by createdAt but it is not working I don't know why... Only group by id which is useless for me. This is working: SELECT "id", "createdAt", "updatedAt" FROM "tables" AS "Table" WHERE "Table"."createdAt" BETWEEN '2014-04-21 20:46:25.938-04' AND '2015-04-21 20:46:25.938-04' GROUP BY "id"; This is what...
postgresql
I've been using mysql for close to 10 years, but have recently jumped into a project that's exposed me to postgres for the first time. Most of the differences aren't a bit deal, but I have been running into some small issues along the way that are throwing me off....
java,sql,postgresql,exception
I'm used to check if the user try to store a valid data with cathcing the ConsratinViolationException, like that: try { //persisitng to a db } catch (ConstraintViolationException e){ //Print message } I'm using PostgreSQL and now I'm under the isssue that the persisitng can violate more than one different...
postgresql,datetime,translation,intervals,postgresql-8.4
There is maybe a way to translate automatically interval as it's shown by postgresql (e.g. "1330 days 10:54:54.266684") to other languages? For instance using locales, or other settings or I have to use replace, e.g. regexp_replace((now()-t.another_date)::text, 'day','dzień')?
postgresql,docker
I'm using the the postgres official image https://registry.hub.docker.com/_/postgres/. And now I'm trying to customize its configuration. For this purpose the command sed is used, e.g. to change the max_connections: sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" /var/lib/postgresql/data/postgresql.conf I tried two methods to apply this configuration. The first is by adding...
java,postgresql
I create a database that contains 4 column (ID_PRODUIT,NOM_PRODUIT,QUANTITE, PRIX).I tried to make a methods that allow me to serach Product(my class)using a string key,however, the query failed to identify the "NOM_PRODUIT" column and it turn "NOM_PRODUIT" to "nom_produit" in the messsage error. Connection conn = DriverManager.getConnection(url, user, passwd); PreparedStatement...
postgresql
I would like to determine the number of days that an account has been open. Ideally, I would like to compare the return value to an integer(days). i.e I would like to see if age(open_date) > 14 If anyone has any better ideas... Thanks...
sql,postgresql
I have to do an aggregation on certain measures, but i also have to say how much distinct values i have for a dimension, which i use for aggregation. I can solve the problem using two sub-selects, but performance is not the best. Is there any way to solve this...
sql,postgresql,shell,hadoop,sqoop
I am trying to use Sqoop to export data from HDFS into Postgresql. However, I receive an error partially through the export that it can't parse the input. I manually went into the file I was exporting and saw that this row had two columns missing. I have tried a...
postgresql,triggers,plpgsql
I'm writing a AFTER UPDATE trigger in postgresql. Actualy I need to get at least one row after update in STATEMENT LEVEL trigger, but there is no OLD or NEW variable there. In FOR EACH ROW trigger I didn't manage to find a sort of batch_last param. But to do...
sql,postgresql,greatest-n-per-group,window-functions,gaps-and-islands
I have data like this: table1 _____________ id way time 1 1 00:01 2 1 00:02 3 2 00:03 4 2 00:04 5 2 00:05 6 3 00:06 7 3 00:07 8 1 00:08 9 1 00:09 I would like to know in which time interval I was on which...
sql,postgresql,sum,aggregate-functions,subtract
I have two unrelated tables: contribution(id,amount, create_at, user_id) solicitude(id, amount, create_at, status_id, type_id, user_id) I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative. How can I do this? Function or query? I tried...
sql,postgresql,select,join
I have a PostgreSQL database, with only SELECT permissions. In this DB there are two tables with the same structure (the same columns). I need to write several query in each table and join the results. There is a way for writing a query like this one? SELECT field1, field2,...
ruby-on-rails,postgresql,activerecord,error-handling
I have the next code to save: Transaction.create(:status => params[:st], :transaction_id => params[:tx], :purchased_at => Time.now).save! But how can I redirect to main root page if this ActiveRecord::RecordNotUnique error appears? Can I catch this error?...
postgresql
In SQL systems other than Postgres, such as MySQL for instance, prepared statements can use question marks ? as a placeholder for data in prepared statements. INSERT INTO foo (id, name) VALUES (?, ?), (?, ?); However, in Postgres the only available placeholders seem to be the numbered placeholders, so...
mysql,sql,sql-update,sql-insert
I have data in this manner: ID SUB Marks 1 English 25 2 Maths 22 3 Science 15 4 English 16 How would I sum the "marks" value with a fixed percentage and give total marks of each ID at once? For example, if I have a fixed percentage of...
postgresql
In PostgreSQL, I'm trying to create a trigger that passes an argument to a function, but it appears that this operation looks for a function whose signature has zero arguments: ERROR: function create_rec_if_needed() does not exist: CREATE TRIGGER after_update_winks AFTER UPDATE ON winks FOR EACH ROW WHEN (NEW.counter > 3)...
ruby-on-rails,ruby,postgresql,ruby-on-rails-4,activerecord
Each question has an array of tags. schema.rb: create_table "questions", force: true do |t| t.text "tags", default: [], array: true How to atomically append to tags? How to prevent dups within the array? I tried question.update_attribute tags: tags << :ruby, but this doesn't work. Rails 4.17 and Postgres. EDIT: This...
sql,postgresql,order,condition
i have the this SQL query: SELECT DISTINCT category FROM merchant ORDER BY category ASC that gives this output: accommodation education food general health money shopping sport transport How to put the row that contains "general" at the start (or the end) of the result?...
postgresql,laravel,laravel-5,laravel-migrations
According to this answer, I have to run a raw query if I want to update an enum in MySQL. But with PostgreSQL, I can't use this query, and enum type for PostgreSQL in Laravel seems strange. Is there any way to update enum in a migration for postgreSQL ?...
sql-server-2008,sql-update
Is it possible to do an update query like this? It may not be, just a thought I had as to possibly a solution to my predicament of terrible data-structure. What I am trying to accomplish is: To update the table prodinformation with a count where the entrytype exists in...
c++,postgresql,linker-error,libpqxx
I'm trying to connect C++ to Postgres. I recently installed libpqxx with Homebrew as follows: brew install libpqxx Then I made it with: make DatabaseTest on the file DatabaseTest.cpp. I ran the following simple program and got a strange error that I'm not sure what to do with... #include <iostream>...
postgresql,plpgsql
I'm working on some code that talks to a PostgreSQL server for the first time (my experience is with SQL Server) and I need help figuring something out. I've always tried to avoid "SELECT * ..." from code, but I can't figure out what the alternative is when calling a...
sql,postgresql
I have a table named Cars and a function named PartsPerCar there is no direct link between carid and partid. the link is though 3 tables in the middle. I do have a function called PartsPerCar which gets carid (it goves via the tables in the middle) and give back...
sql,oracle,sql-update
I have SYSTEM_SQL_CHECK table in which i have saved sql in CHECK_SQL column. This column is Varchar data type. Now i want to update particular sql.I have written below update sql query but it gives an error SQL Error: ORA-00933: SQL command not properly ended. I also tried to query...
sql,postgresql
Inside a larger query, I have to COUNT a variable, then if it is larger than 1, have the count as a string otherwise an empty string: CASE COUNT(measurement.id) > 1 THEN to_char(COUNT(measurement.id),' 999') ELSE '' I'm afraid this is slow because I use COUNT() twice. Is there a better...
sql,postgresql
I have next table: COLUMN_NAME DATA_TYPE PK NULLABLE inc_id bigint YES NO dt_cr timestamp NO NO email varchar(255) NO YES email column is not unique, so I should use GROUP BY email. Question: How can I get from this table minimal date, next row after minimal date and email related...
mysql,sql,sql-update
I would like to update a part of string in a particular column of my database. I have cm2 in the particular column of my database and would like to update it to cm2. My sql query to find the cm2 is: SELECT * FROM `question_table` WHERE `option` LIKE '%cm2%'...
sql,postgresql
I have a query that gives: itemid deadlineneeded delievrydate quantity 200 15/07/15 14/07/15 5 200 15/07/15 14/07/15 10 200 15/07/15 13/07/15 25 201 15/07/15 14/07/15 30 200 14/07/15 10/07/15 3 201 15/07/15 15/07/15 100 It gives the information from multiple tables. Basically it means When items arrive to warehouse (delievrydate)...
postgresql
In my app I need to produce a data output (to be then converted to JSON) for the data in a postgres database (which is actually temperature data gathered by sensors). The data is stored in device_history_log as follows (for data between 11am and noon): row_id;deviceid;sensor_value_raw;last_update 104401;20865735;21.56;"2015-06-10 11:00:14" 104432;493417852;23.9;"2015-06-10 11:00:58"...
sql-server,sql-update
I have a table called acc1152 with the field accno. depending on what the value of this field is, i need to replace it with a new value. these are the values i need to update old value new value 7007 4007 7008 4008 4008 7 7009 4009 7011 4011...
postgresql
Hi I am trying to use a mathematical function on each row in postgresql. But It gives me a error. My Query: Select stock_inventory_line.product_code AS Sku, COUNT(sale_order_line.name) AS Qty_Sold, stock_inventory_line.product_qty AS Current_Qty, (stock_inventory_line.product_qty / Qty_Sold) AS NOM From sale_order_line, product_product, product_template, product_category, stock_inventory_line WHERE sale_order_line.product_id = product_product.id AND product_product.product_tmpl_id =...
java,spring,hibernate,postgresql
What I am currently doing in my application.properties file is: spring.datasource.url=jdbc:postgresql://localhost:5432/myDB?currentSchema=mySchema Isn't there another property for this? Since it looks hacky and according to a post (still finding the link sorry, will update later), it is only applicable to PostgreSQL 9.4....
postgresql,debian,orafce
I'm running the "official" docker container of postgresql in version 9.4. I went inside the running container and installed orafce docker exec -i -t my_postgres bash apt-get install postgresql-9.4-orafce afterwards I've tried to reload and restart the postgresql service, as well as just restarting the whole container, but when I...
java,postgresql,jdbc
I want to update table: id integer NOT NULL, "first" character varying(255), "last" character varying(255), age integer, CONSTRAINT registration_pkey PRIMARY KEY (id) using method: void updateTable(String tableName, String columnName, String value, String columnName2, String value2) { try { String sql = "UPDATE " + tableName + " SET " +...
sql,postgresql,exception,duplicates,upsert
I am running a python script that inserts a large amount of data into a Postgres database, I use a single query to perform multiple row inserts: INSERT INTO table (col1,col2) VALUES ('v1','v2'),('v3','v4') ... etc I was wondering what would happen if it hits a duplicate key for the insert....
postgresql,timestamp,plpgsql
I have the following plpgsql function: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) RETURNS bigint AS $BODY$ DECLARE current_time timestamp with time zone = now(); BEGIN INSERT INTO "TEST"( created) VALUES (current_time) RETURNING id INTO pid; END $BODY$ LANGUAGE plpgsql; select * from test_func(); The above gives an error: column...
postgresql,indexing
Sorry, lots of context before the actual question as we've throughly researched this and I wanted to give you full context. Some context: postgres index-only-scans rely on the visibility map (VM). If a page is not marked as not-fully-visible in the visibility map, postgres fetches that page to ensure the...
postgresql,hex
I want to save hex-string ('A2-5A-47-00-10-00-00-00') into a PostgeSQL database. My column is character varying(30). How can I do this?...