[GENERAL] selecting all columns but one
I have a table with a lot of columns. One of the columns I want to alias so have a query of: select *, column as newname from mytable. The problem is I now have column and newname in the results. I don't want to select column by column. How can I do a select * but omit one? Thanks!
[GENERAL] getting column value length
I need to return all rows in a table where one of the columns 'name' is 37+ characters. In postgres, is there a function to get the length of the columns contents? Thanks!
[GENERAL] open transaction?
I just logged into postgres from the command line and did: begin: select blah; select blah; \q Without thinking I closed by connection before committing or rolling back my transaction. Did postgres handle this for me? How do I see if the transaction is still open? Thanks!
[GENERAL] date stamp on update?
Is there a datatype in postgres that will automatically update the date when the row is updated? I know I can do a timestamp and set the default to now() but once the row is inserted, and then edited, I want the column updated without editing my application code or adding a trigger. Is this possible with Postgres? Thanks!
[GENERAL] grabbing date of last Sunday?
How can I grab the date from the last Sunday based on when I run the query? For example I run it today, and I need to date of 10-5-08, if I ran it next week, I would want 10-12-08, etc. Thanks!
Re: [GENERAL] finding firstname + lastname groups
Great, thanks! On Thu, Jun 19, 2008 at 5:14 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > On Thu, Jun 19, 2008 at 03:38:28PM -0400, blackwater dev wrote: > > The problem is name is not one column but made up of firstname, > > lastname...how do I do this? > > I'd probably do something like: > > SELECT firstname, lastname, COUNT(*) > FROM people > GROUP BY firstname, lastname > HAVING COUNT(*) > 1; > > > Sam > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] finding firstname + lastname groups
I have to find the same firstname+ lastname combo in my db and see which name appears the most so I basically need to do the following: select name, count(name) from people group by name having count(name)>1 The problem is name is not one column but made up of firstname, lastname...how do I do this? Thanks!
Re: [GENERAL] can't cast char to in
Yeah, it was my being stupid, I got it going now. Thanks! On Tue, Apr 22, 2008 at 11:42 AM, Erik Jones <[EMAIL PROTECTED]> wrote: > > On Apr 22, 2008, at 10:34 AM, blackwater dev wrote: > > I have a table with a mileage column that is a character varying (please > > don't ask why :). > > > > I need to do a query where mileage > 500 > > > > select * from cars where mileage>500 > > > > So I need to cast it but everything I try throws an error such as : > > > > ERROR: invalid input syntax for integer: "+" > > > > How can I cast this? > > > > Well, you didn't really give any real information on the format of the > data in your mileage column. However, my guess is that you've got at least > one row with just '+' in the mileage column which is not the same thing as > '+0'. You'll probably need to do a little data cleaning and, once that's > done you should definitely consider switching that to an integer/numeric > data type. > > Erik Jones > > DBA | Emma(R) > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > >
[GENERAL] can't cast char to in
I have a table with a mileage column that is a character varying (please don't ask why :). I need to do a query where mileage > 500 select * from cars where mileage>500 So I need to cast it but everything I try throws an error such as : ERROR: invalid input syntax for integer: "+" How can I cast this? Thanks!
[GENERAL] copy with escape
I have data that I'm running through pg_escape_sting in php and then adding to stdin for a copy command. The problem is "O'reilly" is being changed to "O''Reilly" in the string and then in the db. I saw with the copy command I can specify the escape but it isn't working for me. Should this command fix this double 'single' quote issue when I put it in the db? And what is the proper syntax? COPY mytable FROM stdin with escape Thanks!
[GENERAL] joining on concatonation?
I have a query that is driving me nuts. In one table we have data that is split between two columns and I'm trying to pull in all values from another table where that column is represented by one piece of data. Also, all the info in column2 is unique but not in col 1. table1 col1_pfx col2_number col3 Table2 col1 col3 update table1 set col3=(select col3 from table2 where table2.col1=( table1.col1_pfx || table1.col2_number)); I've tried using a join and all other methods and nothing seems to work. Table2 has a primary key on col1 yet when I do explain posgres still seems to do a sqential scan on that column. Any thoughts? Thanks!
[GENERAL] bulk copy
Hello all, I'm pulling in a csv file nightly and need to pump in into my db. My plan is to pump it into a temp table and then to an update or insert from the temp table to the real table. I'm having an issue, however, with the copy. Here is a my syntax. COPY cars FROM 'cars04.txt' USING DELIMITERS ',' WITH NULL AS '\null'; I'm getting there error: must be superuser to COPY to or from a file HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone. [4] Ok, fair enough, in theory, I don't want to simply trust the file to have clean data so will want to put the data into a huge array (php) which I can clean and then pump into the db. What is the best way to pump all this data in without doing inserts? How can I use stdin? Thanks!
Re: [TLM] Re: [GENERAL] batch insert/update
I was also thinking about adding a 'is_new' column to the table which I would flag as 0, then do a basic copy of all the new rows in with is_new at 1. I'd then do a delete statement to delete all the rows which are duplicate and have a flag of 0 as the copy should leave me some with two rows, one with is_new of 1 and some with 0. Just don't know if this would be best. On Dec 26, 2007 3:13 PM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > On Wed, 26 Dec 2007 20:48:27 +0100 > Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > > > blackwater dev <[EMAIL PROTECTED]> schrieb: > > > > > I have some php code that will be pulling in a file via ftp. > > > This file will contain 20,000+ records that I then need to pump > > > into the postgres db. These records will represent a subset of > > > the records in a certain table. I basically need an efficient > > > way to pump these rows into the table, replacing matching rows > > > (based on id) already there and inserting ones that aren't. Sort > > > of looping through the result and inserting or updating based on > > > the presents of the row, what is the best way to handle this? > > > This is something that will run nightly. > > > Insert you data to a extra table and work with regular SQL to > > insert/update the destination table. You can use COPY to insert the > > data into your extra table, this works very fast, but you need a > > suitable file format for this. > > What if you know in advance what are the row that should be inserted > and you've a batch of rows that should be updated? > > Is it still the fasted system to insert them all in a temp table with > copy? > > What about the one that have to be updated if you've all the columns, > not just the changed ones? > Is it faster to delete & insert or to update? > > updates comes with the same pk as the destination table. > > thx > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
[GENERAL] batch insert/update
I have some php code that will be pulling in a file via ftp. This file will contain 20,000+ records that I then need to pump into the postgres db. These records will represent a subset of the records in a certain table. I basically need an efficient way to pump these rows into the table, replacing matching rows (based on id) already there and inserting ones that aren't. Sort of looping through the result and inserting or updating based on the presents of the row, what is the best way to handle this? This is something that will run nightly. Thanks!
[GENERAL] query on table name to return columns and data types?
How can I run a query based on a table name and get the column names and data types returned? Thanks!
[GENERAL] finding gps within polygon
Hello,Does anyone have a good tutorial on finding gps points within a polygon? I need to be able to pass in a list of gps coordinates and let postgres return to me matching cities from my cities table that are within that polygon. Thanks!
[GENERAL] insert multiple rows
In MySQL, I can insert multiple rows like this: insert into cars values(5, "toyota"),(5,"ford"), etc. How can I do something similiar in PostgreSQL? Thanks! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] mysql replace in postgreSQL?
In MySQL, I can use the replace statement which either updates the data there or inserts it. Is there a comporable syntax to use in postgreSQL? I need to do an insert and don't want to have to worry about if the data is already there or not...so don't want to see if it there, if so do update if not insert...etc. Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
