PostgreSQL create table if not exists

In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL?

Comment character/characters in postgres / postgresql / psql?

What's the character for comments in postgres? SELECT * FROM my_table # pound sign produces a syntax error Thank you cababunga, the following appears to work: SELECT * FROM my_table --...

What is the most recommended way to store time in PostgreSQL using Java?

I'm storing two dates in the PostgreSQL database. First, is the data of visit of a webpage, and the second date is the date of last modification of the webpage(this is get as a long). I have some...

Replicating data between Postgres DBs

I have a Postgres DB that is used by a chat application. The chat system often truncates these tables when they grow to big but I need this data copied to another Postgres database. I will not be...

PSQLException: current transaction is aborted, commands ignored until end of transaction block

I am seeing the following (truncated) stacktrace in the server.log file of JBoss 7.1.1 Final: Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored...

django test app error - Got an error creating the test database: permission denied to create database

When I try to test any app with command (I noticed it when I tried to deploy myproject using fabric, which uses this command): python manage.py test appname I get this error: Creating test...

Postgres return a default value when a column doesn't exist

I have a query where I essentially want a fallback value if a certain column is missing. I was wondering if I can handle this purely in my query (rather than probing first and sending a seperate...

How does PostgreSQL perform writes so much faster than SQLite?

I did a simple integer update performance test. SQLite did only 15 updates per second while PostgreSQL did 1500 updates per second. The number with the SQLite case seems to be normal. The FAQ in...

Go postgresql LIKE query

I'm working with Go and PostgreSQL (pq driver), I have the following query SELECT p.id, p.name, p.description, p.price, p.image, p.rate FROM products AS p WHERE LOWER(p.name) LIKE %$1% ORDER BY...

Return rows of a table that actually changed in an UPDATE

Using Postgres, I can perform an update statement and return the rows affected by the commend. UPDATE accounts SET status = merge_accounts.status, field1 = merge_accounts.field1, field2 =...

Fetching rows from a cursor PostgreSQL

I have the cursor code: BEGIN; DECLARE cliente_cursor CURSOR FOR SELECT * FROM cliente; I want to read all the content from the Table 'cliente': With the use of a cursor. I have the code...

Run a query with a LIMIT/OFFSET and also get the total number of rows

For pagination purposes, I need a run a query with the LIMIT and OFFSET clauses. But I also need a count of the number of rows that would be returned by that query without the LIMIT and OFFSET...

Ambiguous function arguments

The example function: CREATE OR REPLACE FUNCTION update_a_table(id int, name text) RETURNS void AS $$ BEGIN UPDATE a_table SET name = name WHERE id = id; END; $$ LANGUAGE plpgsql; cause this...

Django can't drop database: psycopg2.OperationalError: cannot drop the currently open database

Whenever I try to run my Django tests via manage.py, the tests run fine however at the end when Django is destroying the database the following error occurs: Destroying test database for alias...

Run update inside knex migration

I want to add a column to a table, then perform some work to populate the column as part of the migration. Populating the column requires some manipulation in code. Consider two tables: Users [...

Sequelize.js: Query for not in array ($ne for items in array)

I am looking to pull items from a postgres data base with Sequelize, but only return items that have an id that does not equal any items in a given array. In the Sequelize documentation, there are...

How to make sure the query run with read-only action on MySQL/PostgreSQL database?

Precondition: The database is running at somewhere without of my control, but I have the authorization account to connect the database. I need to make sure all queries running through my...

Postgrex.Error ERROR 42501 insufficient_privilege to create extension citext

I am trying to create a migration. this is the output MIX_ENV=prod DATABASE_URL="URL" mix ecto.migrate [info] execute "CREATE EXTENSION citext;" ** (Postgrex.Error) ERROR 42501...

Getting Exit Code 1 when running restore DB from a .tar file in pgAdmin4 postgresql 10.5

I'm taking an intro to SQL course and I can't get my very first database loaded. I have a file dvdrental.tar, I create a new database dvdrental > right click > Restore > Select File > Pick "Data"...

GIN Index has O(N^2) complexity for array overlap operator?

I ran into an issue with using the && array operator on a GIN index of mine. Basically I have a query that looks like this: SELECT * FROM example WHERE keys && ARRAY[1,2,3,...] This works fine...

GoLang service can't hit Postgres using docker compose

I have an HTML service written in Go. It uses Postgres, but when bringing it all together using docker compose I get " dial tcp 0.0.0.0:5432: connect: connection refused" Works when building...

Column reference is ambiguous in PostgreSQL function

I've created a function like this. create or replace function pesquisar_imoveis_residenciais_venda() returns table(preco decimal) as $$ begin select preco from casa_venda; end; $$ language...

Liquibase: How to change default postgresql schema

By default liquibase create all objects on the public schema. However I created another schema and I want liquibase to use it. I changed database URL by appending...

Postgres converting double double precision to text creates '1.50000000000'

Working with postgres and I need to convert total minutes into the format of 1h 1m. I'm doing this via the following code replace( (119 / 60 + (119 % 60) / 100.0)::text, '.', 'h ' )...

NodeJs using pg client - Jest has detected the following open handle potentially keeping Jest from exiting - TCPWRAP

In some integration tests in my Node.js, I use pg to perform some cleanup of the Postgres tests database after the test have run. I call this in the afterAll(): afterAll(() => { const { Pool }...

PostgreSQL to add type Nvarchar?

Is there any suggestion for PostgreSQL to add type Nvarchar? This is the error that I am getting: Npgsql.PostgresException (0x80004005): 42704: type "nvarchar" does not exist at...

Symfony 5 Fast Track: Database access error

I'm trying to go through the aforementioned tutorial, but I get stuck on the stage between Step 7 and 9 - when the database goes into action. On local server I get An exception occurred in...

postgresql error after installing : connection to server failed

I've installed postgresql on Ubuntu 21.04. when I want to use it with psql command I got this error: psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such...

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres" (Ubuntu)

When I try to open psql with this command: psql -U postgres I get this error: psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication...

Counting in Many-To-Many Relations Sequelize

I am trying to count number of Followers and Followings of a user. as followingCount and followerCount User Model User.init( { id: { allowNull: false, primaryKey: true, ...