I have the following models to associate users with roles: class User < ActiveRecord::Base has_many :user_role_assignments, :dependent => :destroy has_many :user_roles, :through => :user_role_assignments end class UserRole < ActiveRecord::Base has_many :user_role_assignments has_many :users, :through => :user_role_assignments e...
This is how I do in SQL Server: insert into users(name, password) values('admin', '1'); declare @id int; set @id = @@identity; insert into usersroles values(@id, 1); insert into usersroles values(@id, 2); @@identity is in sql server the last inserted identity (serial) How to do the same in Postgres? ...
I have a problem to process all rows from database (PostgreSQL). I get error: "org.postgresql.util.PSQLException: Ran out of memory retrieving query results.". I thing that i need to read all rows in small pieces, but it doesn't work - it read only 100 rows (code below). How to do that ? int i = 0; Statement s = connection...
Is there a way to create a backup of a single table within a database using postgres? And how? Does this also work with the pg_dump command? ...
is it possible to connect from postgres to sql server/oracle linked servers ? if yes than how to do that ? ...
in sql server I do like this: insert into foo(name) values('bob') select @@identity; so I get a query/scalar result displayed how to this with postgres ? ...
Have 2 tables with a linking table between then. USERS +-------+---------+ | userID| Username| +-------+---------+ | 1 | Nate | | 2 | Nic | | 3 | John | +-------+---------+ SITES +--------+---------+ | siteID | Site | +--------+---------+ | 1 | art | | 2 | com | | 3 | web | +-...
I try to do this, but it's a syntax error, what am I doing wrong? declare myid := insert into oameni values(default,'lol') returning id; my table: create table oameni ( id serial primary key, name varhcar(10) ); ...
This is how I do this in sql server. insert into users(name) values('jhon'); set @id = @@identity; -- last serial insert into usersroles(@id, 1) insert into usersroles(@id, 2) insert into usersroles(@id, 3) How can I do the same in postgres (withouth creating a function)? ...
I'm interested in reversing a string in PostgreSQL. ...
i use the slony for replication of postgresql database. it work fine some day. After i use the slony command to delete the replication node, pg_dump does not work, the error message is: pg_dump: schema with OID 73033 does not exist Then i delete the name space of slony in pg_namespace, and pg_dump does not work. So i delete the data of...
Hello all-knowing co-stackers. Our masters thesis project is creating a database schema analyzer. As a foundation to this, we are working on quantifying bad database design. Our supervisor has tasked us with analyzing a real world schema, of our choosing, such that we can identify some/several design issues. These issues are to be used...
In Python, I can write a sort comparison function which returns an item in the set {-1, 0, 1} and pass it to a sort function like so: sorted(["some","data","with","a","nonconventional","sort"], custom_function) This code will sort the sequence according to the collation order I define in the function. Can I do the equivalent in Postg...
I have this function: create or replace function insert_aereo( aereo_type[] ) returns text as $$ begin return 'ok'; end $$ language plpgsql; and this is the parameter type that I created: create type aereo_type as (codice int, modello varchar, marca varchar); Then I call my function: select insert_aereo('{123, "ciao", "pippo"}'...
I just stumbled upon pgpool-II in my search for clustering my Postgres DB (just getting ready to deploy a web app in a couple months). I still have the shakes from excitement, but I'm nervous, as each time I find something this excellent I am soon let down. Have you any experience with pgpool-II, and will it help me run my database in mu...
I am trying to use heroku and appear to be getting a Postgres error but don't have enough information to know what to fix. The error is below, and it looks like it is trying to run delayed_job: > PGError: ERROR: value too long for > type character varying(255) : UPDATE > "delayed_jobs" SET "updated_at" = > '2010-09-12 01:06:59.354515'...
Is there a standard way to figure out which is the most recent log file of the running Postgres instance? pg_config, unfortunately, does not even give information about the log directory. ...
I have Postgres running on Windows and I'm trying to investigate the strange behaviour: There are 17 postgres processes, 8 out of those 17 consume ~300K memory each. Does anybody know what such behavior is coused by? Does anybody know about a tool to investigate the problem? ...
I have a messages table which looks like this: +------------+-------------+----------+ | sender_id | created_at | message | +------------+-------------+----------+ | 1 | 2010-06-14 | the msg | | 1 | 2010-06-15 | the msg | | 2 | 2010-06-16 | the msg | | 3 | 2010-06-14 | the msg | +----------...
I'm working on designing an application with a SQL backend (Postgresql) and I've got a some design questions. In short, the DB will serve to store network events as they occur on the fly, so insertion speed and performance is critical due 'real-time' actions depending on these events. The data is dumped into a speedy default format acros...