database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Tutorials 3547 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 3547
PostgreSQL 을 사용하면서 모은 몇가지 Tip 들...
작성자
정재익(advance)
작성일
2001-10-19 09:12
조회수
16,699

첨부파일: pgsql_tip.txt (28,543bytes)

 

1. SQL 문을 이용하여 내가 만든 table(relation)의 field(attribute) 이름과 그

자료형을 알고자 할때...

SELECT pg_attribute.attname, pg_type.typname

FROM pg_class, pg_attribute, pg_type

WHERE pg_class.relname = '%s' AND

pg_attribute.attnum > 0 AND

pg_attribute.attrelid = pg_class.oid AND

pg_attribute.atttypid = pg_type.oid

여기서 '%s'를 자신이 조사하길 원하는 'relation name'을 적어준다.

 

 

2. NULL과 empty string은 같지 않다.

empty string은 길이가 0인 문자열이고, NULL은 값 자체가 없는 것을 의미한다.

그러므로 insert into table_name (name) values ('');

insert into table_name (name) values (NULL);

은 다른 문장이 되며, name의 값은 서로 다르다.

 

 

3. row의 번호가 들어 있는 field가 없을 경우 row의 serial no를 볼수 있는 방법은?

SELECT oid, * FROM tablename;

 

 

4. date 형으로 정의된 필드가 존재할때 7일 전 자료를 보고자 할 경우.

age()라는 함수를 이용한다.

select * from sometable

where date_part( 'epoch' , age( 'now', somedate ) ) > 604800

 

 

5. date 형을 가진 필드에 자료를 넣고자 할때

hjk=> \d control_veh

Table = control_veh

+----------------------------------+----------------------------------+-------+

| Field | Type | Length|

+----------------------------------+----------------------------------+-------+

| paterno | char() | 25 |

...many fields...

| ultpago | char() | 2 |

| fecalta | date | 4 |

+----------------------------------+----------------------------------+-------+

이런 자료 구조를 보이고 있다면...

hjk=> update control_veh set fecalta='01-01-1800' where plact='440JAS';

ERROR: Relation control_veh does not have attribute fecalta

이렇게 자료 입력을 시도하면 에러가 난다.

hjk=> update control_veh set fecalta='01-01-1800'::date where plact='440JAS';

이와 같이 해 주어야 한다. (type cast를 이용)

 

 

6. field 값이 NULL인 field를 찾으려면...

brecard5=> select * from test where name is null;

Field| Value

-- RECORD 0 --

code| 5678

-- RECORD 1 --

code| 8888

(2 rows)

 

 

7. timestamp 형의 자료로 부터 유닉스의 time()과 같은 형의 timedate를 얻으려면

date_part를 이용한다.

webdb=> select date_part('epoch'::datetime,'now'::datetime) as date2;

date2

---------

915556343

(1 row)

 

 

8. 함수 인자로 예약어 (reserved word) 를 굳이 사용해야 하는 경우?

답변:

> CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD

> SELECT fun1(new);

>

> Thing is that backend gives this error.

> ERROR: parser: parse error at or near ")"

Try double quoting "new", since it's a reserved word.

 

 

9. ACL 을 어떻게 알수 있을까요?

1. the information can be found in the system-table-column PG_CLASS.RELACL:

SELECT RELACL

FROM PG_CLASS

WHERE RELNAME = 't_dummy';

The result seems to be an array of granted permissions, so you should

treat it is an array and look for the appropriate user- or group-name.

Hope this helps ...

2.

SELECT relacl FROM pg_class WHERE relname = '[table name]'

The relacl column contains specific information that must then be parsed to

determine the users which have access to a the table and the privileges

granted.

Example:

{"=","dan=arw","group developers=arw"}

The first section means the PUBLIC does not have any privileges on the

table. Each permission (SELECT, INSERT, UPDATE/DELETE, RULE) is signified

by a letter. r = select, a = insert, w = update/delete, R = rule. User

"dan" has SELECT, INSERT, UPDATE/DELETE rights, but not RULE rights. Group

"developers" has the same privileges.

 

 

10. SSL with PostgreSQL 을 이용할 수 있는가?

> I couldn't find any other info about using SSL with PostgreSQL.

http://www.postgresql.org/devel-corner/docs/postgres/ssl-tcp.htm

I seem to recall hearing that the SSL code was broken in 7.0.*. If so,

you may want to try 7.1beta.

 

 

11. PL/Perl 사용법

1) 질문

I'd like to be able to only store the database of usernames and passwrods

here locally as a md5 hash. (in case the black hats come to visit....I'd

like to make life hard for them) Using AuthPG, I should be able to create

a SQL call to postgres....but there is no native md5 hashing function.

In my ideal blue-sky world....the SQL call would like this:

SELECT name FROM Sample_table WHERE ( (userid='12345') AND

(userhashed=md5out('abc')) )

With the sample table looks like this:

Sample_table:

name userid userhashed

fred 12345 900150983cd24fb0d6963f7d28e17f72

I'd get the string 'fred' in name from Sample_table.

 

Idea 1) A call to a shell script. A question was asked back in 1999 if

there was a way to use a shell script in an SQL call.....that person had

no public responses. Moved onto

Idea 2) use PL/Perl to take in the text to be hashed, and output the

hash. Read the docs, looked on the list for more examples......

This perl code works as I'm expecting.

use MD5;

my $mdval = new MD5;

my $result ;

my $out;

$mdval->add('abc');

$result = $mdval->digest();

$out= unpack("H*" , $result );

print $out;

Attempting to xlate to PL/Perl

settle=# create function md5out3(varchar) returns varchar(32) as '

settle'# use MD5;

settle'# my $mdval = new MD5;

settle'# my $result ;

settle'# my $out;

settle'# $mdval->add($_[0]);

settle'# $result = $mdval->digest();

settle'# $out= unpack("H*" , $result );

settle'# return $out;'

settle-# LANGUAGE 'plperl';

CREATE

settle=# select md5out3('fred');

ERROR: creation of function failed : require trapped by operation mask at

(eval 6) line 2.

 

So.......

What did I do wrong WRT PL/Perl? (Let me guess....having perl call perl

modules causes breakage) Should I be trying something different

to get to my desired end goal?

2) 답변1

> Idea 1) A call to a shell script. A question was asked back in 1999 if

> there was a way to use a shell script in an SQL call.....that person had

> no public responses. Moved onto

> Idea 2) use PL/Perl to take in the text to be hashed, and output the

> hash. Read the docs, looked on the list for more examples......

Nice try :) Good idea, however, you should take into account two things:

a) your functions run under "use Safe" and very restricted as far as what

they could do

b) your function is _not_ a package, it is only a sub, and hence cannot

'use' anything.

A thing to try for you is:

a) change plperl, and where it does 'require Safe;' do 'use MD5; require

Safe;', recompile, etc.

b) change plperl and change permit_only(':default') to

permit_only(':default','require')

It MIGHT work. You might have to add more ops that MD5 code uses though...

Good luck :)

3) 답변2

Actually, a thing to consider would be to have a 'untrusted' PL/perl

language in postgres with use Safe disabled, along with a 'trusted' one.

(pluntrustedperl? plunsafeperl?) Same could be done for pltcl...

> Attempting to xlate to PL/Perl

>

> settle=# create function md5out3(varchar) returns varchar(32) as '

> settle'# use MD5;

> settle'# my $mdval = new MD5;

> settle'# my $result ;

> settle'# my $out;

> settle'# $mdval->add($_[0]);

> settle'# $result = $mdval->digest();

> settle'# $out= unpack("H*" , $result );

> settle'# return $out;'

> settle-# LANGUAGE 'plperl';

> CREATE

> settle=# select md5out3('fred');

> ERROR: creation of function failed : require trapped by operation mask at

> (eval 6) line 2.

You can't use external modules ("use", "require") for security reasons.

FWIW, if I were to write an MD5 function then I'd take one of the

implementations floating around (mhash, Kerberos, OpenSSL, RFC) and make a

C function wrapper around it.

Incidentally, someone has already done this for the upcoming 7.1 release,

but since the function call interface has changed the back port won't be

trivial.

 

 

12. 수치 출력값이 3.68009074974387 로 나옵니다. 이것을 소수 둘째자리까지만

출력하고자 합니다. 어떻게 하면 될까요.

1)

cast it to numeric(x,2)

(where x is the total number of digits, and 2 is two decimal places).

template1=# select 3.68009074974387::numeric(3,2);

?column?

----------

3.68

2)

or use round(value,2)

 

template1=# select round(3.68009074974387, 2);

round

-------

3.68

(1 row)

3)

or

test=# select to_char(3.68009074974387, '99.99');

to_char

---------

3.68

(1 row)

 

 

13. 시스템의 안정성 보다는 일단 빠른 속도를 얻고 싶습니다.

postmaster 시작시 -o -F 옵션을 추가해 주도록 합니다.

 

 

14. superuser 로서 postgresql 사용자와 그 권한을 모두 보고자 한다면...

> as a pgsql superuser, how can i list all the users and their rights?

SELECT * FROM pg_user;

 

 

15. PHP 에서 시간 정보를 DBMS 로 저장하고자 한다. PHP 에서 mktime()-unixtime 함수에

해당하는 postgresql 의 자료형이 무엇인가?

play=> select now()::abstime::integer;

?column?

-----------

979337141

(1 row)

play=> select 979337141::integer::abstime;

?column?

------------------------

2001-01-12 17:05:41-05

(1 row)

The "official" way to get from a datetime type to a Unix timestamp is

date_part('epoch', timevalue):

play=> select date_part('epoch', now());

date_part

-----------

979337212

(1 row)

but I don't know of any easy way to go in the other direction except by

casting to abstime.

 

 

16. 사용자 권한 설정을 알고 싶다. 어떻게 하면 되겠는가?

SELECT relacl FROM pg_class WHERE relname = '[table name]'

The relacl column contains specific information that must then be parsed to

determine the users which have access to a the table and the privileges

granted.

Example:

{"=","dan=arw","group developers=arw"}

The first section means the PUBLIC does not have any privileges on the

table. Each permission (SELECT, INSERT, UPDATE/DELETE, RULE) is signified

by a letter. r = select, a = insert, w = update/delete, R = rule. User

"dan" has SELECT, INSERT, UPDATE/DELETE rights, but not RULE rights. Group

"developers" has the same privileges.

For more details --

http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm

 

 

17. varchar 로 정의된 필드를 int 형으로 cast 할수없다. 어떻게 하면 되겠는가?

먼저 text 로 casting 한 후에 다시 int로 casting 하라.

select varchar_field::text::int from table_name;

 

 

18. PostreSQL 의 Maximum Limitation 은 어떻게 되는가?

I updated the maximum number of columns:

Maximum size for a database? unlimited (60GB databases exist)

Maximum size for a table? 64 TB on all operating

systems

Maximum size for a row? unlimited in 7.1 and later

Maximum size for a field? 1GB in 7.1 and later

Maximum number of rows in a table? unlimited

Maximum number of columns in a table? 250-1600 depending on column

types

Maximum number of indexes on a table? unlimited

 

 

19. JDBC 연결이 잘 되지 않습니다. 간단한 예제를 볼수 없는지요?

다음 파일을 참고로 하기 바란다. classpath 에 다음과 같이 jdbc driver 가

추가되어 있어야 한다.

advance@database:~/web/java-test$ echo $CLASSPATH

.:/usr/local/lib/java/postgresql.jar

ex1.jsp

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

<% page info="Example JSP pre-compiled"

import="java.io.*,java.sql.*,java.text.*" %>

Java-Test: hello

<%!

Connection db;

Statement st;

ResultSet rs;

%>

<%

Class.forName("org.postgresql.Driver");

db = DriverManager.getConnection("jdbc:postgresql:databasename", "username", "password");

st = db.createStatement();

rs = st.executeQuery("SELECT * FROM users");

while(rs.next()) {

%>

<%

}

%>

unameemail

<%= rs.getString("uname") %>

<%= rs.getString("email")%>

<%

rs.close();

st.close();

db.close();

%>

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

 

 

20. RedHat 6.2 에서 PostgreSQL 을 이용하고 있습니다. 그런데 " Too many open

files!" 라는 에러를 만났습니다. 어떻게 해결하면 될까요?

Tim>

>> I set to 16384. with about 100 clients this seems to be enough. Best

>> way to set it is in your local rc script (/etc/rc.d/rc3.d/*local)

Tim>

Tim> How do you go about doing this on a RedHat 6.2 system? There is an

Tim> S99local file but nothing in it on setting maximum open files.

I had to do this last week. On RedHat 6.2 put this at the end

of /etc/rc.d/rc.local:

echo 8192 > /proc/sys/fs/file-max

echo 32768 > /proc/sys/fs/inode-max

It increases file handles to 8192, you are supposed to have four times as many

inode handles as file handles so it increases those too.

 

 

21. PLPGSQL 로 프로그램 작성중 다음과 같은 에러를 만났습니다. 무엇이

문제일까요?

----------------

NOTICE: plpgsql: ERROR during compile of add_one

near line 1

"RROR: parse error near "

----------------

PLPGSQL 을 작성히 M$ 에서 이용하는 에디터를 사용하지 마시기 바랍니다.

PLPGSQL 은 \r\n 을 newline 으로 생각하지 않습니다. PLPGSQL 은 \r 을

whitespace 로 여기지 않기 때문입니다.

(물론 이것은 7.1 에서는 바뀌었습니다)

 

 

22. PLPGSQL 내에서 Transaction 을 다시 열수 있는가?

정답은 안된다 이다. PostgreSQL 은 아직까지 nested transaction을 지원하지 않기 때문이다.

 

 

23. JDBC 내에서 nested query 가 가능한가.

import java.sql.*;

import java.util.*;

import java.io.*;

import java.lang.*;

import javax.servlet.*;

import javax.servlet.http.*;

/*

Adds forecast to nominated project.

*/

public class simpleExample extends HttpServlet {

public static String usernamepassword = "postgres";

private boolean debug=true;

public void doGet(HttpServletRequest req, HttpServletResponse res)

throws ServletException,IOException

//doGet > doPost, enables servlet to responde to both types of http calls.

{

doPost(req, res);

}

//

public void doPost(HttpServletRequest req, HttpServletResponse res)

throws ServletException,IOException

{

PrintWriter out = res.getWriter();

res.setContentType("text/html");

Connection m_connection = null;

Statement stmt = null;

ResultSet rs = null;

out.println("servlet simpleExample called.
");

//

try

{ //open data base connection.

out.println("opening connection.
");

m_connection = GetDBConnection(out);

m_connection.setAutoCommit(false);

out.println("connection opened, creating statement.
");

stmt = m_connection.createStatement();

out.println("statement created.
");

//

try

{//

getListPlayers(m_connection, out);

//

stmt.close();

m_connection.commit();

//commit only needed for servlets updating or inserting. not req'd 4 select use anyway

m_connection.close();

}//

catch(Exception ex)

{//

out.println("Error retrieving project information.
"+ex.toString()+"
");

out.println("");

}//

} catch(Exception ex){// out.println("Error connecting to database. Exception information
"+ex.toString()+"
");

out.println("");

}//end try catch

} // end doGet

//methods below.

//

public void getListPlayers(Connection m_connection, PrintWriter out)

{

boolean debug=true;

Statement stmt = null;

String getPersDetails = "select * from simpleperson";

int age = 0;

String fName=null, team = null;

try

{

stmt = m_connection.createStatement();

//

if(debug)out.println("sampleMethod1 executing getPersDetails="+getPersDetails+"
");

ResultSet rs = stmt.executeQuery(getPersDetails);

out.println("

");

out.println("

");

if (rs !=null)

while (rs.next() )

{

age = rs.getInt("age");

fName = rs.getString("fName");

team = rs.getString("team");

out.println("

");

out.println("

");

out.println("

");

out.println("

");

out.println("

");

out.println("

");

out.println("

");

}

out.println("

fNameageteam
"+fName+""+age+"
");

rs.close();

stmt.close();

}

catch (Exception ex)

{

if(debug)out.println("Exception trapped in getListPlayers. "

+ "
ex.toString() = " + ex.toString() + "
");

}//

}

//

public void getListTeams(Connection m_connection, PrintWriter out, String tName)

{

boolean debug=false, noRecords=true;

Statement stmt = null;

String getTeamInfo = "select * from simpleTeam where name <> '"+tName+"'";

int age = 0;

String teamName=null;

try

{

stmt = m_connection.createStatement();

//

if(debug)out.println("getListTeams executing getTeamInfo="+getTeamInfo+"
");

ResultSet rs = stmt.executeQuery(getTeamInfo);

if (rs !=null)

while (rs.next() )

{

teamName = rs.getString("Name");

out.println("");

noRecords = false;

}

else out.println("");

rs.close();

stmt.close();

if(noRecords)out.println("");

else out.println("");

}

catch (Exception ex)

{

if(debug)out.println("Exception trapped in getListPlayers. "

+ "
ex.toString() = " + ex.toString() + "
");

}//

}

//

//

public void getListTeamsWstmt(Statement stmt, PrintWriter out, String tName)

{

boolean debug=false;

String getTeamInfo = "select * from simpleTeam where name <> '"+tName+"'";

int age = 0;

String teamName=null;

ResultSet rs = null;

try

{

//

if(debug)out.println("getListTeams executing getTeamInfo="+getTeamInfo+"
");

rs = stmt.executeQuery(getTeamInfo);

if (rs !=null)

while (rs.next() )

{

teamName = rs.getString("Name");

out.println(""); }

else out.println("");

rs.close();

out.println("");

}

catch (Exception ex)

{

if(debug)out.println("Exception trapped in getListPlayers. "

+ "
ex.toString() = " + ex.toString() + "
");

}//

}

//

public Connection GetDBConnection(PrintWriter out)

throws Exception

/*

Returns database connection, enables easy change to connect to different connections.

*/

{

Connection Parent_connection = null;

//

Properties info = new Properties();

info.put("user",usernamepassword);

info.put("password",usernamepassword);

boolean debug=false;

try

{ //open data base connection.

if(debug)out.println("loading driver.
");

Class.forName("org.postgresql.Driver");

if(debug)out.println("driver loaded, getting connection to database as "+usernamepassword+"
");

Parent_connection = DriverManager.getConnection("jdbc:postgresql:template1","postgres","postgres");

if(debug)out.println("Connected
");

} catch(Exception ex)

{ //

out.println("Error connecting to database. Exception information
"+ex.toString()+"
");

}//

return Parent_connection;

}//end

//

//

}//end ReviewForeCast

 

(script below to generate tables)

--simpleExample.sql

drop table simplePerson;

drop table simpleTeam;

create table simplePerson(

fName Varchar(20) NOT NULL,

age numeric(5),

team Varchar(20)

);

create table simpleTeam(

Name Varchar(20) NOT NULL,

CoachName Varchar(20)

);

 

--now insert some records.

insert into simplePerson (fName, age, team) values('fred', 15, 'red');

insert into simplePerson (fName, age, team) values('tim', 16, 'red');

insert into simplePerson (fName, age, team) values('mike', 14, 'red');

insert into simplePerson (fName, age, team) values('fred', 20, 'blue');

insert into simplePerson (fName, age, team) values('tim', 21, 'blue');

insert into simplePerson (fName, age, team) values('mike', 22, 'blue');

insert into simpleTeam (Name, coachName) values('red', 'Mr Smith');

insert into simpleTeam (Name, coachName) values('blue', 'Mr Jones');

insert into simpleTeam (Name, coachName) values('green', 'Ms LongName');

 

 

22. indentd 인증의 실패.

 

Tonight I tinkered with getting identd authentication working

with Postgres 7.0.2. It appeared to accept my configuration, but the

connections were failing when it

seemed they shouldn't. The sequence looked like this:

#####

elkhorn@ns elkhorn> psql -d elkhorn -u

psql: Warning: The -u option is deprecated. Use -U.

Username: elkhorn

Password:

psql: IDENT authentication failed for user 'elkhorn'

####

the password was correct, and the user and database named elkhorn

appear on the postgres server.

So from there I tried to verify that identd on nollie was working as

expected. Based some docs I found, I tried a basic raw identd connection:

#####

bash-2.04$ telnet nollie 113

Trying 208.196.32.199...

Connected to nollie.summersault.com.

Escape character is '^]'.

3342 , 23

3342 , 23 : ERROR : NO-USER

#########

I tried something similiar with the identd server and got a

similiar result:

root@philoxenist data]# telnet localhost 113

#########

Trying 127.0.0.1...

Connected to localhost.localdomain.

Escape character is '^]'.

23,2372

23 , 2372 : ERROR : UNKNOWN-ERROR

#############

For reference, I was using a line in pg_hbah.conf like this:

# host all 199.120.185.10 255.255.255.255 ident

sameuser

Both the host and client are running a FreeBSD 4.x

So I'm not sure what's wrong. At the moment this looks like an

identd problem rather than a Postgres issue. :) Perhaps one of you

have run into this before, though?

 

답변 :

Mark Stosberg writes:

> Tonight I tinkered with getting identd authentication working

> with Postgres 7.0.2. It appeared to accept my configuration, but the

> connections were failing when it

> seemed they shouldn't.

Is there a firewall or router between the client and server machines?

I've found the hard way that identd doesn't work for connections that

pass through a NAT-enabled router, because the port numbers are

different on the two sides of the router, so that the port number sent

in the ident request doesn't match anything the identd daemon can see.

(I suppose the router could fix this if it knew about ident requests,

but at least my Netopia router does not do that ...)

> The sequence looked like this:

> #####

> elkhorn@ns elkhorn> psql -d elkhorn -u

> psql: Warning: The -u option is deprecated. Use -U.

Hm, is PGHOST set in the environment? Otherwise this will try to do a

Unix-socket connection, which does not work with ident.

regards, tom lane

 

 

23. macaddr 을 text 형으로 casting 할 수 있는 방법은?

CREATE FUNCTION text(macaddr) returns text AS '

BEGIN

return ;

END;

' LANGUAGE 'plpgsql';

23. large text file 을 import 할려고 하는데, 시간이 너무 많이 걸린다.

해결법은?

fsync 옵션을 끄고 다시 import 를 시도한다.

 

 

24. PostgreSQL 을 실행도중 다음과 같으 에러메시지를 얻었다.

무엇이 잘못된 것일까요?

pq_flush: send() failed: Broken pipe

FATAL: pq_endmessage failed: errno=32

답변 :

Clients disconnecting prematurely, perhaps?

regards, tom lane

 

 

25. 특정 사용자가 없는지 확인하는 함수를 만들수 없을까?

답변:

CREATE FUNCTION f3(name) RETURNS bool AS '

DECLARE

row record;

BEGIN

SELECT * INTO row FROM pg_user WHERE usename = ;

IF FOUND THEN

RAISE NOTICE ''user % exists'', ;

RETURN ''t'';

ELSE

RAISE NOTICE ''user % not found'', ;

RETURN ''f'';

END IF;

END;

' LANGUAGE 'plpgsql';

 

26. Index 시 사용되는 Operator Class 를 확인할 수 있는 방법은?

SELECT am.amname AS acc_name,

opc.opcname AS ops_name,

opr.oprname AS ops_comp

FROM pg_am am, pg_amop amop,

pg_opclass opc, pg_operator opr

WHERE amop.amopid = am.oid AND

amop.amopclaid = opc.oid AND

amop.amopopr = opr.oid

ORDER BY acc_name, ops_name, ops_comp;

 

[Top]
No.
제목
작성자
작성일
조회
3577PostgreSQL 설치하기
정재익
2001-10-24
11799
3573Optimizing PostgreSQL [1]
정재익
2001-10-22
7350
3561Windows 용 PostgreSQL 의 설치 방법 [2]
정재익
2001-10-21
7139
3547PostgreSQL 을 사용하면서 모은 몇가지 Tip 들...
정재익
2001-10-19
16699
3489SSH 터널(tunnel)를사용한 안전한 TCP/IP 접속 [1]
송정훈
2001-10-12
4742
3486PostgreSQL 7.1.3(Write-Ahead Logging) [3]
송정훈
2001-10-11
6693
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2019 DSN, All rights reserved.
작업시간: 0.286초, 이곳 서비스는
	PostgreSQL v11.5로 자료를 관리합니다