I'm attempting to retool myself more towards .NET (coming from an opensource background) and am looking at learning Microsoft SQL Server to complement my skills in C#. How easy would it be to transfer my skills from Postgres to Microsoft SQL Server? Furthermore would a book on SQL Server 2005 (Database Essentials - Step by Step, Microsof...
As title, how can I set a table's column to have the default value the current year and month, in format 'YYYYMM', like 200905 for today? ...
Sorry for a pretty specific question. I have a table (see bottom), and when I try to delete a lot of records from it, my PostgreSQL 8.2.5 spends 98% of the time doing the parent-child constraint. I'm trying to figure out what index should I add to make it go fast. I have to say that all columns on this table have either 0 or null as the...
Assuming I have the following tables. PARENT: PARENT_ID serial, DESCRIPTION character varying(50) CHILD: PARENT_ID integer, CHILD_ID integer, DESCRIPTION character varying(50) What I would like to see is each row in CHILD having a CHILD_ID that starts at 1 and increments by 1, unique per PARENT_ID. It would be similar to a revision n...
How can I save a PostgreSQL table to HTML? ...
Occasionally in our lab, our postgres 8.3 database will get orphaned from the pid file, and we get this message when trying to shut down the database: Error: pid file is invalid, please manually kill the stale server process postgres When this happens, we immediately do a pg_dump so we can restore the database later. But, if we just k...
I have a client that has a PostgreSQL database and he cannot remember the password that we used when the database was setup. Is there a way to recover that information so I do not have to blow away his database and start from scratch? The database is running on a PC. ...
Is it possible to find out if an argument in a function in postgres was defined as table.column%type foo or sometype foo querying e.g. pg_proc? ...
I have a strange problem. The size of my postgresql (8.3) is increasing. So I made a dump and then cleaned up the database and then re-imported the dump. The database size was reduced by roughly 50%. Some infomation: (1) AUTOVACUUM and REINDEX are running regularly in background. (2) Database encoding is ASCII. (3) Database location: /d...
I tried to Google this but it seems like most of the answers are terribly outdated. So, the question is simple: why would I choose one over the other? What are the benefits of each, and what drawbacks do they have? EDIT: Please close this. Apologies, I didn't see the other duplicates when I did a search earlier. ...
Hi everyone, I'm trying to group by month some rows using HQL, but I'm kind of new to that API and can't seem to get it to work. Here is my code: Criteria query = getHibernateSession().createCriteria(SalesPerformance.class); // summary report is grouped by date query.setProjection(Projections.projectionList().add( ...
Hello, We've got a schema in Postgres, and we want to institute a good method to apply schema patches. Currently, we have a series of DDL files that create the schema, tables, sequences, functions, etc. We also have a population script for test environments. Those files are all used to recreate our database environments, for developm...
I have a PHP CLI script, that processes a csv file, inserting it's content to a table in Postgresql database. This is on an Ubuntu server. I use schedtool to control the affinity of the whole script. Schedtool is used to launch the script itself with the -e option. Unfortunately with htop I see that the database thread is spawned as a co...
SELECT STRAIGHT_JOIN s.*, date_part('epoch', s.date) AS unixdate, date_part('epoch', s.expire) as expireunix, u.username, u.fullname, u.photo, u.email, t.topic, t.imageurl FROM stories s, users u, topics t WHERE (s.uid = u.uid) AND (s.tid = t.tid) It says: ERROR: syntax error at or near "s" SQL state: 42601 Character: 23 I'...
I have a table in PostgreSQL with the following structure & data: Question | Answer | Responses --------------------------------------- Burger BigMac 8 Burger Whopper 19 Burger Cheeseburger 4 Drink Coke 22 Drink Water 1 Drink Juice ...
I have a DB table with 25M rows, ~3K each (i.e. ~75GB), that together with multiple indexes I use (an additional 15-20GB) will not fit entirely in memory (64GB on machine). A typical query locates 300 rows thru an index, optionally filters them down to ~50-300 rows using other indexes, finally fetching the matching rows. Response times ...
Hi, How can I get rid of a circular relationship in my db structure. I have an entity called Item. An item can have a sub item/s (circular relationship). An item can have more than one rate depending on what financial year it is(rate_per_year entity created for that purpose and a 1-m relationship). If an item has a sub item, then it ma...
a silly question I suppose... What is the syntax in PostgeSql for inserting varbinary values? MSSQL-way when specifying binary value like 0xFFFF... did't work ...
I have the following data in my "Street_Address_1" column: 123 Main Street Using Postgresql, how would I write a query to update the "Street_Name" column in my Address table? In other words, "Street_Name" is blank and I'd like to populate it with the street name value contained in the "Street_Address_1" column. From what I can ...
I have a table that I would like to partition, but I don't know how to deal with the uniqueness constraints. Is it possible to create a unique constraint across multiple child tables? ...