Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL

[Denisa Cirstescu]

Hello all,

I am trying to add a new column to a really big table and to define an INDEX 
and a FOREIGN KEY on that new column using the following instructions:

ALTER TABLE Employee ADD COLUMN DepartmentId INTEGER;
CREATE INDEX IDX_Employee_DepartmentId ON Employee(DepartmentId);
ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN 
KEY(DepartmentId) REFERENCES Department(DepartmentId);

The table is huge and it takes a lot of time to add the INDEX and the FOREIGN 
KEY although all values are NULL.
Considering that the new DepartmentId column is NULL for all rows at this 
point, is there a way to make the INDEX and FOREIGN KEY creation run faster?

See below information about the size of the table and also the size of its 
associated Primary Key and Indexes:
  Employee339 GB
  Employee_PKEY  46 GB
  IDX_Employee_JobId 46 GB
  IDX_Employee_IsWFH   46 GB


Thank you,
Denisa Cîrstescu

Regex Replace with 2 conditions

[Denisa Cirstescu]

Hi all,

Is there a way to specify 2 conditions in regexp_replace?
I need an SQL function that eliminates all ASCII characters from 1-255 that are 
not A-Z, a-z, 0-9, and special characters % and _  so something like:

SELECT regexp_replace(p_string, E'[' || CHR(1) || '-' || CHR(255) || 
'&&[^A-Za-z0-9%_]]', '', 'g'));

But this syntax is not really working.

I have written a SQL function that achieves this, but I am not happy with it 
because it is hard to read and maintain:

-- Eliminates all ASCII characters from 1-255 that are not A-z, a-z, 0-9, and 
special characters % and _
-- The computed regex expression that is between E[] is CHR(1)-$&-/:-@[-^`{-ÿ].
CREATE OR REPLACE FUNCTION testFunction(p_string CHARACTER VARYING) RETURNS 
VARCHAR AS $$
   SELECT regexp_replace(p_string, E'[' || CHR(1) || '-' || CHR(36) 
|| CHR(38) || '-' || CHR(47) || CHR(58) || '-' || CHR(64) || CHR(91) || '-' || 
CHR(94) || CHR(96) || CHR(123) || '-' || CHR(255) || ']', '', 'g');
$$ LANGUAGE sql IMMUTABLE;

Please help me figure out how to achieve this.

Thanks a lot,
Denisa Cîrstescu

RE: Regex Replace with 2 conditions

[Denisa Cirstescu]

Francisco,

I've tried the version that you are proposing before posting this question, but 
it is not good as it is removing characters that have ASCII code greater than 
255 and those are characters that I need to keep, such as "ă".

SELECT regexp_replace(p_string, E'[^A-Za-z0-9%_]', '', 'g'));

This is the request that I have: write a function that eliminates all ASCII 
characters from 1-255 that are not A-Z, a-z, 0-9, and special characters % and _

Tom,

I have tried what you suggested with the lookahead and it is working.
It is exactly what I needed. The final version of the function is:

CREATE OR REPLACE FUNCTION testFunction(p_string CHARACTER VARYING) 
RETURNS VARCHAR AS $$ 
SELECT regexp_replace(p_string, E'(?=[' || CHR(1) || '-' || 
CHR(255) || '])[^A-Za-z0-9%_]', '', 'g');
$$ LANGUAGE sql IMMUTABLE;


Thanks a lot,
Denisa Cîrstescu


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: Monday, February 5, 2018 4:43 PM
To: Denisa Cirstescu 
Cc: pgsql-gene...@postgresql.org
Subject: Re: Regex Replace with 2 conditions

Denisa Cirstescu  writes:
> Is there a way to specify 2 conditions in regexp_replace?
> I need an SQL function that eliminates all ASCII characters from 1-255 that 
> are not A-Z, a-z, 0-9, and special characters % and _  so something like:
> SELECT regexp_replace(p_string, E'[' || CHR(1) || '-' || CHR(255) || 
> '&&[^A-Za-z0-9%_]]', '', 'g')); But this syntax is not really working.

Nope, because there's no && operator in regexes.

But I think you could get what you want by using lookahead or lookbehind to 
combine additional condition(s) with a basic character-class pattern.
Something like

(?=[\001-\377])[^A-Za-z0-9%_]

regards, tom lane