在临时表中添加新列 [英] Adding a new column in a temporary table

问题描述

我在PostgreSQL函数中有一个临时表，我想插入一个新的 VARCHAR 列。它的值应取决于表的另一列，称为金额。

I have a temporary table in a PostgreSQL function and I want to insert a new VARCHAR column. It should have a value that depends on another column of the table, named "amount".

当金额为正时，我希望将列行的值记为贷方，当金额为负时，应将列记为借方。

When the amount is positive I would like the value of the column row to be credit and when the value is negative the column should be debit.

我还有一个要求：我想将金额列的值四舍五入到第二位

I have one more request: I want to round the value of amount column in 2 decimal digits

推荐答案

您要 ALTER TABLE ... ADD列 ，然后执行更新。

我最初说的是 ALTER TABLE ...添加列...使用，但这在两个方面是错误的。 添加列需 DEFAULT 而不是使用-您可以不能一do而就，因为 DEFAULT 表达式和 USING 表达式都不能引用其他列。

I initially said ALTER TABLE ... ADD COLUMN ... USING but that was wrong on two counts. ADD COLUMN takes a DEFAULT not USING - and You can't do it in one pass because neither a DEFAULT expression nor a USING expression may not refer to other columns.

因此，您必须这样做：

 ALTER TABLE tablename ADD COLUMN colname varchar;
 UPDATE tablename SET colname = ( CASE WHEN othercol < 0 THEN 'Credit' ELSE 'Debit' END );

请仔细考虑零应该是借方还是贷方，并调整 CASE 。

Think carefully about whether zero should be 'Debit' or 'Credit' and adjust the CASE accordingly.

要舍入，请使用 round（amount，2） 。您的问题中没有足够的细节让我确定如何做到；可能是通过 UPDATE 使用 UPDATE修改临时表，而表SET量= round（amount，2），但是没有上下文很难知道这是否正确。 该语句不可撤消地丢弃信息，因此只能在数据副本上使用。

For rounding, use round(amount,2). There isn't enough detail in your question for me to be sure how; probably by UPDATEing the temp table with UPDATE thetable SET amount = round(amount,2) but without the context it's hard to know if that's right. That statement irreversibly throws information away so it should only be used on a copy of the data.

这篇关于在临时表中添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！