In postgres I have a table with a varchar column. The data is supposed to be integers and I need it in iteger type in a query. Some values are empty strings. The following: SELECT myfield::integer FROM mytable yields ERROR: invalid input syntax for integer: "" How can I query a cast and have 0 in case of error during the cast in pos...
In MySQL I can do SELECT * FROM tbl LIMIT 10 In MSSQL I can do SELECT TOP 5 * FROM tbl How do I do this in Postgresql? ...
CREATE OR REPLACE FUNCTION _chkLogin(userid varchar, pwd varchar) RETURNS BOOLEAN AS $BODY$ DECLARE passed BOOLEAN; BEGIN SELECT (_password = $2) FROM _vRegistration WHERE _userid = $1; RETURN passed; END; $BODY$ LANGUAGE 'plpgsql'; When am executing the code above am getting the following error, SELECT _chkLogin('username','abc...
Hello there, i want to know if sqlalchemy has some problems querying a view? Because if i query the view with normal sql on the server like: select * from ViewMyTable where index1 = '608_56_56'; i get a hole butch of entries. But with SQLAlchemy i get only the first one. But in the count is the correct number. I have no idea why...
I am trying to store a dependency tree in a PostgreSQL database. There are about 20,000 software items, each item can depend on several other items. There are several types of dependencies (some are run-time dependencies, some are build-time dependencies and some are test-dependencies). The dependency is recursive and each item only kn...
I'm parsing text documents and inserting them into PostgreSQL DB. My code is written in Java and I use JDBC for DB connectivity. I have encountered very strange error when adding data to DB - it seems that at unpredictable moment (different number of iteration of main loop) Postgres does not see rows just added to tables and fails to per...
Hi folks, I'm trying to figure out a way to speed up a particularly cumbersome query which aggregates some data by date across a couple of tables. The full (ugly) query is below along with an EXPLAIN ANALYZE to show just how horrible it is. If anyone could take a peek and see if they can spot any major issues (which is likely, I'm not ...
Is there a way to add a not null type column to a Postgresql table without doubling its size on disk? If for instance I have a table with some columns defined, and I want to add a column I'd do the following: alter table my_table add new_column int update table my_table set new_column = 0 alter table my_table alter column new_colum...
I have an import script which runs a series of commands to get things from one Postgres DB to another, both running the same Django codebase. For the most part, it uses ./manage.py loaddata to copy over, but some objects require additional processing and I use Django's objects.create() method in a custom script to copy the data. When doi...
I've been working on text parsing task when I found strange Postgres behavior. My original code exposing strange error was written in Java with JDBC connectivity for PostgreSQL (v8.3.3 and v8.4.2 tested), here is my original post: Is it an error of PostgreSQL SQL engine and how to avoid (workaround) it?. I've just ported my Java code giv...
We have currently a Postgres database with a 100 of tables 20 of them with more than 5 000 000 rows, the master DB server runs on Debian 32MB RAM 8 Processors. Additionaly to the master DB we have a Slave DB replicated using Slony. Our application uses Java and Hibernate framework for SQL query, c3p0 as connection pool. Our problem is...
When I began, I used pg_dump with the default plain format. I was unenlightened. Research revealed to me time and file size improvements with pg_dump -Fc | gzip -9 -c > dumpfile.gz. I was enlightened. When it came time to create the database anew, # create tablespace dbname location '/SAN/dbname'; # create database dbname tablespace...
I have a table create table testtable( testtable_rid serial not null, data integer not null, constraint pk_testtable primary key(testtable_rid) ); So lets say I do this code about 20 times: begin; insert into testtable (data) values (0); rollback; and then I do begin; insert into testtable (data) values (0); commit; And f...
Hello all, I have an interesting conundrum which I believe can be solved in purely SQL. I have tables similar to the following: responses: user_id | question_id | body ---------------------------- 1 | 1 | Yes 2 | 1 | Yes 1 | 2 | Yes 2 | 2 | No 1 | 3 | No 2...
It's my first time using PostgreSQL 8.4.2 with Django(I have always used MySQL or sqlite3 in the past). Which value should I use for DATABASE_ENGINE in settings.py, postgresql_psycopg2 or postgresql? How do they differ from each other? ...
Hi This seems possible, but at the same time I am wondering if it actually is possible. I have a table {users}. I want to pull 5 rows from that table, but at the same time, I want to specify one row. The closest sql statement I get is: SELECT u.id, u.full_name, u2.id, u2.full_name FROM users u JOIN users u2 ON u2.id !=...
What are the available options to identify and remove the invalid objects in Postgres ...
Hi. I'm hoping someone can help me a WAL-shipping and warm standby issue. My standby system runs happily for weeks, then all of a sudden it starts looking for .history files that don't exist. It then craps out and I can't successfully restart it without rebuilding the standby. Both systems are running CentOS 4.5 and postgres 8.4.1. The...
Let's say I have something like this: select sum(points) as total_points from sometable where total_points > 25 group by username I am unable to refer to total_points in the where clause because I get the following error: ERROR: column "total_points" does not exist. In this case I'd have no problem rewriting sum(points) in the where ...
I've recently started using PostgreSQL and I'm trying to do things "the right way" as I've understood it. Meaning putting as much work in the database server instead of on the client as possible. So I've created a function with PL/pgSQL that will add data to a table. Since I've a primary key constraint set on that table, and the referen...