Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.
0
votes
0answers
4 views
PostgreSQL INSERT INTO FROM SELECT RETURNING id mappings
I'm using PostgreSQL 9.3.
I want to duplicate some of the db records. Since I'm using an auto-increment pk id for the table, I want to get back the id mappings from the generated ids of duplicated ...
0
votes
0answers
5 views
Ensuring uniqueness in recursive query
I have a very basic parent-child / tree hierarchy and a recursive query that also adds depth and loads everything perfectly… almost. When I attempt to load several nodes, where one of them is a child ...
0
votes
1answer
12 views
How to check current_user is present in particular recordset?
I have Three tables User,Event and EventInvitee.
A user creates an event and invites other users in that event.
Now using below code I get all the users invited in a particular event.
...
0
votes
3answers
24 views
Two columns in subquery in where clause
I have a query:
SELECT s.period, s.year, s.amount
FROM salaries s
I would like to select from salaries table only the rows that have period and year equal to:
SELECT p.period, p.years
FROM ...
0
votes
0answers
14 views
PostgreSQL - checking that restored database has all the data as original
I backup my PostgreSQL database once a week. One time, during the restore, the restored database was 2.9GB in size, while original database size was 3.7GB. The reason for different sizes is well ...
0
votes
1answer
8 views
Using sum() and unnest() with sqlalchemy
I'm trying to figure out proper syntax for using sum and unnest, but I have yet to figure out the proper syntax as currently generated SQL is invalid for PostgreSQL:
sqlalchemy.exc.ProgrammingError: ...
1
vote
2answers
24 views
SQL statement to return grouped nodes
I have a table like the one below:
nodelabel | ipaddr | serviceid
x1 | 1.1.1.1 | 1
x1 | 1.1.1.2 | 2
x1 | 1.1.1.3 | 2
x2 | 2.2.2.1 | 1
x2 | 2.2.2.2 | 3
x3 | ...
2
votes
4answers
31 views
How to create JSON from Query in PHP
I have been looking and trying many different ways to create the JSON below from a postgres table using PHP, but did not work, specially to add the "group". Group should be added when there are ...
0
votes
1answer
15 views
How can I get a data list as string in Postgres SQL query?
My tables are
create table parents(
id integer,
name text,
parent_childs text);
create table childs(
parent_id integer,
name text);
Data:
insert into parents values (1, 'Mueller');
insert into ...
0
votes
1answer
16 views
How to identify recently changed SPs in postgresql?
I want to get the list of stored procedures which were recently changed.
In MS SQL Server, there are system tables which store those information and we can easily retrieve what has changed. ...
0
votes
0answers
16 views
pg_restore: [compress_io] could not uncompress data: invalid block type
I'm getting this error when I restore a Postgres backup from another server:
pg_restore: [compress_io] could not uncompress data: invalid block type
I haven't found much online about what that error ...
0
votes
1answer
31 views
Launching two query via java code
I want to execute two queries in my PostgreSQL database via code java.
The first one create a temporary view and the second one get some data from this view.
This is my code:
String sql = "create or ...
-2
votes
0answers
10 views
how to iterate these json sample in in table
I want to insert these data into Postgres table in table format-
how can we find out the object and values to store in data..??
{"glossary": {"title": "example glossary","GlossDiv": {"title": ...
2
votes
2answers
21 views
Postgres syntax error in if condition
I am trying to calculate commission and according to that commission value (commiss) i want to update a column value fixed_fee
select st1.* ,(st1.order_item_value * st1.commission_rate)/100 ...
0
votes
0answers
7 views
Conquest PACS with postgres
I have a Conquest PACS i would like to use a pSQL database.
But when i query my PACS i keep getting an ***DBerror.
As far, as i can see in the logs, the PACS never quieres the database
My dicom.ini ...
0
votes
2answers
12 views
After PG records manually over-written, auto-creation doesn't start with last ID/ Record not Unique error
This is a funky error, and I have fixed it manually, but want to understand the root behavior of why it happens. Sorry it's a bit long, it's kind of hard to explain, I'm doing my best.
I have a ...
0
votes
1answer
19 views
Django request.method = 'GET' not working
I'm trying to load db table data on my app, in order to do an UPDATE on it, so far, I found out this is done by a request.method = 'GET' don't know if this always the case, but surely it is one of the ...
0
votes
0answers
8 views
connect to postgresql in centos sandbox on local machine
I'm using putty on windows to ssh into a linux sandbox where the postgresql installation is. My ultimate goal is to connect to the postgresql instance sitting in the sandbox from R (via DBI) on the ...
1
vote
2answers
16 views
PostgreSQL 9.3: Display result in specific format using array_agg function
I want to show the given records in the following table into the specific format
which is shown below in the table.
Creating table: Test_1
CREATE TABLE Test_1
(
ColumnA varchar,
ColumnB varchar
);
...
0
votes
1answer
13 views
Updating a many to many join table using sequelize for nodejs
I have a Products table and a Categories table. A single Product can have many Categories and a single Category can have many Products, therefore I have a ProductsCategories table to handle the ...
0
votes
1answer
28 views
Can someone give examples of use case of the ordered-set functions in PostgreSQL 9.4?
I just read the documentation and in specific this page, but I can not figure out one example (for example use case of this functions in analytics) in which I will use this, please could you help me ...
1
vote
0answers
6 views
PostGIS -Ubuntu Installation Error asking for dependencies
I am new to postgre and Ubuntu. I am running a VM with Ubuntu 14.04 LTS Trusty. I am trying install PostgreSql9.4 Database and PostGIS 2.1.Here are the commands I ran on the Ubuntu machine:
sudo ...
0
votes
0answers
10 views
Skipping Postgres specific test using importorskip
I have a module with tests written using py.test.
It has a number of different functions performing the same actions against different database backends - primarily sqlite and PostgreSQL.
I'd like to ...
0
votes
2answers
32 views
SQL sum across large number of tables
Our current database has data spread out in a table-per-store structure at the moment, such that there can be thousands of tables for a particular schema.
Each table has pretty basic data ...
0
votes
0answers
26 views
How to save an IP address as binary using Eloquent and PostgreSQL?
First off, here's the SO question+answer where I got my information - laravel 4 saving ip address to model.
So my table will potentially have millions of row, therefore to keep storage low I opted ...
1
vote
1answer
15 views
PostgreSQL Explain: why does child have larger cost than parent?
Why do some of the lower planning nodes have a cost higher than that of the the top most node? In this article, I found this example
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 ...
0
votes
0answers
11 views
Dynamically change Rails DB connection depending on user (multiple DB servers)
I'm using Rails(3.2)+PostgresSQL(9.4) in my project.
I have a continuously growing database.
I won't describe all the 70 tables and the large amount of partition for each of them so here is a very ...
-1
votes
2answers
21 views
POSTGRES Group by / Inner Join
I am having problems writing a query to summarize the number trips from a OD matrix table. I am still new to using databases other than MS Access, so please forgive my inexperience. SMGZ is the table ...
1
vote
0answers
18 views
Postgres sql file seems to not finish
I am using the postgres docker image to create a local database for development. A feature of the image is that any shell scripts in a directory will be run on startup, I use this to setup my schema ...
0
votes
3answers
19 views
GROUP BY is creating multiple rows instead of 1
This is in Postgresql 8.2 I created the following view and would like the results to be grouped together by the 'employee' field:
CREATE OR REPLACE VIEW stg.vw_dw_employees AS
SELECT zvpa0001.pernr ...
0
votes
2answers
15 views
Query failed: ERROR: invalid byte sequence for encoding “UTF8”: 0x91
I've been trying to create this table but keep getting the following error
Query failed: ERROR: invalid byte sequence for encoding "UTF8": 0x91
The code I've been working with is:
CREATE TABLE ...
2
votes
1answer
23 views
SQLAlchemy default PostgreSQL ARRAY behavior
I have a table I am creating and interacting with using the SQLAlchemy expression engine and I am needing to be able to fetch the values from the ARRAY column as a python tuple not a list. Since the ...
0
votes
1answer
20 views
error bundle install postgresql: `mktmpdir': parent directory is world writable but not sticky
I was trying to install rails with database postgresql and got an error:
Gem::Installer::ExtensionBuildError: ERROR: Failed to build gem native extension.
...
0
votes
0answers
8 views
Using decimals in loopback with postgresql connector, not mapping datatype
I'm trying to use decimal numbers in loopback using a postgresql connector. I read on https://github.com/strongloop/loopback-connector-postgresql/issues/33 that you have to map the property to a ...
1
vote
0answers
25 views
Why is list(Model.objects.all()) 10x slower against an Oracle database as compared to Postgres?
On a django application at my job, we develop locally using postgres but our dev/test/prod servers all use Oracle.
Using essentially the same data (loaded via fixtures), this command:
...
0
votes
0answers
17 views
How can I sum one column from the same table, to produce three different aggregates, using Sequel ORM?
My query is this:
DB[:expense_projects___p].where(:project_company_id=>user_company_id).
left_join(:expense_items___i, :expense_project_id=>:project_id).
select_group(:p__project_name, ...
0
votes
1answer
20 views
How to configure HikariCP for Slick 3.0.0 RC1 on Typesafe conf
I have a Play Application based on the play-scala Typesafe template (Play Scala Seed), and tried to add Slick 3.0.0 to the project and connect to a PostgreSQL database.
First I added the dependencies ...
2
votes
0answers
18 views
How to manage a database with branches and trunk like SVN
I have a project for a web application developed in java using a database in postgres 9.4, for versioning of code we use SVN, creating branches for changes in the project and when the change is ready ...
1
vote
1answer
15 views
Multiple has_many/joins or create new relation in Rails
What is the best way for next case.
User belongs to family (by family_id), family (by building_id) belongs to building, building (by street_id) belongs to street... etc.
street.users is common and ...
2
votes
1answer
27 views
How to turn off SQLAlchemy implicit_returning?
Trying to make insert query:
q_stat_insert = Stat.__table__.insert().values(insert_values)
res = Session.connection().execute(q_stat_insert, params)
but I have trigger on that table that returns ...
0
votes
2answers
18 views
How do I set a time limit for my Bootstrap modal?
I am looking to add a Bootstrap 3 modal to my homepage. Currently, I have it set up so the admin can log in and add a message if for example, the office is closed that day.
The modal is working fine ...
0
votes
0answers
7 views
Eclipse Publishing to Odoo Server
I'm working with Odoo and I'm trying to figure out if you can make changes to your local Odoo repository while working on the database server (instead of a local server). I have few users who are new ...
1
vote
2answers
33 views
PostgreSQL - restored database smaller than original
I have made a backup of my PostgreSQL database using pg_dump to ".sql" file.
When I restored the database, its size was 2.8GB compared with 3.7GB of the source (original) database. The application ...
0
votes
0answers
13 views
Whoosh + django-haystack missing search results
I'm playing around with a relatively straightforward implementation of Whoosh 2.6 and django-haystack 2.3.1 for a search of "contact" objects. However, an example search for "mary" only returns a ...
0
votes
1answer
13 views
Postgres - is it possible to group by substring of one of my fields?
This is my table:
id | integer | not null default nextval('frontend_prescription_id_seq'::regclass)
actual_cost | double precision | not null
...
0
votes
0answers
26 views
Criteriabuilder like, how to do it for Long?
i try use "like" method from Criteriabuilder for get all record based on pattern " 10% ".
I want get record where ID is - 101, 10002, 1003,1000 etc...
I've use this code:
Predicate p = ...
-1
votes
0answers
36 views
SQL queries for calculating users who visited at once every week and every week
I have a usage table with user_id and login time. I want to get the number of users who logged in at least once a day. I also need to get the number of users who logged in at least once a week.
Any ...
0
votes
3answers
34 views
Postgres SQL Single quote Escape
I need a help in escaping single quotes in postgresql insert. I am using a ETL tool for data extraction from source and loading in a postgresql database.The Select statement includes some variables.
...
-1
votes
2answers
29 views
how to use a column value in postgresql
order_item_id order_status weight
158871745 "delivered" 0.3
158032756 "delivered" 0.3
158871745 "return" 0.5
i want to find the difference between ...
0
votes
3answers
22 views
How to return week number + year + friday number of week
In sql it looks like this:
select to_char(d,'WW') as Week
, to_char(d,'YYYY') as Year
, to_char(d,'DD') as "Day of Friday"
, d
from (
select (level-1)*7 + ...
