CODEWITHSUNDEEP
postgresql
Trương Minh Nhựt
Trương Minh Nhựt

Reputation: 51

create tables from sql file on schema PostgreSQL

I try to create tables from file on my schema, but i receive error message. Users table created on public schema was successfully, but other table i created on test1 schema was wrong. I don't know why, help me please. Thank for advance.

CREATE SCHEMA test1 
    --Create sequence
CREATE SEQUENCE test1.table_id_seq START 1;


--Create function to auto generate ID
CREATE OR REPLACE FUNCTION test1.next_id(OUT result bigint) AS $$
DECLARE
    our_epoch bigint := 1314220021721;
    seq_id bigint;
    now_millis bigint;
    shard_id int := 1;
BEGIN
    SELECT nextval('test1.table_id_seq') % 1024 INTO seq_id;

    SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
    result := (now_millis - our_epoch) << 23;
    result := result | (shard_id << 10);
    result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;

--Talbe ----users----
CREATE TABLE users
(
    id bigserial NOT NULL PRIMARY KEY,
    username varchar(30) NOT NULL UNIQUE,
    password varchar NOT NULL,
    first_name varchar(10),
    last_name varchar(10),
    profile_picture varchar,
    create_time timestamp (0) without time zone
);


--Table ----photos----
CREATE TABLE test.photos
(
    id bigint NOT NULL PRIMARY KEY DEFAULT test1.next_id(),
    caption text,
    low_resolution varchar,
    hight_resolution varchar,
    thumnail varchar,
    user_id bigint NOT NULL REFERENCES users(id),
    create_time timestamp (0) without time zone

--Table ----comments----
CREATE TABLE test1.comments
(
    id bigint NOT NULL PRIMARY KEY DEFAULT test1.next_id(),
    create_time timestamp (0) without time zone,
    text text,
    user_id bigint REFERENCES users(id),
    photo_id bigint REFERENCES test1.photos(id)
);

--Table ----likes----
CREATE TABLE test1.likes
(
    photo_id bigint REFERENCES test1.photos(id),
    user_id bigint REFERENCES users(id),
);

--Table ----follows----
CREATE TABLE test1.follows
(
    user_id bigint NOT NULL REFERENCES users(id),
    target_id bigint NOT NULL REFERENCES users(id),
);

CREATE TABLE teset1.feeds
(
    user_id bigint NOT NULL REFERENCES users(id),
    photo_id bigint NOT NULL REFERENCES test1.photos(id),
    create_time timestamp (0) without time zone,
);

Upvotes: 1

Views: 2247

Answers (1)

Josh Kupershmidt
Josh Kupershmidt

Reputation: 2710

Well, it would have been helpful for you to have shown us what errors you were getting and what you tried to do to fix them. But here are some obvious problems with the SQL you posted:

  1. Missing semicolon after "CREATE SCHEMA test1 "
  2. Missing closing paren and semicolon at the end of CREATE TABLE test.photos ...
  3. Dump neglects to create the "test" schema where the "photos" table wants to go.
  4. Several foreign key references of test1.photos.id, but "photos" was created in the "test" schema, not "test1".
  5. Extraneous trailing commas after the last column definitions for several tables
  6. Typo: "teset1.feeds", should be "test.feeds"

TL;DR Whoever created this dump file wasn't paying very close attention.

After fixing all the problems above, I managed to load your SQL.

Upvotes: 3

Related Questions