Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
8 votes
3 answers
9k views

MySQL and PostgreSQL Licence issue

Our Company wants to use MySQL for one software product. This product is not open-source. Is it legal to use it freely ? If not, how about PostgreSQL ? Is this okay for our purpose ?
Josh Morrison's user avatar
16 votes
1 answer
4k views

PostgreSQL exit status when running a file

When running PostgreSQL with a single SQL command error codes are returned as expected: % psql -c "SELECT * FROM AWDASDASDASDAS" my_db ERROR: relation "awdasdasdasdas" does not exist LINE 1: SELECT *...
Adam Matan's user avatar
  • 11.6k
15 votes
1 answer
13k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
Sergey Kudriavtsev's user avatar
2 votes
2 answers
183 views

Database Agnostic Conditional

I'm writing a Drupal module and I want it to be agnostic between PostgreSQL and MySQL. I have a field that gets its value from a conditional, and I originally wrote it as an IF(). Then I found out ...
user1359's user avatar
  • 125
18 votes
1 answer
3k views

How does PostgreSQL handle Checkpoints in the middle of a WAL-enabled backup?

On a PostgreSQL v9.0 I've a WAL archiving system working. So WAL files a regulary archived (when 3 WAL are created or if a WAL is older than 15 minutes). Now I add a binary packup of the PG_DATA ...
regilero's user avatar
  • 243
27 votes
1 answer
159k views

How can I convert a string to a double precision in PostgreSQL? [closed]

How can I convert a string to a double precision in PostgreSQL ? I tried something like : update points set latitude2 = cast(latitude as double) ; where latitude is a string and latitude2 is a ...
yazz.com's user avatar
  • 509
12 votes
3 answers
8k views

How should I represent a latitude and longitude in Postgres without using PostGIS?

How should I represent a latitude and longitude in Postgres without using PostGIS? The system I am using does not allow SQL passthrough so I cannot use POSTGIS.
yazz.com's user avatar
  • 509
3 votes
2 answers
2k views

Effective backup and archive strategy for database and linked files

I am using Postgres to store a variety of application data for a webapp. Part of the application involves storing and retrieving user uploaded files. I am storing the files in the filesystem with ...
busyspin's user avatar
7 votes
3 answers
9k views

Postgresql row-level encryption

Looking over the docs, I didn't see any mention of row-level encryption. Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with ...
Incognito's user avatar
  • 173
5 votes
3 answers
4k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
Bruno's user avatar
  • 1,535
16 votes
1 answer
8k views

PostgreSQL: Disk space not released after TRUNCATE

I haveTRUNCATEd a huge (~120Gb) table called files: TRUNCATE files; VACUUM FULL files; The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space? UPDATE: The ...
Adam Matan's user avatar
  • 11.6k
30 votes
1 answer
91k views

How can I convert from Double Precision to Bigint with PostgreSQL?

I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that? I have tried with to_bigint(myvalue) but that function didn't exist.
Jonas's user avatar
  • 32.9k
64 votes
8 answers
44k views

Is it possible to quickly create/restore database snapshots with PostgreSQL?

First of all, I'm a developer, not a DBA or sysadmin; please be gentle :) I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
Zilk's user avatar
  • 1,111
3 votes
1 answer
897 views

Pgpool-II: sequences advanced by 1 after PITR recovery

I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4 In both cases I'm getting the same result: after online recovery on primary and target nodes all 'serial' values got "+1". Other nodes ...
irr's user avatar
  • 81
20 votes
4 answers
59k views

How to list all postgres tables in one particular schema

Using the psql command line tool, how do I list all postgres tables in one particular schema
user avatar
3 votes
2 answers
790 views

Degrading PostgreSQL 8.4 write performance

Load testing of postgresql 8.4 for OLTP application suitability resulted in unpleasant situation when throughput of the database significantly degraded over time from thousands of write transactions ...
OlegT's user avatar
  • 31
5 votes
1 answer
3k views

PostgreSQL on Ubuntu - Using conf files for recovery

I am using PostgreSQL 8.4 on Ubuntu LTS 10.04.02 server version. I use pg_standby to restore the data and for database recovery created a recovery.conf and placed it on /etc/postgresql/8.4/main where ...
Jamess's user avatar
  • 237
3 votes
2 answers
14k views

Postgresql - Recovery using recovery.conf

I am trying to recover a database using recovery.conf file for making point-in-time copy. On recovery.conf file, I added a recovery target time as below. recovery_target_time = '2011-06-16 04:10:00 ...
Jamess's user avatar
  • 237
5 votes
2 answers
3k views

Removing Archive Logs after PostgreSQL PITR online-recovery

I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2. Replication mode is "on" Load balance mode is "on" (between primary and standby1) I followed the official ...
irr's user avatar
  • 81
33 votes
3 answers
18k views

Source several files at once with psql

I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran ...
xenoterracide's user avatar
3 votes
1 answer
4k views

How do I install pgcrypto on Windows?

I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error: C:: Permission denied How do I solve ...
Jonas's user avatar
  • 32.9k
85 votes
10 answers
290k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don'...
Jonas's user avatar
  • 32.9k
142 votes
3 answers
202k views

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
Jonas's user avatar
  • 32.9k
3 votes
2 answers
8k views

What is the best way to automatically create a snapshot of the table at the certain time?

I have running and quite loaded PostgreSQL server. I need to take snapshots of the certain tables at the certain time (midnight). Those tables are pretty loaded (a lot of updates and inserts). What ...
Worker's user avatar
  • 519
6 votes
2 answers
738 views

Save postgres database from broken ubuntu 10.04 server

Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve). I can still access the filesystem. Is there a chance to transfer the database by only ...
mit's user avatar
  • 507
4 votes
2 answers
7k views

PostgreSQL: count datasets / lines containing NULL values

I'm looking for a easy function to get the number of datasets (of any table) containing NULL values. It's not important how many columns are involved. For example: CREATE TABLE tab1 ( id INTEGER ...
FloE's user avatar
  • 143
351 votes
3 answers
218k views

How can I time SQL-queries using psql?

I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?
Jonas's user avatar
  • 32.9k
5 votes
2 answers
400 views

PostgreSQL: Displaying inheritance relationship?

I want to know if a certain PostgreSQL table is a part of any inheritance relationship (i.e. is a parent or a child of other table(s)). Is there a query for that? Where is the inheritance data stored?...
Adam Matan's user avatar
  • 11.6k
25 votes
5 answers
73k views

PostgreSQL: Creation date of a table

I've recently finished a project, during which many DB tables were created. Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables. Is there a ...
Adam Matan's user avatar
  • 11.6k
11 votes
2 answers
19k views

How to insert an IP-address into an inet column in PostgreSQL?

I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
Jonas's user avatar
  • 32.9k
1 vote
2 answers
2k views

How fast should I expect to load data on a high performance computer?

I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice ...
David's user avatar
  • 1,185
2 votes
1 answer
2k views

Auto Increment Index Field For Create and Update

I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created ...
Kevin Sylvestre's user avatar
113 votes
11 answers
356k views

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: ...
Jonas's user avatar
  • 32.9k
391 votes
4 answers
911k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
Jonas's user avatar
  • 32.9k
2 votes
2 answers
2k views

Table-name.column-name in PostgreSQL?

I would like to use the following syntaxis in my PL/pgSQL code: INSERT INTO table (table.column1, table.column2) VALUES (1, 2); But somehow it doesn't work. Does anybody know if such a notation it ...
Worker's user avatar
  • 519
2 votes
1 answer
3k views

How can I alter the type of a money-column to decimal in PostgreSQL?

I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead. How can I do it? I tried with: alter table my_table alter column vat type decimal;...
Jonas's user avatar
  • 32.9k
55 votes
5 answers
143k views

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. ...
Jonas's user avatar
  • 32.9k
133 votes
2 answers
278k views

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
Jonas's user avatar
  • 32.9k
4 votes
3 answers
1k views

How much could sequential scan gain by GPU?

I am considering hiring someone to implement GPU processing in Postgres to speed up sequential scans on a database located on a ramdisk. How much do you believe I could gain using this approach? I ...
David's user avatar
  • 1,185
18 votes
1 answer
62k views

Pass an array or record to a function in PostgreSQL?

I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.
Worker's user avatar
  • 519
4 votes
2 answers
759 views

Plpgsql seems to be deleting and inserting instead of updating - Why?

I am using postgreSQL 7.4. I have a large table , call it table_a: key1 INT NOT NULL, key2 INT NOT NULL, data INT NOT NULL, itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, (timeofday())::...
youngthing's user avatar
56 votes
3 answers
191k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM mytable;...
Jonas's user avatar
  • 32.9k
18 votes
5 answers
24k views

Finding empty columns of a table in PostgreSQL

What query would return the name of the columns of a table where all rows are NULL?
Seb's user avatar
  • 181
7 votes
1 answer
2k views

Indexing to improve performance of range queries

I've got a relation in a Postgres 8.2 database similar to this: CREATE TABLE foo ( foo_id varchar(160) NOT NULL, bar_id varchar(160) NOT NULL, created bigint NOT NULL, PRIMARY KEY (...
Jonathan's user avatar
  • 173
4 votes
1 answer
3k views

pgAdmin Query Tool throws error when I try to run multiple statements

I'm very new to PostgreSql and still learning the ropes. I have a really long script for setting up a new copy of my database. It creates functions, runs them, does some inserts and a bunch of other ...
Fung's user avatar
  • 193
24 votes
2 answers
17k views

Trigger: move deleted rows to archive table

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis. I would like to have a table called restrictions_deleted, where ...
Adam Matan's user avatar
  • 11.6k
11 votes
5 answers
12k views

Tools to migrate from SQLite to PostgreSQL

I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the ...
David's user avatar
  • 1,185
2 votes
1 answer
3k views

master to multi-slave replication

I am looking at replication of the master database (PostgreSQL) and this is the configuration I have to setup master -- hot-standby (hs) | | --- read only slave (r1) | | --- read only slave (r2) ...
user1667's user avatar
3 votes
2 answers
11k views

Query PostgreSQL 9.0 table on foreign key value?

Noob-type question: Say I have two tables, candies and colors. The colors table holds just code/value pairs like 01 yellow, 02 blue, 03 green, etc. and is referenced in the candies table by code. ...
rec.thegeom's user avatar
3 votes
2 answers
2k views

How to hide metadata from Postgres users?

I need to provide views-only access to some subset of a database, for use by report-writing tools. It's easy enough to use permissions to prevent access to the data in the underlying tables, but I ...
user1608's user avatar