问题描述
我在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 ...添加列...使用
,但这在两个方面是错误的。 添加列
需 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.
要舍入,请使用。您的问题中没有足够的细节让我确定如何做到;可能是通过 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 UPDATE
ing 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.
这篇关于在临时表中添加新列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!