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
1answer
7 views
Use Postgres to parse stringified JSON object
I've been using Postgres to store JSON objects as strings, and now I want to utilize PG's built-in json and jsonb types to store the objects more efficiently.
Basically, I want to parse the ...
0
votes
0answers
14 views
Hierarchical SQL Query WITH parent child items
Category (id, name, parent_id);
1 Electronics NULL
2 Computers 1
3 Notebooks 2
4 Desktops 2
Products (id, name, category_id);
...
0
votes
0answers
16 views
PostgreSQL: Put the values in one column as header in result set?
Let's say I have two tables, one with schools and one with classes. Both tables have SchoolID so they can be matched. They look something like this:
TableSchools
SchoolID SchoolName
1 ...
2
votes
0answers
10 views
Postgres error running seeds.exs (using Ecto) - `string_data_right_truncation`
enter code hereI've got a Postgrex error when running my seeds.exs file with MIX: ** (Postgrex.Error) ERROR (string_data_right_truncation): value too long for type character varying(255)
I googled it ...
0
votes
1answer
8 views
Inline output using query_to_xml in postgres
Running following query in postgresql
select REPLACE(REPLACE(REPLACE(REPLACE(query_to_xml('select 1 "col1",2 "col2",3 "col3"
union all
...
0
votes
0answers
4 views
Recover single piece/table of data from rollback on heroku postgres
Someone accidentally wiped a single piece of data from my heroku postgres db.
I'm looking at the rollback function that allows us to roll back the entire db to a previous point in time.
However, ...
0
votes
0answers
50 views
Error : Can not set java lang String field to java lang String
I have code snippet as shown below that tries to extract a list.
FeedbackComplaint (domain object)
@Entity
@Table(name = "STUDENT_FEEDBACK_COMPLAINT")
@Inheritance(strategy = InheritanceType....
0
votes
0answers
7 views
Close database connection yii
I am using an application in Yii and Postgres database. When I run the application on live server(not local system) I am getting this error
CDbConnection failed to open the DB connection: SQLSTATE[...
0
votes
0answers
5 views
How to update a particular point of a complex geometry in Postgis
I have a table which contains complex geometry, like LINESTRING, POLYGONS etc. By complex I mean any geometry that is not just a single POINT. Besides, I know there is a nice function in Postgis that ...
0
votes
1answer
7 views
GIN Index: not found compound words
I want to search some keywords over textual columns of another table.
I coalesce the textual columns and then create GIN Index for them.
ALTER TABLE debug_regexfreeFlickrFullInfo ADD COLUMN ...
0
votes
0answers
10 views
Empty rows between data in wpf datagrid when comparing PostgreSQL database with textfile
I'm trying to write a tool to compare a database with a textfile.
It should get the data from the PostgreSQL database and check if the textfile contains the value of the database coloumn.
It should ...
0
votes
3answers
16 views
PostgreSQL not triggering when NEW.field is NULL
I have the following trigger on my table of customers so I can track whether the customer name has been changed over time and what the previous versions of the name were.
CREATE OR REPLACE FUNCTION ...
0
votes
0answers
14 views
Scan error on column index 1: unsupported Scan, storing driver.Value type <nil> into type *string
I have a column in postgres which is blank initially
var dtime sql.NullString
err = rows.Scan(&dtime)
if dtime.Valid {
dtimeString = dtime.String
} else {
dtime = sql.NullString{}...
go
0
votes
1answer
25 views
Postgres logical & with bigint
My select SQL is
select id & b'00000000ffffffff'
I need to put mask on id, id is bigint.
Actually the id should be divided on two integers up & down parts.
0
votes
0answers
34 views
Error when using GROUP BY with a query
I have a problem in my SQL query: if I add GROUP BY the database engine always says "must appear in the GROUP BY clause or be used in an aggregate function"
WITH install_count_join_date AS (
SELECT
...
0
votes
3answers
20 views
how to count the the data of specific two coulms of different table in PostgreSQL?
I have two tables exactly having same column name but having different data or values. Now i want to count the total number of data of first column of table_1 union data of first column of table_2. ...
0
votes
1answer
25 views
No buffer space available
I am using an application in Yii and postgres database. When i run the application on live server(not local system) i am getting this error
"CDbConnection failed to open the DB connection: SQLSTATE[...
1
vote
0answers
18 views
F# SQLProvider Columns Order Doesn't match the order in the table
I select from a postgresql view\table and export the values into excel file.
The excel file column order need to be the same as the table, but the sqlProvider select them with abc order...
My Code ...
0
votes
1answer
15 views
Error Key (user_id)=(1) is not present in table “auth_user” when migrating
I am running into this error when i migrate.Is there any way to save data on my database.I know that dropping the auth_user table will fix the issue. I am running postgresql.
Thanks!
FATAL ERROR - ...
0
votes
2answers
20 views
How to connect to POSTGRESQL in Codeigniter 3?
I am trying to connect to PostgreSQL using Codeigniter framework.
Now in my database.php
I have the following code :
$active_group = 'default';
$query_builder = TRUE;
$db['default'] = array(
'...
0
votes
1answer
10 views
Is it possible to know the memory being allocated by the method “CreateSharedMemoryAndSemaphores”?
I am a newbie to databases and Postgres and I am analyzing the memory being allocated by Postgres in the method "CreateSharedMemoryAndSemaphores".
Like variable "size" starts with a default value of ...
0
votes
1answer
13 views
postgres trigger creation - ERROR: no language specified SQL state: 42P13
I am new to trigger. I am trying to create a trigger by following this link - http://www.postgresqltutorial.com/creating-first-trigger-postgresql/. But it gives some error. The code block and the ...
0
votes
1answer
9 views
Export data to SAP HANA
I am new to SAP HANA and I am trying to import data to SAP HANA from Postgresql.Is it is good practise to export as csv from postgres and then import to SAP HANA or are there any other methods?(The ...
0
votes
2answers
18 views
Parameter for Multiple Value in column
I don't know how to as this but, here's what I have as sample:
Table1
firstname middlename lastname
JAMIE JANE SCOTT LACSON
CRYSTAL SANTON ...
0
votes
1answer
9 views
Join queries for jsonb fields
Can anyone help me with a join query for tables with jsonb fields?
If I have two tables and each has a jsonb column value and I need to run some join queries.
Let's say value field in each table has ...
0
votes
1answer
26 views
Is there much change in python code when I want to change the database from sqlite to postgres? [on hold]
So far I've been using sqlite with django, however I might want to deploy it to heroku and therefore use postgresql instead. Other than installing postgres, connect it to my app and repopulate it with ...
0
votes
1answer
19 views
Postgres Function to insert multiple records in two tables
create table public.orders (
orderID serial PRIMARY KEY,
orderdate timestamp NOT NULL
);
create table public.orderdetails (
orderdetailID serial PRIMARY KEY,
orderID integer ...
0
votes
0answers
10 views
RDD with 20 partitions in cluster but no workers being utilized
I have an RDD with 20 partitions from importing from Postgres via JDBC. I have a cluster with 5 workers (5 cores). I am simply trying to count the number of elements in each partition according to:
...
-3
votes
0answers
12 views
Incorporating Flask and psycopg2 to create a webpage [on hold]
I am trying to create a web page in which I need two drop down menu of input and Output parameters. Each parameters has some data. Finally I have to come up with a graph on web page between two ...
1
vote
0answers
9 views
JOOQ and PostgreSQL types in a DSL select
I have a problem with jOOQ 3.8.
So, I have a table in PostgreSQL 9.5, something like.
CREATE TABLE my_table(
id bigserial,
types my_type[]
)
where the my_type is a type like
CREATE TYPE ...
0
votes
2answers
18 views
Custom sequence or windows function in Postgresql
I have a huge table which looks like this
Group | Code | Value
------|------|------
G1 |0 |V1
G1 |2 |V2
G1 |2 |V3
G1 |1 |V4
G1 |2 |V5
G1 |2 |V6
G2 |...
1
vote
1answer
15 views
Optimal query for dense_rank to find multiples
I'm querying a web traffic schema to determine which visits are initial and which a return.
I have two tables SESSION and ALIAS.
CREATE TABLE alias (
person_id vachar(24),
alias varchar(24)
)
...
0
votes
0answers
26 views
How to create pre-aggregated table with 13 columns in group by clause [on hold]
In my Redshift Database, I have a table with 5 billion rows and 20 columns. I use this data for reporting purposes and my SQL query is built dynamically based on user selection. Users can select the ...
0
votes
0answers
11 views
Excel in Mac showing '_' instead of accents when generating report through console
I have a huge query with a lot of relationships, joins and DB functions involved. Here an extract example of the query:
SELECT * FROM (
SELECT DISTINCT
CASE WHEN objeto.objeto_clave IS ...
-1
votes
1answer
20 views
postgresql group by
I'm pretty new with SQL / postresql.
So I would like to group my table by 'user' column:
Initial table:
user | country | time |
-----+---------+------+
1 | A | 5 |
1 | B | 1 ...
-1
votes
1answer
13 views
How to set Postgres path variable on Windows 10
I'm on a windows machine and I am having trouble fixing this error:
I've tried to modify the path variable based on various suggestions online but nothing seems to work. I've been at this for over an ...
0
votes
0answers
14 views
Hibernate could not obtain transaction-synchronized Session for current thread on remote server
I read the other answers on similar questions, but I havem't found a solution to my problem. I have a Tomcat7 server and a Spring application wich uses Hibernate to connect to my PostgreSQL remote DB.
...
-1
votes
1answer
14 views
Failed to render template odoo 9
I'm frustrated with sending mail in odoo 9, and this question is apart of my previous question about sending mail in odoo 9 without mail template.
This time I tried another way to send email in odoo 9 ...
0
votes
0answers
10 views
JSF project configuration to work with pgpool2
We are currently in the process of putting pgpool2 (listening port: 5433) in our system to manage our postgres 9.5 servers (listening port 5432). Pgpool2 is located on a different server than postgres ...
-2
votes
1answer
12 views
In postgresql, how do you find aggregate base on time range
For example, if I have a database table of transactions done over the counter. And I would like to search whether there was any time that was defined as extremely busy (Processed more than 10 ...
0
votes
0answers
20 views
postgres: using ANY on array of timestamps
I have a Postgres function where I need to check whether or not a particular value is in an array of timestamps. Here's the function:
CREATE OR REPLACE FUNCTION public.get_appointments(
...
0
votes
0answers
14 views
Postgresql Issue when deploying Rails app on Heroku?
My application was running fine on Heroku for half year until I detached the postgres database from Heroku and it crashed.
Now it's displaying this error that I can't seem to figure out for a week:
...
1
vote
1answer
24 views
How to partition Spark RDD when importing Postgres using JDBC?
I am importing a Postgres database into Spark. I know that I can partition on import, but that requires that I have a numeric column (I don't want to use the value column because it's all over the ...
0
votes
1answer
16 views
rails associations between two tables
I've got two tables in my Postgres database: categories and products.
I have a one to many relationship defined, one category can have many products.
After I've defined these in the two models in ...
0
votes
1answer
32 views
Remove characters from a string sql
I have a table containing some columns like this :
-------------------------------
id | Tags
-------------------------------
1 | ['tag1','tag2']
2 | []
3 | ['tag1','...
0
votes
0answers
5 views
Sequelize-Auto: Changing Postgres Table Schemas
My co-worker have created a complex database in Postgres, and I'm trying define all the models automatically using Sequelize and Sequelize-Auto.
This database have many schemas, but with Sequelize-...
0
votes
0answers
7 views
permission denied to set parameter“client_min_messages” to “notice” Error on adding a server in pgAdmin
I am very new to pgadmin. I am adding a postgres instance ruuning on Amazon redshift on pgAdmin. After entering all the details I am getting permission denied error.
permission denied to set parameter"...
0
votes
1answer
17 views
Query nested jsonb Postgres column
I have a metadata column of type jsonb.
I know how to check whether it contains a specific key:
obj = Model.create
obj.metadata = {"foo"=>"1", "bar"=>{"baz"=>{"qux"=>2}}}
Model.where("(...
0
votes
0answers
19 views
Datatable with PostgreSQL stored procedure
Is there any way to use this package with "PostgreSQL function" AKA stored procedure?
https://github.com/yajra/laravel-datatables
Note: I'm successfully able to use it without use of stored ...
0
votes
1answer
37 views
PERCENTILE_DISC() in PostgreSQL as a window function
We are porting our system from SQL Server to PostgreSQL. In that we calculate Median Daily Turnovers for all companies on all dates for past 3 months. Below is the simplified query for the same
...
