Tagged Questions
0
votes
3answers
24 views
How to store different date precisions in one PostgreSQL column
I have different date types for some single events.
They can built up like this:
YYYY-MM-DD
or
YYYY-MM
or
YYYY
I want to store the data in one column. How do I have to set up my column ...
0
votes
1answer
13 views
Fixing error “function array_length(bigint[]) does not exist”
I have a recursive query, encapsulated within a function, that returns a hierarchical survey "structure":
CREATE OR REPLACE FUNCTION get_survey_results(IN v_survey_id integer DEFAULT 1, IN ...
0
votes
3answers
45 views
Transaction roll back not working in Postgresql
I am working on PostgreSQL 9.1.4 .
I am inserting the data into 2 tables its working nicely.
I wish to apply transaction for my tables both table exist in
same DB. If my 2nd table going fail on ...
-1
votes
1answer
22 views
Difference Between Two PostgresSQL Queries
I saw following queries in my code and want to make sure I understand them correctly.
Table Name - TABLENAME
Columns - id (varchar2), data (bytes)
UPDATE TABLENAME SET data= data || 'bytes' WHERE ...
0
votes
1answer
10 views
ERROR: type “dblink_pkey_results” already exists when installing pgWatch
I copied pgwatch into www root.
Then I installed extensions with the following command:
apt-get install postgresql-contrib-9.1
Then server restarted. Then I went to pgwatch database with the ...
1
vote
1answer
26 views
Generate series of dates - using date type as input
Documentation for generate_series says that argument can be int or bigint for generate_series(start, stop) and generate_series(start, stop, step) cases and timestamp or timestamp with time zone for ...
0
votes
2answers
36 views
Postgresql server not asking for password for remote connections
I found my posgresql database server is not asking password for user postgres when remote connecting through pgadmin. I mean this is when I connect to remote database server from my local computer ...
0
votes
0answers
19 views
postgresSQL: copy to xml without \n
I want to write results from queries into xml files.
As I read in the documentation postgres supports only text, csv and binaries. So I use the standard text for my file.
The problem is that postgres ...
3
votes
1answer
28 views
How to search for different character sets in postgresql?
I want to search a table in a postgres DB which contains both Arabic and English text. For example:
id | content
-----------------
1 | دجاج
2 | chicken
3 | دجاج chicken
The result would get ...
0
votes
1answer
26 views
Can I control postgresql to use a dedicated index? [duplicate]
As we known, there'is a command called "force index" within mysql database. So this can force mysql to use an index whether optimizer like or not. And my confusion is that how to do the same thing on ...
0
votes
2answers
48 views
Split string after every nth character
Is there a way how to split string after every nth character in PostgreSQL? I thought regexp_split_to_array can be used to do this:
select unnest(regexp_split_to_array('abcdefgh', E'...regexp ...
0
votes
1answer
27 views
Postgres Subquery while inserting data
I am trying to run a query in which a particular column value is a foreign key.
INSERT INTO tbl_test(group_id, test_name, test_code) VALUES (SELECT group_id FROM tbl_group
where group_code = '6868' ...
0
votes
2answers
26 views
a simple Affected Row with Delete in Posgresql
I did this code in order to verify the "delete" sentence, but I want to improve this way because I am sure that you know a better way.
Would you say to me how to make improve this code?
Thanks in ...
0
votes
1answer
45 views
sql filter rows based on condition parent to child hierarchy
Erwin, My bad with the correct example and data. Please find below the updated query with all data as required.
We have master table with all the relationships between the asset IDs as shown below. ...
0
votes
1answer
57 views
Self-referential class: How to get jerarchical structure of employees in a company?
I have a class defined in Python called Employees. Its attribute id_supervisor is the id of the employee who is in charge of him.
One employee can be in charge of (supervise) many other employees.
...
1
vote
1answer
43 views
Inserting File into Postgres database
I am performing Data migration from oracle database to postgres 9.0 database.All fields are successfully transferred except when i am trying to move a column of file(Blob object in Oracle) into the ...
3
votes
1answer
49 views
How to truncate column in order to create indexes?
I have the following table in postgresql:
database=# \d dic
Table "public.dic"
Column | Type | Modifiers
-------------+-------------------------+-----------
...
1
vote
1answer
38 views
How to split the array and store into respected columns of table in Postgresql DataBase
I am inserting the array of elements {idno,age,salary} as {1,10000,30}
in PostgreSQL database and I need to split and store into the respected columns of the table.
How to store the split array ...
0
votes
1answer
34 views
Combine sql rows into additional columns
I have a database table 'stock_usage' that looks something like this.
id | item_id | store_id | quantity | timestamp
-------------------------------------------------------------
...
0
votes
0answers
19 views
postgres synchronization with foreign key constraints under ubuntu
i have a local Postgres Server in 3 local branches, i need to synchronize the three to a central database i tried the database replication in the PostgreSQL Documentation it worked for a Master-Master ...
8
votes
4answers
102 views
IN subquery's WHERE condition affects main query - Is this a feature or a bug?
Assume the two tables:
Table A: A1, A2, A_Other
Table B: B1, B2, B_Other
In the following examples, is something is a condition checked against a fixed value, e.g. = 'ABC' or < 45.
I wrote a ...
0
votes
1answer
28 views
Select in LOOP error in PostgreSQL
Trying to start work following function, with SELECT in LOOP statement for other table records:
CREATE OR REPLACE FUNCTION parts."clearExpiredReserves"()
RETURNS void AS
$BODY$DECLARE
...
0
votes
2answers
61 views
Sync/ replicate more than two databases in postgresql?
I have 5 users which uses 5 different servers(using openerp), each one uses the same database copy. Whenever the user enters data to his database, then it should sync to the 6th server's database an ...
0
votes
1answer
31 views
Add User To User Table In Terminal Rails 4 Postgresql
I am using Rails 4 and Devise 3 and Postgresql for my database. How can I enter a user through the terminal into my user table?
rails db
is how I enter into the database.
0
votes
0answers
25 views
Is there something like show create database in postgres? [duplicate]
I want to use something like Mysql command to show me the structure of postgres tables in a database, Is there a way for it? I main goal is to create an exact copy of the table in another database ...
0
votes
1answer
45 views
django add model field manualy to postgres database
I can't use south and I don't have pgAdmin but I need to add new field to the model at the end of the model. I need to be:
new_field = models.CharField(max_length=200, blank=True, null=True)
I have ...
1
vote
1answer
354 views
pgadmin - an error has occurred, column “datconfig” does not exist
I've a ubuntu server 12.04 running postgresql version 9.1.10, and I've downloaded pgAdmin III 1.8.4 for windows (7, x64).
When I try to estabish a connection I'm getting this message:
an error has ...
0
votes
1answer
28 views
Postgres initialization and permissions [closed]
I'm a complete noob in Databases let alone postgreSQL.
Here is some background information: I recently deleted my data files in order to restart my postgres cluster.
I'm having a bunch of issues ...
2
votes
1answer
53 views
Create copy of PostgreSQL internal C function and load it as user defined function
I would like to create modified version of C function int2_avg_accum located in src/backend/utils/adt/numeric.c. I thought I can (as a start) just compile numeric.h and load int2_avg_accum as user ...
2
votes
1answer
69 views
How can I stop a postgres table from having duplicate record and instead writing the duplicate records in another table
How can I stop a Postgres table from having duplicate record and instead writing the duplicate records in another table. I want to copy data from “table1” to “table2” and if there is any duplicate ...
3
votes
2answers
50 views
ORDER BY lower case of output column
I'm able to run this Postgres query without any issue:
select
(select product_types.name from product_types
where product_types.id = products.product_type_id) AS product_type_name
from products
...
0
votes
1answer
27 views
How to change a table constraint to deferrable in postgresql?
I have this simple contraint set on my sales table
CONSTRAINT sales_stamping_id_key UNIQUE (company_id, stamping_id, invoice_number, billed)
and i can't seem to find a way to change this ...
1
vote
2answers
78 views
Variables for identifiers inside IF EXISTS in a plpgsql function
CREATE OR REPLACE FUNCTION drop_now()
RETURNS void AS
$BODY$
DECLARE
row record;
BEGIN
RAISE INFO 'in';
FOR row IN
select relname from pg_stat_user_tables
WHERE ...
0
votes
1answer
34 views
Correct index's on simple table
I have a table in PostgreSQL 9.1:
_id | integer | not null default nextval('"01f9073e-e6b8-46bf-882f-9a4cd0a69a66__id_seq"'::regclass)
_full_text | tsvector |
tlRecordID | text |
...
0
votes
1answer
45 views
Dump a table without sequence table in postgres
I have a table named test with id column. And id column has updated by sequence called "test_id_seq". This test table was created by the user "A" and another user "B" have the read permission. While ...
1
vote
1answer
37 views
how to create dump for specific schema in postgres DB
I have a Postgres database "rafiu" with many schemas namely test1, test2, test3. In this I want to dump the test2 schema and its data. I tried with the following query
pg_dump -U postgres -n test2 ...
1
vote
1answer
51 views
Convert escaped Unicode character back to actual character in PostgreSQL
Is there a way how I can convert the following string back to the human-readable value? I have some external data where all non-ascii characters are escaped.
Example strings:
16 ...
1
vote
2answers
92 views
Why does Drop Index require commit?
In my multi-threaded program, one thread drops indexes on a table (this happens first), and other threads insert records in the same table. It so happened that when dropping index is attempted, the ...
0
votes
1answer
26 views
Is there a simple command to pack all database ids to have no gaps
I have my Postgre database which I used hibernate sequence to generate ids. But the sequence was common for all tables. But now my plan is to have a separate sequence for each table. Bow the what I ...
0
votes
1answer
41 views
Combine similar data from multiple schemas
I have a table in a common schema which contain details about all enterprises of the application like id, name, logo, web_site_address, schema_name etc. Then there are schemas for each enterprise ...
0
votes
1answer
31 views
Select sum of an array column in PostgresSQL
If I have the following table:
Table "users"
Column | Type | Modifiers
---------------+------------------+-----------
id | integer | not null default ...
1
vote
1answer
37 views
Obfuscating data in postgres
I'd like to obfuscate data in specific columns in postgres 9.1.
For instance, I want to give all the people a 'random' first and last name.
I can generate a pool of names to use:
select name_first ...
0
votes
1answer
36 views
PostgreSQL service starting on Windows without Windows knowing
My server has an application that has a service dependency on PostgreSQL 9.1 running. For some reason when I restart the server the postgres.exe tasks start in the Task manager (all 8 of them for the ...
0
votes
1answer
51 views
Postgres/Postgis - Querying all values in clipped raster
I'm currently using Postgres 9.1
My goal is to clip a PostGIS raster with a polygon. Then I would like either an postgres array or delimited set of values for each of the raster pixels that are ...
0
votes
1answer
22 views
Get the list of allowed hosts in host-based authentication
I am aware that I have to add the IP addresses of remote hosts in pg_hba.conf file and restart the PostgreSQL server for changes to take effect.
But I would like to get a list of hosts currently ...
0
votes
1answer
49 views
UPDATE in PostgreSQL 9.1 seems to misuse placeholder types
Given the following schema (as an example)
CREATE TABLE "test" (
"id" int,
"title" varchar
);
in NodeJS, I try to do an UPDATE with the following
client.query(
'WITH new_vals ...
0
votes
3answers
393 views
how to set up postgres database for local rails project
I recently got a new machine and would now like to work on my projects from github. I'm curious as to how to properly set up the postgres database on my local machine. I have postgresql, pgadmin3 and ...
0
votes
1answer
59 views
Server library could not be loaded and the dynamic link library LIBPQ.dll
Please i have a serious problem here. When i want to debug my application in visual studio i have this message box error : "The ordinal 133 could not be located in the dynamic link library ...
0
votes
3answers
59 views
Counting Number of Users Whose Average is Greater than X in Postgres
I am trying to find out the number of users who have scored an average of 80 or higher. I am using Having in my query but it is not returning the count of number of rows.
The Schema looks like:
...
0
votes
2answers
143 views
Accessing PostgreSQL 9.1 Temporary Tables from PHP / AJAX
DATABASE
I have a normalized Postgres 9.1 database and in it I have written some functions. One function in particular "fn_SuperQuery"(param,param, ...)" returns SET OF RECORD and should be thought ...
