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 version of Postgres when asking questions. Questions concerning administration or advanced features are best ...
0
votes
0answers
10 views
SQL 2 queries vs join
I have 2 tables,
Users
username PK
nickname
Permissions
username FK to Users
permission_text
Given a username, Is it possible to return 1 row with all the permissions and user details?
I can ...
1
vote
0answers
17 views
500 Error when starting Django on Apache with mod_wsgi
I'm getting 500 error while trying to start Django (1.10.5) on Apache (with mod_wsgi) using postgre DB. The installation dependencies are django and. psycopg2. They are installed on a Python 3.6 ...
-1
votes
0answers
18 views
How to choose between MongoDB and PostgreSQL for GIS?
Please don't downvote - I'm trying to find some information on this topic but most articles I'm finding are 3+ years old. I am also fairly new to this side of things and not sure who to ask.
For my ...
0
votes
2answers
19 views
Elixir/Phoenix can't connect to cloud hosted Postgres DB
trying to get my first Phoenix app connected to Compose's Cloud Hosted Postgres DB and am unable to connect. I have verified that it is up and running. I get the following error:
[error] GenServer #...
0
votes
1answer
15 views
PL/Pgsql more than one row retured by a subquery
I have 2 tables. Table1 and Table2. Only table 1 gets populated and then by a trigger I populate values of table2.
So, I have trigger like this,
CREATE OR REPLACE FUNCTION function_copy() RETURNS ...
0
votes
0answers
35 views
Having trouble accessing value of a clicked param
I am having trouble getting the value for my "selected" element from a click event. I have posted the main piece of code below, which I think is causing the issue. I will post the whole code further ...
0
votes
4answers
31 views
Merge multiple columns into one column with multiple rows
In PostgreSQL, how can I merge multiple columns into one column with multiple rows?
The columns are all boolean, so I want to:
Filter for true values only
Replace the true value (1) with the name of ...
0
votes
0answers
18 views
Logging query with parameters in PLPGSQL
When a statement in my PLPGSQL (Postgres 9.6) function is being ran I can see the query on one line, and then all the parameters on another line. A 2-line logging. Something like:
LOG: execute <...
0
votes
1answer
9 views
Best database to store locations of users as they move, priority given to read & write speed?
Hi Stack Overflow community, making some architectural decisions & trying to figure out the best strategy to store locations of 50k users who are moving around, in an environment where we care ...
1
vote
3answers
24 views
Python/ Django Key already exists. Postgres
I Have a project built in django and it uses a postgres database.
This database was populated by CSVs files. So when I want to insert a new object I got the error "duplicated key" because the object ...
0
votes
2answers
20 views
Postgres cumulative sum over earlier months
I have a table that has % of users for that month. I want to add those percents to get a cumulative sum up to that month. I don't think my cumulative function is right.
So, row 5 should show ...
1
vote
1answer
16 views
Inserting into Postgres Multidimensional Text Array from NodeJS Knex
I am attempting to insert a single row into a Postgres table from a NodeJS application using the Knex-Seed-File module for Knex.
Upon each attempt, I receive an error for only one column/field which ...
0
votes
0answers
9 views
How can I extract parts of a Django Postgres DateRangeField
I would appreciate some help. I can't get my head around this.
I want to use the Django Postgres DateRangeField for a model in my app.
It will hold two dates: a from-date and a to-date like in a ...
-3
votes
0answers
15 views
project in code igniter with postgre won't work on my mac
I have my project already finished in CodeIgniter 2.1.0, now I changed my laptop from Windows to Mac, I thought it will be easy.
I used in Windows Code Igniter 2.1.0 and Bitnami WAPP Stack 5.3.18.0. ...
0
votes
1answer
8 views
PWS - Unable to connect UAA to database
I am having an issue on Pivotal Web Services where I am unable to get a connection to the Postgres SQL database for UAA.
I have the service defined and bound to the application, but am putting the ...
0
votes
1answer
13 views
How to access ClearDB via terminal like Postgres
So I used to use Postgres for Heroku and you could access it with a command like:
heroku pg:psql
And it would take you to an interface that looked similar to the local MySQL database that you access ...
0
votes
0answers
19 views
VB.NET - Fill DataGridView with Parameterized DataSet Query
Visual Studio 2015
Context :
I need to create a form to search the database by giving input from TextBox. PostgreSQL.
Code:
Image_dbTableAdapter.Fill(Image_db_DataSet.image_db, searchBox.Text)
...
1
vote
2answers
36 views
Error: column “data_type” of relation does not exist
I am trying to user a trigger to return the 'data_type' of a column from my table. SQL FIDDLE has the whole trigger...but the ket bits are:
FOR each_column IN
SELECT
attname as ...
1
vote
1answer
20 views
MySQL group concat conversion to Postgres using two concatenated columns
How can I create a similar query using Postgres to recreate this MySQL version.
SELECT u.id, GROUP_CONCAT(CONCAT(r.firstname,' ',r.lastname, ' [', r.type,']') SEPARATOR ', ') AS names
FROM reference ...
2
votes
0answers
28 views
PostgreSQL function returning composite type using within sqlalchemy
I have an issue with composite types of postgres within sqlalchemy.
The function my_function() returns a composite type consisting of two float and one text type.
I added the function to sqlalchemy ...
1
vote
3answers
42 views
find rows with same values in mulitple columns with ID's
i have a relatively big table, with a lot of columns and rows.
Among them i have ID, longitude and latitude.
I would like to have a list of ID's which have the same coordinates (latitude and longitude)...
0
votes
0answers
10 views
development database created from dumped production database does not properly show Amazons3 images
I just dumped my Heroku production postgres database into a local postgres database. Everything worked swell, except that the local database is not
displaying images that were uploaded to Amazon S3.
...
0
votes
0answers
7 views
Heroku pg:pull stalls out on tiny database?
$ heroku pg:pull DATABASE_URL mylocaldb2 --app afternoon-sunday-4103
heroku-cli: Pulling postgresql-globular-1902 ---> mylocaldb2
it then stalls out at
...
0
votes
1answer
25 views
Designing a database that handles inventory tracking with product variations
I am designing a database for a project that handles inventory management for shops.
Basically, each Product can have the variations Color, and Size, and obviously, the number of products (stock) of ...
0
votes
2answers
20 views
Understanding Nulls in Aggregation Functions
I have the table R whice contains,
b | c
-------------------
1 | null
-------------------
2 | null
-------------------
3 | 4
-------------------
3 ...
-1
votes
0answers
21 views
vaadin demo editor backend
I would like to use this editor in the vaadin demo:
https://github.com/vaadin/dashboard-demo/tree/7.7/src/main/java/com/vaadin/demo/dashboard/view/reports
I'd like to use the CK-Editor Addon and ...
0
votes
0answers
8 views
(psycopg2.DataError) invalid input syntax for type numeric: “” with SQLAlchemy
I'm attempting to move a database I have created using SQLAlchemy from a MySQL backend to a PostgreSQL, and have therefore changed the connector to the default postrgresql one for SQLAlchemy, psycopg2....
0
votes
0answers
24 views
Need help trying to understand how to modify somebody else's code to work with my data
I am what one might consider an intermediate-beginner at RoR coding. I've build a fairly large website and have most everything working, including several simple instances of this question where I'm ...
0
votes
2answers
19 views
Alias a POSTGRES select statement
I want to create new tables in the query and use them at the end for a final query. I don't remember what the syntax is and can't find it anywhere. (here is a basic example for illustration only).
...
0
votes
0answers
24 views
Dealing with prices in different currencies when handling payments by yourself and Invoices
I have a table Invoice and InvoiceItems and I'll handle payments by myself. A client can pay in different currencies, but the prices of the services I sell are in a single currency, say, Euro. Or at ...
1
vote
2answers
25 views
column alias as NUMBER in postgresSql
I am using a number as alias in select query in PostgresSql
Select json->>'1' as 1 from myTable
It shows an error
"error in query near 1".
It works fine if we dont use number (eg. json-...
0
votes
0answers
16 views
Libpq framework for swift iOS to work with postgreSQL
I've been trying to connect my postgreSQL database to an iOS app on swift for quite a while now. I managed to connect it for macOS development by creating Obj-C Bridging header for system provided ...
1
vote
1answer
29 views
postgreSql “relation does not exist” even though in phpPgAdmin works
Hello I have a database that has 12 tables. I can select everything from 11 of them but using the same query for the 12th doesn't work. But if i try that query from phpPgAdmin then it works. For ...
1
vote
2answers
30 views
Make my table sortable by four columns
I have a user log table that works fine and is sorted by timestamp, but I also need it sortable by IP ADDRESS and USER but can't get it working properly. I want the user to be able to choose a column ...
0
votes
2answers
47 views
sql query extracting data with input from another table
Customer table
customer_id|household_id|loyalty_member|gender|city|state|zip|phone_opt_in|email_opt_in|mail_opt_in
-----------------------------------------------------------------------------
...
0
votes
0answers
20 views
How should I model a rails db structure to store information about words?
I have a Ruby on Rails application where the user can click on any word in a text, which will bring back related information about the base word and the specific inflection (word form) of the word ...
-1
votes
0answers
13 views
fetch multiple rows in one row postgresql and codeigniter3
I am using codeigniter 3 and postgresql.
here is my code :
$this->db->select(['prefix.id as product_id', 'prefix.title as name', 'prefix.short_desc as briefDescription', 'prefix.full_desc as ...
0
votes
2answers
22 views
echo key value from a row before postgres php while loop
Can't believe this is giving me a problem but here it is. An example of the query and code is:
$sql = "SELECT
o.order_id, o.order_number, o.broker_id, o.agent_id
FROM orders o";
$...
0
votes
2answers
29 views
Use Ruby To Convert Any Query To Count Query
In my application I use PG to execute queries defined by the user in-app.
require 'pg'
database = PG.connect(*credentials)
query = 'select id, created_at from users where id % 2 = 0'
database....
0
votes
1answer
32 views
Reform a postgreSQL query using one (or more) index - Example
I am a beginner in PostgreSQL and, after understanding very basic things, I want to find out how I can get a better performance (on a query) by using an index (one or more). ...
0
votes
0answers
6 views
Carriewave_postgresql : OID getting persisted but not image file
Carrierwave_postgresql git: https://github.com/diogob/carrierwave-postgresql
Carrier wave was working fine when I was uploading to file system.
All I needed to do was add an oid column to my model ...
0
votes
0answers
20 views
PostgreSQL: Index usage management [on hold]
I have a dataset and applied a particular index on it. How can I check which index access method is being used on this data and how can I force the system to use this index I have created (if it is ...
1
vote
2answers
25 views
SQL conditional filter
I have the data in the following format:
ID Val
1 abc
1 xyz
2 abc
3 abc
3 pqr
3 xyz
4 abc
Now, I want IDs that do not have any other value than 'abc'. This would mean that I need IDs ...
0
votes
0answers
24 views
Construct an INSERT command from a SELECT
Question: Is there a way in (maybe pl/pgsql function way?) to create an INSERT/UPDATE/DELETE query on a specific table using the values found in my log table (namely the values 'action', 'schema_name',...
0
votes
0answers
14 views
psql: database does not exist but both user and database do exist
I have a user account donato. I have a database food_app_development.
template1=# \connect food_app_development
You are now connected to database "food_app_development" as user "donato".
...
1
vote
2answers
26 views
Search condition in where clause postgres
I am new to postgres, can anyone help me understand the role of ? in the where clause?
Select * from tablename1 as a
join tablename2 as e
on a.column1 = ? and a.id = e.id
and e.range between ? and ...
0
votes
1answer
20 views
I'm having trouble with password field while using pg:pull with my Heroku postgres database
I am using the Cloud9 IDE which has an ubuntu operating system. I recently uninstalled my local SQLlite3 database from my Rails app and successfully installed a local postgres database. I am now ...
1
vote
1answer
40 views
What is the best way to store a foreign key reference to multiple tables?
I have three tables, upvotes, comments, and posts. Since both comments and posts can be upvoted here is what I'm currently using:
CREATE TABLE upvotes(
id PRIMARY KEY,
post_id INTEGER REFERENCES ...
0
votes
0answers
9 views
Bookshelf.js order by column from pivot table
I'm having trouble figuring out how to run an orderBy query based on a value from the pivot table.
Small example:
const Lesson = bookshelf.Model.extend( {
tableName: "lesson"
} );
const Class = ...
1
vote
1answer
19 views
postgresql - update, set DEFAULT in case statement
I get a syntax error when trying to update a table to using the DEFAULT keyword value inside a case statement, as follows:
UPDATE tbl
SET status =
CASE
WHEN ( st.status = '' ) THEN ( ...
