Re: [GENERAL] storing postgres data on dropbox

On Sun, Jun 18, 2017 at 13:16:16 +, Martin Mueller wrote: Why not a PostgreSQL-database somewhere in the cloud? Good question, but it's a question of money and performance. I used MySQL for many years and then moved a dataset to an instance on AWS. The

Re: [GENERAL] JSON to INT[] or other custom type

On Sun, Jun 11, 2017 at 22:35:14 +0100, Rory Campbell-Lange wrote: I'm hoping, in the plpgsql function, to unfurl the supplied json into a custom type or at least an array of ints, and I can't work out how to do that. select * from json_array_elements_text('[[0,

Re: [GENERAL] Limiting DB access by role after initial connection?

On Fri, Jun 09, 2017 at 21:14:15 -0700, Ken Tanzer <ken.tan...@gmail.com> wrote: On Fri, Jun 9, 2017 at 5:38 PM, Bruno Wolff III <br...@wolff.to> wrote: Seems to me they are separate issues. App currently has access to the password for accessing the DB. (Though I could change t

Re: [GENERAL] Limiting DB access by role after initial connection?

On Thu, Jun 08, 2017 at 22:37:34 -0700, Ken Tanzer wrote: My approach was to have the initial connection made by the owner, and then after successfully authenticating the user, to switch to the role of the site they belong to. After investigation, this still seems

Re: [GENERAL] [OT] Help: stories of database security and privacy

On Tue, Apr 11, 2017 at 21:48:58 +0200, Lifepillar wrote: I'd like to take the opportunity to also engage students about the topic of privacy (or lack thereof). So, I am here to ask if you have interesting/(in)famous stories to share on database security/privacy

[GENERAL] Beta rpms for Fedora are missing (at this time)

This is probably a temporary build problem, but I thought mentioning here might get it fixed faster in case it hasn't already been noticed. https://download.postgresql.org/pub/repos/yum/testing/10/fedora/fedora-25-x86_64/ should have rpms but doesn't. I am using test rpms I got from there about

Re: [GENERAL] Is there a point to having both a normal gist index and an exclude index?

On Wed, Apr 05, 2017 at 12:11:09 -0600, Rob Sargent <robjsarg...@gmail.com> wrote: On 04/05/2017 12:04 PM, Bruno Wolff III wrote: On Wed, Apr 05, 2017 at 00:05:31 -0400, Tom Lane <t...@sss.pgh.pa.us> wrote: Bruno Wolff III <br...@wolff.to> writes: ... I create both a

Re: [GENERAL] Is there a point to having both a normal gist index and an exclude index?

On Wed, Apr 05, 2017 at 00:05:31 -0400, Tom Lane <t...@sss.pgh.pa.us> wrote: Bruno Wolff III <br...@wolff.to> writes: ... I create both a normal gist index and an exclude index using the following: CREATE INDEX contains ON iplocation USING gist (network inet_ops); ALTER TABLE iplo

[GENERAL] Is there a point to having both a normal gist index and an exclude index?

I am trying to load a database with about 3.5 million records relating netblocks to locations. I currently don't know whether or not any of the netblocks overlap. If they don't, then I can simplify queries that find the locations of IP addresses. I create the table as follows: DROP TABLE IF

Re: [GENERAL] Unique constraint on field inside composite type.

On Wed, Aug 17, 2016 at 23:02:53 -0700, Silk Parrot wrote: Hi,     I am trying to model a social login application. The application can support multiple login providers. I am thinking of creating a custom type for each provider. e.g. CREATE TABLE user (     uuid UUID

Re: [GENERAL] endash not a graphic character?

On Sun, Aug 21, 2016 at 14:24:16 -0400, Tom Lane wrote: Unfortunately, these particular characters are U+2013 and U+2014 so you lose. Thanks for saving me some time, as it would have taken me quite a while to figure that out. I'll adjust the constraint so that good

Re: [GENERAL] endash not a graphic character?

On Sun, Aug 21, 2016 at 12:30:21 -0500, Bruno Wolff III <br...@wolff.to> wrote: I should also try the equivalent test in perl to see if it is more likely tied to the unicode implementation on my system or if it appears to be Postgres specific. It looks like my locale may not be bei

Re: [GENERAL] endash not a graphic character?

On Sun, Aug 21, 2016 at 08:12:23 +1000, rob stone wrote: You can't use (emdash) or (endash)? Or their hex equivalents. See the Unicode chart. By the way, those aren't the correct codes. That only works if your code treats iso-5589-1 code points as windows 1252 code

Re: [GENERAL] endash not a graphic character?

On Sun, Aug 21, 2016 at 08:12:23 +1000, rob stone wrote: You can't use (emdash) or (endash)? Or their hex equivalents. See the Unicode chart. I am not the source of the data, but I can special case them one way or the other. However I am wondering about my use of

[GENERAL] endash not a graphic character?

I was surprised to find endash and emdash were not graphic characters in en_US. I'm not sure if this is correct behavior, a bug in postgres or a bug in my OS' collation definitions? For example: Dash: area=> select '-' ~ '[[:graph:]]' collate "en_US"; ?column? -- t (1 row) Endash:

Re: [GENERAL] Postgresql-fdw

On Sun, May 22, 2016 at 23:38:43 -0700, John R Pierce wrote: If you want to use postgres to query this data efficiently, you really should import this data into postgres tables, properly indexed for the sorts of queries you wish to do. And it isn't that hard to script

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, May 09, 2016 at 22:43:53 -0400, "D'Arcy J.M. Cain" wrote: Of course PHP scripts have to run as nobody so I have no choice other than to have them store passwords in various config.php files but PHP users are used to that. I would like to fix that but that's a war for

Re: [GENERAL] Using both ident and password in pg_hba.conf

On Mon, May 09, 2016 at 13:39:48 -0700, Adrian Klaver wrote: The above does not make sense to me. Maybe I am not understanding if you mean connect and login as the same thing or not? I could see connecting as 'nobody' and then doing SET ROLE as user. Or connect as

Re: [GENERAL] Recurring and non recurring events.

On Sat, Dec 26, 2015 at 23:03:30 +1100, Kevin Waterson wrote: Thanks, as I am new to postgres, I was unaware of this function. To go with this, I guess I will need a table with which to store intervals, start and end dates? There is are built in range types that

Re: [GENERAL] earthdistance

On Tue, Aug 20, 2013 at 20:38:51 +0200, Olivier Chaussavoine olivier.chaussavo...@gmail.com wrote: I also look at cube extension, but the built in type box - a couple of points - does not require any extension and has a GIST index. It can be used to represent a rectangle on the domain

Re: [GENERAL] earthdistance

On Sat, Aug 10, 2013 at 12:18:48 +0200, Olivier Chaussavoine olivier.chaussavo...@gmail.com wrote: I did not found any geographic indexing with earthdistance, and need it. Some of the earthdistance stuff is based on cube which does have indexing. I don't know how well that indexing works

Re: [GENERAL] seeking SQL book recommendation

On Wed, Jan 23, 2013 at 15:56:10 -0700, Scott Ribe scott_r...@elevated-dev.com wrote: For a client who needs to learn how to query the db: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long

Re: [GENERAL] noobie question

On Thu, Jan 24, 2013 at 14:03:33 -0500, Steve Clark scl...@netwolves.com wrote: It is really called rule_num and relates to in what order firewall rules are applied. And it used to allow the user to place the firewall rules where they want them in relation to other rules. If you just need

Re: [GENERAL] PostgreSQL training recommendations?

On Tue, Oct 16, 2012 at 16:24:08 -0300, Thalis Kalfigkopoulos tkalf...@gmail.com wrote: Also IMHO another difficulty the manual poses is that the reader doesn't have a way to confirm his level of understanding after reading a chapter. It isn't too hard to play with a toy database. I

Re: [GENERAL] Encryption - searching and sorting

On Thu, May 03, 2012 at 15:42:00 +0200, David Welton dav...@dedasys.com wrote: Thoughts? Peter Wayner wrote a book Translucent Databases that has some techniques for helping solve problems like this. It won't magically solve your problem, but might give you some more ideas on how you can do

Re: [GENERAL] GROUP BY or alternative means to group

On Mon, Mar 12, 2012 at 16:18:05 -0400, Michael Gould mgo...@isstrucksoftware.net wrote: You need to include all columns that are not aggregrative columns in the group by. Even though that is the standard it is a pain to list all columns even if you don't need them In later versions of

Re: [GENERAL] GROUP BY or alternative means to group

On Mon, Apr 09, 2012 at 13:55:04 -0400, Michael Gould mgo...@isstrucksoftware.net wrote: Thanks that is a help. I would be nice if any key could be used as those are normally the things I would do group by's This is what the 9.1 documentation says: When GROUP BY is present, it is not valid

[GENERAL] Bad link to beta2 source

The link Download 9.1 Beta 2 source code on http://www.postgresql.org/developer/beta points to http://www.postgresql.org/ftp/source/v9.1beta1 instead of http://www.postgresql.org/ftp/source/v9.1beta2 . -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] Interesting comments about fsync on Linux

I was looking for some information on how write barriers interact with software raid and ran across the following kernel thread referenced on LWN. The suggestion is that fsync isn't really safe on Linux as it is currently implented. (The thread was from February 2008, so it probably still

Re: [GENERAL] Documentation fix regarding atan2

On Wed, Sep 05, 2007 at 10:37:18 +1000, Andrew Maclean [EMAIL PROTECTED] wrote: In Table 9.4 of the documentation atan2 is described as follows: atan2(*x*, *y*) inverse tangent of *x*/*y* I am sure it should read as: atan2(*y*, x) inverse tangent of y/x Aren't those two statements

Re: [GENERAL] greatest/least semantics different between oracle and postgres

On Sat, Jun 30, 2007 at 09:29:23 +0200, Pavel Stehule [EMAIL PROTECTED] wrote: Hello, I have not Oracle, so I cannot test it, but PostgreSQL implementation respect Oracle: http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php Maybe that reference was for an earlier version

[GENERAL] greatest/least semantics different between oracle and postgres

The following is just FYI. I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and noticed that it returned null if ANY of the arguments were null. Out of curiosity I checked postgres' definition of that function and found that it returns null only if ALL of the arguments are

Re: [GENERAL] greatest/least semantics different between oracle and postgres

On Sat, Jun 30, 2007 at 00:15:42 -0400, Tom Lane [EMAIL PROTECTED] wrote: Andrej Ricnik-Bay [EMAIL PROTECTED] writes: On 6/30/07, Bruno Wolff III [EMAIL PROTECTED] wrote: I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and noticed that it returned null if ANY

Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)

On Thu, May 10, 2007 at 20:43:20 -0500, John Gateley [EMAIL PROTECTED] wrote: Sorry if this is a FAQ, I did search and couldn't find much. I need to make my Postgresql installation fault tolerant. I was imagining a RAIDed disk array that is accessible from two (or multiple) computers, with

Re: [GENERAL] simple coordinate system

On Fri, Mar 16, 2007 at 15:55:15 +0100, Robin Ericsson [EMAIL PROTECTED] wrote: On 3/16/07, Tom Lane [EMAIL PROTECTED] wrote: Robin Ericsson [EMAIL PROTECTED] writes: Yes, I've looked at those, I was thinking that point looked like a good type, but it's only 2d, so maybe I need a hint on

Re: [GENERAL] Is This A Set Based Solution?

On Mon, Mar 12, 2007 at 11:15:01 -0700, Stefan Berglund [EMAIL PROTECTED] wrote: I have an app where the user makes multiple selections from a list. I can either construct a huge WHERE clause such as SELECT blah blah FROM foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could

Re: [GENERAL] SQL Question - Group By and % results per row

On Mon, Mar 12, 2007 at 12:53:11 -0700, Mike [EMAIL PROTECTED] wrote: How do I get access to the total of all clicks on per row basis so I can divide it? The only solution that comes to my mind is create a subquery that does a (select count(*) from... where... ) of the original grouped by

Re: [GENERAL] security permissions for functions

On Fri, Mar 09, 2007 at 01:07:23 -0500, Tom Lane [EMAIL PROTECTED] wrote: Certainly --- the point here is merely that that isn't the *default* behavior. We judged quite some time ago that allowing public execute access was the most useful default. Perhaps that was a bad choice, but I

Re: [GENERAL] Setting week starting day

On Thu, Mar 08, 2007 at 20:32:22 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: As I said, it is easy with a function. :-) I was just curious to see if we had something like Oracle's NEXT_DAY function or something like what I described (SET BOW=4; --

Re: [GENERAL] one-to-one schema design question and ORM

On Fri, Mar 09, 2007 at 10:06:52 -0500, Rick Schumeyer [EMAIL PROTECTED] wrote: From a business rules perspective: Some users are not employees (like an admin user) Some employees are not users I can think of two ways to do this: 1) a 1-1 relationship where the user table contains

Re: [GENERAL] Setting week starting day

On Fri, Mar 09, 2007 at 14:59:35 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: It is not hard to calculate, as you can see... but it would be nice if date_trunc('week', date) could do that directly. Even if it became date_trunc('week', date, 4) or date_trunc('week', date, 'Wednesday') it

Re: [GENERAL] Setting week starting day

On Fri, Mar 09, 2007 at 16:44:57 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: Bruno Wolff III [EMAIL PROTECTED] writes: On Fri, Mar 09, 2007 at 14:59:35 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: It is not hard to calculate, as you can see... but it would be nice if date_trunc('week

Re: [GENERAL] Setting week starting day

On Fri, Mar 09, 2007 at 20:13:11 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: Bruno Wolff III [EMAIL PROTECTED] writes: No, it has to be inside the function so that the modular arithmetic is applied to it. Then there's the error I've shown from your command. Can you give me a working

Re: [GENERAL] Setting week starting day

On Fri, Mar 09, 2007 at 23:07:26 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: But how to get the date if the first day of the week is a Wednesday? This example is like the ones I've sent with separate queries that needed being combined -- in a function, probably -- to get the desired

Re: [GENERAL] Setting week starting day

On Sat, Mar 10, 2007 at 00:03:04 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: If I run this query: select date_trunc('week', '2007-03-08'::date + 5); it fails even for that date. The correct answer, would be 2007-03-07 and not 2007-03-12. I want the first day of the week to be

Re: [GENERAL] Can I getting a unique ID from a select

On Mon, Mar 05, 2007 at 17:07:25 -0800, Timasmith [EMAIL PROTECTED] wrote: create view myview as select rownum, t1.field, t2.field from tableOne t1, tableTwo t2 where t1.key = t2.fkey Multiple rows with the same key renders Hibernate useless as it caches the 'row

Re: [GENERAL] Can I getting a unique ID from a select

On Sat, Mar 03, 2007 at 16:46:45 -0800, Timasmith [EMAIL PROTECTED] wrote: On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote: On Thu, Mar 01, 2007 at 06:16:02 -0800, Timasmith[EMAIL PROTECTED] wrote: create view myview as select rownum, t1.field, t2.field from

Re: [GENERAL] Can I getting a unique ID from a select

On Sat, Mar 03, 2007 at 18:12:19 -0600, Bruno Wolff III [EMAIL PROTECTED] wrote: On Thu, Mar 01, 2007 at 06:16:02 -0800, Timasmith [EMAIL PROTECTED] wrote: I am using hibernate, using a view like a read only table and I need a primary key each time a select is issued. create view

Re: [GENERAL] Can I getting a unique ID from a select

On Thu, Mar 01, 2007 at 06:16:02 -0800, Timasmith [EMAIL PROTECTED] wrote: I am using hibernate, using a view like a read only table and I need a primary key each time a select is issued. create view myview as select rownum, t1.field, t2.field from tableOne t1, tableTwo t2 where t1.key =

Re: [GENERAL] Why does group by need to match select fields?

On Wed, Feb 28, 2007 at 16:19:02 -0800, Omar Eljumaily [EMAIL PROTECTED] wrote: select max(amount), payee, id from checks group by payee; Why won't the above work? Is there another way to get the id for the record with the highest amount for each payee? While the DISTINCT ON approach is

Re: [GENERAL] Esay question, about the numeric format

On Thu, Feb 22, 2007 at 12:20:12 +0100, Rafa Comino [EMAIL PROTECTED] wrote: Hi every body I have this query SELECT 20.00::numeric(38,2) and postgre gives me 20, i need that postgre gives me 20.00 What can i do? i suppose this must be easy, but i dont find how to do ir thanks every body

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

On Fri, Feb 23, 2007 at 18:14:25 -0500, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: On friday we upgraded a critical backend server to postgresql 8.2 running on fedora core 4. Umm ... why that particular choice of OS? Red Hat dropped update support for

Re: [GENERAL] [HACKERS] urgent: upgraded to 8.2, getting kernel panics

On Mon, Feb 26, 2007 at 15:57:02 +0200, Devrim GUNDUZ [EMAIL PROTECTED] wrote: Upgrading OS will probably solve your problem; since there is no way to upgrade FC4 kernel unless you want to compile kernel source on your system. And good luck with that. Fedora still back patches stuff from

Re: [GENERAL] change data type int4 to serial

On Wed, Feb 21, 2007 at 11:20:38 -0600, Seb [EMAIL PROTECTED] wrote: Checking the results in pgadmin, this proceeded fine, but now that I want to specify the primary and foreign keys in the tables, I see that the columns needed for this were imported as int4 data type. I would like these

Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

On Sun, Feb 18, 2007 at 12:29:17 +0100, Karsten Hilbert [EMAIL PROTECTED] wrote: The date-of-birth field in our table holding patients is of type timestamp with time zone. One of our patient search queries uses the date-of-birth field to find matches. Since users enter day, month, and year

Re: [GENERAL] Why *exactly* is date_trunc() not immutable ?

On Mon, Feb 19, 2007 at 20:48:07 +0100, Karsten Hilbert [EMAIL PROTECTED] wrote: What time of day were you born ? http://en.wikipedia.org/wiki/Apgar What is the technical reason that makes you wonder ? Because it would make doing the queries simpler. If you aren't collecting the

Re: [GENERAL] requests / suggestions to help with backups

On Thu, Feb 15, 2007 at 22:39:13 -0500, Lou Duchez [EMAIL PROTECTED] wrote: 1) grant select on database ... or, hypothetically, grant select on cluster. The goal would be to create a read-only PostgreSQL user, one who can read the contents of an entire database (or even the entire

Re: [GENERAL] Recover anything from dropped database?

On Thu, Feb 15, 2007 at 10:53:48 -0500, John D. Burger [EMAIL PROTECTED] wrote: I presume from the near-deafening silence there's nothing else I can do, which is no surprise, but I'd still like confirmation about how to restore the backup. (It turns out I can recover the changes

Re: [GENERAL] temp tables in functions?

On Wed, Feb 07, 2007 at 20:40:09 -0800, jws [EMAIL PROTECTED] wrote: Having developed a complex query, I want to wrap it up as a function so that it can take a parameter and return a set of rows. This query is currently written as multiple sql statements that create a few interstitial temp

Re: [GENERAL] ERROR: missing cache data for cache id 27

On Sun, Feb 04, 2007 at 23:43:48 -0800, David Fetter [EMAIL PROTECTED] wrote: On Sun, Feb 04, 2007 at 03:18:07PM -0200, Jorge Godoy wrote: Tom Lane [EMAIL PROTECTED] writes: Jorge Godoy [EMAIL PROTECTED] writes: I'm using PostgreSQL 8.1.4 and psql 8.1.4 as well. This was fixed

Re: [GENERAL] How to allow users to log on only from my application not from pgadmin

On Fri, Feb 02, 2007 at 07:20:04 +0900, Paul Lambert [EMAIL PROTECTED] wrote: How? Use a debugger. If it is encrypted within the source code then the only way to steal the credentials would be to reverse engineer the application. And if someone is going to do that then you can be

Re: [GENERAL] Defining and Using variables in a postgres function

On Fri, Feb 02, 2007 at 17:18:39 +0100, Alban Hertroys [EMAIL PROTECTED] wrote: You can do this: INSERT INTO tbl_email (option_public, agency, id) SELECT $1, $2, MAX(id) + 1 FROM xyz; I just realize you don't so much need a lock, you need a serialized transaction. I can't say I

Re: [GENERAL] How to allow users to log on only from my application not from pgadmin

On Thu, Feb 01, 2007 at 10:24:51 +0900, Paul Lambert [EMAIL PROTECTED] wrote: If you hide the database username and password within your application (i.e. encrypted within the source code) so they cannot see the credentials that you connect to the database with internally then they have

Re: [GENERAL] What's the best way to index this table for speed?

On Thu, Feb 01, 2007 at 10:42:30 -0800, Carl Lerche [EMAIL PROTECTED] wrote: How can I index 2 dimensional data (latitude / longitude) with a status_id column too (integer) so that I can perform the following query as fast as possible: SELECT * FROM profiles WHERE status_id = 1 AND

Re: [GENERAL] How to allow users to log on only from my application not from pgadmin

On Sun, Jan 28, 2007 at 23:46:27 +0200, Andrus [EMAIL PROTECTED] wrote: My application implements field and row level security. I have custom table of users where user privileges are described. However user can login directly to database using pgAdmin. This bypasses the security. How to

Re: [GENERAL] Any Plans for cross database queries on the same server?

On Tue, Jan 30, 2007 at 16:43:14 -0800, Richard Troy [EMAIL PROTECTED] wrote: be better - and once were. (Example, anyone who thinks man pages are great has obviously got a very limited experience from which to base their opinion!) ... As a practical matter today we mostly have a choice of

Re: [GENERAL] Load balancing across disks

On Mon, Jan 29, 2007 at 11:50:35 +0900, Paul Lambert [EMAIL PROTECTED] wrote: In order to balance disk load and ensure faster data access, my current SQL server setup has the data spread across 3 physical disk devices. One question I would like to know which I can't find in the

Re: [GENERAL] PostgreSQL 9.0

On Jan 29, 2007, at 4:27 PM, Karen Hill wrote: I was just looking at all the upcoming features scheduled to make it into 8.3, and with all those goodies, wouldn't it make sense for this to be a 9.0 release instead of an 8.3? It looks like postgresql is rapidly catching up to oracle if 8.3

Re: [GENERAL] PostgreSQL 9.0

On Mon, Jan 29, 2007 at 15:51:54 -0800, Rich Shepard [EMAIL PROTECTED] wrote: On Tue, 30 Jan 2007, Michael Glaesemann wrote: It was *discussed*. 8.1 to 8.2 (as does any move from M.x to M.y where x ­ y) requires a dump and reload. Michael, That's what I thought. However, it never

Re: [GENERAL] column limit

On Thu, Jan 25, 2007 at 10:47:50 -0700, Isaac Ben [EMAIL PROTECTED] wrote: The data is gene expression data with 20,000 dimensions. Part of the project I'm working on is to discover what dimensions are truly independent. But to start with I need to have all of the data available in a

Re: [GENERAL] column limit

On Thu, Jan 25, 2007 at 08:34:08 -0700, Isaac Ben [EMAIL PROTECTED] wrote: Hi, I'm trying to create a table with 20,000 columns of type int2, but I keep getting the error message that the limit is 1600. According to this message

Re: [GENERAL] sequence increment jumps?

On Thu, Jan 25, 2007 at 12:33:51 -0500, John Smith [EMAIL PROTECTED] wrote: guys, i inserted 1 record into my database (default nextval('sequencename'::regclass) where (start 1 increment 1)). then i tried to insert 1 other record twice but both those inserts failed because of a domain check

Re: [GENERAL] Installing PostgreSQL under Cpanel

On Wed, Jan 24, 2007 at 02:17:53 +0800, Erick Papadakis [EMAIL PROTECTED] wrote: I was just looking at the ident/trust/etc authentication banter from pgsql docs. Couldn't make out what greek was on there. When I jostled a bit, and finally understood it, and really wanted to write it in

Re: [GENERAL] Example of RETURNING clause to get auto-generated keys from INSERT

On Tue, Jan 23, 2007 at 23:19:47 -0600, Adam Rich [EMAIL PROTECTED] wrote: And your normal query would be this: INSERT into mytable (id,value) values (1,foo),(2,bar); Your new query would be like this: INSERT into mytable (id,value) values (1,foo),(2,bar) RETURNING id; Note that

Re: [GENERAL] indexing primary and foreign keys w/lookup table

On Wed, Jan 24, 2007 at 20:14:07 -0800, Neal Clark [EMAIL PROTECTED] wrote: I was wondering...I currently have indexes on the primary key id and foreign key id's for tables that resemble the following. Is this a good idea/when would it benefit me? I don't want waste a lot of

Re: [GENERAL] Converting 7.x to 8.x

On Thu, Jan 25, 2007 at 15:43:19 +1100, Chris [EMAIL PROTECTED] wrote: Carlos wrote: What would be the faster way to convert a 7.4.x database into an 8.x database? A dump of the database takes over 20 hours so we want to convert the database without having to do a dump and resptore.

Re: [GENERAL] Password encryption method

On Tue, Jan 23, 2007 at 09:44:28 +0100, Bertram Scharpf [EMAIL PROTECTED] wrote: Hi Bruno, Am Montag, 22. Jan 2007, 23:11:41 -0600 schrieb Bruno Wolff III: If the web server is running on the same machine as the DB, then consider using ident authentication and connecting using domain

Re: [GENERAL] Password encryption method

On Tue, Jan 23, 2007 at 09:01:56 -0800, Richard Troy [EMAIL PROTECTED] wrote: On Mon, 22 Jan 2007, Bruno Wolff III wrote: On Mon, Jan 22, 2007 at 20:25:48 +0100, Bertram Scharpf [EMAIL PROTECTED] wrote: What I want to do is the following: 1. Login in from a program

Re: [GENERAL] Postgresql.conf

On Tue, Jan 23, 2007 at 10:12:13 -0500, Brandon Aiken [EMAIL PROTECTED] wrote: Out of curiosity, has the COUNT(*) with no WHERE clause slowness been fixed in 8.x? Or is it still an issue of there's no solution that won't harm aggregates with WHERE clauses? Probably not in the sense that you

Re: [GENERAL] Postgresql.conf

On Tue, Jan 23, 2007 at 14:15:23 -0500, Jeremy Haile [EMAIL PROTECTED] wrote: But there are ways that we could optimize count(*) queries for specific circumstances right? Obviously this isn't trivial, but I think it would be nice if we could maintain a number of rows count that could be used

Re: [GENERAL] Date Questions

On 01/23/07 17:22, Robert Sanford wrote: January 07 of 2007 is a Sunday. Based on the documentation I would expect that date to be the first day of the second week of the year 2007. That's not what I'm getting. When I run: Read the 'week' documentation carefully. ISO weeks start on Mondays.

Re: [GENERAL] Password encryption method

On Sun, Jan 21, 2007 at 15:16:37 +0200, Andrus [EMAIL PROTECTED] wrote: No, the tables would be on the server, the same as was already being done. Using a separate table makes it more future proof. To access tables in server, you need to login into server. To login into server, you need

Re: [GENERAL] Password encryption method

On Mon, Jan 22, 2007 at 20:25:48 +0100, Bertram Scharpf [EMAIL PROTECTED] wrote: What I want to do is the following: 1. Login in from a program on a client as a particualar user. For this case you shouldn't need to do anything tricky as long as the user is login in as themselves. Just

Re: [GENERAL] Password encryption method

On Fri, Jan 19, 2007 at 09:31:49 +0100, Bertram Scharpf [EMAIL PROTECTED] wrote: Hi, looking at the source code I find out that this works: sandbox=# create role joe login password 'verysecret'; CREATE ROLE sandbox=# create function validate_user_8_1(text,text) returns boolean

Re: [GENERAL] Password encryption method

On Fri, Jan 19, 2007 at 18:24:32 +0200, Andrus [EMAIL PROTECTED] wrote: It might make more sense to use your own table of users and hashed passwords rather than postgres'. This would depend somewhat on the overlap of users who are using your application and those who connect directly

Re: [GENERAL] Multiple column index question.

On Fri, Jan 19, 2007 at 15:22:12 -0500, Jan Muszynski [EMAIL PROTECTED] wrote: If I have an index that's composed of 2 columns: Index index1 on tableA (foo,bar) and I then: Select cola, colb from tableA where foo=value Will index1 still be used, or am I looking at a seqscan

Re: [GENERAL] Multiple column index usage question

On Fri, Jan 19, 2007 at 18:20:47 -0500, Jeremy Haile [EMAIL PROTECTED] wrote: That's interesting. So if you have a composite index on two columns, is there much of a reason (usually) to create single indexes on each of the two columns? I guess the single indexes might be slightly faster

Re: [GENERAL] [1/2 OFF] Varlena.com inaccessible from .br (Blocked?)

On Wed, Jan 17, 2007 at 07:54:55 -0200, Jorge Godoy [EMAIL PROTECTED] wrote: Bruno Wolff III [EMAIL PROTECTED] writes: The don't block a host I used to access. And not on several different Brazilian networks from different carriers. The traffic stops at speakeasy from my house (ADSL from

Re: [GENERAL] How I can read-back a serial value just inserted?

On Mon, Jan 01, 2007 at 18:46:26 +0100, dfx [EMAIL PROTECTED] wrote: Dear Sirs, my question is very simple: when I insert a row whith a serial field, a value is automatically generated; how can I know this value, strictly of my row, without the risk of to read the value of another

Re: [GENERAL] Performance with very large tables

On Mon, Jan 15, 2007 at 11:52:29 +0100, Jan van der Weijde [EMAIL PROTECTED] wrote: Does anyone have a suggestion for this problem ? Is there for instance an alternative to LIMIT/OFFSET so that SELECT on large tables has a good performance ? Depending on exactly what you want to happen, you

Re: [GENERAL] Performance with very large tables

On Tue, Jan 16, 2007 at 12:06:38 -0600, Bruno Wolff III [EMAIL PROTECTED] wrote: Depending on exactly what you want to happen, you may be able to continue where you left off using a condition on the primary key, using the last primary key value for a row that you have viewed, rather than

Re: [GENERAL] Coercion in PGSQL?

On Tue, Jan 16, 2007 at 04:14:26 -0800, Max Ueda [EMAIL PROTECTED] wrote: Some results made me think of coercion between int types. For example, atributing a int8 value into a int2 variable. Does it really happen (coercion)? Is the int8 value automatically converted into int2, or an error

Re: [GENERAL] [1/2 OFF] Varlena.com inaccessible from .br (Blocked?)

On Tue, Jan 16, 2007 at 19:47:28 -0200, Jorge Godoy [EMAIL PROTECTED] wrote: There's been a while since I could use the website for the last time because it looks like Brazilian networks are blocked somewhere after routers from speakeasy.net (220.ge-3-0.er1.sfo1.speakeasy.net from this

Re: [GENERAL] Remove duplicate rows

On Thu, Jan 11, 2007 at 18:51:57 +0100, Jiří Němec [EMAIL PROTECTED] wrote: Hello, I need to remove duplicates rows from a subquery but order these results by a column what is not selected. There are logically two solutions but no works. SELECT DISTINCT sub.foo FROM (SELECT ...) AS sub

Re: [GENERAL] generate_series with month intervals

On Thu, Jan 11, 2007 at 20:07:29 +0100, Marcus Engene [EMAIL PROTECTED] wrote: Hi list, I'd like to generate the latest year dynamically with generate_series. This select works day wise: This works but looks grotesque: select distinct date_trunc ('month', now()::date + s.a)::date

Re: [GENERAL] SELECT INTO using Views?

On Tue, Jan 09, 2007 at 10:10:46 -0600, Jeanna Geier [EMAIL PROTECTED] wrote: If I cast the entire operation to an INT: (a.area * su.units_per_sqfoot::integer)::integer AS area_sq or by (a.area * su.units_per_sqfoot)::integer AS area_sq, I'm getting an 'ERROR: integer out of range'

Re: [GENERAL] Questions about horizontal partitioning

On Tue, Jan 09, 2007 at 08:28:29 -0500, Chander Ganesan [EMAIL PROTECTED] wrote: It would. A query that uses an inner join implies that a matching entry must exist in both tables - so the join must occur, otherwise you could be returning rows that don't satisfy the join condition. While

Re: [GENERAL] Newbie Constraint ?

On Mon, Jan 08, 2007 at 14:55:29 -0600, Jeanna Geier [EMAIL PROTECTED] wrote: Not quite sure what the: CONSTRAINT Relationship182 is exactly... can anyone help me with this one? Haven't seen this one yet... It is the name of that particular constraint. You would use that if you were

Re: [GENERAL] COPY FROM and sequences

On Mon, Jan 08, 2007 at 20:20:42 -0500, Matthew Terenzio [EMAIL PROTECTED] wrote: Is it true that you can't use COPY FROM to fill a table with a SERIAL type column? Or rather, how does one approach that situation most effectively? In older versions of postgres you couldn't, in recent

Re: [GENERAL] Questions about horizontal partitioning

On Tue, Jan 09, 2007 at 10:33:52 -0500, Tom Lane [EMAIL PROTECTED] wrote: No, that's still not right. With a LEFT JOIN you know that each row of the narrow table will produce at least one row in the join view. What you don't know is whether the row could produce more than one join row

  1   2   3   4   5   6   7   >