PostgreSQL version of pivoted-gcs

[harvineet]

The script concepts/pivoted/pivoted-gcs.sql uses REGEXP_CONTAINS which is not supported by PostgreSQL.
I tried LIKE and ILIKE but got 0 rows.

Could you please suggest an alternative to using REGEXP_CONTAINS?

[tnaumann]

What about COUNT(REGEXP_MATCHES(...)) > 0?

[alistairewj]

You could also use ~ which is functionally the same.

…

On Wed, Mar 25, 2020 at 7:34 PM Tristan Naumann ***@***.***> wrote: What about COUNT(REGEXP_MATCHES(...)) > 0? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#117 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJZE5A63ANYCFMRT7JR5QTRJKIHXANCNFSM4LTFMI3Q> .

[harvineet]

Thanks!
Using ~ works in PostgreSQL. It results in 3451788 rows. The script after adding ~ and commands to create table:

DROP TABLE IF EXISTS pivoted_gcs CASCADE;
CREATE TABLE pivoted_gcs as
with nc as
(
select
  patientunitstayid
  , nursingchartoffset as chartoffset
  , min(case
      when nursingchartcelltypevallabel = 'Glasgow coma score'
       and nursingchartcelltypevalname = 'GCS Total'
       and nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
       and nursingchartvalue not in ('-','.')
          then cast(nursingchartvalue as numeric)
      when nursingchartcelltypevallabel = 'Score (Glasgow Coma Scale)'
       and nursingchartcelltypevalname = 'Value'
       and nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
       and nursingchartvalue not in ('-','.')
          then cast(nursingchartvalue as numeric)
      else null end)
    as gcs
  , min(case
      when nursingchartcelltypevallabel = 'Glasgow coma score'
       and nursingchartcelltypevalname = 'Motor'
       and nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
       and nursingchartvalue not in ('-','.')
          then cast(nursingchartvalue as numeric)
      else null end)
    as gcsmotor
  , min(case
      when nursingchartcelltypevallabel = 'Glasgow coma score'
       and nursingchartcelltypevalname = 'Verbal'
       and nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
       and nursingchartvalue not in ('-','.')
          then cast(nursingchartvalue as numeric)
      else null end)
    as gcsverbal
  , min(case
      when nursingchartcelltypevallabel = 'Glasgow coma score'
       and nursingchartcelltypevalname = 'Eyes'
       and nursingchartvalue ~ '^[-]?[0-9]+[.]?[0-9]*$'
       and nursingchartvalue not in ('-','.')
          then cast(nursingchartvalue as numeric)
      else null end)
    as gcseyes
  from nursecharting
  -- speed up by only looking at a subset of charted data
  where nursingchartcelltypecat in
  (
    'Scores', 'Other Vital Signs and Infusions'
  )
  group by patientunitstayid, nursingchartoffset
)
-- apply some preprocessing to fields
, ncproc AS
(
  select
    patientunitstayid
  , chartoffset
  , case when gcs > 2 and gcs < 16 then gcs else null end as gcs
  , gcsmotor, gcsverbal, gcseyes
  from nc
)
select
  patientunitstayid
  , chartoffset
  , gcs
  , gcsmotor, gcsverbal, gcseyes
FROM ncproc
WHERE gcs IS NOT NULL
OR gcsmotor IS NOT NULL
OR gcsverbal IS NOT NULL
OR gcseyes IS NOT NULL
ORDER BY patientunitstayid;