Postgres insert or update command

There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the FROM clause. Which technique is more appropriate depends on the specific circumstances. The new post-update values of the table's columns are used.

See Section 7. The name optionally schema-qualified of the table to update. If ONLY is specified before the table name, matching rows are updated in the named table only.

If ONLY is not specified, matching rows are also updated in any tables inheriting from the named table. A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. The name of a column in table. The column name can be qualified with a subfield name or array subscript, if needed. An expression to assign to the column.

The expression can use the old values of this and other columns in the table. It does not matter if that value is 1,2, or Add a comment. Active Oldest Votes. Improve this answer. Jim Jones Jim Jones I'm not sure whether it works here because of you inserting the id directly or not but in my case, I use DEFAULT to do it, it skipped a lot of ids and btw, I've added the query I used in the question, please look on that and let me know whether I'm doing something wrong — Awesome Stickz.

AwesomeStickz I just added a comment to your message. Alright, thanks for letting me know about it, I just worried that it may end up reaching max of bigserial too but it's just really really hard to do that so it's not a problem anymore — Awesome Stickz. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Helping communities build their own LTE networks.

Any indexes that satisfy the predicate which need not actually be partial indexes can be inferred. Explicitly specifies an arbiter constraint by name, rather than inferring a constraint or index.

An expression that returns a value of type boolean. Note that condition is evaluated last, after a conflict has been identified as a candidate to update. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises. Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint.

The count is the number of rows inserted or updated. The single row must have been inserted rather than updated. Otherwise oid is zero.

In this example, the len column is omitted and therefore it will have the default value:. Increment the sales count of the salesperson who manages the account for Acme Corporation, and record the whole updated row along with current time in a log table:.

Insert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in the did column. Note that the special excluded table is used to reference values originally proposed for insertion:.

Insert a distributor, or do nothing for rows proposed for insertion when an existing, excluded row a row with a matching constrained column or columns after before row insert triggers fire exists.

Example assumes a unique index has been defined that constrains values appearing in the did column:.