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
4 views
Postgres trigger interfering with 'on conflict do update set'
I've got a SQL insert that should increment the version column and update the time column on a primary key conflict.
insert into items (pk, time, version, name)
values (9999, now(), 1)
...
0
votes
0answers
6 views
Bulk MIgration from MongoDB to PostgreSQL using MoSql
Hi I need to migrate my MongoDB into PostgreSQL.I used MoSql tool for that purpose.I created a collection map as following and saved in test.yaml file.
test_db:
check_collection:
:columns:
...
0
votes
1answer
15 views
Django points to the wrong version of Postgres
I downgraded Postgres.app from 9.6 to 9.5 by moving the Postgres.app desktop app. I updated the database by doing
sudo /usr/libexec/locate.updatedb
And it looks like it is initiating database from ...
1
vote
0answers
18 views
How to import multiple column tab separated text file to a single column PostgreSQL table?
I have a 30 GB tab separated text file with 11 columns. I want to import it to a single column PostgreSQL table. How can I do this?
What should I put for delimiter variable in \copy command? Every ...
1
vote
1answer
17 views
How can I traverse a tree bottom-up to calculate a (weighted) average of node values in PostgreSQL?
The typical example for e.g. summing a whole tree in PostgreSQL is using WITH RECURSIVE (Common Table Expressions). However, these examples typically go from top to bottom, flatten the tree and ...
0
votes
0answers
8 views
Slow HQL queries with Hibernate cache enabled
Hi guys it is my first question here, so please be gentle :D
I do not know WHY and HOW can I execute my HQL query as fast as an SQL query. I mean it takes around 200 ms from PostGreSQL database and ~...
1
vote
1answer
12 views
Postgresql sessions from psql and psycopg2
I am running two terminal sessions, in the first one I've opened psql, and in the second one ipython with psycopg2 imported.
I'm connected to the same db in both sessions. When I update a table ...
0
votes
0answers
10 views
Differences between storing uploaded files in an array vs json in Rails Active Record (Postgresql)
I'm building a Ruby on Rails app with Carrierwave using Active Record and Postgres.
I wonder what are the differences (pros/cons) for storing multiple file uploads in a column as array vs json. What ...
0
votes
0answers
11 views
Unable to access Posgresql database from remote server but i'm able to access with pgAdmin client
db=QSqlDatabase::addDatabase("QPSQL","MainDB");
db.setHostName("xxx.xxx.xxx.xxx");// remote pc ip
db.setPort(5432);
db.setDatabaseName("Users");
db.setUserName("postgres");
db.setPassword("test");
if(...
0
votes
1answer
20 views
In postgresql how to change the value in jsonb type cloumn?
Now I want to use postgresql function to change the jsonb type cloumn's value,now I have a function achieve the type json
CREATE OR REPLACE FUNCTION "json_object_set_key"(
"json" json,
"...
1
vote
0answers
18 views
postgres pg_bulkload c filter function in c programming
I'm using postgressql. Reading the data from file and loading it using pg_bulkload- C filter. As per documentation, C filter is much faster than SQL filter.
I'm new to C. Gone through this ...
0
votes
1answer
22 views
How to downgrade Postgres from 9.6 to 9.5
I removed my 9.6 postgres.app (removing the application by dragging it to the trash) and downloaded postgres.app.
However, when I did
locate initdb
It still points to the wrong location:
/...
0
votes
1answer
19 views
Regex Queries without using Native SQL queries - JPA
My question is how to get values from the database (PostgreSQL) by regex, BUT not using native SQL queries.
The only way I found is to use Hibernate Criteria API:
criteria.add(Restrictions....
-1
votes
0answers
16 views
stored procedure in postgres loop
I have this database:
(each community has more than one collection , and each collection has more than one textvalue row in the metadatavalue table)
i want to make stored procedure in postgres to ...
-3
votes
0answers
15 views
Can anyone suggest best tool for oracle to postgresql migration
Can anyone suggest best tool for oracle to postgreSQL migration
0
votes
0answers
14 views
CUBA platform postgresql connection timed out
I'm trying to connect my cuba platform application in to a postgresql db. earlier it worked fine. but now it is throwing an exception that connection is timed out I make all the configurations right ...
0
votes
2answers
26 views
Calculate Percentage in postgresql
I have a query which returns the results
time t_count cumulative_t_count zone hour
_____ _____ _________________ ____ ____
1 10 10 1 1
2 20 30 ...
2
votes
2answers
29 views
Select random from SQL table which evenly distributes across a column's different values
I have a table USERS which holds demographic information. For brevity, lets say one of the columns AGE can have one of five values (16, 17, 18, 19, 20).
I would like an SQL query which gives me say, ...
0
votes
0answers
11 views
Can't install psycopg2 successfully
Requirement already satisfied: psycopg2 in/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages
This is the error message I get when I run
pip install psycopg2
in my ...
2
votes
2answers
16 views
Unable to execute a command in Psql from the Terminal
I'm trying to execute several commands in postgresql which I pay via the command line, but it returns the result from only the 2nd one:
psql -d data1 -U user123 -c "select count(*) from table1; ...
0
votes
0answers
9 views
How to repeat same value for certain condition achive
I have raw data inside metric_name column : Inbound Utilization (%) and Inbound Throughput (Mbps). The query is set if Inbound Utilization > 30 , the result_util =1 while the Inbound Throughput ...
0
votes
0answers
11 views
Postgres: Adding rows to “sparse” table
I have a "sparse" table in Postgres, described in Pandas: Adding zero values where no rows exist (sparse) . I'd like to add explicit rows with zero, just as described in http://stackoverflow.com/a/...
0
votes
1answer
17 views
How to Decode This ByteA Value to String Format?
I have an array of PHP objects that has been serialized and stored in a mySQL Blob field. I converted the mySQL database to postgreSQL, and in postgres that field is in ByteA format. I'm reading the ...
1
vote
1answer
19 views
org.hibernate.HibernateException: More than one row with the given identifier was found
org.hibernate.HibernateException: More than one row with the given identifier was found: 578, for class: com.hibernate.query.performance.persistence.model.Store
The database does not have duplicate ...
0
votes
0answers
12 views
Resolve data returned from psql in graphql
I'm attempting to add graphQL to my existing application.
I currently back Express endpoints with psql db calls.
I aim to use psql to access my data and then use the results of these queries in my ...
0
votes
0answers
4 views
Retrieve records with duplicate values in a single colum with ActiveRecord and Postgres?
I would like to retrieve records on a table who have duplicate values on a single column. I am using Rails 4.2.7 with Postgres 9.6.1.
For instance, I might want to get all the users with the same ...
1
vote
2answers
16 views
Postgresql, Copy data to a new table from a foreign table
I'm trying to do a job that will copy data from a foreign table called "m_aduana" of the schema "nathalia" to my schema "publico" and my table "mae_aduana".
I need to do a query that copies all the ...
1
vote
0answers
9 views
PostgreSql Pass an array of strings as an argument to a function
I'm creating a function that gets two arrays, one of integers and one of strings then creates a materialized view, naming it with a concatenation of these parameters. In this way:
CREATE OR REPLACE ...
1
vote
0answers
11 views
Flask-migrate makes SQLite migration instead of PSQL
Hi I've exported in virtualenv database with command
export DATABASE_URL="postgresql://localhost/dbname"
But after running
python manage.py init
and
python manage.py db migrate
Result is:
...
0
votes
1answer
47 views
Why am I getting a syntax error at group by?
I have created the following query but when I run it I get a syntax error at the GROUP BY clause but when I remove it PostgreSQL gives me the error that I need to add t.bsn to a GROUP BY clause. How ...
2
votes
2answers
25 views
Using variables in a PL/pgSQL function
Postgres PL/pgSQL docs say:
For any SQL command that does not return rows, for example INSERT
without a RETURNING clause, you can execute the command within a
PL/pgSQL function just by writing ...
0
votes
2answers
11 views
Does case matter when 'auto' loading data from S3 into a Redshift table?
I am loading data from S3 into Redshift using the COPY command, the gzip flag and the 'auto' format, as per this documentation on loading from S3, this documentation for using the 'auto' format in ...
0
votes
0answers
21 views
How do I declare the precision value for a datetime array column in Ruby on Rails
I am using Rails 5 and PostgreSQL.
How do I store Datetimes in an PostgreSQL array with precision 6?
Given a migration with this content:
create_table :contracts do |t|
t.datetime :start_date, ...
-2
votes
1answer
31 views
PHP and no saving data in db [on hold]
i have problem, i want to intregate another code-site in an existing programm as submenü.
for html i us smarty and for the functions php. db is postgresql.
But when i fill the fields and click on ...
1
vote
1answer
17 views
Index creation taking forever on postgres
I was trying to create an index to an integer column using a btree index, but it was taking forever (more than 2 hours!).
The table has 17.514.879 lines. I didn't expect it to take that long.
After ...
0
votes
1answer
22 views
Postgres regexp replace not working
Im trying to create a regexp for this query:
SELECT gruppo
FROM righe_conto_ready
WHERE regexp_replace(gruppo,'(\[{1})|(\].*?\[)|(\].*$)','','g') = '[U6][U53]'
LIMIT 10
This is an example of 'gruppo'...
0
votes
0answers
21 views
Execute two simultaneous queries to the same table in POSTGRES
I'm quite new to SQL and postgres functions and I'm trying to do something for a presentation. I'm trying to execute two simultaneous queries to a postgresql table, A DELETE and an INSERT. I need the ...
0
votes
2answers
27 views
Generate nested the same structure table in Json script by using php and mysql
My problem with json and php: How to generate something like below part of json script table contain another Table with the same structure nested in it? I want to generate that from php code using ...
0
votes
1answer
18 views
Conditionally select a column in postgres SQL
say I have a table like this:
table messages
id | show_message | message
3 | false | "some message"
4 | true | "another message"
I want to only select the message column if ...
0
votes
2answers
42 views
SQL - Is there a way to hide a column from a select?
i made a query that shows the result i want (close enough)... but it's not perfect as should be.
I'm counting the number of registers per user..
The SQL:
SELECT us.name, _reg FROM tb_user AS us
...
0
votes
0answers
7 views
Read column comment with libpg api
I need to read the column comment using lippg api?
Is there a function that does it or should I select this information is some internal table? Which one?
Thanks,
Vinicius
0
votes
0answers
11 views
How to return multiple result sets when querying in POSTGRESQL
I'm new to postgresql. I'm looking to return 2 result sets, in my query, is this possible? My current query is using 'UNION ALL' where i'm getting dupplicate data.
WITH logged_in AS (
select ...
-1
votes
1answer
28 views
Equivalent of mysqli statement in PostgreSQL
While trying to remake a MySQL project into PostgreSQL by replacing all the MySQL commands with that of equivalent PostgreSQL commands, I'm stuck here.
$results = $mysqli->query("SELECT * FROM ...
2
votes
0answers
52 views
SQL Server's SET NOCOUNT ON Postgresql equivalent [on hold]
I've to help migrating a SQL Server 2008 database to a PostgreSQL 9.4.10 one. The original querys have SET NOCOUNT ON and SET NOCOUNT OFF several times, and I can't change this fact so ignoring the ...
1
vote
1answer
15 views
org.postgresql.util.PSQLException: Malformed function or procedure escape syntax at offset 1
Currently I am facing issue to fetch, postgresql function data via JDBC in DAO class. Connection is establishing properly.
CallableStatement mngStatement = conn
....
2
votes
0answers
23 views
Create criteria statement, get the result and the row count with one query
I'm trying to fetch results and also the row count inside a single query for pagination.
This is what I got so far:
public List<User> getUsers(int page, int size, boolean somecondition) {
...
0
votes
0answers
6 views
authentication with strongloop and postgresql
I'm trying to create authentication in my app, but I'm junior frontend developer without knowledge how work authentication on backend. So, I started with node.js-strongloop and node-postgresql as ...
0
votes
0answers
19 views
PostgreSQL: restoring table from sql using psql return 'ERROR: invalid input syntax for integer'
I trying to restore sql dump that looks like this:
COPY table_name (id, oauth_id, foo, bar) FROM stdin;
1 142 \N xxxxxxx
2 142 \N yyyyyyy
<dozen similar lines>
last line in this dump: \...
1
vote
2answers
10 views
PostgreSQL: Deleting records to keep the one with the latest timestamps
Let's say I have a table with a column of timestamps and a column of IDs (numeric). For each ID, I'm trying to delete all the rows except the one with the latest timestamp.
Here is the code I have so ...
1
vote
1answer
26 views
DELETE recursive PostgreSQL
I have a table upload_temp as it follows:
CREATE TABLE upload_temp (
codigo serial PRIMARY KEY NOT NULL,
codigo_upload_temp_pai INTEGER,
nome TEXT NOT NULL,
codigo_extensao INTEGER,
...
