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
7 views
Postgresql full text search beginner
I have the following tables in my database:
create table COMPANY (
id_company SERIAL not null,
name VARCHAR(250) not null,
...);
create table CLASSIFIED_AD (
...
1
vote
0answers
7 views
Multiple “pg_dump” calls in Powershell, only one is run
I'm creating a Powershell script to backup 2 PostgreSQL databases using Postgres' pg_dump tool. I'm using Powershell 2.0 on Windows 7 and PostgreSQL 9.3.
The simplified script looks like this:
$...
-2
votes
1answer
13 views
Error undefined methode 'interger' when make migrate
I'm building a database with Ruby, but when I tried to make a model (database table) a couple errors showed up, and I don't understand what is " in block in change" and "change". Here's the error.
&...
0
votes
0answers
3 views
DSS boxcarring skipping operation
I'm using DSS 3.5.0 with PostgreSQL, and a set of operations in a request box is not working in a very peculiar way. I've successfully used request boxes containing thousands of operations in this ...
0
votes
0answers
5 views
Cross Tab Grouping Issue in Redshift
I am forming the data for cross tab report in redshift. I couldn't form for few scenarios.
For example, if student has 3 assignments, output should be
assignment1:0|assignment2:0|assignment3:0
...
1
vote
0answers
10 views
Python Flask sqlalchemy Insert new column into table - heroku postgresql
BACKGROUND INFOS:
I have an app which is hosted by heroku on a postgresql DB.
I have already some data in this DB and now I have to add a new row in one of my tables.
Usually I deleted the old DB ...
0
votes
0answers
8 views
PostgresSQL Row Level Security with check procedure
I'd like to define a procedure to check if a row should be editable ( I want to use this function elsewhere in my application ) and have this function called via with check
create or replace function ...
0
votes
2answers
15 views
Execute Query string in postgreSQL
I have a query string in variable
suppose Select Now();
I want to execute it same like SQL do.
I use
Execute 'Select Now()';
But It throws an exception
prepared statement "SELECT now()" does not ...
0
votes
1answer
20 views
Deserializing SQL table to object collection
I'm trying to deserialize an SQL table to a collection of objects in PHP and I'm lost as to how I can do so.
In C#, the equivalent would be:
public class Object
{
public string objectProperty { ...
1
vote
2answers
13 views
PostgreSQL Function Syntax Error in DECLARE clause
I am unable to create this function as I get syntax error near INTEGER in line 6. Am I missing something here?
CREATE OR REPLACE FUNCTION public.update_application_status_by_token(applicationToken ...
0
votes
1answer
26 views
How to scan SQL array to []int64 in Go?
I am scanning an array of int from Postgres DB and it is returning as []uint8. I need them in []int64, how can I convert them into []int64 or how can I return them from the DB as []int64? In my query ...
go
2
votes
0answers
25 views
DateTime withTimeAtStartOfDay with timezone
could somebody help or give me some hint with following problem. I have trouble with creating Timestamp in Java. In my db i have timestamp stored in UTC time for example 2016-10-20 23:30:00.000000 ...
1
vote
1answer
13 views
Why psycopg2 returns same result for repeated SELECT?
When I execute ordinary Select correct results are returned, but when I execute this select for DB uptime it returns same first result all the time. I did check Postgres logs and I see that select is ...
0
votes
1answer
11 views
Postgresql: could not connect to server: No such file or directory
Ubuntu 14
I install postgresql server on remote server(digitalocean).
Run command psql and display errors:
postgres@ubuntu-512mb-fra1-01:/home/deploy$ psql
perl: warning: Setting locale failed.
...
0
votes
1answer
19 views
Spring JPA + postgres - how do you store a createdOn field?
I am trying to create my model for User as follows, but I am having trouble with createdOn. I've created a column in my postgres db table of data type Date called createdOn.
How can I have the date ...
0
votes
0answers
17 views
django.db.utils.ProgrammingError: column am.amcanorder does not exist
I am using PostgreSQL as my database and using django tenant schemas. But when I run the following command python manage.py migrate_schemas --shared I get the error saying
(checkpoint_env)
G:\...
0
votes
1answer
9 views
Index Results of Postgres Table Function
I'm looking to create a Postgres function that caches the results of a long-running query so it only needs to be executed once per transaction:
CREATE OR REPLACE FUNCTION get_records (
_state ...
1
vote
1answer
11 views
psycopg2.ProgrammingError: syntax error at or near “stdin” error when trying to copy_from redshift
I am having this problem when I am trying to copy to AWS redshift.
This is the code I am trying to run:
with open('path/to/files, 'rb') as fo:
cursor.copy_from(fo, 'schema.table', sep=',')
...
0
votes
1answer
12 views
Trying to set constraint - new primary key in table, but getting an error: column “amgettuple” does not exist (pgadmin3)
I am creating a new table using pgadmin3 and when I click on constraints to add a primary key, I get this error:
I searched on google but I'm not finding this "amgettuple" issue. Does anyone know ...
0
votes
0answers
18 views
python traverse CTE in a double for loop?
I have 2 for loops within each-other. For each row 'A', 'B', 'C' in loop1, I need to access the hierarchical tree to find all the parents of a group 'X' in loop2. This makes me use CTE where I need to ...
1
vote
0answers
9 views
The Hibernate's PostgresUUIDType analogue in EclipseLink for mapping java.util.UUID to Postgres's “uuid” type
When using Hibernate it is possible to mark field of JPA-entity with @org.hibernate.annotations.Type(type="org.hibernate.type.PostgresUUIDType") if you want to map fields of type java.util.UUID to ...
0
votes
1answer
22 views
Improving Django Slice Step Performance
I have an application where I am using a Django instance to store logs from a number of separate entities. I then have a UX component that grabs log files from the django instance and graphs the ...
1
vote
2answers
22 views
Django queryset behind the scenes
**
Difference between creating a foreign key for consistency and for joins
**
I am fine to use Foreignkey and Queryset API with Django.
I just want to understand little bit more deeply how it ...
1
vote
0answers
21 views
Format nested JSON object from Postgres query
I want to get a JSON object formatted similar to this:
{
"username": "USERNAME",
"teamname": "TEAMNAME",
"logs": [
{
"log": {
"log_id": 29,
"person_id": 3,
"...
0
votes
1answer
15 views
Decrypting Data From Database Before Sending Response Back Node.js
Update
The actual problem I am having is placing the value back inside the response after I decrypt it. I just need to know how to place a value back in the response after I manipulate it.
...
0
votes
1answer
21 views
socket.rb:206:in `bind': Address already in use - bind(2) for 127.0.0.1:3000 (Errno::EADDRINUSE)
I am getting the following error when I run RAILS_ENV=production bundle exec rails s
`bind': Address already in use - bind(2) for 127.0.0.1:3000 (Errno::EADDRINUSE)
I have tried some of the other ...
4
votes
2answers
20 views
Add Missing monthly dates in a timeseries data in Postgresql
I have monthly time series data in table where dates are as a last day of month. Some of the dates are missing in the data. I want to insert those dates and put zero value for other attributes.
Table ...
1
vote
0answers
5 views
How to set up PostgreSQL database with associations between tables (foreign keys) with Sequelize
I'm using PostgreSQL, Sequelize, and Sequelize-cli.
Using sequelize-cli and some manipulation in an IDE I set up the following model and migration file (simplified for the example, I didn't include ...
4
votes
3answers
30 views
SQL: Selecting rows whose distinct values are a subset of another table's distinct values
Given the following tables:
plan_role
id | plan_id | role_id
----+---------+---------
7 | 1 | 10
8 | 1 | 20
9 | 1 | 30
10 | 2 | 10
11 | 2 | ...
0
votes
0answers
19 views
PHP - Interacting with command line processes
Heres an example of what I need to do with the command line:
$ heroku login
Its response is:
Enter your Heroku credentials.
Email:
So I enter:
[email protected]
Then it responds:
Password (...
0
votes
1answer
23 views
PostgreSQL Slow query, Order by LIMIT
List item
mydbab=# \d+ table1;
Table "dbmydb.table1"
Column | Type | Modifiers | Scol5rage | Stats target | Description
----------------+---------+-----------+-------...
-1
votes
1answer
10 views
Postgresql Start failed
psql (PostgreSQL) 9.5.5
sorry for my eng lang.
can't connect to DataBase.
postgresql.service - LSB: PostgreSQL RDBMS server
Loaded: loaded (/etc/init.d/postgresql; bad; vendor preset: enabled)
...
0
votes
2answers
21 views
Keeping a column in sync with another column in Postgres
I'm wondering if it's possible to have a column always kept in sync with another column in the same table.
Let this table be an example:
+------+-----------+
| name | name_copy |
+------+-----------+...
0
votes
0answers
7 views
Convert Npgsql text type to C# type
I need to find way to convert Npgsql type (given as text) to C# Type object.
Example of code:
public Type ConvertFromNpgsqlType(string a_sNpglsqType)
{
//TODO
if (string.Equals(a_sNpglsqType, "...
0
votes
1answer
21 views
Postgis <@> operator, what does it do?
I am comparing to points with <@> to get the distance between them. I was told it would return the distance in miles if the points contained latitude and longitude coordinates.
I can't seem to ...
1
vote
1answer
29 views
Postgresql/SQL - My date/timestamp logic seems solid but producing nulls
I have a massive table full of hospital visit information. Each row corresponds to one visit. The visit/row itself has a unique ID but also contains a person ID (patient) to match back to that persons ...
0
votes
2answers
21 views
calculating percentages for repeat rate cohorts
I wrote a repeat rate query that gives me cohort repeat rate data in the following format:
cohort_join_day | repeat_day | repeat_users
11/15/16 | 0 | 10000
11/15/16 | 1 ...
1
vote
1answer
12 views
How to query for info from 2 tables with sequelize
So I have two tables: Class and Course. Each Class has a course_id that defines what type of course it is on the Course table. Here are my models:
const Class = sequelize.define('class', {
...
0
votes
1answer
15 views
Django ORM compound filter with variables
I'm by no means a Django expert, and need some guidance on a problem.
For background, I have a older .NET project that I've been tasked to turn into Django project. The project gets a full list of ...
0
votes
1answer
29 views
How do you insert variables into a database table using Postgre SQL via C++?
I have a C++ program that inserts values into a database table. I can't directly hardcode the values in because the data is constantly being updated, but I'm really confused about the syntax.
When I ...
0
votes
2answers
10 views
postgresql - how to get a handle on fields with ':' character in them
can't seem to get access to the addr:housenumber field in osm data using psql.
Here is the command I'm trying and I'm getting a syntax error:
select planet_osm_polygon.addr:housenumber from ...
4
votes
0answers
34 views
Why is ActiveRecord leaving off the last element when parsing a string as data for an array attribute?
I'm using Postgres. I have an ActiveRecord model with an integer array column, like so:
t.integer "foo", array: true
If I assign a ruby array to it, it works as expected:
> @instance.foo = [6,7,...
0
votes
0answers
11 views
Query JSON property / value case insensitive [duplicate]
I have a SQL query below that assembles some JSON to get a user by email address. I'm looking for a way to use ilike with a JSON query, so that I can match the same result I would get below with ...
2
votes
2answers
37 views
Short-circuit a UNION query when first result found
I have built a very basic UNION query in order to determine the 'type' of a UUID passed into my query like so:
(
SELECT
CASE WHEN id IS NOT NULL THEN 'player_id' ELSE '' END AS uuid_type
FROM ...
0
votes
0answers
25 views
Is there any performance difference between using inner join vs left join? (Please take a look, not a duplicate) [duplicate]
I was just writing a query when I thought of this. My query will yield exactly the same result using either inner join or left join, since I'm joining tables on a foreign key relationship, selecting ...
0
votes
0answers
21 views
Postgresql filtering a grouped view
I have a grouped view that I need to filter. My query is using the indexes correctly but the query plan gets some extra steps when I filter against a grouped view vs filtering the table directly and ...
-1
votes
2answers
19 views
Return a limit of 2 records for each distinct column value
Assume I have a table that looks like this:
| Col A | Col B | Col C |
|-------|-------|-------|
| 1 | A | 54 |
| 1 | A | 56 |
| 1 | B | 55 |
| 1 | B | 51 |
...
0
votes
2answers
27 views
Is there a way to run a single PSQL Aggregate Function without hitting a database table?
For example, I'd like to run:
REGEXP_REPLACE("What's My Name?", "[^a-z0-9_\-]", "-");
and simply see what it returns, instead of doing a search against a DB Table. I tried to run it in the CLI and ...
0
votes
2answers
53 views
The parameters don't pass to the function
this.command.CommandText = "\"GetForeignKeysTable\"";
this.command.CommandType = CommandType.StoredProcedure;
this.command.Parameters.AddWithValue("tableName", resource);
FKDataTable.Load(this.command....
0
votes
1answer
16 views
Spring MVC JPA Native Insert With Generated Column Returned
These are the things I need to accomplish when inserting into PostgreSQL via JPA.
1) Insert to a table with the following added to the end of the insert "ON CONFLICT DO NOTHING" (Or some annotation ...
