SELECT INTO with regexp_replace() doesn't write changes into newly generated table

Question

I have been using this query in Postgres 9.1 in order to remove '' from existing data.
Below is a sample of the data before running regexp_replace():

6''
6''
6''
20''
12''
18''
20''
8''
10''
''
''

Upon running:

select REGEXP_REPLACE(regexp_replace, $$^'*$$, '' , 'g')
from temp_4 order by id;

I receive this clean output:

6
6
6
20
12
18
20
8
10
( ) <- stand-in for " "
( )

however when attempt to then write these results into a table, say using:

select * into table_3 from (select REGEXP_REPLACE(tbl, $$^"''*,*$$, '' , 'g') from temp_2 order by id) as temp_3;

I receive

6
6
6
20
12
18
20
8
10
''
''

Where '' has been removed from all values, except in the case it was the only value present. I have attempted to whitelist using [\w\s*] all other values instead of blacklist with this regex ^'*, but in both instances values of ''. I feel like I'm taking crazy pills.

How do I write my table where I can replace '' with 0 or null values?

Also, is my syntax terribly wrong in my attempts to write this data to other tables? Is there a better way to do this?

Answer 1

Your regexp_replace() statement is invalid. It would work like this:

SELECT regexp_replace(tbl, '('''')$', '' , 'g')
FROM (
 VALUES
  ('6''''')
 ,('6''''')
 ,('6''''')
 ,('20''''')
 ,('12''''')
 ,('18''''')
 ,('20''''')
 ,('8''''')
 ,('10''''')
 ,('''''')
 ,('''''')
) tbl(tbl)

Your SELECT INTO statement is invalid. It would look like this:

SELECT regexp_replace(tbl, '('''')$', '' , 'g')
INTO   temp table_4
FROM   temp_2
ORDER  BY id;

But I would use neither.

SELECT INTO is discouraged. Only supported for historical reasons. Use CREATE TABLE AS instead, which is the SQL standard way. Per documentation:

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

Bold emphasis mine.

For the presented examples, rtrim() is much simpler and faster:

CREATE TEMP TABLE table_4 AS 
SELECT rtrim(tbl, '''')     -- trim all trailing '
FROM   temp_2
ORDER  BY id;