Обсуждение: the difference between psql , createdb, dropuser
On Sunday, October 27, 2019, Setve <setve@protonmail.com> wrote:
Its simply an abstraction so one can execute those actions in a shell without including and dealing with raw SQL - thus removing SQL-injection exposure.
David J.
On Sun, Oct 27, 2019 at 4:23 PM Setve <setve@protonmail.com> wrote:
When scripting the initial set up of a system, it is easier to pass the name to one of these programs, then to embed them into the middle of an SQL command properly escaped and quoted. (The names of the programs themselves are ancient history, if starting from scratch they probably begin with "pg_")
Cheers,
Jeff
On 2019-10-28 01:01, Jeff Janes wrote: > On Sun, Oct 27, 2019 at 4:23 PM Setve <setve@protonmail.com > <mailto:setve@protonmail.com>> wrote: > > I can execute the create user or create db .etc... command at a > PostgreSQL interactive terminal so why are these commands > "createuser , createdb , dropdb etc.... separately and what is their > purpose? > <https://www.reddit.com/r/PostgreSQL/comments/dnyr0n/i_can_execute_the_create_user_or_create_db_etc/> > > > When scripting the initial set up of a system, it is easier to pass the > name to one of these programs, then to embed them into the middle of an > SQL command properly escaped and quoted. (The names of the programs > themselves are ancient history, if starting from scratch they probably > begin with "pg_") Also, in the distant past, there was no CREATE USER command and the createuser program inserted directly into pg_shadow. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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,
Password:
SET
SET
ERROR: syntax error at or near "id"
LINE 7: FROM category a JOIN cte c ON (c.prior id
^
=================================================
[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]$
-- 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.
On Mon, Oct 28, 2019 at 6:34 PM Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
On 2019-10-28 01:01, Jeff Janes wrote:
> On Sun, Oct 27, 2019 at 4:23 PM Setve <setve@protonmail.com
> <mailto:setve@protonmail.com>> wrote:
>
> I can execute the create user or create db .etc... command at a
> PostgreSQL interactive terminal so why are these commands
> "createuser , createdb , dropdb etc.... separately and what is their
> purpose?
> <https://www.reddit.com/r/PostgreSQL/comments/dnyr0n/i_can_execute_the_create_user_or_create_db_etc/>
>
>
> When scripting the initial set up of a system, it is easier to pass the
> name to one of these programs, then to embed them into the middle of an
> SQL command properly escaped and quoted. (The names of the programs
> themselves are ancient history, if starting from scratch they probably
> begin with "pg_")
Also, in the distant past, there was no CREATE USER command and the
createuser program inserted directly into pg_shadow.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, Oct 29, 2019 at 5:12 AM mallikarjun t <mallit333@gmail.com> wrote:
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,
Maybe try sending a brand new email with a proper subject line and to an appropriate list (probably ora2pg since the query you says its generating simply isn't valid in PostgreSQL).
David J.