Re: [SQL] pg_dump fails (timestamp out of range)
Tom Lane wrote: T E Schmitz <[EMAIL PROTECTED]> writes: pg_dump: ERROR: timestamp out of range pg_dump: SQL command to dump the contents of table "server_hit_bin" failed: PQendcopy() failed. You should treat this as a corrupt-data exercise: you need to identify and fix (or delete) the offending row(s). In this case you might try tests like "bin_end_date_time > '1 Jan '" and so on to see if you can determine exactly which rows are bad. Dear Tom, I can't thank you enough for the above advice. The test above identified 3 records and once they were removed I was able to dump the DB. -- Best Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] pg_dump fails (timestamp out of range)
On Mon, Feb 26, 2007 at 11:05:08AM +, T E Schmitz wrote: > I can't thank you enough for the above advice. The test above identified > 3 records and once they were removed I was able to dump the DB. You'll be wanting to make sure your hardware is fixed after this, don't forget, or you'll end up in the same place next time. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pg_dump fails (timestamp out of range)
Andrew Sullivan wrote: On Mon, Feb 26, 2007 at 11:05:08AM +, T E Schmitz wrote: I can't thank you enough for the above advice. The test above identified 3 records and once they were removed I was able to dump the DB. You'll be wanting to make sure your hardware is fixed after this, don't forget, or you'll end up in the same place next time. This was the last step to get rid of the darned machine. Everything else had already been moved over to the new server. Wish I'd posted a week earlier; that would have saved me a month's rent. -- Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Change Default Database
Hi list, I wrote a database creation script that begins with commands to drop the existing database (if it exists) and create it from scratch. These commands execute fine, the problem is that all subsequent commands are executed on the default database 'postgres'. What command can I use to set the default database to my newly created database (for the duration of the script)? SQL Server has a 'USE [dbname]' command, is there an equivalent command in PostgreSQL? Thanks in advance, Rommel Edwards Software Developer, Barbados, Caribbean. <>
Re: [SQL] Change Default Database
Rommel the iCeMAn wrote: Hi list, I wrote a database creation script that begins with commands to drop the existing database (if it exists) and create it from scratch. These commands execute fine, the problem is that all subsequent commands are executed on the default database 'postgres'. What command can I use to set the default database to my newly created database (for the duration of the script)? SQL Server has a 'USE [dbname]' command, is there an equivalent command in PostgreSQL? See "man psql" (or the client applications section of the manuals) for details on the backslash commands. \c will be what you're after. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Change Default Database
On 2/26/07, Rommel the iCeMAn <[EMAIL PROTECTED]> wrote: Hi list, I wrote a database creation script that begins with commands to drop the existing database (if it exists) and create it from scratch. These commands execute fine, the problem is that all subsequent commands are executed on the default database 'postgres'. What command can I use to set the default database to my newly created database (for the duration of the script)? SQL Server has a 'USE [dbname]' command, is there an equivalent command in PostgreSQL? Thanks in advance, Rommel Edwards Software Developer, Barbados, Caribbean. If using psql: \c dbname then the rest of the commands. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Change Default Database
Thanks for the swift responses, I tried the \c command but I got the following error: ERROR: syntax error at or near "\" SQL state: 42601 Character: 520 Here's a code snippet ... -- -- TOC entry 1685 (class 1262 OID 16453) -- Name: test_db; Type: DATABASE; Schema: -; Owner: postgres -- DROP DATABASE IF EXISTS test_db; CREATE DATABASE test_db WITH TEMPLATE = template0 ENCODING = 'UTF8'; ALTER DATABASE test_db OWNER TO postgres; \c test_db; -- query tool doesn't like this command :-( Rommel Edwards Software Developer, Barbados, Caribbean. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] getting at sequence previous/next values
Hello, I've got a table that looks basically like: id_show | serial show_name | text show_type | text created_on | timestamp without time zone When looking at a row with an id_show of value n, I'd like to have an easy way of knowing the preceding and next values of id_show in the sequence (which might have holes). Is there some convenient way to get that info, or must I do a full-blown select? Thanks, ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Change Default Database
On 2/26/07, Rommel the iCeMAn <[EMAIL PROTECTED]> wrote: Thanks for the swift responses, I tried the \c command but I got the following error: ERROR: syntax error at or near "\" SQL state: 42601 Character: 520 Here's a code snippet ... -- -- TOC entry 1685 (class 1262 OID 16453) -- Name: test_db; Type: DATABASE; Schema: -; Owner: postgres -- DROP DATABASE IF EXISTS test_db; CREATE DATABASE test_db WITH TEMPLATE = template0 ENCODING = 'UTF8'; ALTER DATABASE test_db OWNER TO postgres; \c test_db; -- query tool doesn't like this command :-( Rommel Edwards Software Developer, Barbados, Caribbean. \c only works in psql. See: http://www.postgresql.org/docs/8.2/static/app-psql.html ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Change Default Database
On Mon, 2007-02-26 at 10:52, Rommel the iCeMAn wrote: > Hi list, > > I wrote a database creation script that begins with commands to drop > the existing database (if it exists) and create it from scratch. These > commands execute fine, the problem is that all subsequent commands are > executed on the default database 'postgres'. What command can I use to > set the default database to my newly created database (for the > duration of the script)? SQL Server has a 'USE [dbname]' command, is > there an equivalent command in PostgreSQL? A couple of points: 1: PLEASE don't post html email, especially with an annoying background that makes it harder to read your message. 2: What kind of script is this? sql? bash? PHP? perl? If you post the script, we can probably help you out a bit. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Change Default Database
On Mon, 2007-02-26 at 14:52, Rommel the iCeMAn wrote: > Hi again, > > Apologies for the HTML. > > This is an SQL script, it was generated by doing a schema-only database dump > using pgAdminIII (which in turn uses pgdump I believe). I've attached the > entire script. I was attempting to run this script in pgAdminIII because > that's what I used to generate it, but the more I read it seems I should try > the psql command line tool to execute this script. I'm getting the > impression that pgAdmin doesn't recognize the /connect directive. Please CC the list, as more eyes are more likely to have an answer than just mine... Yeah, I always run my scripts from psql, often called from a bash shell script. That way I can substitute server / database names in the shell script depending on what I'm trying to do. \ commands exist within the framework of psql, not outside. If pgAdmin doesn't have an equivalent type of command structure, then it won't work for this. You could always drop the db by hand, create it, connect to it, and THEN run the rest of your script, but I too think it would be easier to just run the script against psql. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Change Default Database
I seem to be blundering a lot today! I thought I was replying to the entire list, didn't realize I replied to one person :-) Nothing was wrong with my script, I assumed that since it was generated by pgAdmin that I could run it inside pgAdmin. It works perfectly when I run it using psql. It is very much like SQL Server's osql command line tool. Thanks to everyone that helped ... So I've learnt three valuable lessons: 1. It's better to execute scripts using the command line tool 'psql'. 2. Make sure I'm replying to the list and not to an individual. 3. Do not post HTML messages!!! :-)) Thanks again everyone. Rommel Edwards Software Developer, Barbados, Caribbean. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Change Default Database
On Mon, 2007-02-26 at 16:02, Rommel the iCeMAn wrote: > I seem to be blundering a lot today! I thought I was replying to the entire > list, didn't realize I replied to one person :-) > > Nothing was wrong with my script, I assumed that since it was generated by > pgAdmin that I could run it inside pgAdmin. It works perfectly when I run it > using psql. It is very much like SQL Server's osql command line tool. Thanks > to everyone that helped ... > > So I've learnt three valuable lessons: > > 1. It's better to execute scripts using the command line tool 'psql'. > > 2. Make sure I'm replying to the list and not to an individual. > > 3. Do not post HTML messages!!! :-)) And don't forget number 4. The pgsql mailing lists provide better support than most commercial db companies do. > Thanks again everyone. You're welcome, and considering the weather we're having here in Chicago, I might book a flight to come and visit you down there... :) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Change Default Database
B!!!, I was there late last November, it must be far worse now! I'll be sticking close to the forums from now on :-) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Syntax Error in COPY when “create function”
Am I forgetting some limitation? I get " ERROR: syntax error at or near "$1" SQL state: 42601 "with this CREATE FUNCTION Import1() RETURNS text AS $$ DECLARE path Char(100); BEGIN path = 'C:/zhl/hjs/anc_area.att'; COPY anc_areaAtt FROM path CVS; RETURN path; END; $$ LANGUAGE plpgsql; SELECT Import1() ; But COPY can work well when I just put the string in the command: CREATE FUNCTION Import2() RETURNS text AS $$ DECLARE path Char(100); BEGIN COPY anc_areaAtt FROM 'C:/zhl/hjs/anc_area.att' CSV; return path; END; $$ LANGUAGE plpgsql; SELECT Import2() ; Does this mean the "filename" in COPY command can not be a variable? By the way, my OS is windowXP. Thanks and Regards, Hongliu
