Обсуждение: the difference between psql , createdb, dropuser

Поиск
Список
Период
Сортировка

the difference between psql , createdb, dropuser

От
Setve
Дата:

Re: the difference between psql , createdb, dropuser

От
"David G. Johnston"
Дата:
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.
 

Re: the difference between psql , createdb, dropuser

От
Jeff Janes
Дата:
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

Re: the difference between psql , createdb, dropuser

От
Peter Eisentraut
Дата:
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



Re: the difference between psql , createdb, dropuser

От
mallikarjun t
Дата:
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]$ psql < VIEW_output_daily_jagran.sql
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]$

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


Re: the difference between psql , createdb, dropuser

От
"David G. Johnston"
Дата:
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.