You need to install the package postgresql-contrib-9.1 in your system first. (Adapt to your version number! Here is the currently available list of packages.) That’s the case under Debian, Ubuntu & friends anyway. Using a system user with the necessary privileges: apt-get install postgresql-contrib-9.1 If your currently logged in user does not have the necessary … Read more
DO command vs. PL/pgSQL function A DO command does not return rows. You can send NOTICES or RAISE other messages (using default LANGUAGE plpgsql), or you can write to a (temporary) table and later SELECT from it to get around this. But really, create a function instead, where you can define a return type with … Read more
The question seems to imply that pg_restore is executing these SQL commands and you wouldn’t want to see them in the output. But outputting them is what it’s only supposed to do. pg_restore has two modes of operation, with or without connecting to a database. When it’s called without a database (-d option) as shown … Read more
You can probably do it from the command line without using files: pg_dump -U user –schema=”fromschema” database | sed ‘s/fromschmea/toschema/g’ | psql -U user -d database Note that this searches and replaces all occurrences of the string that is your schema name, so it may affect your data.
You could do something like the following, however it is better to include it in the create table as a_horse_with_no_name suggests. if NOT exists (select constraint_name from information_schema.table_constraints where table_name=”table_name” and constraint_type=”PRIMARY KEY”) then ALTER TABLE table_name ADD PRIMARY KEY (id); end if;
It’s slow because it needs to locate the top offset rows and scan the next 100. No amounts of optimization will change that when you’re dealing with huge offsets. This is because your query literally instruct the DB engine to visit lots of rows by using offset 3900000 — that’s 3.9M rows. Options to speed … Read more
The advantages you have for choosing a character datatype as a primary key field is that you may choose what data it can show. As an example, you could have the email address as the key field for a users table. The eliminates the need for an additional column. Another advantage is if you have … Read more
I was trying the same as you, and I got answer in stackoverflow only, It is possible to create ENUM with default value. Here is what I got for you. CREATE TYPE status AS ENUM (‘Notconfirmed’,’Coming’, ‘Notcoming’, ‘Maycome’); CREATE TABLE t ( id serial, s status default ‘Notconfirmed’ — <==== default value ); INSERT INTO … Read more
Normally Sequelize.js adapt itself with the database. So the autoincrement attribute corresponds to a serial type in PostgreSQL. I already worked with Sequelize and what I remember is that you don’t need to specify an id as a primary key. Sequelize will do it for you. Try to not add an id attribute and see … Read more
In your migrations you can do something like: $table->json(‘field_name’); And in your model you add the field to the $casts property to instruct Eloquent to deserialize it from JSON into a PHP array: class SomeModel extends Model { protected $casts = [ ‘field_name’ => ‘array’ ]; } Source: https://laravel.com/docs/5.1/eloquent-mutators#attribute-casting Note: This is also relevant answer … Read more