0

I just run this query

SELECT row_to_json(tt) FROM (
WITH first_admission_time AS
(
  SELECT
      p.subject_id, p.dob, p.gender
      , MIN (a.admittime) AS first_admittime
      , MIN( ROUND( (cast(admittime as date) - cast(dob as date)) / 365.242,2) )
          AS first_admit_age
  FROM patients p
  INNER JOIN admissions a
  ON p.subject_id = a.subject_id
  GROUP BY p.subject_id, p.dob, p.gender
  ORDER BY p.subject_id
)
, age as
(
  SELECT
      subject_id, dob, gender
      , first_admittime, first_admit_age
      , CASE
          -- all ages > 89 in the database were replaced with 300
          -- we check using > 100 as a conservative threshold to ensure we capture all these patients
          WHEN first_admit_age > 100
              then '>89'
          WHEN first_admit_age >= 14
              THEN 'adult'
          WHEN first_admit_age <= 1
              THEN 'neonate'
          ELSE 'middle'
          END AS age_group
  FROM first_admission_time
)
select age_group, gender   , count(subject_id) as NumberOfPatients from age group by age_group, gender ) tt;

she works fine, and this is the output:

                          row_to_json                          
--------------------------------------------------------------  
{"age_group":"adult","gender":"F","numberofpatients":15476} 
{"age_group":"neonate","gender":"M","numberofpatients":4245} 
{"age_group":">89","gender":"F","numberofpatients":1294} 
{"age_group":"neonate","gender":"F","numberofpatients":3629} 
{"age_group":"adult","gender":"M","numberofpatients":21179} 
{"age_group":">89","gender":"M","numberofpatients":697} 
(6 rows)

after that I just try to export the result to json format by tapping :

\copy (
SELECT row_to_json(tt) FROM (
WITH first_admission_time AS
(
  SELECT
      p.subject_id, p.dob, p.gender
      , MIN (a.admittime) AS first_admittime
      , MIN( ROUND( (cast(admittime as date) - cast(dob as date)) / 365.242,2) )
          AS first_admit_age
  FROM patients p
  INNER JOIN admissions a
  ON p.subject_id = a.subject_id
  GROUP BY p.subject_id, p.dob, p.gender
  ORDER BY p.subject_id
)
, age as
(
  SELECT
      subject_id, dob, gender
      , first_admittime, first_admit_age
      , CASE
          -- all ages > 89 in the database were replaced with 300
          -- we check using > 100 as a conservative threshold to ensure we capture all these patients
          WHEN first_admit_age > 100
              then '>89'
          WHEN first_admit_age >= 14
              THEN 'adult'
          WHEN first_admit_age <= 1
              THEN 'neonate'
          ELSE 'middle'
          END AS age_group
  FROM first_admission_time
)
select age_group, gender   , count(subject_id) as NumberOfPatients from age group by age_group, gender ) tt
) TO '/media/mobelite/0e5603b2-b1ad-4662-9869-8d0873b65f80/testJson/PatientAgeAndMortality.json';

I just add copy\ ( .....) TO '/media/mobelite/0e5603b2-b1ad-4662-9869-8d0873b65f80/testJson/PatientAgeAndMortality.json';

this is the error that I found :

ERROR: syntax error at end of input

LINE 1: ...nts from age group by age_group, gender ) test ) TO STDOUT;

The json file is created but empty PS: I just run this command which also save result in a json file and she works fine :

\copy (  SELECT row_to_json(test) FROM (SELECT gender, COUNT(*) FROM patients GROUP BY gender ) test) TO '/media/mobelite/0e5603b2-b1ad-4662-9869-8d0873b65f80/testJson/test.json';

I don't understand the problem

1

1 Answer 1

0

To fix this error, we must put all the query in a single line.

Thank you @IliaMaskov for the reference Postgres: \copy syntax error in .sql file .

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.