1
vote
1answer
19 views

Update all columns from another table

I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this: update tableA set * ...
0
votes
1answer
31 views

Postgresql condition on subquery results

This is a new question based on the solution to my previous question I have the following tables: users int (pk) user_id date last_visit int (fk) local_site sites int (pk) site_id site_stats ...
2
votes
1answer
65 views

How to reuse an update trigger for multiple tables in postgresql?

In the database for a new project, I've started the convention of having each table have a last modified timestamp column. To implement this, I wrote a trigger for each table: CREATE TRIGGER ...
2
votes
2answers
131 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
3
votes
2answers
92 views

Insert RECORD into a table in a trigger function

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table ...
1
vote
2answers
97 views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
0
votes
1answer
48 views

Issues with 'plus symbol' output in PSQL on update query

Simple but frustrating issue... I'm trying to do an update command to correct a misspelling with this field: grouper=> select * from master_list WHERE group ~ 'JustAdopted'; group | ...
1
vote
3answers
83 views

How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

I'm working on PostgreSQL and I have a table like this called words_table: I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
1
vote
1answer
78 views

numbering rows consecutively for a number of tables

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to ...
1
vote
1answer
72 views

scenario to update or insert big amount of data

I try to find the best scenario to update or insert 1 million contacts in my postgres database. My actual scenario is: read contacts 1000 per 1000 and try to insert multiple rows with one request ...
1
vote
1answer
82 views

Create constraint for stopping mass update to a table

I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.
5
votes
2answers
1k views

How to optimization database for heavy I/O from updates (software and hardware)

The situation I have a postgresql 9.2 database which is quite heavily updated all the time. The system is hence I/O bound, and I'm currently considering making another upgrade, I just need some ...
1
vote
3answers
328 views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
0
votes
1answer
105 views

String replace using concatenated strings from various columns

I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order. The specification says: ...
5
votes
2answers
3k views

Optimizing bulk update performance in Postgresql

Using PG 9.1 on Ubuntu 12.04. It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = ...
0
votes
2answers
2k views

Use CASE to select columns in UPDATE query?

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so: SELECT CASE WHEN val = 0 THEN column_x WHEN val = 1 THEN column_y ELSE 0 END AS ...
0
votes
1answer
64 views

Comparing two tables for a UUID change and fix it

I have two tables which I'm trying to reconcile the differences of in postgresql. Table A is old and needs updating. Table B is an updated, schema identical version of Table A which I have the data ...
2
votes
0answers
121 views

How to execute a non-table-locking update operation on PostgreSQL? [closed]

Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
3
votes
2answers
553 views

postgreSQL update set only where cast is possible and ignore error?

I have 2 columns in a PostgreSQL table. The mac_address_temp column is for migration from character type to MAC-address type: mac_address | macaddr | mac_address_temp | character ...
2
votes
2answers
2k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor ...
1
vote
4answers
446 views

Performance degradation while updating tables having 10s of millions of records

I want to update tables ( my be 20-30 ) having 10s of millions of records each. The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...