I'm using PyGreSQL to access my DB. In the use-case I'm currently working on; I am trying to insert a record into a table and return the last rowid... aka the value that the DB created for my ID field: create table job_runners ( id SERIAL PRIMARY KEY, hostname varchar(100) not null, is_available boolean default...
I use decodeURIComponent and encodeURIComponent in Javascript. Before I store this data in a UTF-8-PostgreSQL-Database, I should decode them: $my_data = pg_escape_string(utf8_encode($_POST['my_data'])); I'm looking for a PostgreSQL-Function to convert Javascript-Encoded Data. ...
Hey, I need to use an .accdb database, and to do that it needs to be imported into PostgreSQL. I believe this would be a simple and straightforward problem (I expected it had been already solved), but a simple solution escapes me. I'll add that I don't have access to Access (lol), and my solution is loosely dependant on that. If th...
I am using GridSQL where I get some performance problems whenever the SQL pattern INNER JOIN (SELECT arises. I am therefore considering rewriting all these queries into two queries, one creating a temporary table using the exact select statement and the other query joining with the temporary table, so the pattern would be INNER JOIN temp...
I have a table like so: call_activity ( call_id TEXT, activity_type TEXT, activity_time TIMESTAMP, PRIMARY KEY(call_id, activity_type, activity_time) ) activity_type may be one of about 9 different strings: 'started' 'completed' (about 5 variations on this) 'other' (these are the states that I want to display) A...
I'm project managing an intranet application being developed at work. We're in the early planning stages. I've previously done all my development in Python using Django, but as we're a windows shop we're probably going to go with ASP.NET MVC. We won't really be able to afford a SQLServer license though, so we were perhaps looking into u...
I have decided that instead of deleting rows from a postgres database, i rather hide the rows so that in case the rows were deleted by accident, i can still retrieve them. What is the best approach to hide rows from a database? Do you recommend creating a column named "Active" and giving it a value of either 1 or 0, 1 meaning active and ...
Hey everyone, I was wondering if anyone could point me in the direction of some good resources (web sites, technical articles/journals, books, etc.) related to database monitoring/performance? I am looking to write a paper that explores what kinds of statistics are useful in database monitoring, and to whom they are useful for. So, I...
I have a problem with PostgeSQL and NHibernate. I have tried HQL and Criteria and select returns nothing. I used log4net to get generated query, replaced parameters with value and query returns result, why is that? Is there a special configuration for Postgre or NHibernate? I even tried with lowering (lower(column) like lower(:param)) an...
Hi u all, I have a server in wich is installed Postgresql. In this server i want to install theMicrosoft Active Directory. Can i do this without a problems? Can i change the user that run the Postgresql service without a problem? When i installa the AD all the machine users will be deleted so i need to create domain users to run that s...
In PSQL, is there a good way of finding all the tables that inherit from another table? Ideally, I could get the data from a SQL query, but at this point, I'd be happy with any reliable method. ...
Hi, I am using Postgres and I have multiple schemas (i.e. S1 and S2). I would like to run a query that uses tables in S1 and S2. is it possible to do something like this: select * from S1.table1, S2.table2 Thanks for all replies. ...
I have an issue, I'm trying to insert a new row into a postgres database table and get the following error ERROR: duplicate key violates unique constraint "n_clients_pkey" Here my query insert into n_clients(client_name) values( 'value'); I'm using postgres 8.1.11 PostgreSQL 8.1.11 on x86_64-redhat-linux-gnu, compiled by GCC gcc...
Hi, how can i call my own function stored in postgres DB, while i am selecting data throught Doctrine_Query::create() ->select('schema.my_function(id)') ...
I have a table that I would like to be able to present "ranked X out of Y" data for. In particular, I'd like to be able to present that data for an individual row in a relatively efficient way (i.e. without selecting every row in the table). The ranking itself is quite simple, it's a straight ORDER BY on a single column in the table. Po...
What's the best way to make psycopg2 pass parameterized queries to PostgreSQL? I don't want to write my own escpaing mechanisms or adapters and the psycopg2 source code and examples are difficult to read in a web browser. If I need to switch to something like PyGreSQL or another python pg adapter, that's fine with me. I just want simple...
Is there a way for me to force a specific join order in Postgres? I've got a query that looks like this. I've eliminated a bunch of stuff that was in the real query, but this simplification demonstrates the issue. What's left shouldn't be too cryptic: Using a role/task security system, I'm trying to determine whether a given user has pr...
I have a client-server application which gets all the data out of a couple of tables, recalculates something and stores it. Example: Each Item has a 'Bill of Materials' = the list and quantities of which other items it is made out of. Therefore the cost of an item is the sum of the cost of the items in its BOM * their quantities. Ultim...
i have just installed postgresql and i specified password x during installation. when i try to do createdb and specify any password i get the message createdb: could not connect to database postgres: FATAL: password authentication failed for user same for createuser. how should i start? Can i add myself as a user to the database? ...
A few days ago, I ran into an unexpected performance problem with a pretty standard Django setup. For an upcoming feature, we have to regenerate a table hourly, containing about 100k rows of data, 9M on the disk, 10M indexes according to pgAdmin. The problem is that inserting them by whatever method literally takes ages, up to 3 minutes...