CODEWITHSUNDEEP
postgresql
precose
precose

Reputation: 614

Prevent trailing spaces during insert?

I have this INSERT statement and there seems to be trailing spaces at the end of the acct_desc fields. I'd like to know how to prevent trailing spaces from occurring during my insert statement.

INSERT INTO dwh.attribution_summary
SELECT d.adic,
       d.ucic,
       b.acct_type_desc as acct_desc,
       a.begin_mo_balance as opening_balance,
       c.date,
      'fic' as userid
FROM  fic.dim_members d 
JOIN  fic.fact_deposits a ON d.ucic = a.ucic 
JOIN  fic.dim_date c ON a.date_id = c.date_id 
JOIN  fic.dim_acct_type b ON a.acct_type_id = b.acct_type_id
WHERE c.date::timestamp = current_date - INTERVAL '1 days';

Upvotes: 4

Views: 5329

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

Use the PostgreSQL trim() function. There is trim(), rtrim() and ltrim().
To trim trailing spaces:

...
rtrim(b.acct_type_desc) as acct_desc,
...

If acct_type_desc is not of type text or varchar, cast it to text first:

...
rtrim(b.acct_type_desc::text) as acct_desc,
...

If acct_type_desc is of type char(n), casting it to text removes trailing spaces automatically, no trim() necessary.

Upvotes: 6

Andre Silva
Andre Silva

Reputation: 4928

Besides what others have said, add a CHECK CONSTRAINT to that column, so if one forgets to pass the rtrim() function inside the INSERT statement, the check constraint won't.

For example, check trailing spaces (in the end) of string:

ALTER TABLE dwh.attribution_summary 
ADD CONSTRAINT tcc_attribution_summary_trim
CHECK (rtrim(acct_type_desc) = acct_type_desc);

Another example, check for leading and trailing spaces, and consecutive white spaces in string middle):

ALTER TABLE dwh.attribution_summary 
ADD CONSTRAINT tcc_attribution_summary_whitespace
CHECK (btrim(regexp_replace(acct_type_desc, '\s+'::text, ' '::text, 'g'::text)) = acct_type_desc);

Upvotes: 3

Jonathan Leffler
Jonathan Leffler

Reputation: 754490

What is the type of acct_desc?

  • If it is CHAR(n), then the DBMS has no choice but to add spaces at the end; the SQL Standard requires that.
  • If it is VARCHAR(n), then the DBMS won't add spaces at the end.
  • If PostgresSQL supported them, the national variants of the types (NCHAR, NVARCHAR) would behave the same as the corresponding non-national variant does.

Upvotes: 2

Related Questions