postgresql

Does postgres automatically change record ids?

I'm relying on the id field of postgresql records not changing in a rails app. Certain tests have indicated that postgres might be changing the ids of some of the records. Does postgres do this? ...

pratical example of Postgres regex substring or regexp_matches

I have been trying to figure the following for a couple days. Please HELP PostgreSQL table : locations Id State -------------------- 1 New York 2 Texas input = 'Greetings from Texas to all Cowboys' output: row containing Texas SELECT id, state FROM locations WHERE state ~* substring(input from state) ...

Values missing in postgres serial field

Hi I run a small site and use PostgreSQL 8.2.17 (only version available at my host) to store data. In last few months there were 3 crashes of database system on my server and every time it happened 31 ID's from serial field (primary key) in one of the tables were missing. 93 ID's are missing now. Table: CREATE TABLE "REGISTRY" ( "ID" ...

Postgresql php and bytesequence issue

I have set the html meta tag to uft8 Also the form charset to utf8 To be uber sure i have run the php function utf8_encode() on the offending string even after all this i am getting a postgres error when executing the query via the php function pg_query. Yet when i copy and paste the query into a command line it executes fine. The que...

Hibernate @GeneratedValue annotation setting existing value to new Object (violating primary key constraint)

Hi, I have an attribute set like: @GeneratedValue @Id private long id; Howerver, when I call the persist function, the id is set with a value that already exists in my Postgres database. I checked the my_seq field there and it is set correctly so I don't know where hibernate is getting that existing number from. Note that this was w...

An I/O error occured while sending to the backend - Postgres

We are having a random problem inserting data into our Postgres database. I can't seem to reproduce the below error but its happening to our customers quite often. An I/O error occured while sending to the backend Yes occurred is misspelled in this error. I can't seem to find anything on it. I was hoping to find more in logs but ...

INSERT or return row if entry already exists in a table with UNIQUE constraint

Greetings, I have this table, on a postgreSQL 8.4 server : CREATE TABLE tags ( tagid bigserial PRIMARY KEY, name text NOT NULL, value text NOT NULL, UNIQUE(name,value) ); The normal INSERT behavior is to throw an error when new values break the unique...

distinct() function (not select qualifier) in postgres

I just came across a SQL query, specifically against a Postgres database, that uses a function named "distinct". Namely: select distinct(pattern) as pattern, style, ... etc ... from styleview where ... etc ... Note this is NOT the ordinary DISTINCT qualifier on a SELECT -- at least it's not the normal syntax for the DISTINCT qualifier...

how do i remove overlapping lines in postgis

I have a typical database with millions of polygons as land parcels and i want to split these polygons onto lines and then remove the lines that overlap eachother. These lines will be used purely for rendering in mapnik/and or geoserver as at the moment every parcel boundary gets rendered twice. i propose to split the parcel polygons i...

sql direct way to get number of rows in table

Hi again people of stackoverflow. I have a routine that has a step that I find unnecessary lets say you want to get all the images from a gallery, and limit a certain number of images per page. $db = PDO object $start = (pagenum x images per page) $limit = (images per page) $itemsdata = $db->query("SELECT id,name FROM gallery LIMIT $sta...

Are there any major pitfalls using PostgreSQL as a replacement for SqlSever

I'm just looking at the possibility of using postgresql as an alternative to sqlserver for a .NET web app. (as a cloud hosted sqlserver costs an arm and a leg once the size of your data is over 20GB or so) so really looking for something with reasonably good performance that can hold a lot of data. However, I'm not entirely sure wha...

Permissions required to allow arbitrary sql to be executed safely

I want to create an SQL sandbox that will allow users to execute arbitrary SELECT queries at a PostGIS database. Essentially, I want to allow users access to psql to play with. Obviously this is a security disaster if write access is allowed. How can I create a system such that querying data is allowed, but there is no reasonable poss...

Postgres raises a "ACTIVE SQL TRANSACTION" (Errcode: 25001)

I use psycopg2 for accessing my postgres database in python. My function should create a new database, the code looks like this: def createDB(host, username, dbname): adminuser = settings.DB_ADMIN_USER adminpass = settings.DB_ADMIN_PASS try: conn=psycopg2.connect(user=adminuser, password=adminpass, host=host) cur = conn.c...

How do you do GIS queries on Heroku using the shared database?

I have a table of geocoded locations with latitude and longitude. I'd like my database query to return a filtered list of them, ordered by distance from a fixed point. There are a few options which I've explored: Postgresql's earthdistance contrib is probably closest to my needs; however, I've found no indication that this is install...

Problem in inserting more number of records if it is transaction based

In our application we allow user to enter email ids comma separated. We take the email ids separate it and insert as records in a table. When they enter email ids less than 10000, everything goes fine. If it goes beyond 10000 the whole application hangs, we are not able to login the application. Technical Details Java Servlet and JSP...

PostgreSQL - Using COALESCE keyword in SSIS

When I am trying to use the COALESCE key word in an OLE DB Source control, it always returns a negative number in the results set. I am using a Native OLE DB\PostgreSQL Native Provider as a connection to the database and other key words are working correctly. I run the query in PGAdminIII and it returns a numeric data type. I re-engin...

PostgreSQL query for current minute

My web application needs to get the results of all the items which have their time value for this minute. My table has a time attributes id, message, time, ..., where time is of type timestamp with timezone. I need to get a list of all entries where the time is the current minute. Comparing the entire value does not match because the ...

postgres query for sales projections

================= This is my query: SELECT SUM(sub_total) AS sales, CASE WHEN (sub_total<100) THEN '0-99' WHEN (sub_total>=100 AND sub_total<200) THEN '100-199' WHEN (sub_total>=200 AND sub_total<300) THEN '200-299' WHEN (sub_total>=300 AND sub_total<400) THEN '300-399' WHEN (sub_total>=400 AND sub_total<5...

creating timestamp from columns in postgres

i have 2 rows in a column, one feeding me the julian date (number of days since Jan 1, 1970, with that day being 1), and the second column is the number of minutes past midnight of the current day (why it was done this way, i have no idea). i would like to get my sql query to create a timestamp out of these two columns. if i had access...

How do I return one element from string_to_array() in PostgreSQL 8.4?

I want to parse a field with the following type of value: "DAVE EBERT CONSTRUCTION~139 LENNOX STREET~SANTA CRUZ, CA 95060~~Business Phone Number:(831) 818-3170" I would like to do a query like: Update mytable set street = string_to_array(myfield,'~')[2] But string_to_array does not "return" an array so it can't be chained in this wa...