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
5 views
Why does inserting duplicate values trigger this error in Postgres on a Rails 5 application?
I am doing a bulk insertion of duplicate values (duplicate type and uuid) using the below Postgres query.
I see an error in my logs like ActiveRecord::StatementInvalid: PG::CardinalityViolation: ...
0
votes
1answer
11 views
Minimum PostgreSQL requirement to view all databases?
Long time MariaDB/MySQL DBA, two weeks into being a Postgres DBA and I'm stumped...
I am creating a script that scans a host and ennumerates the databases but I'm having difficulty nailing down the ...
0
votes
1answer
8 views
PostgreSQL - Have one serial type column but reset every day (unique combination with other date type column)
i need to have this table that will have a serial type column in my PostgreSQL database that will reset every day and will be unique combination with other date type column.
For example today i ...
0
votes
1answer
24 views
Syntax error on PostgreSQL create table without double quotes [duplicate]
Trying to create tables without double quoting the name is resulting in a syntax error, only quoting it works:
# CREATE TABLE user (
email TEXT,
first_name TEXT,
last_name TEXT,
password TEXT,
plan ...
0
votes
1answer
10 views
How to create CSV and save it in a variable for further processing in postgresql?
Facing kind of a mini challenge here today.
I want to create CSV string from a column in a table in postgresSQL using a SQL query inside a stored function and want to be able to store into another ...
-1
votes
0answers
20 views
Get max value of a column before it becomes zero, then sum all max values within the same year R
I am working with a plant (agricultural) growth model, simulating daily growth of plant (crop). This daily growth is recorded in a table, and I am using R to process the data stored in such table.
...
1
vote
0answers
9 views
Error raised at top level: Fluent.EntityError.noDatabase
I am trying to fix an error I have been getting recently when I run my Vapor project.
It builds fine, but when it runs, it crashes. Here is my log:
fatal error: Error raised at top level: Fluent....
0
votes
1answer
21 views
Group by any of multiple columns
Is it possible to create some sort of chain of grouping in Postgres? So let's say I have the following chart:
CREATE TABLE foo AS
SELECT row_number() OVER (), *
FROM ( VALUES
( 'X', 'D', 'G', 'P' ),...
0
votes
1answer
17 views
Postgresql won't return display query result from if statement
This is the query I ran, and successfully was able to execute. But postgresql only display "CREATE FUNCTION -- Query returned successfully in 265 msec."
-- For every order that has been received, ...
0
votes
0answers
9 views
Proper way to use Dropwizard dbi onDemand
According to the Dropwizard documebtation, DBI dao instances created with DBI.onDemand can open & close connections as needed. I have observed a situation where if you're running Dropwizard inside ...
1
vote
1answer
21 views
Dapper WHERE IN string statement with Postgres
I've seen Dapper WHERE IN statement with ODBC
But I'm not sure Dapper supports WHERE IN ("String1", "String2") syntax for Postgres. Is this supported? I tried digging through the code but I really ...
0
votes
0answers
21 views
Standard deviation of a set of dates
I have a table of transactions with columns id | client_id | datetime and I have calculated the mean of days between transactions to know how often this transactions are made by each client:
SELECT *,...
1
vote
2answers
15 views
Altering column type from int to bigint frees space?
I have table with an int type column, in a table of ~ 15 M rows.
OS windows 7 and C disk (where postgres is installed) shows that:
59 GB free of 238 GB
Then I changed this column type to bigint:
...
0
votes
1answer
14 views
Postgres server shutdown from simple select all on a small dataset from ActiveRecord query
I have the following endpoint in my rails app:
def index
if params.key?(:start)
if params[:start].to_i.to_s == params[:start] #unix epoche time format
p = Param
...
0
votes
0answers
21 views
Improve ranking times on multiple JSONB fields search in PostgreSQL
My search times are actually quite fast now but as soon as I start to rank them for the best results I hit a wall. The more hits I get, the slower it gets. For uncommon terms the search takes ~2ms and ...
1
vote
0answers
11 views
PostgeSQL in Amazon EC2
I try to set up Amazon EC2 with Vagrant and Chef-zero. Chef contains only one receipt for the installation of Nginx, Ruby, PostgreSQL, that is those utilities that are necessary for a Ruby on Rails ...
0
votes
1answer
16 views
django.db.utils.ProgrammingError: operator does not exist: character varying = integer
Guys i kindly need your help to figure out why am getting this error when i ran my script on a server. the server runs a postgres db i think it is something to do with the object type of the data it's ...
4
votes
1answer
28 views
How can I log `PREPARE` statements in PostgreSQL?
I'm using a database tool (Elixir's Ecto) which uses prepared statements for most PostgreSQL queries. I want to see exactly how and when it does that.
I found the correct Postgres config file, ...
-2
votes
0answers
32 views
Ruby Rails design table
I have a model named OrderingRequest I am using state_event_machine gem to record different states,I have states called order_place and order_reject. When I click order_place a dropdown with 3 values ...
1
vote
0answers
18 views
connect to PostgreSQL 9.5 database from Visual Studio 2015 via Npgsql
I installed NpgsqlDdexProvider-3.1.0, through NuGet added to the project packages Npgsql v.3.0.5, EntityFramework5.Npgsql v.3.0.5, EntityFramework v.5.0.0. I try to add connection to database via ...
1
vote
1answer
21 views
Index for ranking JSONB search results in PostgreSQL
I'm currently optimizing my search results on jsonb fields of PostgreSQL. I'm using Postgres 9.6. My ultimate goal is to search on multiple fields within my jsonb document and rank the results ...
-4
votes
1answer
27 views
How do I add some values to the SQL table without the insert into select statement ?
I want to add the values to the columns where name=Michael for example it seems there is no way to do that
0
votes
2answers
24 views
Postgresql Add WHERE clauses in an UPDATE request
I intend to write a SQL request UPDATE and I'd like to verify if table2.fld5 already contains 'Hello' in order to not write it several times, but didn't find a way to achieve this verification.
The ...
1
vote
1answer
29 views
How to select column only when it's a number? Postgresql
Hello there i'm using postgree and i searched a few codes but nothing is working for me:
select nick from tb_player where nick NOT LIKE '%[^0-9]%'
But it returns pretty much everything with or ...
0
votes
0answers
21 views
Postgresql multiple conditional where clause with different limits
I need to fetch data from a table with multiple if conditions. I have 3 tables: Questions, levels, difficulty_levels using which I need to fetch the data.
EG:
Select *
from questions q
join level l ...
-2
votes
2answers
45 views
SQL QUERY for insert statement in select query
Output format :
INSERT INTO Table (columns) VALUES (records of this table)
Example:
INSERT INTO Table1 (columns of Table1 ) VALUES (records of this table1)
INSERT INTO Table2 (columns of Table2) ...
0
votes
0answers
21 views
Postgresql how to store PHP microtime (true)
I am trying to store PHP microtime to postgres DB, i tried to use timestamp datatype however i got an error.
this is how i am trying to store it to BD (Laravel framework)
<?php
$...
1
vote
1answer
21 views
Can I make reports on Report Designer (MS Visual Studio 2015) for PostgreSQL 9.5?
I use Visual Studio 2015 + PostgreSQL 9.5. Can I make reports on MS Report Designer for PostgreSQL? Where can I find documentation to do it?
1
vote
1answer
37 views
Different ORDER BY behavior on localhost and production
I found a problem with different ORDER BY behavior on my localhost and production DB.
I have on localhost PostgreSQL 9.3.5.1 and on production machine 9.5.5. Both are installed on Linux based systems ...
0
votes
1answer
17 views
Npgsql + EF doesn't close readers
I try to port an ASP.Net MVC5 project to PostgreSQL using Npgsql. But it has an error in this code:
public ActionResult Index()
{
var roles = Roles.GetAll();
foreach (var role in roles)
{...
0
votes
2answers
31 views
Will huge table entries slow down query performance?
Let's say I have a table persons that looks like this:
|id | name | age |
|---|------|-----|
|1 |foo |21 |
|2 |bar |22 |
|3 |baz |23 |
and add a new column history where I store a big ...
0
votes
0answers
14 views
Postgres ODBC compilation on Windows without openssl dependency
How to do Postgres ODBC driver compilation on Windows(postgres 9.5.4) without openssl dependency, tried using https://odbc.postgresql.org/docs/win32-compilation.html document,but unable to connect to ...
1
vote
1answer
26 views
Postgresql stored procedure return select result set
In Microsoft SQL server I could do something like this :
create procedure my_procedure @argument1 int, @argument2 int
as
select *
from my_table
where ID > @argument1 and ID < @...
-3
votes
2answers
34 views
Search a string with spaces similar to full string in JAVA
I have an issue which I am facing I would really appreciate your help.
I am using java and connecting it to postgres DB. I tried writing a query with LIKE and it works, but what I am looking is regex ...
2
votes
0answers
21 views
Postgresql full text search for similar words
Trying to implement postgresql full text search example by following these steps;
I have created a table:
CREATE TABLE posts
(
id serial primary key,
content varchar(255),
tags varchar(255),
title ...
0
votes
1answer
21 views
How to verify Postgres backup has been successful?
What's the best way to verify that the backup and restore of a Postgres database has succeeded without issues?
I am using the following to backup/restore:
sudo -u postgres pg_dump -Ft db > ...
0
votes
0answers
12 views
pytest-django run with migrations ignores database triggers
My Django app depends on a database with some triggers setup. I use this part of the documentation to set up the triggers in the test database for the pytest runner.
@pytest.fixture(scope='session')
...
0
votes
1answer
16 views
Postgresql SQL View: Complex Structure
id Event No BrandId
1 10 B2
2 10 B8
3 11 B1
4 13 B9
5 13 B3
I want the ...
0
votes
0answers
22 views
Spring framework desrialize failed
What i am doing is serializing a class and store it in database, then reading it and deserialize it using SpringFramework, and getting an exception on deserialization bytes into class.
Exception:
...
-1
votes
2answers
31 views
How to insert JSON into Postgres via java?
I have created a table books in PostgreSql.
CREATE TABLE books ( id integer, data json );
My Json:
{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }
How can i ...
-1
votes
0answers
4 views
Klipfolio not showing postgres uuid data type
I am working on dashboard, using postgre database as a datasource and Klipfolio as visualization tool. The problem is that Klipfolio is not showing uuid data type from my posgres database. What can be ...
0
votes
0answers
12 views
cygwin error: conflicting types for ‘select’
I want to Install RecDB on Windows & I am trying to install it using Cygwin.
Facing many problems from 2 days.
Currently i am getting this error while compiling :
/usr/include/w32api/winsock2.h:...
0
votes
1answer
10 views
change PostgreSQL data directory
I installed postgres 9.5 quite a while ago, and since a few months ago have upgraded to 9.6 and used that. Recently I shut down my computer and restarted postgres, but when I connect to the 9.6 ...
2
votes
1answer
28 views
extract the second found matching substring using Postgresql
I use the bellow query to extract a value from a column that stores JSON objects.
The issue with it, it does only pull the first value matching to the regex inside SUBSTRING which is -$4,000....
1
vote
0answers
20 views
Design of multiple tables “e.g. Post and Comment” ordered by one column “date” in both tables
I use Postgres 9.6 and Django 1.10, but this may be more generic than the software packages I am using. However due to Django ORM limitations or/and my understanding, it may be dependent on these ...
0
votes
2answers
22 views
VBS opens a CMD but can't read next commands to go on other directory
I am trying to create vbs file so that it will be executed by the MS Scheduler in order to backup my Postgresql database because i can't seem to find a way in using agent since i installed it but it ...
0
votes
1answer
19 views
Get the latest messages between many users
In my app, users
CREATE TABLE users (
id bigserial PRIMARY KEY,
username varchar(50) NOT NULL
);
can send messages
CREATE TABLE messages (
id bigserial PRIMARY KEY,
from_id bigint ...
0
votes
0answers
15 views
Connecting to Host Postgresql From inside a Container
I have a python application running inside a container.
The application needs to connect to PostgreSQL that is running in host (not in any other container ).
I tried various approaches spread around ...
0
votes
1answer
12 views
Load database into PostgreSQL from text file
ALL,
I have a text file which contains multiple SQL statements, like:
CREATE TABLE a();
CREATE TABLE b();
INSERT INTO a() VALUES();
INSERT INTO b() VALUES();
This file is generated from the SQLite ...
1
vote
1answer
18 views
Postgres: “AT TIME ZONE 'localtime'”== “AT TIME ZONE 'utc'”?
I'm struggling to understand how "AT TIME ZONE 'localtime'" exactly work? By playing with it, I found out that it acts exactly as "AT TIME ZONE 'UTC'"... But why? Is "localtime" a synonym of "UTC" in ...
