0

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?

3
  • SELECT regexp_replace($$6''$$, $$^'*$$, '' , 'g'); returns 6''. Could you please post a set of consistent examples? Also, remove the double quotes if they are not part of the real data. Commented Mar 28, 2014 at 16:43
  • I have edited the data to more appropriately fit. What do you mean by more consistent examples? Also Yes my issue is that select regexp_replace($$6''$$, $$^'*$$, '','g'); returns 6'', when I specifically dilineate '' with the regex of ^'*. I had tested my regex with regexpal.com in order to ensure that my string was correctly matchin, hence my confusion as to why 6'' is cleaned appropriately but '' by itself is not recognized.
    – Mansquid
    Commented Mar 28, 2014 at 17:12
  • In other words, your regexp_replace() removes quotes from the start of a line, so at least one of the example data, query and output does not match the others. Commented Apr 1, 2014 at 9:10

1 Answer 1

2

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;
6
  • 1
    select into .. from .. to create a new table is not defined in the SQL standard. The select into syntax in the standard is only defined for to read a single row into one or more host variables (e.g. in PL/pgSQL). To create a new table based on a select, the SQL standard only defines create table .. as ...
    – user1822
    Commented Apr 1, 2014 at 6:08
  • Why not use dollar-quotation? Commented Apr 1, 2014 at 9:10
  • @a_horse_with_no_name: Thanks, I had the reason backwards. Fixed. Commented Apr 1, 2014 at 13:52
  • @dezso: No particular reason. I tend to use the shortest form. In this case either form is equally long. Commented Apr 1, 2014 at 13:53
  • @ErwinBrandstetter I always get confused by these multiplicated qutes so I always use dollar quoting in similar cases. I simply find it more readable. Commented Apr 1, 2014 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.