If you've found one or more of my blog posts helpful, why not say thanks by buying me a coffee or beer?
The numbers in the error are specific to you, but if you see something like this, be sure to check if your indexes are somehow corrupted.
Try performing your query again.
Do a barrel roll!
Error connecting to the server: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.
If you're running Windows, PostgreSQL and you've seen this, it's probably driving you bonkers!
No matter what programming language you use, what interface library or even the default client, you'll get this damn cryptic error!
Thankfully, the solution is simple. Postgre is fine, your setup is not broken.
If you've got Nod32 antivirus, then it is known to have some incompatibility issues. Apparently you have to find "postmaster.exe" and add it to the exclusions list.
That can be done via "Setup" > "Advanced Setup Tree" > "Antivirus and Spyware" > "Exclusions" > "Add".
Otherwise, you'll also have issues if you have NetLimiter.
I'm still rocking the old v1.30 so these instructions will vary with the newer ones.
This worked like magic for me, so I hope it helps you too!
Now back to practicing things that matter, like walking on air!
PS. SHE IS HOT!
So the annoying thing about character fields is that they have a limit.
When you try to save the row and the data is too long, it'll fail.
So to increase the limit, run this query:
ALTER TABLE tablename
ALTER COLUMN url TYPE varchar(500),
ALTER COLUMN description TYPE varchar(300); That's it! The fields url/description will now have the lengths of 500/300 respectively and maintain the original data.
BOOM! Headshot!
Took me some time to get this working but I finally got it!
You can grab the Python 2.4 to 3.2 binaries (for both x86 and x64) from StickPeople.
Assuming you already have Apache and PHP installed and configured correctly, what you'll need now are:
These were the latest versions at time of writing, but it shouldn't vary the setup that much.
Installation for Postgre should be pretty straight forward.
Choose a place to stash it, give it a root password and skip through the bits that don't really matter to you.
Extract phpPgAdmin and open up "conf\config.inc.php".
Change:
$conf['servers'][0]['host'] = '';
to
$conf['servers'][0]['host'] = 'localhost';
If you're unable to login (even though you're SURE the password is correct) and keep getting "Login disallowed for security reasons", see here.
Something I've been meaning to do for a while now is sort text searches by relevancy. There are alot of examples in the Postgre documentation but I found them a bit vague to understand.
The way I was using it was a little different to the way they were trying to provide examples for, because my table already had a tsvector field which stored the information ready for searching.
This example below is based on the things I've learnt from Haitham Mohammad's blog.
SELECT table.*, ts_rank_cd(tsvector_field, plainto_tsquery('microsoft software')) AS rank
FROM table
WHERE tsvector_field @@ plainto_tsquery('microsoft software')
ORDER BY rank Assuming that your table has a field called "tsvector_field" which is pre-populated with text search information, the function ts_rank_cd() will create a ranking of the results which allows you to sort by relevancy. The trick is that you have to give it both arguments in your "WHERE" comparison clause in order to make it work.
[ Source ]
Sometimes you just need to quickly skim sample data from the database, but reading long data horizontally is a bit of a mess.
To read results vertically, toggle the expanded display mode using "\x".
content=# \x
Expanded display is on.
content=# select * from table_name;
-[ RECORD 1 ]-----------+-----------------------------
id | 8
when | 2010-03-02 13:37:33.64327+11
salutation | Mr
first_name | twig
address_street_number | 123
address_street_name | French Girl
address_suburb | Has A Reason
address_state | For Hurting Boyfriend
address_postcode | 1234
address_country | France
no_promotional_material | t
content=# \x
Expanded display is off.
In case you're wondering, the french girl reference is from a song called "The Presidents of USA - French Girl".
Most SQL servers should support LPAD() and RPAD().
The syntax for these functions are "LPAD(string, length, pad)".
The padding is space by default, but just set it to "0" to use zeros.
Edit "conf/config.inc.php" and search through the file for:
$conf['extra_login_security'] = true; Set the value to "false" and logins should now work.
After installing Postgres SQL, there was a new user in the Win7 and it was annoying! It showed up everytime I wanted to log in, and hell why would I wanna log in as a database account?
To get rid of it, run command prompt as Administrator and paste in:
REG ADD "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList" /v postgres /d 0 /t REG_DWORD /f
[ Source ]
In the console, type
pg_dump -h db.domain.com -U username database_name -a -i -f output_filename.txt -t table_name_1 -t table_name_2 -t table_name_3
The switches used are:
| Switch | Description |
|---|---|
| -h | Hostname of database. |
| -U | Username. |
| -a | Data only. |
| -i | Ignore version difference between pg_dump and running database server. |
| -d | Use INSERT instead of COPY. |
| -f | Output filename. |
| -t | Table name (can use more than once). |
To restore the data back into the database, connect to it and then type:
\i output_filename.txt
This will execute the commands in the output file, inserting all the data into the database you're connected to.
