[GENERAL] how to replace last 4 digital phone number into star using regexp_replace?

hi, friends, Our phone number format is 11 digital, i want to replace last 4 digital into star, for example: Replace this string including 11 digital phone number: '1891018,1391018,232,abc' into: '1891018,1391018,232,abc' I try to use: select regexp_replace('

[GENERAL] Does PostgreSQL support BIM(Building Information Modeling) storage？

Does PostgreSQL support BIM(Building Information Modeling) storage？Or how? I can only find few infomation: http://repository.tudelft.nl/islandora/object/uuid:dcb7fc18-208c-4e3b-bc2a-d24a2346d44b?collection=research Are there any other articles/books/software recommended? Thanks！ peng

[GENERAL] are there any method that "Update" command not affect other unrelated indices?

Hi, I have the following table: CREATE TABLE A ( a1 integer not null, a2 integer, a3 integer, a4 integer ) and have the following four indices: create index ind_a1 on A USING gist(a1); create index ind_a2 on A USING gist(a2); create index ind_a3 on A USING gist(a3); create index ind_a4

Re: [GENERAL] are there any method that "Update" command not affect other unrelated indices?

h. > > > On Wed, Oct 13, 2010 at 9:03 AM, sunpeng wrote: > >> >> the question is why all four indices updated in the execution of >> SPI_execute_plan()? >> I think there should only one index, that is ind_a4 be updated, how to >> avoid other three indices updated? >> thanks! >> >>

[GENERAL] how to get current sql execution time?

when I use the psql to send a sql, how to get current sql execution time?

[GENERAL] how to write an optimized sql with two same subsql?

We have a table A: CREATE TABLE A( uid integer, groupid integer ) Now we use this subsql to get each group's count: SELECT count(*) as count FROM A GROUP BY groupid ORDER BY groupid Then we try to find the group pair with following conditions: SELECT c.groupid as groupid1,d.groupid as groupi

Re: [GENERAL] how to write an optimized sql with two same subsql?

AS clusterid FROM _mcir_2347694 c, _mcir_2347694_clusterid2 d WHERE c.uid = d.uid GROUP BY d.clusterid 2010/10/14 Rob Sargent > > > On 10/14/2010 05:34 PM, sunpeng wrote: > > We have a table A: > > CREATE TABLE A(

[GENERAL] how to get the height of index tree?

Hi, how to get the height of R* or B tree of created index ?

Re: [GENERAL] how to get the height of index tree?

what's the base of log*a*(*n*) function ? is that base a=16? or something else? how to know this base ? 2010/10/22 sunpeng > Hi, how to get the height of R* or B tree of created index ? >

[GENERAL] why update is slower on my pc?

I use my laptop to execute the following sql, it's: mydb=# update _mcir_2597431_clusterid2 set clusterid = 3; UPDATE 104770 Time: 8666.447 ms and on my pc: mydb=# update _mcir_2597431_clusterid2 set clusterid = 3; UPDATE 104770 Time: 27171.203 ms First I wondered whether the write speed on pc is

[GENERAL] How to store fixed size images?

We have many small size(most fixed size) images, how to store them? There are two options: 1. Store images in folders, managed by os file system, only store path in postgresql 2. Store image as bytea in postgresql How do you usually store images? Thanks! peng

Re: [GENERAL] How to store fixed size images?

Thank you, Jeff! peng On Wed, Jun 18, 2014 at 12:15 AM, Jeff Janes wrote: > On Mon, Jun 16, 2014 at 6:10 PM, sunpeng wrote: > > We have many small size(most fixed size) images, how to store them? There > > are two options: > > 1. Store images in folders, managed by os fi

[GENERAL] Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1

When I do migration from Mysql to PostgreSQL: firstly dump data from mysql in cmd(encoding is GBK) is WIN8: mysqldump -v -nt --complete-insert=TRUE --compatible=postgresql --default-character-set=utf8 --skip-add-locks --compact --no-create-info --skip-quote-names -uroot -p test >dbdata.sql then lo

Re: [GENERAL] Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1

nd use Ultraedit to see first 16 bytes: FF D8 FF E0 5C 30 10 4A 46 49 46 5C 30 01 01 5C It's different from mysql workbench to see: FF D8 FF E0 00 10 4a 46 49 46 00 01 01 00 00 01 peng On Tue, Jul 1, 2014 at 9:18 PM, Kevin Grittner wrote: > sunpeng wrote: > > > load data to po

[GENERAL] How to use ADO to insert BYTEA data?

I try to write this code for postgresql (for mysql working fine): try { m_pRecordset->AddNew(); iTimes++; VARIANT bitdata[3]; SAFEARRAY *psafe[3] = {NULL, NULL, NULL}; SAFEARRAYBOUND band[3];

[GENERAL] which odbc version (32 or 64 bit) should be installed in Client ?

my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which odbc version (32 or 64 bit) should be installed in Client ? is psqlodbc_09_01_0200.zip or psqlodbc_09_01_0200-x64.zip?or others? thks! peng -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes t

[GENERAL] which odbc version (32 or 64 bit) should be installed in Client ?

my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which odbc version (32 or 64 bit) should be installed in Client ? is psqlodbc_09_01_0200.zip or psqlodbc_09_01_0200-x64.zip?or others? thks! peng

Re: [GENERAL] which odbc version (32 or 64 bit) should be installed in Client ?

when using psqlodbc_09_01_0200.zip, when i store picture to bytea using vc2008, calling appendchunk will get com_error. On 7/4/14, sunpeng wrote: > my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which odbc > version (32 or 64 bit) should be installed in Clien

Re: [GENERAL] which odbc version (32 or 64 bit) should be installed in Client ?

DODB.Recordset error '800a0c93' Illegal Operation. If you are using an ADODB.Recordset to UPDATE / INSERT (e.g. using the AddNew method), use a direct SQL statement instead (see Article #2191). see http://tutorials.aspfaq.com/8000x-errors/why-do-i-get-800a0c93-errors.html how to do? p

Re: [GENERAL] which odbc version (32 or 64 bit) should be installed in Client ?

Thank you, all friends, I will post "appendchunk error hresult 800a0c93" to pg_odbc mailing list. peng On 7/4/14, John R Pierce wrote: > On 7/3/2014 5:08 PM, sunpeng wrote: >> my server is pg 9.1.13 in win8 64, my client is win xp 32 bit, which >> odbc version (32 or 64

Re: [GENERAL] Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1

"0x" into "E'\\xx'" to load data into postgresql. On Fri, Jul 4, 2014 at 3:27 PM, Albe Laurenz wrote: > sunpeng wrote: > >>> load data to postgresql in cmd(encoding is GBK) is WIN8: > >>> > >>> psql -h localhost -d

[GENERAL] Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL?

Hi, Friends, are there any ETL tools (free or commercial) available for PostgreSQL? Thanks! peng

Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!

Cool, I hope I could be a member of patch reviewers. peng sun On Thu, Jun 16, 2011 at 4:58 AM, Merlin Moncure wrote: > On Wed, Jun 15, 2011 at 3:49 PM, Josh Berkus wrote: > > PostgreSQL community members: > > > > Do you love the PostgreSQL project? Do you want to contribute to it? > > Do you w

[GENERAL] hi, is that the standard:ISO/IEC 13249-6:2006 if I investigate the data mining extension for SQL language?

Recently I noticed there are only 1-4, 9-14 parts in SQL:2008 standard: ISO/IEC 9075-1:2008. Here is: http://www.iso.org/iso/iso_catalogue/catalogue_tc/catalogue_detail.htm?csnumber=45498 why don't they define 5-8 parts in this standard? I also noticed there is a standard for data mining: ISO

[GENERAL] Hi,Frirends, are there any commands in pgsql/bin/ corresponding "create tablespace"?

Hi,Frirends, Are there any commands in pgsql/bin/ corresponding "create tablespace"? I know: createuser <==> create role createdb <==>create database Thanks! peng

[GENERAL] where are the getting buf data from disk really done?

I noticed at the function StartBufferIO() in bufmgr.c, there is no really getting buffer data from disk, only set InProgressBuf = buf; and return true; i wondered where is the getting buf data from disk really done? are there a background process to do this work? which function does this work? than

[GENERAL] how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection?

how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection? if i use eclipse cdt to attach the postgres process forked by postmaster, this part of codes from the begining of this PostgresMain() to the "for (;;)" loop could not

[GENERAL] how to set CACHEDEBUG ?

I noticed there is a piece of code: #ifdef CACHEDEBUG #define InitCatCache_DEBUG2 \ do { \ elog(... } while(0) #else #define InitCatCache_DEBUG2 #endif Now I'd like to set CACHEDEBUG, how to set up it ? where ? thanks peng

[GENERAL] only one namespace allowed by a authid at pg_namespace table?

only one namespace allowed by a authid at pg_namespace table? for in the function: static void recomputeNamespacePath(void){ ... if (strcmp(curname, "$user") == 0) { /* $user --- substitute namespace matching user name, if any */ HeapTupletuple; tupl

[GENERAL] why there are two TargetEntrys in Query when i use "select count(catcode) from pois group by catcode"?

I've the table CREATE TABLE pois ( uid integer not null, name VARCHAR(128), catcode VARCHAR(32) not null, catname VARCHAR(32), others VARCHAR(32) ); after i execute "select count(*) from pois group by catcode"; the log of query.targetList is : :targetList ( {TARGETENTRY

[GENERAL] when to update pg_statistic relation?

when to update pg_statistic relation? is it when inserting a new tuple of any user's relations? and the relation pg_stats at document 8.4 chapter 44.55.pg_stats hasn't been used anymore ? another question is: I noticed when i send the sql :"select catcode from pois goup by catcode", the function :d

[GENERAL] what's the relation between pathkey and path in optimize phase?

what's the relation between pathkey and path in optimize phase? thanks peng

[GENERAL] when to update pg_statistic relation?

when to update pg_statistic relation? is it when inserting a new tuple of any user's relations? and the relation pg_stats at document 8.4 chapter 44.55.pg_stats hasn't been used anymore ? another question is: I noticed when i send the sql :"select catcode from pois goup by catcode", the function :d

[GENERAL] hi,is it dangerous to only use tuple pointer through heap_getnext()?

hi,i use these codes to store only pointer of tuple : HeapTuple *tuple; tuple = heap_getnext(pHeapScanDesc,ForwardScanDirection); while(tuple){ //[1#]here i only store the pointer of tuple in an array for later using,that means i don't retrive attribute data from this tuple ,is this

[GENERAL] how to get Point * from datum =heap_getattr(....)?

hi,i have this table: CREATE TABLE pois( uid integer not null, name VARCHAR(128), catcode VARCHAR(32) not null, catname VARCHAR(32), others VARCHAR(32) ); SELECT AddGeometryColumn('pois', 'location', 4214, 'POINT', 2); and then in my codes,i use this invoke to get location column i

[GENERAL] how to get Point * from datum =heap_getattr(....)?

hi,i have this table: CREATE TABLE pois( uid integer not null, name VARCHAR(128), catcode VARCHAR(32) not null, catname VARCHAR(32), others VARCHAR(32) ); SELECT AddGeometryColumn('pois', 'location', 4214, 'POINT', 2); and then in my codes,i use this invoke to get location column i

[GENERAL] which function should i invoke to create a table and insert tuples?

hi,when i do experiment on postgresql 8.4,i need to create a table and insert some tuples,which function should i invoke? for example,i want to create a table with "create table test (uid int,catcode int)" and insert tuples with "insert into test values(1,1)". thanks millions! peng

Re: [GENERAL] which function should i invoke to create a table and insert tuples?

it's in source codes,actually i'm writting codes in postgresql source codes,just to verify some of my ideas. C language is used. 2010/5/17 Guy Rouillier > On 5/17/2010 10:31 AM, sunpeng wrote: > >> hi,when i do experiment on postgresql 8.4,i need to create a table and

[GENERAL] hi,i write a function in postgresql source code, how to register this function?

hi,i write a function in postgresql source code, how to register this function? it is not an aggregate function. i don't use 34.3"User-Defined Functions" described in http://www.postgresql.org/docs/8.4/interactive/xfunc.html, i just write it in postgresql sourcecode, how to register this function t

Re: [GENERAL] hi,i write a function in postgresql source code, how to register this function?

It's just in postgresql 8.4 source code,e.g in /backend/executor/functions.c, not in sql,not in pl/pgsql 2010/6/16 Raymond O'Donnell > On 16/06/2010 17:42, sunpeng wrote: > > hi,i write a function in postgresql source code, how to register this > > function? > >

[GENERAL] are there any documents describe the index mechanic?

are there any documents describe the index mechanic? For example, how to store the B tree in tables in hard disk? thanks! peng

[GENERAL] How to restore a Plan from a stored plan text?

I've used the following codes to translate the PlannedStmt node to a char string: PlannedStmt * pltl = (PlannedStmt *) linitial(plantree_list); Plan *pl = pltl->planTree; char *s; s = nodeToString(pl); How to restore from this s to Plan? I noticed using func parseNodeString() in /backends/

Re: [GENERAL] How to restore a Plan from a stored plan text?

*, void*) function to support Plan node? 2010/9/3 Tom Lane > sunpeng writes: > > I've used the following codes to translate the PlannedStmt node to a char > > string: > > PlannedStmt * pltl = (PlannedStmt *) linitial(plantree_list); > > Plan *pl = pltl->planTr

[GENERAL] How to let the created table visible to current process when using SPI_execute("create table ...")?

When Postmaster starts, I've forked another process AP just as syslogger, bgwritter,... In the process AP, If I can't find a table, I would create one, the codes are: char * sqlCreate_DM_ = "create table DM_( ...); "; SPI_connect(); int ret = SPI_ex

[GENERAL] Fwd: how to write the sql if i want to find a geometry's d-distance neighbors?

I can't mail it to: postgis-us...@postgis.refractions.net so could administrator forword this mail to them? thanks! From: sunpeng Date: 2010/9/12 Subject: how to write the sql if i want to find a geometry's d-distance neighbors? To: postgis-us...@postgis.refractions.net Hi,

[GENERAL] hi, how to let the inserted tuple visible to other backend when current backend hasn't finish?

hi, These codes are in the postgresql engine, just assume they are in PortalRun() function: //1.create table structure char *relname = "test"; ... relOid = heap_create_with_catalog(relname, ); CommandCounterIncrement(); ... //2.then i can use SPI_execute to create index on this created table SP

[GENERAL] why can't see the updated value after SPI_execute("update ....", false, 1);

First I use SPI_execute("update bool succ; SPI_connect(); int ret = SPI_execute("update where uid = 1", false, 1);//later will add error processing if (ret == SPI_OK_UPDATE && SPI_processed == 1) { succ = true; } SPI_finish(); Then I use SPI_execute("select

Re: [GENERAL] why can't see the updated value after SPI_execute("update ....", false, 1);

I then added , yet it still doesn't work. 2010/9/12 Tom Lane > sunpeng writes: > > First I use SPI_execute("update > > bool succ; > >SPI_connect(); > > int ret = SPI_execute("update where uid = 1", false, 1);//later

[GENERAL] Are there any commands to see the created index info?

Are there any commands to see the created index info? For example, if I have a table A (has 100,000 tuples )with index A_INDEX, how to see the A_INDEX info, such as the deep of B+ tree? peng

[GENERAL] how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection?

how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection? if i use eclipse cdt to attach the postgres process forked by postmaster, this part of codes from the begining of this PostgresMain() to the "for (;;)" loop could not

[GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?

once i have created mydb and several relations in it,are there any sql commands used to list all the tables in this mydb? i noticed there are no database( pg_database.oid) field in pg_class table,so i can not use select relname from pg_class,pg_database where pg_database.datname like 'mydb' and pg_

Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?

if a superuser administrator wants to list all the tables in all the databases,how do the postgresql interval implement it? will the postgresql interval load all the pg_ tables in all the databases to get the final answer? Thanks! peng 2010/4/8 Kenichiro Tanaka > Hello sunpeng > >

[GENERAL] hi, friends. are there any performance tuning materials for postgreSQL recommended?

Hi, friends. Are there any performance tuning resouces for postgreSQL recommended, such as ppt, books or articles? Thanks! peng

[GENERAL] hi, firends, what's the difference between using PostgreSQL's share buffer and os's file buffer?

hi, firends, I have a 64bit 128GB machine, I have two choices: 1. I could set PostgreSQL share_buffer to a large value, such as 100GB, let os uses the remaining 28G memory for file system buffer 2. I also could set PostgreSQL share_buffer to a small value, such as 10GB, l

[GENERAL] are there any methods to disable updating index before inserting large number tuples?

hi, friends, Now each hour I want to insert about 20 millions tuples into table A, which has a btree index. How to disable index update before inserting those 20 millions tuples, and then enable it after those tuples having being inserted? Just as the followings: >disable index update >ins

[GENERAL] why can't my account be used at wiki.postgresql.org after having registered in www.postgresql.org?

Hi, I just registered my acount using this url: https://www.postgresql.org/account/signup/, then i use the new account to login www.postgresql.org, everything works well. But when I try to login wiki.postgresql.org using the same account, i got an error: There is no user by the name "myaccount"

[GENERAL] Why warm-standby doesn't work using file-based log shipping method?

I try to setup warm-standby using file-based log shipping method: Master: 5432 port Standby:6432 port at same machine Master's : - wal_level = archive - archive_mode = on - archive_command = 'cp %p /home/postgres/archive/%f' Standby's restore_command = 'cp /home/postgres/archive/%f %p'