[GENERAL] large text fields
when ever I've dealt with a field that would require more text than 250 characters, I've always used the 'text' datatype. It appears I've hit an example where this won't work. users are trying to insert what amounts to about 2 pages worth of text and I'm seeing this error: PostgresSQL query failed: ERROR: Tuple is too big: size 1 Are there any other options other than using a BLOB datatype ? which I'm completely unfamiliar with. Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Production | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED] | Shreveport, LA 71101 | http://www.shreve.net -
[GENERAL] problem with date range
have a query like so: select crimeid, areaid, sum( CASE when dateof='1-8-2000' then total else 0 end) as crimes1 from stats GROUP BY crimeid, areaid; This works and gives me results like I want like so: crimeid|areaid|crimes1 ---+--+--- 4| 2| 0 5| 2| 0 5| 3| 20 6| 2| 0 7| 2| 0 8| 2| 0 9| 2| 0 10| 2| 0 11| 2| 0 12| 2| 0 But what I really need would be the same query as above but give me results which show total crimes for the Current year to date, and also total crimes for 'last week' Im wanting 'last week' to be the last complete week. Say today is 01/14/2000 the last complete week would be 01/02/2000 --> 01/08/2000 Any suggestions would be appreciated. Kevin ---- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Production | 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] get the previous assigned sequence value
I've been looking through the online docs, so far have not found this information. After an INSERT, I want to retrieve the value of the sequence I use for unique_ids Any suggestions ? Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Production | 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] questing using array
Just trying to get a handle on how to work with an array as a datatype.
For exampel I set up a table:
namevarchar (20),
ageids int4[]
Made an INSERT like:
insert into TABLENAME (name, ageids) values ('test', '{1, 2, 3, 4}');
What I haven't been able to figure out is how to do a select where one of
the ageids = a particular number.
I'd like to do something like
select * from tablename where ( any ageids = 3 )
just don't know the syntax.. if this is even possible. Any suggestions
would be appreciated.
Kevin
--------
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Production | 333 Texas St #175| FAX:318.221.6612
[EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] datetime datatype question
If from the psql command line I use select current_timestamp it shows like this: 1999-10-01 14:30:53-05 However if I insert into a datetime field, when I do a select it formats it like so: Sun Oct 03 10:00:00 1999 CDT Is there a way I can retrieve the information from a datetime field, and format it like the first example ? Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 Production| 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] Error when creating tables
I read some posts regarding this error on some of the mailing lists archives, but couldn't find an explanation. After creating a table, I get the following error: NOTICE: _outNode: don't know how to print type 715 psql shows the table having been created however. Anything to be concerned about? Something else I'm wondering about is upgrading from postgresql 6.3.2 to postgresql 6.4.2 or possibly 6.5 in the past what I've done is to backup a database with pg_dump (with some options) on a database to create a backup along with the schema and permisions etc.. then on the new postgresql server do a createdb 'dbname' and run psql -e databasename < backupfile Is this the correct way to do this or is there an easier way. I've got about 50 databases to move over from 6.3.2 to 6.4.2 If this is the correct way to do this. I found before when moving from some other version of postgres to 6.3.2 that sometimes the backed up file would not restore correctly to the new postgresql server, and I had to manually edit the backup file before running pgsql dbname < backupfile Any suggestions would be appreciated. Kevin ---- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] Upgrading from 6.3->6.4.2/6.5b1 possible
Following this thread... just wondering is this a known problem of
updating from 6.3x to 6.4x or 6.5 ? or is this an isolated problem from
one user. We are looking to upgrade our postgresql 6.3.2 soon, (I think
we've been having some of the same problems described below ('backend
cache invalidation')
Kevin
On Sun, 30 May 1999, Gregory Maxwell wrote:
> On Sun, 30 May 1999, Oliver Elphick wrote:
>
> > Gregory Maxwell wrote:
> > >
> > >AHH! The pgsql v6.3 backend has begun crashing horribly on me since I've
> > >added a few more database backed webpages (with messages like 'backend
> > >cache invalidation...').. I figured that upgrading to 6.4.2 would solve my
> > >problems.. But I can't upgrade! Things I've tried:
> > >
> > >* Using old pg_dumpall -z > file and psql -e template1 < file (after
> > > install and initdb)
> > >* Using the pg_dumpall from the new version.
> > >* Using pg_upgrade
> > >* Rereading the docs dozens of times
> > >* having a friend try it.
> > >* all of the above with both 6.4.2 and 6.5beta
> > >
> > >Every time I start loading, it chokes up on the dump output and falls into
> > >a constant stream of parse errors and messages like "PQsendQuery() --
> > >query is too long. Maximum length is 8191"
> > >
> > >Argh!!! I've got websites constantly crashing and coustomers ready to
> > >revolt, what can I do???
> >
> > edit the dump file; change every line to an insert command. Run that
> > so that you can see what it is objecting to.
> >
> > 6.3's dump did not cover everything, so you are likely to have to edit the
> > dump file to make it reloadable.
>
> Is there a better way, perhaps even a perl script? My dump is quite large
> (~1 million rows?) and manual editing is right out. If I'm going to go
> through that kind of labor I'll probably switch to MySQL (which has a
> larger web userbase, even though I prefer postgres for it's
> completeness)..
>
> Argh.. Are you aware of any patches to v6.3 that might improve my
> stability without breaking compatibility?
>
> Thanks
>
>
>
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #175| FAX:318.221.6612
[EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] select from into question
Just hoping some magic SQL can get me out of this one easily enough. I have a field of type varchar a sample would look something like this: 'categoryname/subcategoryname/someotherinformation/012345' all I want in this field is that last bit of information after the last '/' ie: '012345' I'd like to either replace this field with this number alone or insert into another newly created field. Any suggestions would be appreciated. Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] Optimizations for busy DB??
On Thu, 13 May 1999, Brian wrote: > > Oh, yeah, you should upgrade to 6.4.2! > > I am assuming I will have to export all my data and reimport into 6.4.2, > which is a task for sureI will make this jump soon when I have > some downtime. Are there any estimates on a final release of postgresql 6.5 ? Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #175| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] applying 6.4.2 feature patch
Has anyone had any luck/problems installing the 6.4.2 feature patch. I tried to install it, and it had problems finding all the files to patch, had to specify each one. And once all was said done, postgresql was completely hosed. Could even display a list of available databases with out it complaining about an error using 'select' Any feedback or suggestions would be appreciated. I would like to get the LIMIT function which is provided by the feature patch. Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
RE: [GENERAL] limit select question
On Wed, 31 Mar 1999, Michael Davis wrote: > Good point. I am running 6.5. So it has been fixed. I am not sure what > patch is needed to make this work on 6.4.2. Is version 6.5 to be had some where, or is it only in beta? I sure could use that feature on a project I'm working on right now... Kevin ---- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
RE: [GENERAL] limit select question
On Wed, 31 Mar 1999, Michael Davis wrote: > Select * From table_name limit 1; I'm running postgres6.4.2, and this query fails with: ERROR: parser: parse error at or near "1" I was told I could gain this feature by adding the 6.4.2 feature patch. But when I tried installing the patch, anything and everything I tried in psql failed with an error on 'select' Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] limit select question
I've been asked if there is a way to LIMIT the number of results displayed when doing a SELECT with postgresql, by a former user of mysql. Wasn't sure if there is an easy way to do this or not. I've don't it before, but in a very round about way... any suggestions. > with mySQL and SELECT queries > you can LIMIT the number of results displayed to the screen. I can't > seem to find any such switch in the SELECT commands in postgres. Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] character limit on db names in PGSQL or what?
had a db named: prysm_shreveport with one table named 'shreveport' came back later and did: createdb prysm_shreveport2 psql -e prysm_shreveport2 < shreveport2.sql --> which creates table 'shreveport2' then: psql prysm_shreveport2 which to my suprise had 2 tables! shreveport, and shreveport2 So I started with: createdb prysm_shreveport3 psql prysm_shreveport3 suprise, suprise, it already has two tables in it, shreveoprt, and shreveport2 so what I can tell createdb prysm_shreveport3 (does in deed create a new database, that I can see by doing: psql then \l to list all dbs) destroydb prysm_shreveport3 will actually delete the database. but to do something like psql prysm_shreveport3 doesn't actually connect me to that database it actually connects me to prysm_shreveport even though my command line looks like this in psql: prysm_shreveport3=> even if there is no database listed as prysm_shreveport2 I can do: psql prysm_shreveort2 and it looks and acts as though I'm connected to prysm_shreveport2 when actually it's prysm_shreveport that I'm connected to. I'm thinking an error message somewhere along the line or something would have been nice, and fortunately no data was lost in this process, very easily could have been however. currently running postgres 6.3.x I've got 6.4.x running at home, I was going to try it there too, just to see what happens. I just thought this was strange. Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] trouble with pgaccess
I've just recently installed postgresql 6.4.2 from source. Installation appeared to complete just fine. I'm just having trouble getting pgaccess to work. I've read several files that supposedly explain how to fix the problem I'm having, but I'm not having much luck, or don't undertand. I keep getting the following error: Error in startup script: couldn't load file "libpgtcl.so": libpgtcl.so: cannot open shared object file: No such file or directory while executing "load libpgtcl.so" (procedure "main" line 6) invoked from within "main $argc $argv" (file "/usr/local/pgsql/bin/pgaccess" line 4954) Any suggestions sure would be appreciated. Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] need help separating a field
I have a table with a field of 'username_password' where the data is in the format of: username/password what I would really like to do, is create a new field of 'username' and another of 'password' and with some magic SQL statement select all from username_password, and spit up the pair, and insert them back into the newly created 'username' and 'password' fields. Is this even possible? and if so, would anyone be willing to give me a hint? Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] select using date
On Fri, 8 Jan 1999, Jose' Soares wrote: > Try: > > select current_date, CURRENT_DATE - INTERVAL '1 DAY'; > ?column?|?column? > --+-- > 1999-01-08|1999-01-07 00:00:00+01 > (1 row) > > PostgreSQL has a syntax sligth different than SQL92. You have to enclose > '1 DAY' instead of '1' DAY. Well, I tried: select current_date, CURRENT_DATE - INTERVAL '1 DAY'; but receive the following: ERROR: There is no operator '-' for types 'date' and 'timespan' You will either have to retype this query using an explicit cast, or you will have to define the operator using CREATE OPERATOR After trying somethings, I was able to get this to work: select CURRENT_DATE -1; will subtract 1 day from the current date... which gives me what I need for now. Thanks Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] select using date
Trying to use select statement using CURRENT_DATE Which works fine like so: select * from headlines where dateof = CURRENT_DATE order by dateof desc But I'm also wanting to do something similar to: select * from headlines where dateof = CURRENT_DATE - INTERVAL '1' DAY order by dateof desc Basically just trying to subtract 1 day from the CURRENT_DATE When I try the above I get ERROR: parser: parse error at or near "day" Any suggestions would be appreciated. Kevin ---- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] alter table ?
On Fri, 13 Nov 1998, Postgres DBA wrote: > Yeah, that's true, but you can simply overcome this problem with following > query that you should issue right after "alter" query: > update passwd set spamblock='TRUE'; Yea, I did that, but I guess what I'm looking for, was that when ever anything was added to the table. Regardless of whether or not the spamblock was specified, it would be set to 'TRUE' Kevin ---- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] Still the problem with the autoincrement field
On Tue, 6 Oct 1998, Marc Eggenberger wrote:
>
> and when I want to add some data .. how to do this?
> when I do the following:
>
> insert into offene_stellen values ('', 'Maurer', 'Buchs', 'Rheinhalter Claudio',
>'flexibilitaet', 'gutes Team');
>
> the id field is always 0, and when I do a:
>
> insert into offene_stellen values ('Maurer', 'Buchs', 'Rheinhalter Claudio',
>'flexibilitaet', 'gutes Team');
Not exactly sure what the problem is here.. but you should not have to
call on the increment sequence at all durring the insert.. it's automatic
Maybe it's because you are not specifying which fields to insert into like
so:
insert into table_name (field1, field2, field3, field4)
values ('data1', 'data2', 'data3', 'data4');
Kevin
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612
[EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] How to have a autoincrement field?
On Tue, 6 Oct 1998, Marc Eggenberger wrote:
> With the current database I need a field that is automaticly
> incremented when data is added. I looked in the docs, and the
> mailinglist archive ... could find any suitable ...
I suppose there may be an easier/better way than this. But what I do when
I create the table I create a sequence, then have a field which
automatically increments the field ... Like so:
CREATE SEQUENCE game_id;
CREATE TABLE games (
game_id INT DEFAULT nextval('game_id') PRIMARY KEY,
game_title varchar (250),
etc
)
Hope that helps.
Kevin
--------
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612
[EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] importing into postgres
I'm using the IMPORT Table feature of pgaccess to import a .txt file.
I've done this before and usually have no problems..
The table I'm importing into however has a field which I intended to be an
auto numbering field.. INT DEFAULT nextval('team_id') and I have a
sequence of team_id
however when I try to import my .txt file, I can't leave the "team_id"
blank, it complains because it can't be a NULL value.. I can't manually
insert a number in the .txt file either.
I was hoping to import these items then just run update.. to get my
'team_id'
I also tried adding nextval('team_id') in my .txt file for that field no
luck.
any suggestions?
Kevin
----
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612
[EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] date datatype
Is there an easy way to get Postgres to get the datatype of date to return a Unix timestamp Kevin Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] Re: [HACKERS] NEW POSTGRESQL LOGOS
On Wed, 3 Jun 1998, David Gould wrote: > > I think it needs more attitude though, the mascot > > looks too tame sitting on its fat duff. To really > > appeal to Americans, the penguin should have a hot > > pink mohawk, mirrorshades, an ammo belt draped about > > its shoulders, an Uzi tucked under one wing... > > > > ...and the severed head of Bill Gates under the other. Sounds good.. print it! Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] ODBC driver
On Tue, 2 Jun 1998, Brett W. McCoy wrote: > Did you create a Postgres user with the 'createuser' tool (usually run by > the postgres superuser (but not root))? This allows designated users to > use postgresql databases, and in some cases, create and destroy and even > set database permissions for other users. Also, don't you have to set up Postgres to accept users from another machine.. I forget exactly where you do that. Kevin -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
Re: [GENERAL] extracting date information?
On Tue, 19 May 1998, Jose' Soares Da Silva wrote: > If you use "datetime" type instead of "date" and set datestyle = 'Postgres' > your data will be like: Sat Dec 12 00:00:00 1998 CET > instead of: 1998-12-12 I actually stumbled across this after my initial post. Now that my dates are being retreived in this format: Sat Dec 12 00:00:00 1998 CET Is there a way to format this to leave off the "time" portion and maybe the "timezone" info ? -------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612 [EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
[GENERAL] extracting date information?
Any hints or clues on this one?
Using Postgresql6.3 and PHP2
I have a simple database with a list of events, one of the fields is of
the data type "date".
When I call this information from the database, it prints out the date
in the form of "1998-05-05"
What I would really like to do, when this date is retrieved, Is to
format it something like "Thursday May 5, 1998" Anything more
descriptive than "1998-05-05" would be great.
I've tried using the "date(format,date)" call from PHP by saving the
date in a variable, then:
echo date("YMdl","$thedate");
But when it is displayed it always prints out this date:
"1969Dec31Wednesday"
Any suggestions or comments would be appreciated.
Kevin
--
--
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech| 333 Texas St #619| FAX:318.221.6612
[EMAIL PROTECTED] | Shreveport, LA 71101 | http://www.shreve.net
--
