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;
Assuming current Postgres 9.4.
Since you are dealing with just ~ 20 names of products, it's largely irrelevant how you provide those to the query. An index would be a waste of time, Postgres is going to use a sequential scan anyway (judging from what you disclosed in the question).
You can use a temporary table, which lives for the duration of the session, not just transaction like you seem to assume. And you can even create indexes on temp tables, it just wouldn't help in this case.
Be sure to run ANALYZE
on temp tables manually:
Or you can unnest a provided array literal just as well. But don't rely on the outdated trick of unnesting two arrays in the SELECT
list in parallel. This is error prone. Move it to the from clause. I'd suggest:
SELECT
...
LEFT JOIN unnest('{name1,name2,name3}'::text[]) WITH ORDINALITY
product_names (name, type_id) USING (type_id)
...
More:
Assuming the column name type_id
is unambiguous in the tables to the left.
If you don't actually have ascending type_id
starting with 1 like your example suggests, consider the new variant of unnest()
taking multiple array parameters:
SELECT
...
LEFT JOIN unnest('{4,7,9}'::int[], '{name1,name2,name3}'::text[])
product_names (type_id, name) USING (type_id)
...
More:
Best Answer
This replacement can be done with:
From the documentation:
The last argument
'g'
is meant to replace all occurrences, otherwise it would replace only the first found.You may also add a WHERE clause to limit the rows updated to those that match: