Re: [GENERAL] Understanding PostgreSQL Storage Engines

Hey, 2010/10/9 Pavel Stehule > Hello > > 2010/10/8 Carlos Mennens : > > I know that MySQL uses MyISAM storage engine by default and was just > > trying to look on Google to try and see if I could understand what > > storage engine does PostgreSQL use by default when I generate a > > database / t

Re: [GENERAL] Gripe: bytea_output default => data corruption

Hey all, And it is 9.0, i.e another major version. IMO the higher major versions not necessarily must be 100% backward compatible. So, think twice next time you update the major version. 2010/10/13 Raymond O'Donnell > On 13/10/2010 01:37, ljb wrote: > >> Defaulting bytea output from the backend

[GENERAL] How to determine failed connection attempt due to invalid authorization (libpq)?

Hey all, We using libpq. There is only CONNECTION_BAD status which is signaled about failed connection. Nevertheless, is there way to check validity of username / password ? -- // Dmitriy.

Re: [GENERAL] querying the version of libpq

Hey Massa, Again, I suggest PQlibVersion() or PQversion() instead of PQlibpqVersion(). Why this "pq" redundancy in name? Waste of bytes. 2010/10/14 Massa, Harald Armin > And the dynamic linker will take any version of libpq.so that has the same >> major number, meaning that you >> cannot assu

Re: [GENERAL] querying the version of libpq

2010/10/14 Massa, Harald Armin > Dmitriy, > >> >> Again, I suggest PQlibVersion() or PQversion() instead of >> PQlibpqVersion(). >> >> Why this "pq" redundancy in name? Waste of bytes. >> >> the first PQ qualifies the name-space "those are PostgresQl functions". > the library is called libpq, and

Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model

Hey Tony, 2010/10/21 Tony Cebzanov > I have a web application with a Postgres backend. In my initial > prototype, I decided not to have a Postgres database user created for > each application user, opting instead to use my own users table. > IMO, you are trying to reinvent the wheel. Although,

Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model

Hey Tony, 2010/10/22 Tony Cebzanov > Hi Dmitriy, > > On 10/21/10 4:21 PM, Dmitriy Igrishin wrote: > > IMO, you are trying to reinvent the wheel. Although, you may do it just > for > > fun. :-) > > Surely I am, but I think it made sense at the time. It doesn

Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model

Hey Peter, 2010/10/22 Peter Bex > On Fri, Oct 22, 2010 at 12:21:17AM +0400, Dmitriy Igrishin wrote: > > Hey Tony, > > > > 2010/10/21 Tony Cebzanov > > > > > I have a web application with a Postgres backend. In my initial > > > prototype, I decided

Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model

Hey Tony, 2010/10/27 Tony Cebzanov > On 10/23/10 11:01 AM, Craig Ringer wrote: > > Yep. As for not explicitly mentioning "lower" roles when granting a > > higher role (ie "admin" isn't also a "user") - role inheritance. > > I knew about role inheritance, I just didn't know about the > pg_has_rol

Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model

Hey Craig, 2010/10/27 Craig Ringer > On 27/10/10 04:49, Dmitriy Igrishin wrote: > > Hey Tony, > > > > 2010/10/27 Tony Cebzanov > <mailto:tony...@andrew.cmu.edu>> > > > > On 10/23/10 11:01 AM, Craig Ringer wrote: > > > Yep. As for n

Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table

Hey all, Why not to use MVC approach by implementing a model, which uses, e.g. scrollable cursors? I believe that wxWidgets supports MVC. 2010/10/30 Peter Geoghegan > On 29 October 2010 21:52, Rob Richardson > wrote: > > A customer was reviewing the database that supports the application we >

Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table

Hey Peter, Dave > I've seen that behaviour before in similar applications, but it had a > > major downside: the number of rows returned was not known in advance > > of scrolling down to the last one. So you couldn't visualise the size > > of the record set based on the size and relative position o

Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table

result set :-) 2010/10/30 Dave Page > On Sat, Oct 30, 2010 at 4:14 PM, Dmitriy Igrishin > wrote: > > Do you mean that cursors (regular, not holdable) live only inside a > > transactions? > > But it is possible to check transaction status from another part of > pgAdm

Re: [GENERAL] Implementing replace function

Hey Alexander, Pavel The solution like below should works IMO, but it does not. insert into pref_users(id, first_name, last_name, female, avatar, city, last_ip) select $1, $2, $3, $4, $5, $6, $7 where not exists (update pref_users set first_name = $2, l

Re: [GENERAL] Implementing replace function

Okay, Pavel, will wait for 9.1 :-) It is a common case - insert new row if it cannot be updated. 2010/10/31 Pavel Stehule > Hello > > 2010/10/31 Dmitriy Igrishin : > > Hey Alexander, Pavel > > > > The solution like below should works IMO, but it does not. >

Re: [GENERAL] Save and load jpg in a PostgreSQL database

Hey Fernando, If you need to store binary data in a table you should use bytea data type. Than, in case of libpq: If you want to transmit binary data from client to server in text format you must prepare (escape) it for including into you SQL command (e.g., INSERT). If you can transmit the data fr

Re: [GENERAL] Save and load jpg in a PostgreSQL database

;. > > Changing "image.jpg" to my image file, the PostgreSQL already convert this > and save in a database. > Is this true? And how can i load this to imageBox in my system? Which type > must be declared my variable? > > Thanks to your help. > > Em 04/11/2010 1

Re: [GENERAL] Why facebook used mysql ?

Hey all, IMO that they choiced MySQL because of no knowledge about PostgreSQL and about valid database designs. Just garbage of data for SELECTing with minimal efforts on data integrity and database server programming (ala typical PHP project). Sorry :-) 2010/11/9 Tom Lane > Vick Khera writes:

Re: [GENERAL] Schema tool

Hey Aram, I recommend dbWrench by Nizana. It has a nice synchronization capabilities, forward / reverse engineering and supports many built-in PostgreSQL types and user-defined types as well. NB: it is commercial application. Another alternative is a MicroOLAP Database Designer. The both tools a

Re: [GENERAL] More then 1600 columns?

Hey Mark, Yeah, I can't imagine an entity in a real project even with more than 100 columns. Its rare case. But if you entities (rows/tuples) of some class (table) can contains variable set of columns (properties) you can look at hstore contrib module. 2010/11/12 John R Pierce > On 11/11/10 9:2

Re: [GENERAL] More then 1600 columns?

Hey Peter, Unfortunately, there is no indexes on arrays (only on expressions). With hstore we can easily create GiST index for effective access. 2010/11/12 Peter Bex > On Fri, Nov 12, 2010 at 10:17:50AM +0300, Dmitriy Igrishin wrote: > > Hey Mark, > > > > Yeah, I can

Re: [GENERAL] More then 1600 columns?

Hey Pavel, Really ? I am sorry, I'll try ! 2010/11/12 Pavel Stehule > 2010/11/12 Dmitriy Igrishin : > > Hey Peter, > > > > Unfortunately, there is no indexes on arrays (only on expressions). > > there are indexes on arrays - you can use a GiST, Gin indexes. &g

Re: [GENERAL] More then 1600 columns?

bout it at all. But thank you very much to points me about indexes on arrays ! 2010/11/12 Pavel Stehule > 2010/11/12 Dmitriy Igrishin : > > Hey Peter, > > > > Unfortunately, there is no indexes on arrays (only on expressions). > > there are indexes on arrays - you c

Re: [GENERAL] More then 1600 columns?

Sounds like semi-structured data handling. For this tasks hstore would be ideal, IMO. 2010/11/12 John R Pierce > I looked up the OP's domain.They develop medical research data analysis > software. > > > That sort of software and the kinds of data analysis they do tortures SQL > databases.

Re: [GENERAL] More then 1600 columns?

I can't imagine how to maintain a database with tables with 1600 columns... I can't imagine how to simple work with this garbage of data via SQL... 2010/11/13 Clark C. Evans > On Fri, 12 Nov 2010 21:10 +, "Dann Corbit" wrote: > > If (for access) the single table seems simpler, then > > a vie

Re: [GENERAL] More then 1600 columns?

ea expect column name size and number of > columns postgres has huge advantages over other systems. > > > > *From:* Dmitriy Igrishin [mailto:dmit...@gmail.com] > *Sent:* Friday, November 12, 2010 4:30 PM > *To:* Clark C. Evans > *Cc:* Dann Corbit; Mark Mitchell; Tom Lane; pg

Re: [GENERAL] The first dedicated PostgreSQL forum

Hey, Yeah, we feel here at home and don't need forum. 2010/11/13 Raymond O'Donnell > On 13/11/2010 03:43, Elliot Chance wrote: > >> Hi everyone, >> >> This is my first post on the mailing list :) >> >> As of today I have started the first dedicated postgres forum at: >> http://forums.postgresql

Re: [GENERAL] PostgreSQL database model creation

Hey Miguel, You may try DbWrench or MicroOLAP database designer software. 2010/11/10 Miguel Vaz > Hi, > > Is there anything like MySQL workbench for PostgreSQL? > > I know there are a few alternatives, but was hoping to hear specific > choices and not just some random anonymous internet list li

Re: [GENERAL] Comparing first 3 numbers of a IPv4 address?

Hey Alexander, 2010/11/20 Alexander Farber > Hello, > > I'm trying to program a PHP-script, where users > can rate the "goodness" of the other players: > >create table pref_rep ( >id varchar(32) references pref_users(id) check (id <> > author), >author var

Re: [GENERAL] Comparing first 3 numbers of a IPv4 address?

Farber > I'm actually hoping to use inet (or cidr?) instead of strings... > > > > On 11/20/10, Dmitriy Igrishin wrote: > > Hey Alexander, > > > > 2010/11/20 Alexander Farber > > > >> Hello, > >> > >> I'm trying to

Re: [GENERAL] Comparing first 3 numbers of a IPv4 address?

libpq). But is it worth it ? :-) 2010/11/20 Alexander Farber > I think inet is a number internally, there is probably a more effective > way... > > On 11/20/10, Dmitriy Igrishin wrote: > > You can. host() just extract IP address from a value of inet > > type as text, st

Re: [GENERAL] Comparing first 3 numbers of a IPv4 address?

Hey Tom, Thanks for you solution! 2010/11/20 Tom Lane > Alexander Farber writes: > > 1) if I'd like to compare just the first 3 numbers of > > the IP address instead of the 4, how can I do it? > > regression=# select network(set_masklen('123.45.67.42'::inet, 24)) = > regression-# network(set_

Re: [GENERAL] finding rows with invalid characters

Hey Sim, Maybe this helps: http://blog.tapoueh.org/articles/blog/_Getting_out_of_SQL_ASCII,_part_2.html 2010/11/21 Sim Zacks > I am using PG 8.2.17 with UTF8 encoding. > "PostgreSQL 8.2.17 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 > (Gentoo 4.1.1)" > > One of my tables somehow has i

Re: [GENERAL] finding rows with invalid characters

at is in there, but this table will probably never be audited. > > > Sim > > > > On 11/23/2010 10:33 AM, Dmitriy Igrishin wrote: > > Hey Sim, > > 2010/11/23 Sim Zacks > >> On 11/21/2010 05:55 PM, Dmitriy Igrishin wrote: >> >> Hey Sim, >

Re: [GENERAL] Escaping string for LIKE

Hey David, I guess, that you should escape, like this dmitigr=> SELECT regexp_replace('di_ma%', '(%|_)', E'\\1', 'g'); regexp_replace di\_ma\% 2010/11/23 David Sheldon > If I have a string, and I want to search for everything that starts with > that string then I could

Re: [GENERAL] alter table add column - specify where the column will go?

Hey Alexander, Ordinal positions of columns can't be set manually by ALTER TABLE. 2010/11/24 Alexander Farber > Hello, > > is there a syntax to add a column not at the last place, but > somewhere inbetween or do I have to dump/restore the table? > > For example if I'd like to add last_logout ri

Re: [GENERAL] alter table add column - specify where the column will go?

Please note, that in cases when you can't do simple dump - fix - restore (e.g. in production) you can always create view(s) with ordinal positions of columns convenient for you. 2010/11/24 Dmitriy Igrishin > Hey Alexander, > > Ordinal positions of columns can't be set manu

Re: [GENERAL] alter table add column - specify where the column will go?

It is easy to create view based on SELECT which explicitly specifies the columns names. IMO it is better to hide tables structures behind views and work with them, since views are not materialized and it is easy to drop and recreate or just add another view into the database. With this approach you

Re: [GENERAL] diagram tools?

Hey all, I recommend also look at dbWrench - http://www.dbwrench.com/ 2010/11/25 Joshua Tolley > On Wed, Nov 24, 2010 at 10:23:15AM -0800, DM wrote: > > There are many of them, I use SchemaSpy java based - easy to generate. > > Here are several other possibilities, which I've taken from the hel

[GENERAL] Terms advice.

Hey all, I am working on C++ library to work with PostgreSQL. I am trying to follow strong correctness in terms. One of my current dilemmas is how to distinguish the results of commands in correct terms. E.g., is it correct to call commands which returns tuples as "queries" but commands like whic

Re: [GENERAL] Terms advice.

m petty and not worthy of such attention. But I believe that extra correctness helps better understand any library or program. BTW. "query" and "command" are synonyms, as I mentioned above. :-) 2010/11/27 Craig Ringer > On 11/26/2010 09:37 PM, Dmitriy Igrishin wrote: >

Re: [GENERAL] Question about OID and TCID

Hey Zhipan, 2010/11/27 Zhipan Wang > Hi, > > I want to access part of a table on the disk sequentially, i,e., when I get > to a tuple in the table, I need to read several pages of data in the table > starting from this tuple. I guess CTID could be translated to physical > address on the disk to

Re: [GENERAL] Proper Permissions for /usr/local/pgsql/data

Hey Rich, You should chown your data dir (/usr/local/pgsql/data) to "postgres" before you run initdb. 2010/12/2 Rich Shepard > I'm manually replacing -8.3.3 with -9.0.1 I have the old data/ directory > and pg_dumpall files safely stored. Following the directions at: >

Re: [GENERAL] number of rows returned by a query

Hey Konstantin, Use MOVE ALL, then PQcmdTuples to get number of rows, then MOVE BACKWARD ALL to point cursor at initial position. 2010/12/2 Konstantin Izmailov > Maybe this question was discussed previously, but I could not find an > answer: > > An application used PQntuples function to obtain

Re: [GENERAL] Do we want SYNONYMS?

Hey, Joshua, -general, If the user create a schema for placing synonyms for all functions of all schemas in the database then will it be possible to make dump of this schema but not only with CREATE synonyms clauses, but with functions definitions also ? :-) It would be nice. 2010/12/6 Joshua D.

Re: [GENERAL] Do we want SYNONYMS?

What is synonym? Is it a reference? Can I dump DDL of the object by synonym? If no, I personally don't see how it can be used. Maybe it can be used to create 7 synonyms for some table and let application use different synonym depends on day of the week... :-) I don't see how it can be used... 2010

Re: [GENERAL] Do we want SYNONYMS?

Hey Michael, 2010/12/6 Michael C Rosenstein > Synonyms would be very helpful to us. We just migrated our application from > Oracle, where we used synonyms to toggle between between two schemas: one > schema could be loaded with new data, while synonyms pointed the web > application to the live

Re: [GENERAL] Do we want SYNONYMS?

Ahh, catalog :-) But PostgreSQL has a templates. If I understood you correctly, the problem is to let the application works with same object names of the objects in a different databases? 2010/12/6 Michael C Rosenstein > What is "schema" in this context? >> > > Oracle "schema" == Postgres "datab

Re: [GENERAL] Do we want SYNONYMS?

2010/12/7 Gauthier, Dave > I think aliasing non-table/view was mixed by Tom, but just as FYI, aliasing > column names would be very helpful in my apps. Aliasing "last_name", > "lastname", "surname" together in a people table for example. We have many > design sites that have identical data conce

Re: [GENERAL] Do we want SYNONYMS?

2010/12/7 Dmitriy Igrishin > > > 2010/12/7 Gauthier, Dave > > I think aliasing non-table/view was mixed by Tom, but just as FYI, aliasing >> column names would be very helpful in my apps. Aliasing "last_name", >> "lastname", "surname&qu

Re: [GENERAL] Do we want SYNONYMS?

2010/12/7 Michael C Rosenstein > On 12/6/10 4:09 PM, Tom Lane wrote: > >> Michael C Rosenstein writes: >> >>> What is "schema" in this context? >>> >> Oracle "schema" == Postgres "database": a collection of objects >>> (tables, functions, triggers, views, etc) owned by a user. >>> >> >> T

Re: [GENERAL] Do we want SYNONYMS?

Hey Daniel, Again link to oracle.com... During this thread I believe that synonyms gives nothing except confusion and mess. 2010/12/7 Daniel Verite >Tom Lane wrote: > > > Taken at face value from a Postgres perspective, these statements seem > > to imply that different ownership and pe

Re: [GENERAL] Do we want SYNONYMS?

2010/12/7 Andy Colson > On 12/7/2010 8:12 AM, Daniel Verite wrote: > >>Vick Khera wrote: >> >> On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake >>> wrote: >>> Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the communi

Re: [GENERAL] Tool for data modeling and ER diagram

Hey Jaiswal, "Best" is relative word. It is matter of taste. I recommend dbWrench. http://www.dbwrench.com/ 2010/12/7 Jaiswal Dhaval Sudhirkumar > > Hi List, > > What is the best tool of data modeling and ER diagram for PostgreSQL. > > > -- > Thanks & Regards > DJ > > > The information transmi

[GENERAL] OID of type by name.

Hey general@, SELECT oid FROM pg_type WHERE typname = 'integer'; oid - (0 rows) SELECT oid FROM pg_type WHERE typname = 'int4'; oid - 23 (1 row) How can I get OID by name rather than alias ? -- // Dmitriy.

Re: [GENERAL] OID of type by name.

SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 AND typname::regtype = 'integer'; Many thanks to Florian Pflug. 2010/12/8 Dmitriy Igrishin > Hey general@, > > SELECT oid FROM pg_type WHERE typname = 'integer'; > oid > - &

Re: [GENERAL] OID of type by name.

Yeah, thank you very much! I've found it already too, but not post back! Thanks! 2010/12/8 Tom Lane > Dmitriy Igrishin writes: > >> How can I get OID by name rather than alias ? > > > SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <

Re: [GENERAL] OID of type by name.

Actually, all I need is to: SELECT oid::regtype, oid FROM pg_type WHERE ... to make cache of OIDs. 2010/12/8 Dmitriy Igrishin > Yeah, thank you very much! > > I've found it already too, but not post back! > > Thanks! > > 2010/12/8 Tom Lane > > Dmitriy Igris

[GENERAL] Extended query protocol and exact types matches.

Hey general@, To be assured and just for calmness. Problem: 1. CREATE TABLE test_tab (id integer, dat varchar(64)); 2. INSERT INTO test_tab VALUES($1, $2) via PQexecParams, where paramTypes[0] == OID of bigint, paramTypes[1] == OID of text. Questions: Whether this case falls to ht

Re: [GENERAL] A cronjob for copying a table from Oracle

Hey Thomas, Alexander 2010/12/10 Thomas Kellerer > Alexander Farber, 10.12.2010 12:53: > > On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer >> wrote: >> >>> And I'm not sure how to copy the Oracle's strange DATE column best into PostgreSQL, without losing precision? >>> >>> Oracle's

Re: [GENERAL] A cronjob for copying a table from Oracle

Hey Alexander, Can you post the SQL with call of the function (SQL_UPSERT) I guess ? 2010/12/10 Alexander Farber > Please help, struggling since hours with this :-( > > I've created the following table (columns here and in the proc > sorted alphabetically) to acquire data copied from Oracle: >

Re: [GENERAL] A cronjob for copying a table from Oracle

Hey Adrian, 2010/12/10 Adrian Klaver > On Friday 10 December 2010 8:51:19 am Alexander Farber wrote: > > Please help, struggling since hours with this :-( > > > > I've created the following table (columns here and in the proc > > sorted alphabetically) to acquire data copied from Oracle: > > > >

Re: [GENERAL] Extended query protocol and exact types matches.

) are safe? 2010/12/10 Merlin Moncure > On Thu, Dec 9, 2010 at 1:24 PM, Dmitriy Igrishin > wrote: > > Hey general@, > > > > To be assured and just for calmness. > > > > Problem: > > > > 1. CREATE TABLE test_tab (id integer, dat varchar(64)); &g

Re: [GENERAL] A cronjob for copying a table from Oracle

> On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote: > > Hey Adrian, > > > > 2010/12/10 Adrian Klaver > > > > > On Friday 10 December 2010 8:51:19 am Alexander Farber wrote: > > > > Please help, struggling since hours with this :-(

Re: [GENERAL] A cronjob for copying a table from Oracle

2010/12/10 Adrian Klaver > On 12/10/2010 09:45 AM, Dmitriy Igrishin wrote: > >> Huh! Yes, indeed ! But how is it possible ?! I see >>EMAIL = _EMAIL, >>EMAILID = _EMAILID, >> >> rather than >>

Re: [GENERAL] Extended query protocol and exact types matches.

2010/12/10 Merlin Moncure > On Fri, Dec 10, 2010 at 12:40 PM, Dmitriy Igrishin > wrote: > > Hey Merlin, > > > > Thank you for explanation ! > > > > Yes, I understand that specifying NULL instead real OID will provoke > > the parser attempts to infer

Re: [GENERAL] SSL ques

Hey, Sounds like you does not have SSL library installed. Try to install, for example, OpenSSL first and then try to build you software. 2010/12/12 zab08 > when I install sysbench-0.4.8, after the make command: > > > /usr/bin/ld: warning: libssl.so.6, needed by > /opt/PostgreSQL/9.0/lib/libpq.

Re: [GENERAL] C++ code - PGRES_TUPLES_OK is not returned when DELETE performed

Hey Yan Cheng, The status of command is PGRES_COMMAND_OK only when performed command was not returns data, e.g. DELETE (without RETURNING clause), BEGIN, COMMIT etc. The status PGRES_TUPLES_OK only when performed command was SELECT, SHOW or UPDATE / DELETE with RETURNING clause and etc. 2010/12/1

Re: [GENERAL] Load C++ for functions?

Hey Elliot, See http://www.postgresql.org/docs/9.0/static/extend-cpp.html Probably compiling with -fno-exceptions solve you problem. 2010/12/24 Elliot Chance > I'm trying to link up a C++ project with postgres functions, the following > code compiles (as C++): > > extern "C" { >#includ

Re: [GENERAL] PowerDesigner 15

28 декабря 2010 г. 13:02 пользователь el dorado написал: > Hello! > > I would like to get more flexible tool for database management that EMS of > pgadmin. So now I'm trying to use PD 15 with Postgres 8.4.1. It's very > exciting :) > So what can I say? > It seems to be there are no problems with

Re: [GENERAL] Concatenating several rows with a semicolon

2010/12/28 Alexander Farber > Hello, > > I'm working on a small app, which receives a list of 20 players in XML > format. > > The initial version works ok and I use there just 1 SQL statement and thus > it is easy for me to fetch results row by row and print XML at the same > time: > >

Re: [GENERAL] Concatenating several rows with a semicolon

2010/12/28 Dmitriy Igrishin > > > 2010/12/28 Alexander Farber > > Hello, >> >> I'm working on a small app, which receives a list of 20 players in XML >> format. >> >> The initial version works ok and I use there just 1 SQL statement and thus &g

Re: [GENERAL] Concatenating several rows with a semicolon

2010/12/29 Alexander Farber > Hello Bill and others, > > I don't agree about yw being a bad thing > since I have weekly raings in my app, > but your XML suggestion - > > On Tue, Dec 28, 2010 at 9:29 PM, Bill Moran > wrote: > > > > If there are multiple entries for pref_money, then each one shoul

Re: [GENERAL] Concatenating several rows with a semicolon

2010/12/29 Alexander Farber > On Tue, Dec 28, 2010 at 10:31 PM, Dmitriy Igrishin > wrote: > >> > > >> > > >> > ... etc ... > >> > > > > > Well, generally storing data in attributes should be avoided: > > &

Re: [GENERAL] Concatenating several rows with a semicolon

2010/12/29 Alexander Farber > Hello Dmitriy, > > I think this combination of attributes and children: > > >> >> > > >> >> > > >> >> > ... etc ... > >> >> > > > will be a good balance between size and my original problem > (combining user data and their stats in 1 chunk of information). > > A

Re: [GENERAL] Row-level permissions?

Hey gvim, 2010/12/30 gvim > Is it possible, with PostgreSQL 9.0, to restrict access to specific table > rows by `id`? I want a user to be able to INSERT new rows but not UPDATE or > DELETE rows with `id` < 1616. > I believe that first you need to restrict SELECT. You can do it by creating view:

Re: [GENERAL] How to deal with field on the database that stores variable array's?

2011/1/1 Andre Lopes > Hi, > > I need to develop a database table that will aceept a variable array field. > My doubt is how to deal with updates on the > array field. How can I store the information of the array fields? There any > examples on the Internet on how to deal with > this subject? > >

Re: [GENERAL] array_agg-like thing over arrays

Hey Andrew, 2011/1/15 Andrew Sullivan > Hi all, > > I could swear I remember being able to do this in the past, but it > doesn't seem I can remember how, and I have been unable to find > anything in The Fine Materials. > > I want to produce the output of rows of arrays as an aggregated array: >

Re: [GENERAL] Why can't I change a password

Hey Steve, 2011/1/16 Steve Litt > Hi all, > > I've somehow messed up something. > > psql super > psql's synopsis is psql [option...] [dbname [username]] Thus, the call "psql super" connects psql to a database "super" but since username unspecified it is connected with current Unix user (w

Re: [GENERAL] libpq: how to retrieve query result?

Hey 17 января 2011 г. 23:00 пользователь Вячеслав Блинников написал: > I retrieve asynchronous queries results using PQgetResult() which returns > NULL not in case of some error, but when results end - so there is no way to > know if database responded successfully or there happened some error? >

Re: [GENERAL] libpq: multiple commands within single query

Hey, 18 января 2011 г. 14:24 пользователь Вячеслав Блинников написал: > By which rules database returns results for multiple commands within single > query? > For example I send (execute) such query (obtain different information about > just connected client): > "SELECT column1 FROM table1; SELEC

Re: [GENERAL] Store base64 in database. Use bytea or text?

2011/1/26 Andy Colson > On 1/25/2011 4:27 PM, Andre Lopes wrote: > >> Hi, >> >> I need to put some images on Base64 in a PostgreSQL database. Wich >> type should I use and what is the difference between using bytea or >> text to store Base64? >> >> Best Regards, >> >> > If they are encoded in bas

Re: [GENERAL] Store base64 in database. Use bytea or text?

2011/1/26 Peter Geoghegan > On 25 January 2011 22:27, Andre Lopes wrote: > > Hi, > > > > I need to put some images on Base64 in a PostgreSQL database. Wich > > type should I use and what is the difference between using bytea or > > text to store Base64? > > I really don't think you want to do th

Re: [GENERAL] Store base64 in database. Use bytea or text?

2011/1/26 Andre Lopes > Thanks for all the reply's. > > I will be using PHP for now to insert data. > > So I shouldn't use base64 to store images or any other kind of files. > I'm new to storing files in the database. This will be my first > experience. > You may want to use large objects to stor

Re: [GENERAL] Store base64 in database. Use bytea or text?

u query will like that: -- Pseudo-code INSERT INTO img (dat) VALUES (BYTEA_ESCAPED_FROM_PHP); > > > On Tue, Jan 25, 2011 at 10:58 PM, Dmitriy Igrishin > wrote: > > > > > > 2011/1/26 Andre Lopes > >> > >> Thanks for all the reply's. > >>

Re: [GENERAL] Moving from SQL Anywhere to PostGres - First Time

2011/1/25 Jasen Betts > On 2011-01-20, Robert Paresi wrote: > > Hello, > > > > We have 700 user install base using Sybase SQL Anywhere 9.02 > > > > We are looking at migrating these installations over to PostGres > > > 1. Very Very Short Answer Please - why should we? > > postgres is the best d

Re: [GENERAL] Store base64 in database. Use bytea or text?

How would you like to cache them ? On a file system of client (e.g., WEB-server) ? Why would you like to cache them ? > > > > > On Wed, Jan 26, 2011 at 2:09 PM, Bill Moran > wrote: > > In response to Dmitriy Igrishin : > > > >> 2011/1/26 Andre Lopes > &

Re: [GENERAL] Additional Grants To SuperUser?

2011/2/4 Carlos Mennens > On Fri, Feb 4, 2011 at 2:18 PM, David Johnston wrote: > > Not to be smart about it but you could just logon as carlos (or a > different > > superuser you create for this purpose) and issue "Create Database xxx" > and > > "Create Role xxx" statements and see whether they

Re: [GENERAL] Additional Grants To SuperUser?

2011/2/7 Carlos Mennens > On Fri, Feb 4, 2011 at 5:08 PM, Dmitriy Igrishin > wrote: > > These all (SUPERUSER, CREATEDB, SUPERUSER) are role attributes. > > By performing ALTER ROLE postgres NOSUPERUSER it is possible to > > turn role with a superuser status into a r

Re: [GENERAL] How to retrieve values of 'array' field using C library (libpq)

Hey Anton, 2011/2/9 Anton Maksimenkov > Hi. > > I use libpq in my program. And I can retrieve int and symbolic fields > as simple as > > > int unameFN, moneyFN... > char *unamePTR, *moneyPTR... > > unameFN = PQfnumber(res, "uname"); > moneyFN = PQfnumber(res, "money"); > unamePTR = PQgetvalue(re

Re: [GENERAL] Size of varchar in an array

Hey, 2011/2/8 RW Shore > I'm using the following type definition: > create type typedef.BASIC_PEDIGREE as ( > DATE_ADDED TIMESTAMP, > DESCRIPTION VARCHAR(128) [10] > )\c > > I understand that PostgreSQL doesn't enforce the array length [10]. > However, I'd like to write an applic

Re: [GENERAL] SELECT INTO array[i] with PL/pgSQL

Hey, 2011/2/8 Edoardo Panfili > On 07/02/11 22.15, Julia Jacobson wrote: > >> Dear PostgreSQL community, >> >> Please consider the following minimal example: >> >> CREATE TABLE example (row_id SERIAL, value TEXT); >> INSERT INTO example(value) VALUES ('val1'); >> INSERT INTO example(value) VALUE

Re: [GENERAL] SELECT INTO array[i] with PL/pgSQL

2011/2/14 Merlin Moncure > On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson > wrote: > > Dear PostgreSQL community, > > > > Please consider the following minimal example: > > > > CREATE TABLE example (row_id SERIAL, value TEXT); > > INSERT INTO example(value) VALUES ('val1'); > > INSERT INTO examp

Re: [GENERAL] SELECT INTO array[i] with PL/pgSQL

2011/2/15 Merlin Moncure > On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin > wrote: > > 2011/2/14 Merlin Moncure > >> > >> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson > > >> wrote: > >> > Dear PostgreSQL community, >

Re: [GENERAL] SELECT INTO array[i] with PL/pgSQL

2011/2/16 Merlin Moncure > On Tue, Feb 15, 2011 at 7:51 AM, Dmitriy Igrishin > wrote: > > > > > > 2011/2/15 Merlin Moncure > >> > >> On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin > >> wrote: > >> > 2011/2/14 Merlin Moncure

[GENERAL] Disconnecting idle connections on timeout.

Hey general@, I want the server automatically disconnects idle connections on timeout. I've tried to find setting parameter for such setting, but failed. Am I missing something or it is not implemented in Postgres? -- // Dmitriy.

Re: [GENERAL] why is there no TRIGGER ON SELECT ?

Hey Melvin, 2011/2/22 Melvin Davidson > > Other than "It's currently not available", can anyone provide a logical > explanation of why triggers cannot be implemented for SELECT statements, or > rules for SELECT must be DO INSTEAD SELECT? > > PostgreSQL was derived from Ingres, and Ingres had a n

Re: [GENERAL] why is there no TRIGGER ON SELECT ?

2011/2/22 Melvin Davidson > Dmitriy > > > >Why not use function which returns table and wrap the > >logging (auditing) code in it ? > > Because to use a trigger function, you need a trigger, and as previously > stated, you cannot have a trigger on select. The same applies for a rule. > Yes, you c

Re: [GENERAL] array size

Hey, 2011/2/24 akp geek > Hi all - > > I am trying to find the number of elements in the array. > Right now I am using array_upper and array_lower. Is there any other way of > getting the number of elements? > You may use array_length() function, e.g. dmitigr=> SELECT array_length(

Re: [GENERAL] Dynamic binding in plpgsql function

2011/3/2 Merlin Moncure > On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule > wrote: > > Hello > > > > 2011/3/2 Pierre Racine : > >> Hi, > >> > >> I would like to write a generic plpgsql function with a text parameter > being a callback function name so that my general function can call this > callb

  1   2   3   >