Re: the difference between psql , createdb, dropuser - Mailing list pgsql-admin

Dear  Team,

Iam trying to migrate data oracle to postgres, I am using conversion tool ora2pg, data is converted, but I am dumping data into postgres I will get following error message,

=================================================

[postgres@localhost daily_jagran]$ cat VIEW_output_daily_jagran.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 20.0
-- Copyright 2000-2019 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=prdb.jagran.com;sid=orcl;port=1521

SET client_encoding TO 'UTF8';

SET search_path = daily_jagran;
\set ON_ERROR_STOP ON

CREATE OR REPLACE VIEW category_tree_vw (nodeid, nodename, parentid, nodelevel) AS WITH RECURSIVE cte AS (
SELECT  a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN  a.CAT_TYPE_ID  ELSE a.parent_id END  parent_id,1 as level
 FROM category a
 WHERE parent_id is null
  UNION ALL
select a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN  a.CAT_TYPE_ID  ELSE a.parent_id END  parent_id,(c.level+1)
FROM category a  JOIN cte c ON (c.prior id

UNION
select id, b.name, 0 parent_id, 0
from CATEGORY_TYPE b
where is_active = a.parent_id AND c.1 and is_deleted = a.0
)

) SELECT * FROM cte WHERE is_active = 1 and is_deleted = 0
UNION

select 0,cast('Categories / Sub-categories' as varchar(100)),-1,-1

order by 1
 ;


CREATE OR REPLACE VIEW hp_main_slider (article_id, tags, created_date, language_id, title, bigtitle, summary, slide_path, display_title, thumbnail_path, article_priority, rank) AS SELECT  ARTICLE_ID,TAGS,CREATED_DATE,LANGUAGE_ID,TITLE,BIGTITLE,SUMMARY,SLIDE_PATH,DISPLAY_TITLE,THUMBNAIL_PATH,ARTICLE_PRIORITY,RANK
 FROM (SELECT a.id article_id,
    a.tags,
    TO_CHAR(b.created_date,'dd Mon, yyyy') created_date,
    b.language_id,
    d.title,
    b.title bigtitle,
    b.summary,
    d.path slide_path,
    b.comments display_title,
    d.thumbnail_path,
    a.article_priority,
    rank() over (order by CASE WHEN d.modified_date='' THEN  d.created_date  ELSE d.modified_date END  DESC) rank
  FROM article a,
    article_language b,
    article_media d
  WHERE a.id              = b.article_id
  AND a.id                = d.article_id
  AND a.IS_ACTIVE         = 1
  AND a.is_deleted        = 0
  AND a.deleted_date     IS NULL
  AND b.IS_ACTIVE         = 1
  AND b.is_deleted        = 0
  AND b.deleted_date     IS NULL
  AND d.IS_ACTIVE         = 1
  AND d.is_deleted        = 0
  AND d.DELETED_DATE     IS NULL
  AND d.media_type_id     = 4
  AND (b.expiry_date      > LOCALTIMESTAMP
  OR b.EXPIRY_DATE       IS NULL)
  AND a.ARTICLE_PRIORITY = userenv('client_info')
  order by rank
  ) alias5
WHERE RANK <= 1;

CREATE OR REPLACE VIEW topic_category_tree_vw (nodeid, nodename, parentid, nodelevel) AS WITH RECURSIVE cte AS (
SELECT  a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN  a.CAT_TYPE_ID  ELSE a.parent_id END  parent_id,1 as level
 FROM category a
 WHERE id =1296817087
  UNION ALL
select a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN  a.CAT_TYPE_ID  ELSE a.parent_id END  parent_id,(c.level+1)
FROM category a
 JOIN cte c ON (c.prior id

UNION

select id, b.name, 0 parent_id, 0
from CATEGORY_TYPE b
where is_active = a.parent_id AND c.1 and is_deleted = a.0
)

) SELECT * FROM cte WHERE is_active = 1 and is_deleted = 0
UNION

select 0,cast('Categories / Sub-categories' as varchar(100)),-1,-1

order by 1;



[postgres@localhost daily_jagran]$

Kindly check and revert back, the issue.

Regards,

Mallikarjunarao,

+91-8142923383.